maxscale配合MHA搭建读写分离的高可用架构（基于GTID replication主从架构，mysql5.6）

基于GTID的主从replication并配合MHA搭建高可用架构，请参考之前的博客：http://linzhijian.blog.51cto.com/1047212/1906434。这里只叙述如何在此基础上增加maxscale中间件，实现读写分离的功能。

MaxScale是maridb开发的一个MySQL数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。官方文档：https://mariadb.com/kb/en/mariadb-enterprise/about-mariadb-maxscale/

测试环境简要介绍：

master:192.168.110.131:3306

slave1: 192.168.110.132:3306

slave2: 192.168.110.130:3306

maxscale: 192.168.110.132

maxscale安装：

1、依赖包安装：

        yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit -y

2、maxscale包下载：

        https://downloads.mariadb.com/files/MaxScale         maxscale-2.0.5-1.centos.6.x86_64.rpm

3、创建监控用户：

    mysql> create user scalemon@'%' identified by "scalemon";    mysql> grant replication slave, replication client on *.* to scalemon@'%';

4、创建路由用户：

    mysql> create user scaleroute@'%' identified by "scaleroute";    mysql> grant select on mysql.* to scaleroute@'%';    mysql> grant show databases on *.* to 'scaleroute'@'%';

5、修改配置文件：

vim /etc/maxscale.conf

# MaxScale documentation on GitHub:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md# Global parameters## Complete list of configuration options:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md[maxscale]threads=1log_info=1logdir=/tmp/# Server definitions## Set the address of the server to the network# address of a MySQL server.#[server1]type=serveraddress=192.168.110.131port=3306protocol=MySQLBackend[server2]type=serveraddress=192.168.110.132port=3306protocol=MySQLBackend[server3]type=serveraddress=192.168.110.130port=3306protocol=MySQLBackend# 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/master/Documentation/Monitors/MySQL-Monitor.md[MySQL Monitor]type=monitormodule=mysqlmonservers=server1,server2,server3user=scalemonpasswd=scalemonmonitor_interval=10000# Service definitions## Service Definition for a read-only service and# a read/write splitting service.## ReadConnRoute documentation:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md#[Read-Only Service]#type=service#router=readconnroute#servers=server2,server3#user=scaleroute#passwd=scaleroute#router_options=slave# ReadWriteSplit documentation:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md[Read-Write Service]type=servicerouter=readwritesplitservers=server1,server2,server3user=scaleroutepasswd=scaleroutemax_slave_connections=100%# This service enables the use of the MaxAdmin interface# MaxScale administration guide:# https://github.com/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md[MaxAdmin Service]type=servicerouter=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=listenerservice=Read-Write Serviceprotocol=MySQLClientport=4006[MaxAdmin Listener]type=listenerservice=MaxAdmin Serviceprotocol=maxscaledsocket=default

6、启动服务：

    service maxscale start  或者 maxscale --conf=/etc/maxscale.conf

7、检查maxscale日志：

cat /tmp/maxscale1.log

MariaDB Corporation MaxScale    /tmp/maxscale1.log Fri Apr  7 12:26:23 2017-----------------------------------------------------------------------2017-04-07 12:26:23   notice : Working directory: /tmp2017-04-07 12:26:23   notice : MariaDB MaxScale 2.0.5 started2017-04-07 12:26:23   notice : MaxScale is running in process 108662017-04-07 12:26:23   notice : Configuration file: /etc/maxscale.cnf2017-04-07 12:26:23   notice : Log directory: /tmp2017-04-07 12:26:23   notice : Data directory: /var/lib/maxscale2017-04-07 12:26:23   notice : Module directory: /usr/lib64/maxscale2017-04-07 12:26:23   notice : Service cache: /var/cache/maxscale2017-04-07 12:26:23   notice : The logging of informational messages has been enabled.2017-04-07 12:26:23   notice : Initialise CLI router module V1.0.0.2017-04-07 12:26:23   notice : Loaded module cli: V1.0.0 from /usr/lib64/maxscale/libcli.so2017-04-07 12:26:23   notice : Initializing statemend-based read/write split router module.2017-04-07 12:26:23   notice : Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so2017-04-07 12:26:23   notice : Initialise the MySQL Monitor module V1.4.0.2017-04-07 12:26:23   notice : Loaded module mysqlmon: V1.4.0 from /usr/lib64/maxscale/libmysqlmon.so2017-04-07 12:26:23   notice : No query classifier specified, using default 'qc_sqlite'.2017-04-07 12:26:23   notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so2017-04-07 12:26:23   info   : qc_sqlite loaded.2017-04-07 12:26:23   info   : qc_sqlite: In-memory sqlite database successfully opened for thread 140338182019040.2017-04-07 12:26:23   notice : Encrypted password file /var/lib/maxscale/.secrets can't be accessed (No such file or directory). Password encryption is not used.2017-04-07 12:26:23   info   : Notification service feedback is not enabled.2017-04-07 12:26:23   info   : Read-Write Service: User scaleroute@% for database mysql added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User repl@192.168.% for database no db added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User linzj@192.168.110.% for database ANY added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User mha@192.168.110.% for database ANY added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User plum@192.168.110.% for database ANY added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User plum@192.168.110.131 for database no db added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User test@127.0.0.1 for database ANY added to service user table.2017-04-07 12:26:23   info   : Read-Write Service: User scalemon@% for database no db added to service user table.2017-04-07 12:26:23   notice : Loaded 8 MySQL Users for service [Read-Write Service].2017-04-07 12:26:23   notice : Loaded module MySQLClient: V1.1.0 from /usr/lib64/maxscale/libMySQLClient.so2017-04-07 12:26:23   notice : Listening connections at 0.0.0.0:4006 with protocol MySQL2017-04-07 12:26:23   info   : Started session [0] for Read-Write Service service 2017-04-07 12:26:23   info   : Initialise MaxScaled Protocol module.2017-04-07 12:26:23   notice : Loaded module maxscaled: V2.0.0 from /usr/lib64/maxscale/libmaxscaled.so2017-04-07 12:26:23   notice : Listening connections at /tmp/maxadmin.sock with protocol MaxScale Admin2017-04-07 12:26:23   info   : Started session [0] for MaxAdmin Service service 2017-04-07 12:26:23   notice : MaxScale started with 1 server threads.2017-04-07 12:26:23   notice : Started MaxScale log flusher.2017-04-07 12:26:23   notice : Server changed state: server1[192.168.110.131:3306]: new_master. [Running] -> [Master, Running]2017-04-07 12:26:23   notice : Server changed state: server2[192.168.110.132:3306]: new_slave. [Running] -> [Slave, Running]2017-04-07 12:26:23   notice : Server changed state: server3[192.168.110.130:3306]: new_slave. [Running] -> [Slave, Running]2017-04-07 12:26:23   notice : A Master Server is now available: 192.168.110.131:3306

8、登陆maxscale管理器，检查后端数据库状态信息：

maxadmin -S /tmp/maxadmin.sock

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Slave, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list servicesServices.--------------------------+----------------------+--------+---------------Service Name              | Router Module        | #Users | Total Sessions--------------------------+----------------------+--------+---------------Read-Write Service        | readwritesplit       |      1 |     1MaxAdmin Service          | cli                  |      2 |     2--------------------------+----------------------+--------+---------------MaxScale>

9、验证maxscale的monitor插件：

关闭mysql2的数据库服务：sh /home/linzj/shell/mysql.sh stop

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Slave, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Downserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------

重新拉起mysql2的数据库服务： sh /home/linzh/shell/mysql.sh start

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Downserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------

由于配置了skip_slave_start = 1这个参数，mysql2重启后需要手工start slave启动复制线程。

mysql> start slave;

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Slave, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------

10、验证读写分离：

[root@ansible log]#  mysql -ulinzj -plinzj -P4006 -h 192.168.110.130Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 11069Server version: 5.5.5-10.0.0 2.0.5-maxscale Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.linzj@192.168.110.130:4006  12:54:  [(none)]>linzj@192.168.110.130:4006  12:54:  [(none)]>linzj@192.168.110.130:4006  12:54:  [(none)]>linzj@192.168.110.130:4006  12:54:  [(none)]>select @@hostname;+------------+| @@hostname |+------------+| mysql2     |+------------+1 row in set (0.00 sec)linzj@192.168.110.130:4006  12:54:  [(none)]>begin;Query OK, 0 rows affected (0.00 sec)linzj@192.168.110.130:4006  12:54:  [(none)]>select @@hostname;+------------+| @@hostname |+------------+| mysql1     |+------------+1 row in set (0.00 sec)linzj@192.168.110.130:4006  12:54:  [(none)]>rollback;Query OK, 0 rows affected (0.00 sec)linzj@192.168.110.130:4006  12:54:  [(none)]>select @@hostname;+------------+| @@hostname |+------------+| mysql2     |+------------+1 row in set (0.00 sec)linzj@192.168.110.130:4006  12:54:  [(none)]>select @@hostname;+------------+| @@hostname |+------------+| mysql2     |+------------+1 row in set (0.00 sec)

从select的结果可知：读操作都路由到mysql2（slave）上去执行，而写操作路由到mysql1（master）上去执行，读写分离功能实现。

11、MHA高可用情形一（master crash）

环境：启动MHA manager进程， 手工pkill mysql1的数据库服务

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Slave, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Downserver2            | 192.168.110.132 |  3306 |           0 | Master, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+-------------------

[root@ansible shell]# tail /var/log/masterha/app1/manager.log Check MHA Manager logs at ansible:/var/log/masterha/app1/manager.log for details.Started automated(non-interactive) failover.Invalidated master IP address on mysql1(192.168.110.131:3306)Selected mysql2(192.168.110.132:3306) as a new master.mysql2(192.168.110.132:3306): OK: Applying all logs succeeded.mysql2(192.168.110.132:3306): OK: Activated master IP address.ansible(192.168.110.130:3306): OK: Slave started, replicating from mysql2(192.168.110.132:3306)mysql2(192.168.110.132:3306): Resetting slave info succeeded.Master failover to mysql2(192.168.110.132:3306) completed successfully.

从MHA的failover日志可知，mysql1主库down,mysql2升级成新的master主库。从maxscale管理界面上也可以看到，maxscale也能正确地识别了后端数据库的状态。

12、MHA高可用情形二（online change）

环境：继续上面的例子，目前mysql2为主库，mysql1重启后根据MHA的failover日志的信息重新配置change master作为从库加入集群。

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Runningserver2            | 192.168.110.132 |  3306 |           0 | Master, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Slave, Runningserver2            | 192.168.110.132 |  3306 |           0 | Master, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------

采用MHA online change的方式，将mysql1重新升级成主库。

shell>sh /home/linzj/shell/masterha_switch.sh manual_online_change

MHA Manager is not running on app1(2:NOT_RUNNING).Fri Apr  7 18:00:21 2017 - [info] MHA::MasterRotate version 0.56.Fri Apr  7 18:00:21 2017 - [info] Starting online master switch..Fri Apr  7 18:00:21 2017 - [info] Fri Apr  7 18:00:21 2017 - [info] * Phase 1: Configuration Check Phase..Fri Apr  7 18:00:21 2017 - [info] Fri Apr  7 18:00:21 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Fri Apr  7 18:00:21 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..Fri Apr  7 18:00:21 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..Fri Apr  7 18:00:21 2017 - [info] GTID failover mode = 1Fri Apr  7 18:00:21 2017 - [info] Current Alive Master: mysql2(192.168.110.132:3306)Fri Apr  7 18:00:21 2017 - [info] Alive Slaves:Fri Apr  7 18:00:21 2017 - [info]   mysql1(192.168.110.131:3306)  Version=5.6.34-log (oldest major version between slaves) log-bin:enabledFri Apr  7 18:00:21 2017 - [info]     GTID ONFri Apr  7 18:00:21 2017 - [info]     Replicating from 192.168.110.132(192.168.110.132:3306)Fri Apr  7 18:00:21 2017 - [info]   ansible(192.168.110.130:3306)  Version=5.6.34-log (oldest major version between slaves) log-bin:enabledFri Apr  7 18:00:21 2017 - [info]     GTID ONFri Apr  7 18:00:21 2017 - [info]     Replicating from 192.168.110.132(192.168.110.132:3306)Fri Apr  7 18:00:21 2017 - [info]     Not candidate for the new Master (no_master is set)Fri Apr  7 18:00:21 2017 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..Fri Apr  7 18:00:21 2017 - [info]  ok.Fri Apr  7 18:00:21 2017 - [info] Checking MHA is not monitoring or doing failover..Fri Apr  7 18:00:21 2017 - [info] Checking replication health on mysql1..Fri Apr  7 18:00:21 2017 - [info]  ok.Fri Apr  7 18:00:21 2017 - [info] Checking replication health on ansible..Fri Apr  7 18:00:21 2017 - [info]  ok.Fri Apr  7 18:00:21 2017 - [info] mysql1 can be new master.Fri Apr  7 18:00:21 2017 - [info] From:mysql2(192.168.110.132:3306) (current master) +--mysql1(192.168.110.131:3306) +--ansible(192.168.110.130:3306)To:mysql1(192.168.110.131:3306) (new master) +--ansible(192.168.110.130:3306) +--mysql2(192.168.110.132:3306)Fri Apr  7 18:00:21 2017 - [info] Checking whether mysql1(192.168.110.131:3306) is ok for the new master..Fri Apr  7 18:00:21 2017 - [info]  ok.Fri Apr  7 18:00:21 2017 - [info] mysql2(192.168.110.132:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.Fri Apr  7 18:00:21 2017 - [info] mysql2(192.168.110.132:3306): Resetting slave pointing to the dummy host.Fri Apr  7 18:00:21 2017 - [info] ** Phase 1: Configuration Check Phase completed.Fri Apr  7 18:00:21 2017 - [info] Fri Apr  7 18:00:21 2017 - [info] * Phase 2: Rejecting updates Phase..Fri Apr  7 18:00:21 2017 - [info] Fri Apr  7 18:00:21 2017 - [info] Executing master ip online change script to disable write on the current master:Fri Apr  7 18:00:21 2017 - [info]   /usr/bin/master_ip_online_change --command=stop --orig_master_host=mysql2 --orig_master_ip=192.168.110.132 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='mha' --new_master_host=mysql1 --new_master_ip=192.168.110.131 --new_master_port=3306 --new_master_user='mha' --new_master_password='mha' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slaveFri Apr  7 18:00:21 2017 637019 Set read_only on the new master.. ok.Fri Apr  7 18:00:21 2017 639365 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds){'Time' => '281','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '55','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.130:33058'}{'Time' => '73','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '56','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.131:50008'}Fri Apr  7 18:00:22 2017 144206 Waiting all running 2 threads are disconnected.. (max 1000 milliseconds){'Time' => '281','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '55','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.130:33058'}{'Time' => '73','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '56','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.131:50008'}Fri Apr  7 18:00:22 2017 649414 Waiting all running 2 threads are disconnected.. (max 500 milliseconds){'Time' => '282','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '55','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.130:33058'}{'Time' => '74','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '56','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.131:50008'}Fri Apr  7 18:00:23 2017 153220 Set read_only=1 on the orig master.. ok.Fri Apr  7 18:00:23 2017 154715 Waiting all running 2 queries are disconnected.. (max 500 milliseconds){'Time' => '282','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '55','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.130:33058'}{'Time' => '74','Command' => 'Binlog Dump GTID','db' => undef,'Id' => '56','Info' => undef,'User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for binlog to be updated','Host' => '192.168.110.131:50008'}Fri Apr  7 18:00:23 2017 658225 Killing all application threads..Fri Apr  7 18:00:23 2017 659547 done.Disabling the VIP on old master: mysql2 Fri Apr  7 18:00:24 2017 - [info]  ok.Fri Apr  7 18:00:24 2017 - [info] Locking all tables on the orig master to reject updates from everybody (including root):Fri Apr  7 18:00:24 2017 - [info] Executing FLUSH TABLES WITH READ LOCK..Fri Apr  7 18:00:24 2017 - [info]  ok.Fri Apr  7 18:00:24 2017 - [info] Orig master binlog:pos is mysql-bin.000003:188723758.Fri Apr  7 18:00:24 2017 - [info]  Waiting to execute all relay logs on mysql1(192.168.110.131:3306)..Fri Apr  7 18:00:24 2017 - [info]  master_pos_wait(mysql-bin.000003:188723758) completed on mysql1(192.168.110.131:3306). Executed 0 events.Fri Apr  7 18:00:24 2017 - [info]   done.Fri Apr  7 18:00:24 2017 - [info] Getting new master's binlog name and position..Fri Apr  7 18:00:24 2017 - [info]  mysql-bin.000007:191Fri Apr  7 18:00:24 2017 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='mysql1 or 192.168.110.131', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';Fri Apr  7 18:00:24 2017 - [info] Executing master ip online change script to allow write on the new master:Fri Apr  7 18:00:24 2017 - [info]   /usr/bin/master_ip_online_change --command=start --orig_master_host=mysql2 --orig_master_ip=192.168.110.132 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='mha' --new_master_host=mysql1 --new_master_ip=192.168.110.131 --new_master_port=3306 --new_master_user='mha' --new_master_password='mha' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slaveFri Apr  7 18:00:24 2017 190044 Set read_only=0 on the new master.Enabling the VIP - 192.168.110.100 on the new master - mysql1 Fri Apr  7 18:00:27 2017 - [info]  ok.Fri Apr  7 18:00:27 2017 - [info] Fri Apr  7 18:00:27 2017 - [info] * Switching slaves in parallel..Fri Apr  7 18:00:27 2017 - [info] Fri Apr  7 18:00:27 2017 - [info] -- Slave switch on host ansible(192.168.110.130:3306) started, pid: 11714Fri Apr  7 18:00:27 2017 - [info] Fri Apr  7 18:00:28 2017 - [info] Log messages from ansible ...Fri Apr  7 18:00:28 2017 - [info] Fri Apr  7 18:00:27 2017 - [info]  Waiting to execute all relay logs on ansible(192.168.110.130:3306)..Fri Apr  7 18:00:27 2017 - [info]  master_pos_wait(mysql-bin.000003:188723758) completed on ansible(192.168.110.130:3306). Executed 0 events.Fri Apr  7 18:00:27 2017 - [info]   done.Fri Apr  7 18:00:27 2017 - [info]  Resetting slave ansible(192.168.110.130:3306) and starting replication from the new master mysql1(192.168.110.131:3306)..Fri Apr  7 18:00:27 2017 - [info]  Executed CHANGE MASTER.Fri Apr  7 18:00:28 2017 - [info]  Slave started.Fri Apr  7 18:00:28 2017 - [info] End of log messages from ansible ...Fri Apr  7 18:00:28 2017 - [info] Fri Apr  7 18:00:28 2017 - [info] -- Slave switch on host ansible(192.168.110.130:3306) succeeded.Fri Apr  7 18:00:28 2017 - [info] Unlocking all tables on the orig master:Fri Apr  7 18:00:28 2017 - [info] Executing UNLOCK TABLES..Fri Apr  7 18:00:28 2017 - [info]  ok.Fri Apr  7 18:00:28 2017 - [info] Starting orig master as a new slave..Fri Apr  7 18:00:28 2017 - [info]  Resetting slave mysql2(192.168.110.132:3306) and starting replication from the new master mysql1(192.168.110.131:3306)..Fri Apr  7 18:00:28 2017 - [info]  Executed CHANGE MASTER.Fri Apr  7 18:00:29 2017 - [info]  Slave started.Fri Apr  7 18:00:29 2017 - [info] All new slave servers switched successfully.Fri Apr  7 18:00:29 2017 - [info] Fri Apr  7 18:00:29 2017 - [info] * Phase 5: New master cleanup phase..Fri Apr  7 18:00:29 2017 - [info] Fri Apr  7 18:00:29 2017 - [info]  mysql1: Resetting slave info succeeded.Fri Apr  7 18:00:29 2017 - [info] Switching master to mysql1(192.168.110.131:3306) completed successfully.

MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Slave, Runningserver2            | 192.168.110.132 |  3306 |           0 | Master, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server             | Address         | Port  | Connections | Status              -------------------+-----------------+-------+-------------+--------------------server1            | 192.168.110.131 |  3306 |           0 | Master, Runningserver2            | 192.168.110.132 |  3306 |           0 | Slave, Runningserver3            | 192.168.110.130 |  3306 |           0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------

从MHA的online change日志中可以发现，mysql1重新升级成主库，mysql2降级为从库。而maxscale也可以准确的识别出后端数据库的状态。

13、通过sysbench压测maxscale性能。

压测脚本test_sysbench.sh如下：

#!/bin/shssh root@ansible '/usr/bin/ansible all -m shell -a "sh /home/linzj/shell/mysql.sh stop; sh /home/linzj/shell/mysql.sh start"'sleep 60sysbench --test=oltp --mysql-host=192.168.110.130 --mysql-port=4006 --mysql-user=linzj --mysql-password=linzj --mysql-db=sbtest1 --oltp-num-tables=10 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=complex --oltp-nontrx-mode=select --oltp-read-only=on --max-time=300 --num-threads=16 run > /tmp/130_4006_readonly.logssh root@ansible '/usr/bin/ansible all -m shell -a "sh /home/linzj/shell/mysql.sh stop; sh /home/linzj/shell/mysql.sh start"'sleep 60sysbench --test=oltp --mysql-host=192.168.110.130 --mysql-port=4008 --mysql-user=linzj --mysql-password=linzj --mysql-db=sbtest1 --oltp-num-tables=10 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=complex --oltp-nontrx-mode=select --oltp-read-only=on --max-time=300 --num-threads=16 run > /tmp/130_4008_readonly.logssh root@ansible '/usr/bin/ansible all -m shell -a "sh /home/linzj/shell/mysql.sh stop; sh /home/linzj/shell/mysql.sh start"'sleep 60sysbench --test=oltp --mysql-host=192.168.110.131 --mysql-port=3306 --mysql-user=linzj --mysql-password=linzj --mysql-db=sbtest1 --oltp-num-tables=10 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=complex --oltp-nontrx-mode=select --oltp-read-only=on --max-time=300 --num-threads=16 run > /tmp/131_3306_readonly.logssh root@ansible '/usr/bin/ansible all -m shell -a "sh /home/linzj/shell/mysql.sh stop; sh /home/linzj/shell/mysql.sh start"'sleep 60sysbench --test=oltp --mysql-host=192.168.110.130 --mysql-port=4006 --mysql-user=linzj --mysql-password=linzj --mysql-db=sbtest1 --oltp-num-tables=10 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=complex --oltp-nontrx-mode=select --oltp-read-only=off --max-time=300 --num-threads=16 run > /tmp/130_4006_readwrite.logssh root@ansible '/usr/bin/ansible all -m shell -a "sh /home/linzj/shell/mysql.sh stop; sh /home/linzj/shell/mysql.sh start"'sleep 60sysbench --test=oltp --mysql-host=192.168.110.131 --mysql-port=3306 --mysql-user=linzj --mysql-password=linzj --mysql-db=sbtest1 --oltp-num-tables=10 --oltp-table-size=500000 --report-interval=100  --max-requests=0 --oltp-test-mode=complex --oltp-nontrx-mode=select --oltp-read-only=off --max-time=300 --num-threads=16 run > /tmp/131_3306_readwrite.log

这里我通过ansible管理3台MySQL，在做压测前重启3台机器，保证压测结果不受到缓冲的影响。

ansible(192.168.110.130)部署了maxscale中间件，该压测脚本部署在mysql1(192.168.110.131)。分只读和混合读写两种情况压测，只读分别测试130机的4006读写分离端口、4008只读端口和131机的3306数据库端口。

因为压测时间只有600s，并且由于虚拟机性能的缘故，只开了16个线程进行并发，测试结果可能偏差较大。但是仍然可以总结出一些结论：

1、关闭事务（即autocommit=1）的情况下，通过maxscale访问方式性能远高于直连数据库方式

2、混合读写的情况下，通过maxscale访问方式有一定的性能损耗。