详解：MaxScale中间件部署数据库读写分离

共计 5563 个字符，预计需要花费 14 分钟才能阅读完成。

操作系统:CentOS Linux release 7.3.1611 (Core)

数据库:MariaDB-10.2.6-linux-glibc_214-x86_64

MaxScale 服务器:10.200.10.55

主服务器:172.16.8.56

从服务器:172.16.8.57

从服务器:172.16.8.58

1.maxscale 的安装方式有很多，例如源码安装、rpm、二进制构建等，我选择二进制进行安装。

根据场景需要下载相对应的版本，下载地址；https://mariadb.com/downloads/maxscale

[root@localhost ~]# groupadd maxscale
    [root@localhost ~]# useradd -g maxscale maxscale
    [root@localhost ~]# cd /usr/local
    [root@localhost local]# wget https://downloads.mariadb.com/MaxScale/2.1.3/centos/7server/x86_64/maxscale-2.1.3.centos.7.tar.gz
    [root@localhost local]# tar zxvf maxscale-2.1.3.centos.7.tar.gz
    [root@localhost local]# ln -s maxscale-2.1.3.centos.7 maxscale
    [root@localhost local]# cd maxscale
    [root@zhu56 maxscale]# chown -R maxscale var

建议创建软连接，这样有助于以后的版本升级及后期维护。

2. 首次安装 maxscale 需要创建日志相关目录

[root@localhost ~]# mkdir /var/log/maxscale
    [root@localhost ~]# mkdir /var/lib/maxscale
    [root@localhost ~]# mkdir /var/run/maxscale
    [root@localhost ~]# mkdir /var/cache/maxscale

3. 以下目录必须具备 maxscala 用户权限

[root@localhost ~]# chown maxscale /var/log/maxscale
    [root@localhost ~]# chown maxscale /var/lib/maxscale
    [root@localhost ~]# chown maxscale /var/run/maxscale
    [root@localhost ~]# chown maxscale /var/cache/maxscale

4. 为了能让 Maxscale 能顺利启动，还需要创建配置文件，在 Maxscale 目录下有配置文件模板拷贝到 etc 下即可。

[root@localhost ~]# cp /usr/local/maxscale/etc/maxscale.cnf.template /etc/maxscale.cnf

5. 在修改配置文件之前，需要在主服务器上创建一个用户并给予授权，而这个用户用于 MySQL 监控、路由功能

MariaDB [(none)]> create user 'jiankongdb'@'%' identified by 'jiankong123';
    MariaDB [(none)]> grant SELECT on mysql.user to 'jiankongdb'@'%';
    MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'jiankongdb'@'%';
    MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'jiankongdb'@'%';
    MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'jiankongdb'@'%';
    MariaDB [(none)]> grant REPLICATION CLIENT on *.* to 'jiankongdb'@'%';
 
    MariaDB [(none)]> GRANT replication slave, replication client,SELECT ON *.* TO jiankongdb@'%';

6. 查看授权情况

MariaDB [(none)]> SHOW GRANTS FOR'jiankong'@'%';

7. 接下来就开始修改 maxscale.cnf 配置文件，否则无法启动。

[root@localhost ~]# vim /etc/maxscale.cnf
 
    # MaxScale documentation on GitHub:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
 
    # Global parameters
    #
    # Complete list of configuration options:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
    #全局配置
    [maxscale]
    threads=1
 
    # Server definitions
    #
    # Set the address of the server to the network
    # address of a MySQL server.
    #
 
    [server1]
    type=server
    address=172.16.8.56
    port=3306
    protocol=MySQLBackend
    serv_weight=1
 
    [server2]
    type=server
    address=172.16.8.57
    port=3306
    protocol=MySQLBackend
    serv_weight=3
 
    [server3]
    type=server
    address=172.16.8.58
    port=3306
    protocol=MySQLBackend
    serv_weight=3
 
 
    # Monitor for the servers
    #
    # This will keep MaxScale aware of the state of the servers.
    # MySQL Monitor documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md
    #MariaDB 状态监控
    [MySQL Monitor]
    type=monitor
    module=mysqlmon
    servers=server1,server2,server3
    user=jiankong
    passwd=jiankong123
    monitor_interval=10000
    detect_stale_master=true #即使从全挂掉, 保证主担任读写
 
    # Service definitions
    #
    # Service Definition for a read-only service and
    # a read/write splitting service.
    #
 
    # ReadConnRoute documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md
    #读
    [Read-Only Service]
    type=service
    router=readconnroute
    servers=server1,server2,server3
    user=jiankong
    passwd=jiankong123
    router_options=slave
    enable_root_user=1 #允许 root 用户登录执行
    weightby=serv_weight #主从权重
 
    # ReadWriteSplit documentation:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md
    #写
    [Read-Write Service]
    type=service
    router=readwritesplit
    servers=server1,server2,server3
    user=jiankong
    passwd=jiankong123
    max_slave_connections=100%
    use_sql_variables_in=master #保证会话的一致性
    enable_root_user=1 #允许 root 登录
    max_slave_replication_lag=3600 #允许从超出主的同步时间, 超出则不路由
 
    # This service enables the use of the MaxAdmin interface
    # MaxScale administration guide:
    # https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md
 
    [MaxAdmin Service]
    type=service
    router=cli
 
    # Listener definitions for the services
    #
    # These listeners represent the ports the
    # services will listen on.
    #
 
    [Read-Only Listener]
    type=listener
    service=Read-Only Service
    protocol=MySQLClient
    port=4008
 
    [Read-Write Listener]
    type=listener
    service=Read-Write Service
    protocol=MySQLClient
    port=4006
 
    [MaxAdmin Listener]
    type=listener
    service=MaxAdmin Service
    protocol=maxscaled
    socket=default

保存并退出。
8. 下面创建启动脚本

[root@localhost ~]# cp /usr/local/maxscale-2.1.3.centos.7/share/maxscale.service /usr/lib/systemd/system/
    [root@localhost ~]# vim /usr/lib/systemd/system/maxscale.service

9. 修改 maxscale.service 中的 ExecStart=///bin/maxscale 为 ExecStart=/usr/local/maxscale/bin/maxscale

[root@localhost ~]# chmod 755 /usr/lib/systemd/system/maxscale.service
    [root@localhost ~]# systemctl enable maxscale
    [root@localhost ~]# systemctl daemon-reload
    [root@localhost ~]# systemctl start maxscale

10. 添加变量值

[root@localhost ~]# vi /etc/profile // 最后一行添加以下内容保存退出！PATH=$PATH:/usr/local/maxscale/bin
    export PATH
 
    [root@localhost ~]# source /etc/profile // 使其变量立即生效 

11. 接下来就可以使用 MaxAdmin 进行管理。MaxAdmin 是一个简单的客户端管理界面，可用于与 MariaDB MaxScale 服务器进行交互，可以显示 MariaDB MaxScale 内部的统计信息状态以及对 MariaDB MaxScale 操作的控制。详情：
https://mariadb.com/kb/en/mariadb-enterprise/maxadmin-admin-interface/

[root@localhost ~]# maxadmin // 回车
    MaxScale> list servers
    Servers.
    ---------------+--------------+-------+-------------+-----------------
    Server | Address | Port | Connections | Status 
    ---------------+--------------+-------+-------------+-----------------
    server1 | 172.16.8.56 | 3306 | 0 | Master, Running
    server2 | 172.16.8.57 | 3306 | 0 | Slave, Running
    server2 | 172.16.8.58 | 3306 | 0 | Slave, Running
    ---------------+--------------+-------+-------------+-----------------

12. 至此 MaxScale 已经配置完成。现在就可以使用客户端连接 Maxscale 服务器端 端口为 4006。

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配

腾讯云新客低至 82 元 / 年，老客户 99 元 / 年

代金券：在阿里云专用满减优惠券