基于GTID的主从replication并配合MHA搭建高可用架构，请参考之前的博客：http://linzhijian./1047212/1906434。这里只叙述如何在此基础上增加maxscale中间件，实现读写分离的功能。
MaxScale是maridb开发的一个MySQL数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。官方文档：/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、依赖包安装：
yuminstalllibaio.x86_64libaio-devel.x86_64novacom-server.x86_64libedit-y
2、maxscale包下载：/files/MaxScale
maxscale-2.0.5-1.centos.6.x86_64.rpm
3、创建监控用户：mysql>createuserscalemon@'%'identifiedby"scalemon";
mysql>grantreplicationslave,replicationclienton*.*toscalemon@'%';
4、创建路由用户：mysql>createuserscaleroute@'%'identifiedby"scaleroute";
mysql>grantselectonmysql.*toscaleroute@'%';
mysql>grantshowdatabaseson*.*to'scaleroute'@'%';
5、修改配置文件：
vim /etc/maxscale.conf#MaxScaledocumentationonGitHub:
#/mariadb-corporation/MaxScale/blob/master/Documentation/Documentation-Contents.md
#Globalparameters
#
#Completelistofconfigurationoptions:
#/mariadb-corporation/MaxScale/blob/master/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=1
log_info=1
logdir=/tmp/
#Serverdefinitions
#
#Settheaddressoftheservertothenetwork
#addressofaMySQLserver.
#
[server1]
type=server
address=192.168.110.131
port=3306
protocol=MySQLBackend
[server2]
type=server
address=192.168.110.132
port=3306
protocol=MySQLBackend
[server3]
type=server
address=192.168.110.130
port=3306
protocol=MySQLBackend
#Monitorfortheservers
#
#ThiswillkeepMaxScaleawareofthestateoftheservers.
#MySQLMonitordocumentation:
#/mariadb-corporation/MaxScale/blob/master/Documentation/Monitors/MySQL-Monitor.md
[MySQLMonitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=scalemon
passwd=scalemon
monitor_interval=10000
#Servicedefinitions
#
#ServiceDefinitionforaread-onlyserviceand
#aread/writesplittingservice.
#
#ReadConnRoutedocumentation:
#/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadConnRoute.md
#[Read-OnlyService]
#type=service
#router=readconnroute
#servers=server2,server3
#user=scaleroute
#passwd=scaleroute
#router_options=slave
#ReadWriteSplitdocumentation:
#/mariadb-corporation/MaxScale/blob/master/Documentation/Routers/ReadWriteSplit.md
[Read-WriteService]
type=service
router=readwritesplit
servers=server1,server2,server3
user=scaleroute
passwd=scaleroute
max_slave_connections=100%
#ThisserviceenablestheuseoftheMaxAdmininterface
#MaxScaleadministrationguide:
#/mariadb-corporation/MaxScale/blob/master/Documentation/Reference/MaxAdmin.md
[MaxAdminService]
type=service
router=cli
#Listenerdefinitionsfortheservices
#
#Theselistenersrepresenttheportsthe
#serviceswilllistenon.
#
#[Read-OnlyListener]
#type=listener
#service=Read-OnlyService
#protocol=MySQLClient
#port=4008
[Read-WriteListener]
type=listener
service=Read-WriteService
protocol=MySQLClient
port=4006
[MaxAdminListener]
type=listener
service=MaxAdminService
protocol=maxscaled
socket=default
6、启动服务：servicemaxscalestart或者maxscale--conf=/etc/maxscale.conf
7、检查maxscale日志：
cat /tmp/maxscale1.logMariaDBCorporationMaxScale/tmp/maxscale1.logFriApr712:26:23
-----------------------------------------------------------------------
-04-0712:26:23notice:Workingdirectory:/tmp
-04-0712:26:23notice:MariaDBMaxScale2.0.5started
-04-0712:26:23notice:MaxScaleisrunninginprocess10866
-04-0712:26:23notice:Configurationfile:/etc/f
-04-0712:26:23notice:Logdirectory:/tmp
-04-0712:26:23notice:Datadirectory:/var/lib/maxscale
-04-0712:26:23notice:Moduledirectory:/usr/lib64/maxscale
-04-0712:26:23notice:Servicecache:/var/cache/maxscale
-04-0712:26:23notice:Theloggingofinformationalmessageshasbeenenabled.
-04-0712:26:23notice:InitialiseCLIroutermoduleV1.0.0.
-04-0712:26:23notice:Loadedmodulecli:V1.0.0from/usr/lib64/maxscale/libcli.so
-04-0712:26:23notice:Initializingstatemend-basedread/writesplitroutermodule.
-04-0712:26:23notice:Loadedmodulereadwritesplit:V1.1.0from/usr/lib64/maxscale/libreadwritesplit.so
-04-0712:26:23notice:InitialisetheMySQLMonitormoduleV1.4.0.
-04-0712:26:23notice:Loadedmodulemysqlmon:V1.4.0from/usr/lib64/maxscale/libmysqlmon.so
-04-0712:26:23notice:Noqueryclassifierspecified,usingdefault'qc_sqlite'.
-04-0712:26:23notice:Loadedmoduleqc_sqlite:V1.0.0from/usr/lib64/maxscale/libqc_sqlite.so
-04-0712:26:23info:qc_sqliteloaded.
-04-0712:26:23info:qc_sqlite:In-memorysqlitedatabasesuccessfullyopenedforthread14033818040.
-04-0712:26:23notice:Encryptedpasswordfile/var/lib/maxscale/.secretscan'tbeaccessed(Nosuchfileordirectory).Passwordencryptionisnotused.
-04-0712:26:23info:Notificationservicefeedbackisnotenabled.
-04-0712:26:23info:Read-WriteService:Userscaleroute@%fordatabasemysqladdedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Userrepl@192.168.%fordatabasenodbaddedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Userlinzj@192.168.110.%fordatabaseANYaddedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Usermha@192.168.110.%fordatabaseANYaddedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Userplum@192.168.110.%fordatabaseANYaddedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Userplum@192.168.110.131fordatabasenodbaddedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Usertest@127.0.0.1fordatabaseANYaddedtoserviceusertable.
-04-0712:26:23info:Read-WriteService:Userscalemon@%fordatabasenodbaddedtoserviceusertable.
-04-0712:26:23notice:Loaded8MySQLUsersforservice[Read-WriteService].
-04-0712:26:23notice:LoadedmoduleMySQLClient:V1.1.0from/usr/lib64/maxscale/libMySQLClient.so
-04-0712:26:23notice:Listeningconnectionsat0.0.0.0:4006withprotocolMySQL
-04-0712:26:23info:Startedsession[0]forRead-WriteServiceservice
-04-0712:26:23info:InitialiseMaxScaledProtocolmodule.
-04-0712:26:23notice:Loadedmodulemaxscaled:V2.0.0from/usr/lib64/maxscale/libmaxscaled.so
-04-0712:26:23notice:Listeningconnectionsat/tmp/maxadmin.sockwithprotocolMaxScaleAdmin
-04-0712:26:23info:Startedsession[0]forMaxAdminServiceservice
-04-0712:26:23notice:MaxScalestartedwith1serverthreads.
-04-0712:26:23notice:StartedMaxScalelogflusher.
-04-0712:26:23notice:Serverchangedstate:server1[192.168.110.131:3306]:new_master.[Running]->[Master,Running]
-04-0712:26:23notice:Serverchangedstate:server2[192.168.110.132:3306]:new_slave.[Running]->[Slave,Running]
-04-0712:26:23notice:Serverchangedstate:server3[192.168.110.130:3306]:new_slave.[Running]->[Slave,Running]
-04-0712:26:23notice:AMasterServerisnowavailable:192.168.110.131:3306
8、登陆maxscale管理器，检查后端数据库状态信息：
maxadmin -S /tmp/maxadmin.sockMaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Slave,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservices
Services.
--------------------------+----------------------+--------+---------------
ServiceName|RouterModule|#Users|TotalSessions
--------------------------+----------------------+--------+---------------
Read-WriteService|readwritesplit|1|1
MaxAdminService|cli|2|2
--------------------------+----------------------+--------+---------------
MaxScale>
9、验证maxscale的monitor插件：
关闭mysql2的数据库服务：sh /home/linzj/shell/mysql.sh stopMaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Slave,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Down
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
重新拉起mysql2的数据库服务： sh /home/linzh/shell/mysql.sh startMaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Down
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
由于配置了skip_slave_start = 1这个参数，mysql2重启后需要手工start slave启动复制线程。
mysql> start slave;MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Slave,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
10、验证读写分离：[root@ansiblelog]#mysql-ulinzj-plinzj-P4006-h192.168.110.130
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
mandsendwith;or\g.
YourMySQLconnectionidis11069
Serverversion:5.5.5-10.0.02.0.5-maxscaleSourcedistribution
Copyright(c)2000,,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
linzj@192.168.110.130:400612:54:[(none)]>
linzj@192.168.110.130:400612:54:[(none)]>
linzj@192.168.110.130:400612:54:[(none)]>
linzj@192.168.110.130:400612:54:[(none)]>select@@hostname;
+------------+
|@@hostname|
+------------+
|mysql2|
+------------+
1rowinset(0.00sec)
linzj@192.168.110.130:400612:54:[(none)]>begin;
QueryOK,0rowsaffected(0.00sec)
linzj@192.168.110.130:400612:54:[(none)]>select@@hostname;
+------------+
|@@hostname|
+------------+
|mysql1|
+------------+
1rowinset(0.00sec)
linzj@192.168.110.130:400612:54:[(none)]>rollback;
QueryOK,0rowsaffected(0.00sec)
linzj@192.168.110.130:400612:54:[(none)]>select@@hostname;
+------------+
|@@hostname|
+------------+
|mysql2|
+------------+
1rowinset(0.00sec)
linzj@192.168.110.130:400612:54:[(none)]>select@@hostname;
+------------+
|@@hostname|
+------------+
|mysql2|
+------------+
1rowinset(0.00sec)
从select的结果可知：读操作都路由到mysql2(slave)上去执行，而写操作路由到mysql1(master)上去执行，读写分离功能实现。
11、MHA高可用情形一(master crash)
环境：启动MHA manager进程， 手工pkill mysql1的数据库服务MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Slave,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Down
server2|192.168.110.132|3306|0|Master,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+-------------------[root@ansibleshell]#tail/var/log/masterha/app1/manager.log
CheckMHAManagerlogsatansible:/var/log/masterha/app1/manager.logfordetails.
Startedautomated(non-interactive)failover.
InvalidatedmasterIPaddressonmysql1(192.168.110.131:3306)
Selectedmysql2(192.168.110.132:3306)asanewmaster.
mysql2(192.168.110.132:3306):OK:Applyingalllogssucceeded.
mysql2(192.168.110.132:3306):OK:ActivatedmasterIPaddress.
ansible(192.168.110.130:3306):OK:Slavestarted,replicatingfrommysql2(192.168.110.132:3306)
mysql2(192.168.110.132:3306):Resettingslaveinfosucceeded.
Masterfailovertomysql2(192.168.110.132:3306)completedsuccessfully.
从MHA的failover日志可知，mysql1主库down,mysql2升级成新的master主库。从maxscale管理界面上也可以看到，maxscale也能正确地识别了后端数据库的状态。
12、MHA高可用情形二(online change)
环境：继续上面的例子，目前mysql2为主库，mysql1重启后根据MHA的failover日志的信息重新配置change master作为从库加入集群。MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Running
server2|192.168.110.132|3306|0|Master,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Slave,Running
server2|192.168.110.132|3306|0|Master,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
采用MHA online change的方式，将mysql1重新升级成主库。
shell>sh /home/linzj/shell/masterha_switch.sh manual_online_changeMHAManagerisnotrunningonapp1(2:NOT_RUNNING).
FriApr718:00:21-[info]MHA::MasterRotateversion0.56.
FriApr718:00:21-[info]Startingonlinemasterswitch..
FriApr718:00:21-[info]
FriApr718:00:21-[info]*Phase1:ConfigurationCheckPhase..
FriApr718:00:21-[info]
FriApr718:00:21-[warning]Globalconfigurationfile/etc/fnotfound.Skipping.
FriApr718:00:21-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/f..
FriApr718:00:21-[info]Readingserverconfigurationfrom/etc/masterha/f..
FriApr718:00:21-[info]GTIDfailovermode=1
FriApr718:00:21-[info]CurrentAliveMaster:mysql2(192.168.110.132:3306)
FriApr718:00:21-[info]AliveSlaves:
FriApr718:00:21-[info]mysql1(192.168.110.131:3306)Version=5.6.34-log(oldestmajorversionbetweenslaves)log-bin:enabled
FriApr718:00:21-[info]GTIDON
FriApr718:00:21-[info]Replicatingfrom192.168.110.132(192.168.110.132:3306)
FriApr718:00:21-[info]ansible(192.168.110.130:3306)Version=5.6.34-log(oldestmajorversionbetweenslaves)log-bin:enabled
FriApr718:00:21-[info]GTIDON
FriApr718:00:21-[info]Replicatingfrom192.168.110.132(192.168.110.132:3306)
FriApr718:00:21-[info]NotcandidateforthenewMaster(no_masterisset)
FriApr718:00:21-[info]ExecutingFLUSHNO_WRITE_TO_BINLOGTABLES.Thismaytakelongtime..
FriApr718:00:21-[info]ok.
FriApr718:00:21-[info]CheckingMHAisnotmonitoringordoingfailover..
FriApr718:00:21-[info]Checkingreplicationhealthonmysql1..
FriApr718:00:21-[info]ok.
FriApr718:00:21-[info]Checkingreplicationhealthonansible..
FriApr718:00:21-[info]ok.
FriApr718:00:21-[info]mysql1canbenewmaster.
FriApr718:00:21-[info]
From:
mysql2(192.168.110.132:3306)(currentmaster)
+--mysql1(192.168.110.131:3306)
+--ansible(192.168.110.130:3306)
To:
mysql1(192.168.110.131:3306)(newmaster)
+--ansible(192.168.110.130:3306)
+--mysql2(192.168.110.132:3306)
FriApr718:00:21-[info]Checkingwhethermysql1(192.168.110.131:3306)isokforthenewmaster..
FriApr718:00:21-[info]ok.
FriApr718:00:21-[info]mysql2(192.168.110.132:3306):SHOWSLAVESTATUSreturnedemptyresult.Tocheckreplicationfilteringrules,temporarilyexecutingCHANGEMASTERtoadummyhost.
FriApr718:00:21-[info]mysql2(192.168.110.132:3306):Resettingslavepointingtothedummyhost.
FriApr718:00:21-[info]**Phase1:ConfigurationCheckPhasecompleted.
FriApr718:00:21-[info]
FriApr718:00:21-[info]*Phase2:RejectingupdatesPhase..
FriApr718:00:21-[info]
FriApr718:00:21-[info]Executingmasteriponlinechangescripttodisablewriteonthecurrentmaster:
FriApr718:00:21-[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_slave
FriApr718:00:21637019Setread_onlyonthenewmaster..ok.
FriApr718:00:21639365Waitingallrunning2threadsaredisconnected..(max1500milliseconds)
{'Time'=>'281','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'55','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.130:33058'}
{'Time'=>'73','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'56','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.131:50008'}
FriApr718:00:22144206Waitingallrunning2threadsaredisconnected..(max1000milliseconds)
{'Time'=>'281','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'55','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.130:33058'}
{'Time'=>'73','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'56','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.131:50008'}
FriApr718:00:22649414Waitingallrunning2threadsaredisconnected..(max500milliseconds)
{'Time'=>'282','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'55','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.130:33058'}
{'Time'=>'74','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'56','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.131:50008'}
FriApr718:00:23153220Setread_only=1ontheorigmaster..ok.
FriApr718:00:23154715Waitingallrunning2queriesaredisconnected..(max500milliseconds)
{'Time'=>'282','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'55','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.130:33058'}
{'Time'=>'74','Command'=>'BinlogDumpGTID','db'=>undef,'Id'=>'56','Info'=>undef,'User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated','Host'=>'192.168.110.131:50008'}
FriApr718:00:23658225Killingallapplicationthreads..
FriApr718:00:23659547done.
DisablingtheVIPonoldmaster:mysql2
FriApr718:00:24-[info]ok.
FriApr718:00:24-[info]Lockingalltablesontheorigmastertorejectupdatesfromeverybody(includingroot):
FriApr718:00:24-[info]ExecutingFLUSHTABLESWITHREADLOCK..
FriApr718:00:24-[info]ok.
FriApr718:00:24-[info]Origmasterbinlog:posismysql-bin.000003:188723758.
FriApr718:00:24-[info]Waitingtoexecuteallrelaylogsonmysql1(192.168.110.131:3306)..
FriApr718:00:24-[info]master_pos_wait(mysql-bin.000003:188723758)completedonmysql1(192.168.110.131:3306).Executed0events.
FriApr718:00:24-[info]done.
FriApr718:00:24-[info]Gettingnewmaster'sbinlognameandposition..
FriApr718:00:24-[info]mysql-bin.000007:191
FriApr718:00:24-[info]Allotherslavesshouldstartreplicationfromhere.Statementshouldbe:CHANGEMASTERTOMASTER_HOST='mysql1or192.168.110.131',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='xxx';
FriApr718:00:24-[info]Executingmasteriponlinechangescripttoallowwriteonthenewmaster:
FriApr718:00:24-[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_slave
FriApr718:00:24190044Setread_only=0onthenewmaster.
EnablingtheVIP-192.168.110.100onthenewmaster-mysql1
FriApr718:00:27-[info]ok.
FriApr718:00:27-[info]
FriApr718:00:27-[info]*Switchingslavesinparallel..
FriApr718:00:27-[info]
FriApr718:00:27-[info]--Slaveswitchonhostansible(192.168.110.130:3306)started,pid:11714
FriApr718:00:27-[info]
FriApr718:00:28-[info]Logmessagesfromansible...
FriApr718:00:28-[info]
FriApr718:00:27-[info]Waitingtoexecuteallrelaylogsonansible(192.168.110.130:3306)..
FriApr718:00:27-[info]master_pos_wait(mysql-bin.000003:188723758)completedonansible(192.168.110.130:3306).Executed0events.
FriApr718:00:27-[info]done.
FriApr718:00:27-[info]Resettingslaveansible(192.168.110.130:3306)andstartingreplicationfromthenewmastermysql1(192.168.110.131:3306)..
FriApr718:00:27-[info]ExecutedCHANGEMASTER.
FriApr718:00:28-[info]Slavestarted.
FriApr718:00:28-[info]Endoflogmessagesfromansible...
FriApr718:00:28-[info]
FriApr718:00:28-[info]--Slaveswitchonhostansible(192.168.110.130:3306)succeeded.
FriApr718:00:28-[info]Unlockingalltablesontheorigmaster:
FriApr718:00:28-[info]ExecutingUNLOCKTABLES..
FriApr718:00:28-[info]ok.
FriApr718:00:28-[info]Startingorigmasterasanewslave..
FriApr718:00:28-[info]Resettingslavemysql2(192.168.110.132:3306)andstartingreplicationfromthenewmastermysql1(192.168.110.131:3306)..
FriApr718:00:28-[info]ExecutedCHANGEMASTER.
FriApr718:00:29-[info]Slavestarted.
FriApr718:00:29-[info]Allnewslaveserversswitchedsuccessfully.
FriApr718:00:29-[info]
FriApr718:00:29-[info]*Phase5:Newmastercleanupphase..
FriApr718:00:29-[info]
FriApr718:00:29-[info]mysql1:Resettingslaveinfosucceeded.
FriApr718:00:29-[info]Switchingmastertomysql1(192.168.110.131:3306)completedsuccessfully.MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Slave,Running
server2|192.168.110.132|3306|0|Master,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>listservers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server|Address|Port|Connections|Status
-------------------+-----------------+-------+-------------+--------------------
server1|192.168.110.131|3306|0|Master,Running
server2|192.168.110.132|3306|0|Slave,Running
server3|192.168.110.130|3306|0|Slave,Running
-------------------+-----------------+-------+-------------+--------------------
从MHA的online change日志中可以发现，mysql1重新升级成主库，mysql2降级为从库。而maxscale也可以准确的识别出后端数据库的状态。
13、通过sysbench压测maxscale性能。
压测脚本test_sysbench.sh如下：#!/bin/sh
sshroot@ansible'/usr/bin/ansibleall-mshell-a"sh/home/linzj/shell/mysql.shstop;sh/home/linzj/shell/mysql.shstart"'
sleep60
sysbench--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=16run>/tmp/130_4006_readonly.log
sshroot@ansible'/usr/bin/ansibleall-mshell-a"sh/home/linzj/shell/mysql.shstop;sh/home/linzj/shell/mysql.shstart"'
sleep60
sysbench--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=16run>/tmp/130_4008_readonly.log
sshroot@ansible'/usr/bin/ansibleall-mshell-a"sh/home/linzj/shell/mysql.shstop;sh/home/linzj/shell/mysql.shstart"'
sleep60
sysbench--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=16run>/tmp/131_3306_readonly.log
sshroot@ansible'/usr/bin/ansibleall-mshell-a"sh/home/linzj/shell/mysql.shstop;sh/home/linzj/shell/mysql.shstart"'
sleep60
sysbench--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=16run>/tmp/130_4006_readwrite.log
sshroot@ansible'/usr/bin/ansibleall-mshell-a"sh/home/linzj/shell/mysql.shstop;sh/home/linzj/shell/mysql.shstart"'
sleep60
sysbench--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=16run>/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数据库端口。
只读(开启事务)
机器端口transactionsresponse time of 95%
maxscale机400635340582.21ms
maxscale机400812350763.91ms
数据库本机330680570354.22ms
混合读写(开启事务)
机器端口transactionsresponse time of 95%
maxscale机400668992435.64ms
数据库本机330667592651.76ms
只读(关闭事务)
机器端口transactionsresponse time of 95%
maxscale机400694300819.34ms
maxscale机400832169143.48ms
数据库本机330623381080.55ms
混合读写(关闭事务)
机器端口transactionsresponse time of 95%
maxscale机400612278778.41ms
数据库本机330625631550.51ms
因为压测时间只有600s，并且由于虚拟机性能的缘故，只开了16个线程进行并发，测试结果可能偏差较大。但是仍然可以总结出一些结论：
1、关闭事务(即autocommit=1)的情况下，通过maxscale访问方式性能远高于直连数据库方式
2、混合读写的情况下，通过maxscale访问方式有一定的性能损耗。
mysql maxscale mha_maxscale配合MHA搭建读写分离的高可用架构（基于GTID replication主从架构 mysql5.6）...
