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

操作系统:CentOS7 (Core)

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

MaxScale服务器:192.168.40.134

主服务器:192.168.40.132

从服务器:192.168.40.133

 

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'jiankongdb'@'%';

注意：

在/var/lib/maxscale文件夹上面建立文件夹.secrets，然后使用命令：maxkeys .secrets/生成加密文件.secrets。再把这个.secrets文件拷贝到/var/lib/maxscale下面，而且这个.secrets文件的格式为：

-r--------   1 maxscale maxscale   48 5月  29 15:54 .secrets

再使用这个文件生成密码：

在当前目录使用指令：maxpasswd  jiankong123生成密码C5828F2BBE20D9758BDECF33173C0317，到时候需要用到。

 

 

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=192.168.40.132

    port=3306

    protocol=MySQLBackend

    serv_weight=1

 

    [server2]

    type=server

    address=192.168.40.133

    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

    user=jiankongdb

    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

    user=jiankongdb

    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 假如该指令运行失败，可以尝试以下指令：maxscale --config=/etc/maxscale.cnf

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。

在MaxAdmin客户端管理界面进行操作：

停止MaxScale：shutdown maxscale

停止monitor：shutdown monitor

shutdown service

shutdown listener