MySQL5.7一主两从MHA自动切换
这篇文章主要讲解了“MySQL5.7一主两从MHA自动切换”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL5.7一主两从MHA自动切换”吧！
数据库架构：一主两从
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
一、master添加vip
/sbin/ifconfigenp0s3:1192.168.8.88/24
ifconfigenp0s3:flags=4163<UP,BROADCAST,RUNNING,MULTICAST>mtu1500inet192.168.8.57netmask255.255.255.0broadcast192.168.8.255inet6fe80::5198:593b:cdc5:1f90prefixlen64scopeid0x20<link>ether08:00:27:c0:45:0dtxqueuelen1000(Ethernet)RXpackets72386bytes9442794(9.0MiB)RXerrors0dropped0overruns0frame0TXpackets24221bytes2963104(2.8MiB)TXerrors0dropped0overruns0carrier0collisions0enp0s3:1:flags=4163<UP,BROADCAST,RUNNING,MULTICAST>mtu1500inet192.168.8.88netmask255.255.255.0broadcast192.168.8.255ether08:00:27:c0:45:0dtxqueuelen1000(Ethernet)lo:flags=73<UP,LOOPBACK,RUNNING>mtu65536inet127.0.0.1netmask255.0.0.0inet6::1prefixlen128scopeid0x10<host>looptxqueuelen1000(LocalLoopback)RXpackets84bytes9492(9.2KiB)RXerrors0dropped0overruns0frame0TXpackets84bytes9492(9.2KiB)TXerrors0dropped0overruns0carrier0collisions0virbr0:flags=4099<UP,BROADCAST,MULTICAST>mtu1500inet192.168.122.1netmask255.255.255.0broadcast192.168.122.255ether52:54:00:f4:55:bbtxqueuelen1000(Ethernet)RXpackets0bytes0(0.0B)RXerrors0dropped0overruns0frame0TXpackets0bytes0(0.0B)TXerrors0dropped0overruns0carrier0collisions0
二、failover 自动切换测试
1.手动停止master的MySQL进程
mysqladmin-uroot-pmysqlshutdown
2.查看manager日志
[root@managerbin]#tail-f/var/log/masterha/app1/manager.logThuOct2519:34:532018-[warning]GoterroronMySQLselectping:2006(MySQLserverhasgoneaway)ThuOct2519:34:532018-[info]ExecutingSSHcheckscript:exit0ThuOct2519:34:532018-[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=SELECTThuOct2519:34:532018-[info]HealthCheck:SSHto192.168.8.57isreachable.Monitoringserver192.168.8.58isreachable,Masterisnotreachablefrom192.168.8.58.OK.ThuOct2519:34:542018-[warning]GoterroronMySQLconnect:2003(Can'tconnecttoMySQLserveron'192.168.8.57'(111))ThuOct2519:34:542018-[warning]Connectionfailed2time(s)..Monitoringserver192.168.8.59isreachable,Masterisnotreachablefrom192.168.8.59.OK.ThuOct2519:34:542018-[info]Masterisnotreachablefromallothermonitoringservers.Failovershouldstart.ThuOct2519:34:552018-[warning]GoterroronMySQLconnect:2003(Can'tconnecttoMySQLserveron'192.168.8.57'(111))ThuOct2519:34:552018-[warning]Connectionfailed3time(s)..ThuOct2519:34:562018-[warning]GoterroronMySQLconnect:2003(Can'tconnecttoMySQLserveron'192.168.8.57'(111))ThuOct2519:34:562018-[warning]Connectionfailed4time(s)..ThuOct2519:34:562018-[warning]Masterisnotreachablefromhealthchecker!ThuOct2519:34:562018-[warning]Master192.168.8.57(192.168.8.57:3306)isnotreachable!ThuOct2519:34:562018-[warning]SSHisreachable.ThuOct2519:34:562018-[info]Connectingtoamasterserverfailed.Readingconfigurationfile/etc/masterha_default.cnfand/etc/masterha/app1.cnfagain,andtryingtoconnecttoallserverstocheckserverstatus..ThuOct2519:34:562018-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping.ThuOct2519:34:562018-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf..ThuOct2519:34:562018-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf..ThuOct2519:34:572018-[info]GTIDfailovermode=1ThuOct2519:34:572018-[info]DeadServers:ThuOct2519:34:572018-[info]192.168.8.57(192.168.8.57:3306)ThuOct2519:34:572018-[info]AliveServers:ThuOct2519:34:572018-[info]192.168.8.58(192.168.8.58:3306)ThuOct2519:34:572018-[info]192.168.8.59(192.168.8.59:3306)ThuOct2519:34:572018-[info]AliveSlaves:ThuOct2519:34:572018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:572018-[info]GTIDONThuOct2519:34:572018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:572018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2519:34:572018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:572018-[info]GTIDONThuOct2519:34:572018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:572018-[info]Checkingslaveconfigurations..ThuOct2519:34:572018-[info]Checkingreplicationfilteringsettings..ThuOct2519:34:572018-[info]Replicationfilteringcheckok.ThuOct2519:34:572018-[info]Masterisdown!ThuOct2519:34:572018-[info]Terminatingmonitoringscript.ThuOct2519:34:572018-[info]Gotexitcode20(Masterdead).ThuOct2519:34:572018-[info]MHA::MasterFailoverversion0.58.ThuOct2519:34:572018-[info]Startingmasterfailover.ThuOct2519:34:572018-[info]ThuOct2519:34:572018-[info]*Phase1:ConfigurationCheckPhase..ThuOct2519:34:572018-[info]ThuOct2519:34:582018-[info]GTIDfailovermode=1ThuOct2519:34:582018-[info]DeadServers:ThuOct2519:34:582018-[info]192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]CheckingmasterreachabilityviaMySQL(doublecheck)...ThuOct2519:34:582018-[info]ok.ThuOct2519:34:582018-[info]AliveServers:ThuOct2519:34:582018-[info]192.168.8.58(192.168.8.58:3306)ThuOct2519:34:582018-[info]192.168.8.59(192.168.8.59:3306)ThuOct2519:34:582018-[info]AliveSlaves:ThuOct2519:34:582018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2519:34:582018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]StartingGTIDbasedfailover.ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]**Phase1:ConfigurationCheckPhasecompleted.ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase2:DeadMasterShutdownPhase..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]Forcingshutdownsothatapplicationsneverconnecttothecurrentmaster..ThuOct2519:34:582018-[info]ExecutingmasterIPdeactivationscript:ThuOct2519:34:582018-[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=rootThuOct2519:34:582018-[info]done.ThuOct2519:34:582018-[warning]shutdown_scriptisnotset.Skippingexplicitshuttingdownofthedeadmaster.ThuOct2519:34:582018-[info]*Phase2:DeadMasterShutdownPhasecompleted.ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase3:MasterRecoveryPhase..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase3.1:GettingLatestSlavesPhase..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]Thelatestbinarylogfile/positiononallslavesismysql-bin.000010:707ThuOct2519:34:582018-[info]RetrievedGtidSet:a92f70a4-d5ea-11e8-af28-080027c0450d:7-9ThuOct2519:34:582018-[info]Latestslaves(Slavesthatreceivedrelaylogfilestothelatest):ThuOct2519:34:582018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2519:34:582018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]Theoldestbinarylogfile/positiononallslavesismysql-bin.000010:707ThuOct2519:34:582018-[info]RetrievedGtidSet:a92f70a4-d5ea-11e8-af28-080027c0450d:7-9ThuOct2519:34:582018-[info]Oldestslaves:ThuOct2519:34:582018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2519:34:582018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase3.3:DeterminingNewMasterPhase..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]Searchingnewmasterfromslaves..ThuOct2519:34:582018-[info]Candidatemastersfromtheconfigurationfile:ThuOct2519:34:582018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2519:34:582018-[info]GTIDONThuOct2519:34:582018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)ThuOct2519:34:582018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2519:34:582018-[info]Non-candidatemasters:ThuOct2519:34:582018-[info]Searchingfromcandidate_masterslaveswhichhavereceivedthelatestrelaylogevents..ThuOct2519:34:582018-[info]Newmasteris192.168.8.58(192.168.8.58:3306)ThuOct2519:34:582018-[info]Startingmasterfailover..ThuOct2519:34:582018-[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)ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase3.3:NewMasterRecoveryPhase..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]Waitingalllogstobeapplied..ThuOct2519:34:582018-[info]done.ThuOct2519:34:582018-[info]Gettingnewmaster'sbinlognameandposition..ThuOct2519:34:582018-[info]mysql-bin.000010:747ThuOct2519:34:582018-[info]Allotherslavesshouldstartreplicationfromhere.Statementshouldbe:CHANGEMASTERTOMASTER_HOST='192.168.8.58',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='xxx';ThuOct2519:34:582018-[info]MasterRecoverysucceeded.File:Pos:Exec_Gtid_Set:mysql-bin.000010,747,a92f70a4-d5ea-11e8-af28-080027c0450d:1-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-4ThuOct2519:34:582018-[info]ExecutingmasterIPactivatescript:ThuOct2519:34:582018-[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..ThuOct2519:34:582018-[error][/usr/lib/perl5/vendor_perl/MHA/MasterFailover.pm,ln1612]FailedtoactivatemasterIPaddressfor192.168.8.58(192.168.8.58:3306)withreturncode10:0ThuOct2519:34:582018-[warning]Proceeding.ThuOct2519:34:582018-[info]**Finishedmasterrecoverysuccessfully.ThuOct2519:34:582018-[info]*Phase3:MasterRecoveryPhasecompleted.ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase4:SlavesRecoveryPhase..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]*Phase4.1:StartingSlavesinparallel..ThuOct2519:34:582018-[info]ThuOct2519:34:582018-[info]--Slaverecoveryonhost192.168.8.59(192.168.8.59:3306)started,pid:20757.Checktmplog/var/log/masterha/app1/192.168.8.59_3306_20181025193457.logifittakestime..ThuOct2519:34:592018-[info]ThuOct2519:34:592018-[info]Logmessagesfrom192.168.8.59...ThuOct2519:34:592018-[info]ThuOct2519:34:582018-[info]Resettingslave192.168.8.59(192.168.8.59:3306)andstartingreplicationfromthenewmaster192.168.8.58(192.168.8.58:3306)..ThuOct2519:34:582018-[info]ExecutedCHANGEMASTER.ThuOct2519:34:582018-[info]Slavestarted.ThuOct2519:34:582018-[info]gtid_wait(a92f70a4-d5ea-11e8-af28-080027c0450d:1-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-4)completedon192.168.8.59(192.168.8.59:3306).Executed0events.ThuOct2519:34:592018-[info]Endoflogmessagesfrom192.168.8.59.ThuOct2519:34:592018-[info]--Slaveonhost192.168.8.59(192.168.8.59:3306)started.ThuOct2519:34:592018-[info]Allnewslaveserversrecoveredsuccessfully.ThuOct2519:34:592018-[info]ThuOct2519:34:592018-[info]*Phase5:Newmastercleanupphase..ThuOct2519:34:592018-[info]ThuOct2519:34:592018-[info]Resettingslaveinfoonthenewmaster..ThuOct2519:35:002018-[info]192.168.8.58:Resettingslaveinfosucceeded.ThuOct2519:35:002018-[info]Masterfailoverto192.168.8.58(192.168.8.58:3306)completedsuccessfully.ThuOct2519:35:002018-[info]Deletedserver1entryfrom/etc/masterha/app1.cnf.ThuOct2519:35:002018-[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.ThuOct2519:35:002018-[info]Sendingmail..
日志中显示主库已经切换，新的主库为192.168.8.58
3.查看主机192.168.8.58和192.168.8.59的信息
192.168.8.58
mysql>showslavestatus\GEmptyset(0.00sec)mysql>showmasterstatus\G***************************1.row***************************File:mysql-bin.000010Position:747Binlog_Do_DB:Binlog_Ignore_DB:Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450d:1-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-41rowinset(0.00sec)
mysql>showvariableslike'read_only';+---------------+-------+|Variable_name|Value|+---------------+-------+|read_only|OFF|+---------------+-------+
192.168.8.59
mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.58Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000010Read_Master_Log_Pos:747Relay_Log_File:slave2-relay-bin.000002Relay_Log_Pos:414Relay_Master_Log_File:mysql-bin.000010Slave_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:747Relay_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-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-4Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:
mysql>showvariableslike'read_only';+---------------+-------+|Variable_name|Value|+---------------+-------+|read_only|ON|+---------------+-------+
可以看到192.168.8.58变成了新的主库，read_only变成了OFF，192.168.8.59作为192.168.8.58的从库，read_only依然为ON。
4.主从数据测试
在192.168.8.58创建表t7
mysql>createtablet7(idint(6));QueryOK,0rowsaffected(0.04sec)mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7|+----------------+
在192.168.8.59进行查看
mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7|+----------------+
可以看到新的主从复制正常。
5.切换完之后你会发现 MHA Manager 监控程序会自动死掉，官网有如下解释和解决方式：
Running MHA Manager from daemontools
Currently MHA Manager process does not run as a daemon. if failover completed
successfully or the master process was killed by accident, the manager stops
working. to run as a daemon, daemontool. or any external daemon program
can be used. Here is an example to run from daemontools.
解决方法：
vi/usr/local/bin/manager_status_check#!/bin/bashwhile:doMGECHECK=`ps-ef|grepmasterha_manager|egrep-vgrep|wc-l`if[$MGECHECK-eq0];then/usr/local/bin/masterha_start.shelseecho"MHAmanagerstart"fisleep5done
chmodu+x/usr/local/bin/manager_status_checknohup/usr/local/bin/manager_status_check&
写入/etc/rc.d/rc.local 开机自动启动
echo"nohup/usr/local/bin/manager_status_check&">>/etc/rc.d/rc.local
6.原主库加入MHA
首先修改旧主库 192.168.8.57 的参数 my.cnf，打开从库相关的参数，要重启.
方法一：由于有GTID，我们可以直接就 change master 切换过去
对比一下数据
192.168.8.57
mysql>usetestReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6|+----------------+
192.168.8.58
mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7|+----------------+
旧主库直接 change master to
mysql>changemasterto->master_host='192.168.8.58',->master_port=3306,->master_user='repl',->master_password='mysql',->master_auto_position=1;QueryOK,0rowsaffected,2warnings(0.05sec)
查看192.168.8.57slave进程状态
mysql>startslave;QueryOK,0rowsaffected(0.07sec)mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.58Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000010Read_Master_Log_Pos:912Relay_Log_File:master-relay-bin.000007Relay_Log_Pos:619Relay_Master_Log_File:mysql-bin.000010Slave_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:912Relay_Log_Space:1080Until_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:1-5Executed_Gtid_Set:a92f70a4-d5ea-11e8-af28-080027c0450d:1-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-5Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)
可以看到slave进程已经启动，新的主库为192.168.8.58
设置192.168.8.57参数read_only=1
mysql>setglobalread_only=1;QueryOK,0rowsaffected(0.00sec)mysql>showvariableslike'read_only';+---------------+-------+|Variable_name|Value|+---------------+-------+|read_only|ON|+---------------+-------+
查看192.168.8.57数据复制情况
mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7|+----------------+
可以看到此时t7已经复制成功。
方法二：
若修复原主库耗费时间较长，建议重新初始化192.168.8.57从库，而非使用change master to
7.修改MHA配置文件
此时发现MHA配置文件app1.cnf出现缺失
[serverdefault]manager_log=/var/log/masterha/app1/manager.logmanager_workdir=/var/log/masterha/app1master_binlog_dir=/mysql/datamaster_ip_failover_script=/usr/local/bin/master_ip_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_changepassword=mysqlping_interval=1remote_workdir=/tmprepl_password=mysqlrepl_user=replreport_script=/usr/local/bin/send_reportsecondary_check_script=/usr/local/bin/masterha_secondary_check-s192.168.8.58-s192.168.8.59shutdown_script=""ssh_user=rootuser=root[server2]candidate_master=1check_repl_delay=0hostname=192.168.8.58port=3306[server3]hostname=192.168.8.59port=3306
完善之后如下：
[serverdefault]manager_log=/var/log/masterha/app1/manager.logmanager_workdir=/var/log/masterha/app1master_binlog_dir=/mysql/datamaster_ip_failover_script=/usr/local/bin/master_ip_failovermaster_ip_online_change_script=/usr/local/bin/master_ip_online_changepassword=mysqlping_interval=1remote_workdir=/tmprepl_password=mysqlrepl_user=replreport_script=/usr/local/bin/send_reportsecondary_check_script=/usr/local/bin/masterha_secondary_check-s192.168.8.57-s192.168.8.59shutdown_script=""ssh_user=rootuser=root[server1]candidate_master=1check_repl_delay=0hostname=192.168.8.57port=3306[server2]hostname=192.168.8.58port=3306[server3]hostname=192.168.8.59port=3306
8.重启监控程序
MHA复制健康检查
./masterha_check_repl--conf=/etc/masterha/app1.cnfThuOct2520:32:042018-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping.ThuOct2520:32:042018-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf..ThuOct2520:32:042018-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf..ThuOct2520:32:042018-[info]MHA::MasterMonitorversion0.58.ThuOct2520:32:052018-[info]GTIDfailovermode=1ThuOct2520:32:052018-[info]DeadServers:ThuOct2520:32:052018-[info]AliveServers:ThuOct2520:32:052018-[info]192.168.8.57(192.168.8.57:3306)ThuOct2520:32:052018-[info]192.168.8.58(192.168.8.58:3306)ThuOct2520:32:052018-[info]192.168.8.59(192.168.8.59:3306)ThuOct2520:32:052018-[info]AliveSlaves:ThuOct2520:32:052018-[info]192.168.8.57(192.168.8.57:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2520:32:052018-[info]GTIDONThuOct2520:32:052018-[info]Replicatingfrom192.168.8.58(192.168.8.58:3306)ThuOct2520:32:052018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2520:32:052018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2520:32:052018-[info]GTIDONThuOct2520:32:052018-[info]Replicatingfrom192.168.8.58(192.168.8.58:3306)ThuOct2520:32:052018-[info]CurrentAliveMaster:192.168.8.58(192.168.8.58:3306)ThuOct2520:32:052018-[info]Checkingslaveconfigurations..ThuOct2520:32:052018-[info]Checkingreplicationfilteringsettings..ThuOct2520:32:052018-[info]binlog_do_db=,binlog_ignore_db=ThuOct2520:32:052018-[info]Replicationfilteringcheckok.ThuOct2520:32:052018-[info]GTID(withauto-pos)issupported.SkippingallSSHandNodepackagechecking.ThuOct2520:32:052018-[info]CheckingSSHpublickeyauthenticationsettingsonthecurrentmaster..ThuOct2520:32:052018-[info]HealthCheck:SSHto192.168.8.58isreachable.ThuOct2520:32:052018-[info]192.168.8.58(192.168.8.58:3306)(currentmaster)+--192.168.8.57(192.168.8.57:3306)+--192.168.8.59(192.168.8.59:3306)ThuOct2520:32:052018-[info]Checkingreplicationhealthon192.168.8.57..ThuOct2520:32:052018-[info]ok.ThuOct2520:32:052018-[info]Checkingreplicationhealthon192.168.8.59..ThuOct2520:32:052018-[info]ok.ThuOct2520:32:052018-[info]Checkingmaster_ip_failover_scriptstatus:ThuOct2520:32:052018-[info]/usr/local/bin/master_ip_failover--command=status--ssh_user=root--orig_master_host=192.168.8.58--orig_master_ip=192.168.8.58--orig_master_port=3306ThuOct2520:32:062018-[info]OK.ThuOct2520:32:062018-[warning]shutdown_scriptisnotdefined.ThuOct2520:32:062018-[info]Gotexitcode0(Notmasterdead).MySQLReplicationHealthisOK.
启动MHA监控程序
./masterha_start.sh
查看manager日志
ThuOct2520:33:072018-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping.ThuOct2520:33:072018-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf..ThuOct2520:33:072018-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf..ThuOct2520:33:072018-[info]MHA::MasterMonitorversion0.58.ThuOct2520:33:092018-[info]GTIDfailovermode=1ThuOct2520:33:092018-[info]DeadServers:ThuOct2520:33:092018-[info]AliveServers:ThuOct2520:33:092018-[info]192.168.8.57(192.168.8.57:3306)ThuOct2520:33:092018-[info]192.168.8.58(192.168.8.58:3306)ThuOct2520:33:092018-[info]192.168.8.59(192.168.8.59:3306)ThuOct2520:33:092018-[info]AliveSlaves:ThuOct2520:33:092018-[info]192.168.8.57(192.168.8.57:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2520:33:092018-[info]GTIDONThuOct2520:33:092018-[info]Replicatingfrom192.168.8.58(192.168.8.58:3306)ThuOct2520:33:092018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)ThuOct2520:33:092018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledThuOct2520:33:092018-[info]GTIDONThuOct2520:33:092018-[info]Replicatingfrom192.168.8.58(192.168.8.58:3306)ThuOct2520:33:092018-[info]CurrentAliveMaster:192.168.8.58(192.168.8.58:3306)ThuOct2520:33:092018-[info]Checkingslaveconfigurations..ThuOct2520:33:092018-[info]Checkingreplicationfilteringsettings..ThuOct2520:33:092018-[info]binlog_do_db=,binlog_ignore_db=ThuOct2520:33:092018-[info]Replicationfilteringcheckok.ThuOct2520:33:092018-[info]GTID(withauto-pos)issupported.SkippingallSSHandNodepackagechecking.ThuOct2520:33:092018-[info]CheckingSSHpublickeyauthenticationsettingsonthecurrentmaster..ThuOct2520:33:092018-[info]HealthCheck:SSHto192.168.8.58isreachable.ThuOct2520:33:092018-[info]192.168.8.58(192.168.8.58:3306)(currentmaster)+--192.168.8.57(192.168.8.57:3306)+--192.168.8.59(192.168.8.59:3306)ThuOct2520:33:092018-[info]Checkingmaster_ip_failover_scriptstatus:ThuOct2520:33:092018-[info]/usr/local/bin/master_ip_failover--command=status--ssh_user=root--orig_master_host=192.168.8.58--orig_master_ip=192.168.8.58--orig_master_port=3306ThuOct2520:33:092018-[info]OK.ThuOct2520:33:092018-[warning]shutdown_scriptisnotdefined.ThuOct2520:33:092018-[info]Setmasterpinginterval1seconds.ThuOct2520:33:092018-[info]Setsecondarycheckscript:/usr/local/bin/masterha_secondary_check-s192.168.8.57-s192.168.8.59ThuOct2520:33:092018-[info]Startingpinghealthcheckon192.168.8.58(192.168.8.58:3306)..ThuOct2520:33:092018-[info]Ping(SELECT)succeeded,waitinguntilMySQLdoesn'trespond..
到此，自动切换及恢复工作测试完毕。
感谢各位的阅读，以上就是“MySQL5.7一主两从MHA自动切换”的内容了，经过本文的学习后，相信大家对MySQL5.7一主两从MHA自动切换这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
