MySQL5.7+MHA+Keepalived failover自动切换
数据库架构：一主两从
master：192.168.8.57
slave1：192.168.8.58
slave2：192.168.8.59
manager：192.168.8.60
MHA工具包：
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58.tar.gz
keepalived-1.4.5.tar.gz
一、环境配置过程如下：
http://blog.itpub.net/30135314/viewspace-2217762/
二、切换测试
1.在192.168.8.57关闭MySQL进程
1mysqladmin-uroot-pmysqlshutdown2.查看MHA manager日志
MonOct2913:55:182018-[warning]GoterroronMySQLselectping:2006(MySQLserverhasgoneaway)MonOct2913:55:182018-[info]Executingsecondarynetworkcheckscript:/usr/local/bin/masterha_secondary_check-s192.168.8.58-s192.168.8.59--user=root--master_host=192.168.8.57--master_ip=192.168.8.57--master_port=3306--master_user=root--master_password=mysql--ping_type=SELECTMonOct2913:55:182018-[info]ExecutingSSHcheckscript:exitMonOct2913:55:182018-[info]HealthCheck:SSHto192.168.8.57isreachable.Monitoringserver192.168.8.58isreachable,Masterisnotreachablefrom192.168.8.58.OK.MonOct2913:55:192018-[warning]GoterroronMySQLconnect:2003(Can'tconnecttoMySQLserveron'192.168.8.57'(111))MonOct2913:55:192018-[warning]Connectionfailed2time(s)..Monitoringserver192.168.8.59isreachable,Masterisnotreachablefrom192.168.8.59.OK.MonOct2913:55:192018-[info]Masterisnotreachablefromallothermonitoringservers.Failovershouldstart.MonOct2913:55:202018-[warning]GoterroronMySQLconnect:2003(Can'tconnecttoMySQLserveron'192.168.8.57'(111))MonOct2913:55:202018-[warning]Connectionfailed3time(s)..MonOct2913:55:212018-[warning]GoterroronMySQLconnect:2003(Can'tconnecttoMySQLserveron'192.168.8.57'(111))MonOct2913:55:212018-[warning]Connectionfailed4time(s)..MonOct2913:55:212018-[warning]Masterisnotreachablefromhealthchecker!MonOct2913:55:212018-[warning]Master192.168.8.57(192.168.8.57:3306)isnotreachable!MonOct2913:55:212018-[warning]SSHisreachable.MonOct2913:55:212018-[info]Connectingtoamasterserverfailed.Readingconfigurationfile/etc/masterha_default.cnfand/etc/masterha/app1.cnfagain,andtryingtoconnecttoallserverstocheckserverstatus..MonOct2913:55:212018-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping.MonOct2913:55:212018-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf..MonOct2913:55:212018-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf..MonOct2913:55:222018-[info]GTIDfailovermode=1MonOct2913:55:222018-[info]DeadServers:MonOct2913:55:222018-[info]192.168.8.57(192.168.8.57:3306)MonOct2913:55:222018-[info]AliveServers:MonOct2913:55:222018-[info]192.168.8.58(192.168.8.58:3306)MonOct2913:55:222018-[info]192.168.8.59(192.168.8.59:3306)MonOct2913:55:222018-[info]AliveSlaves:MonOct2913:55:222018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:222018-[info]GTIDONMonOct2913:55:222018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:222018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)MonOct2913:55:222018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:222018-[info]GTIDONMonOct2913:55:222018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:222018-[info]Checkingslaveconfigurations..MonOct2913:55:222018-[info]read_only=1isnotsetonslave192.168.8.58(192.168.8.58:3306).MonOct2913:55:222018-[info]read_only=1isnotsetonslave192.168.8.59(192.168.8.59:3306).MonOct2913:55:222018-[info]Checkingreplicationfilteringsettings..MonOct2913:55:222018-[info]Replicationfilteringcheckok.MonOct2913:55:222018-[info]Masterisdown!MonOct2913:55:222018-[info]Terminatingmonitoringscript.MonOct2913:55:222018-[info]Gotexitcode20(Masterdead).MonOct2913:55:222018-[info]MHA::MasterFailoverversion0.58.MonOct2913:55:222018-[info]Startingmasterfailover.MonOct2913:55:222018-[info]MonOct2913:55:222018-[info]*Phase1:ConfigurationCheckPhase..MonOct2913:55:222018-[info]MonOct2913:55:232018-[info]GTIDfailovermode=1MonOct2913:55:232018-[info]DeadServers:MonOct2913:55:232018-[info]192.168.8.57(192.168.8.57:3306)MonOct2913:55:232018-[info]CheckingmasterreachabilityviaMySQL(doublecheck)...MonOct2913:55:232018-[info]ok.MonOct2913:55:232018-[info]AliveServers:MonOct2913:55:232018-[info]192.168.8.58(192.168.8.58:3306)MonOct2913:55:232018-[info]192.168.8.59(192.168.8.59:3306)MonOct2913:55:232018-[info]AliveSlaves:MonOct2913:55:232018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:232018-[info]GTIDONMonOct2913:55:232018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:232018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)MonOct2913:55:232018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:232018-[info]GTIDONMonOct2913:55:232018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:232018-[info]StartingGTIDbasedfailover.MonOct2913:55:232018-[info]MonOct2913:55:232018-[info]**Phase1:ConfigurationCheckPhasecompleted.MonOct2913:55:232018-[info]MonOct2913:55:232018-[info]*Phase2:DeadMasterShutdownPhase..MonOct2913:55:232018-[info]MonOct2913:55:232018-[info]Forcingshutdownsothatapplicationsneverconnecttothecurrentmaster..MonOct2913:55:232018-[info]ExecutingmasterIPdeactivationscript:MonOct2913:55:232018-[info]/usr/local/bin/master_ip_failover--orig_master_host=192.168.8.57--orig_master_ip=192.168.8.57--orig_master_port=3306--command=stopssh--ssh_user=rootMonOct2913:55:242018-[info]done.MonOct2913:55:242018-[warning]shutdown_scriptisnotset.Skippingexplicitshuttingdownofthedeadmaster.MonOct2913:55:242018-[info]*Phase2:DeadMasterShutdownPhasecompleted.MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]*Phase3:MasterRecoveryPhase..MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]*Phase3.1:GettingLatestSlavesPhase..MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]Thelatestbinarylogfile/positiononallslavesismysql-bin.000020:375MonOct2913:55:242018-[info]RetrievedGtidSet:a92f70a4-d5ea-11e8-af28-080027c0450d:11-13MonOct2913:55:242018-[info]Latestslaves(Slavesthatreceivedrelaylogfilestothelatest):MonOct2913:55:242018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:242018-[info]GTIDONMonOct2913:55:242018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:242018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)MonOct2913:55:242018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:242018-[info]GTIDONMonOct2913:55:242018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:242018-[info]Theoldestbinarylogfile/positiononallslavesismysql-bin.000020:375MonOct2913:55:242018-[info]RetrievedGtidSet:a92f70a4-d5ea-11e8-af28-080027c0450d:11-13MonOct2913:55:242018-[info]Oldestslaves:MonOct2913:55:242018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:242018-[info]GTIDONMonOct2913:55:242018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:242018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)MonOct2913:55:242018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:242018-[info]GTIDONMonOct2913:55:242018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]*Phase3.3:DeterminingNewMasterPhase..MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]Searchingnewmasterfromslaves..MonOct2913:55:242018-[info]Candidatemastersfromtheconfigurationfile:MonOct2913:55:242018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledMonOct2913:55:242018-[info]GTIDONMonOct2913:55:242018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)MonOct2913:55:242018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)MonOct2913:55:242018-[info]Non-candidatemasters:MonOct2913:55:242018-[info]Searchingfromcandidate_masterslaveswhichhavereceivedthelatestrelaylogevents..MonOct2913:55:242018-[info]Newmasteris192.168.8.58(192.168.8.58:3306)MonOct2913:55:242018-[info]Startingmasterfailover..MonOct2913:55:242018-[info]From:192.168.8.57(192.168.8.57:3306)(currentmaster)+--192.168.8.58(192.168.8.58:3306)+--192.168.8.59(192.168.8.59:3306)To:192.168.8.58(192.168.8.58:3306)(newmaster)+--192.168.8.59(192.168.8.59:3306)MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]*Phase3.3:NewMasterRecoveryPhase..MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]Waitingalllogstobeapplied..MonOct2913:55:242018-[info]done.MonOct2913:55:242018-[info]Gettingnewmaster'sbinlognameandposition..MonOct2913:55:242018-[info]mysql-bin.000021:415MonOct2913:55:242018-[info]Allotherslavesshouldstartreplicationfromhere.Statementshouldbe:CHANGEMASTERTOMASTER_HOST='192.168.8.58',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='xxx';MonOct2913:55:242018-[info]MasterRecoverysucceeded.File:Pos:Exec_Gtid_Set:mysql-bin.000021,415,a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-7MonOct2913:55:242018-[info]ExecutingmasterIPactivatescript:MonOct2913:55:242018-[info]/usr/local/bin/master_ip_failover--command=start--ssh_user=root--orig_master_host=192.168.8.57--orig_master_ip=192.168.8.57--orig_master_port=3306--new_master_host=192.168.8.58--new_master_ip=192.168.8.58--new_master_port=3306--new_master_user='root'--new_master_password=xxxUndefinedsubroutine&main::FIXME_xxx_create_usercalledat/usr/local/bin/master_ip_failoverline94.Setread_only=0onthenewmaster.Creatingappuseronthenewmaster..MonOct2913:55:242018-[error][/usr/lib/perl5/vendor_perl/MHA/MasterFailover.pm,ln1612]FailedtoactivatemasterIPaddressfor192.168.8.58(192.168.8.58:3306)withreturncode10:0MonOct2913:55:242018-[warning]Proceeding.MonOct2913:55:242018-[info]**Finishedmasterrecoverysuccessfully.MonOct2913:55:242018-[info]*Phase3:MasterRecoveryPhasecompleted.MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]*Phase4:SlavesRecoveryPhase..MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]*Phase4.1:StartingSlavesinparallel..MonOct2913:55:242018-[info]MonOct2913:55:242018-[info]--Slaverecoveryonhost192.168.8.59(192.168.8.59:3306)started,pid:7627.Checktmplog/var/log/masterha/app1/192.168.8.59_3306_20181029135522.logifittakestime..MonOct2913:55:252018-[info]MonOct2913:55:252018-[info]Logmessagesfrom192.168.8.59...MonOct2913:55:252018-[info]MonOct2913:55:242018-[info]Resettingslave192.168.8.59(192.168.8.59:3306)andstartingreplicationfromthenewmaster192.168.8.58(192.168.8.58:3306)..MonOct2913:55:242018-[info]ExecutedCHANGEMASTER.MonOct2913:55:242018-[info]Slavestarted.MonOct2913:55:242018-[info]gtid_wait(a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-7)completedon192.168.8.59(192.168.8.59:3306).Executed0events.MonOct2913:55:252018-[info]Endoflogmessagesfrom192.168.8.59.MonOct2913:55:252018-[info]--Slaveonhost192.168.8.59(192.168.8.59:3306)started.MonOct2913:55:252018-[info]Allnewslaveserversrecoveredsuccessfully.MonOct2913:55:252018-[info]MonOct2913:55:252018-[info]*Phase5:Newmastercleanupphase..MonOct2913:55:252018-[info]MonOct2913:55:252018-[info]Resettingslaveinfoonthenewmaster..MonOct2913:55:252018-[info]192.168.8.58:Resettingslaveinfosucceeded.MonOct2913:55:252018-[info]Masterfailoverto192.168.8.58(192.168.8.58:3306)completedsuccessfully.MonOct2913:55:252018-[info]Deletedserver1entryfrom/etc/masterha/app1.cnf.MonOct2913:55:252018-[info]-----FailoverReport-----app1:MySQLMasterfailover192.168.8.57(192.168.8.57:3306)to192.168.8.58(192.168.8.58:3306)succeededMaster192.168.8.57(192.168.8.57:3306)isdown!CheckMHAManagerlogsatmanager:/var/log/masterha/app1/manager.logfordetails.Startedautomated(non-interactive)failover.InvalidatedmasterIPaddresson192.168.8.57(192.168.8.57:3306)Selected192.168.8.58(192.168.8.58:3306)asanewmaster.192.168.8.58(192.168.8.58:3306):OK:Applyingalllogssucceeded.FailedtoactivatemasterIPaddressfor192.168.8.58(192.168.8.58:3306)withreturncode10:0192.168.8.59(192.168.8.59:3306):OK:Slavestarted,replicatingfrom192.168.8.58(192.168.8.58:3306)192.168.8.58(192.168.8.58:3306):Resettingslaveinfosucceeded.Masterfailoverto192.168.8.58(192.168.8.58:3306)completedsuccessfully.MonOct2913:55:252018-[info]Sendingmail..Unknownoption:conf可以看到192.168.8.57上MySQL进程宕掉之后，新主库切至192.168.8.58，和192.168.8.59形成新的主从环境。
3.查看VIP
123456789101112131415161718192021221:lo:<LOOPBACK,UP,LOWER_UP>mtu65536qdiscnoqueuestateUNKNOWNgroupdefaultqlen1000link/loopback00:00:00:00:00:00brd00:00:00:00:00:00inet127.0.0.1/8scopehostlovalid_lftforeverpreferred_lftforeverinet6::1/128scopehostvalid_lftforeverpreferred_lftforever2:enp0s3:<BROADCAST,MULTICAST,UP,LOWER_UP>mtu1500qdiscpfifo_faststateUPgroupdefaultqlen1000link/ether08:00:27:4d:70:17brdff:ff:ff:ff:ff:ffinet192.168.8.58/24brd192.168.8.255scopeglobalnoprefixrouteenp0s3valid_lftforeverpreferred_lftforeverinet192.168.8.88/24scopeglobalsecondaryenp0s3valid_lftforeverpreferred_lftforeverinet6fe80::6a31:3e92:8b6f:83c0/64scopelinknoprefixroutevalid_lftforeverpreferred_lftforeverinet6fe80::5198:593b:cdc5:1f90/64scopelinktentativenoprefixroutedadfailedvalid_lftforeverpreferred_lftforever3:virbr0:<NO-CARRIER,BROADCAST,MULTICAST,UP>mtu1500qdiscnoqueuestateDOWNgroupdefaultqlen1000link/ether52:54:00:f4:55:bbbrdff:ff:ff:ff:ff:ffinet192.168.122.1/24brd192.168.122.255scopeglobalvirbr0valid_lftforeverpreferred_lftforever4:virbr0-nic:<BROADCAST,MULTICAST>mtu1500qdiscpfifo_fastmastervirbr0stateDOWNgroupdefaultqlen1000link/ether52:54:00:f4:55:bbbrdff:ff:ff:ff:ff:ffVIP192.168.8.88/24已经飘至192.168.8.58机器。
4.查看slave进程
192.168.8.58
12mysql>showslavestatus\GEmptyset(0.00sec)192.168.8.59
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.58Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000021Read_Master_Log_Pos:415Relay_Log_File:slave2-relay-bin.000002Relay_Log_Pos:414Relay_Master_Log_File:mysql-bin.000021Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:415Relay_Log_Space:622Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:58Master_UUID:a92f70a4-d5ea-11e8-af28-080027c0450fMaster_Info_File:/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450b:1-4,a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-7Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:可以看到从库192.168.8.59的主库变成192.168.8.58
5.测试复制
192.168.8.58（主库）
mysql>createtablet10(idint(10));QueryOK,0rowsaffected(0.19sec)mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t10||t2||t3||t4||t5||t6||t7||t8||t9|+----------------+192.168.8.59（从库）
123456789101112131415mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t10||t2||t3||t4||t5||t6||t7||t8||t9|+----------------+新的主从数据复制正常。
三、将旧主库加入复制环境
修复完192.168.8.57之后，将此节点变成从库加入到环境当中，可以直接change master to
changemastertomaster_host='192.168.8.58',master_port=3306,master_user='repl',master_password='mysql',master_auto_position=1;12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364mysql>startslave;QueryOK,0rowsaffected(0.07sec)mysql>setglobalread_only=1;QueryOK,0rowsaffected(0.00sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.58Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000021Read_Master_Log_Pos:583Relay_Log_File:master-relay-bin.000002Relay_Log_Pos:582Relay_Master_Log_File:mysql-bin.000021Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:583Relay_Log_Space:830Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:58Master_UUID:a92f70a4-d5ea-11e8-af28-080027c0450fMaster_Info_File:/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450f:8Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-8Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:查看t10有没有复制到192.168.8.57
mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t10||t2||t3||t4||t5||t6||t7||t8||t9|+----------------+修改/etc/masterha/app1.cnf，将[server1]添加上
四、将复制还原到原始状态（主库为192.168.8.57）
1.重置复制
192.168.8.57
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162mysql>stopslave;QueryOK,0rowsaffected(0.01sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:Master_Host:192.168.8.58Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000021Read_Master_Log_Pos:583Relay_Log_File:master-relay-bin.000003Relay_Log_Pos:454Relay_Master_Log_File:mysql-bin.000021Slave_IO_Running:NoSlave_SQL_Running:NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:583Relay_Log_Space:1090Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:58Master_UUID:a92f70a4-d5ea-11e8-af28-080027c0450fMaster_Info_File:/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450f:8Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-8Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:192.168.8.58
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374mysql>resetslave;QueryOK,0rowsaffected(0.00sec)mysql>setglobalread_only=1;QueryOK,0rowsaffected(0.00sec)mysql>changemasterto->master_host='192.168.8.57',->master_port=3306,->master_user='repl',->master_password='mysql',->master_auto_position=1;QueryOK,0rowsaffected,2warnings(0.05sec)mysql>startslave;QueryOK,0rowsaffected(0.00sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.57Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000021Read_Master_Log_Pos:194Relay_Log_File:slave1-relay-bin.000002Relay_Log_Pos:367Relay_Master_Log_File:mysql-bin.000021Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:194Relay_Log_Space:615Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:57Master_UUID:a92f70a4-d5ea-11e8-af28-080027c0450dMaster_Info_File:/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-8Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)192.168.8.59
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677mysql>stopslave;QueryOK,0rowsaffected(0.01sec)mysql>resetslave;QueryOK,0rowsaffected(0.10sec)mysql>changemasterto->master_host='192.168.8.57',->master_port=3306,->master_user='repl',->master_password='mysql',->master_auto_position=1;QueryOK,0rowsaffected,2warnings(0.05sec)mysql>startslave;QueryOK,0rowsaffected(0.01sec)mysql>setglobalread_only=1;QueryOK,0rowsaffected(0.00sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.57Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000021Read_Master_Log_Pos:194Relay_Log_File:slave2-relay-bin.000002Relay_Log_Pos:367Relay_Master_Log_File:mysql-bin.000021Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:194Relay_Log_Space:575Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:57Master_UUID:a92f70a4-d5ea-11e8-af28-080027c0450dMaster_Info_File:/mysql/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450b:1-4,a92f70a4-d5ea-11e8-af28-080027c0450d:1-13,a92f70a4-d5ea-11e8-af28-080027c0450f:1-8Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)2.重启192.168.8.57和192.168.8.58上边keepalived进程，使VIP飘至192.168.8.57。
3.重启MHA manager进程并观察复制情况。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
