这篇文章主要介绍“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_ip_online_change内容

#!/usr/bin/envperl#Copyright(C)2011DeNACo.,Ltd.##Thisprogramisfreesoftware;youcanredistributeitand/ormodify#itunderthetermsoftheGNUGeneralPublicLicenseaspublishedby#theFreeSoftwareFoundation;eitherversion2oftheLicense,or#(atyouroption)anylaterversion.##Thisprogramisdistributedinthehopethatitwillbeuseful,#butWITHOUTANYWARRANTY;withouteventheimpliedwarrantyof#MERCHANTABILITYorFITNESSFORAPARTICULARPURPOSE.Seethe#GNUGeneralPublicLicenseformoredetails.##YoushouldhavereceivedacopyoftheGNUGeneralPublicLicense#alongwiththisprogram;ifnot,writetotheFreeSoftware#Foundation,Inc.,#51FranklinStreet,FifthFloor,Boston,MA02110-1301USAusestrict;usewarningsFATAL=>'all';useGetopt::Longqw(:configpass_through);usePod::Usage;useMHA::MasterMonitor;useMHA::MasterFailover;useMHA::MasterRotate;useMHA::ManagerConst;my$master_state="";my$help;my$version;$|=1;GetOptions('help'=>\$help,'version'=>\$version,'master_state=s'=>\$master_state);my$exit_code=1;if($version){print"masterha_master_switchversion$MHA::ManagerConst::VERSION.\n";exit0;}if($help){pod2usage(0);}if($master_stateeq"dead"){$exit_code=MHA::MasterFailover::main(@ARGV);}elsif($master_stateeq"alive"){$exit_code=MHA::MasterRotate::main(@ARGV);}else{pod2usage(1);}exit$exit_code;##############################################################################Documentation#############################################################################=pod=head1NAMEmasterha_master_switch-SwitchingMySQLmasterservertooneofotherslaveservers=head1SYNOPSIS#Formasterfailovermasterha_master_switch--master_state=dead--global_conf=/etc/masterha_default.cnf--conf=/usr/local/masterha/conf/app1.cnf--dead_master_host=host1#Foronlinemasterswitchmasterha_master_switch--master_state=alive--global_conf=/etc/masterha_default.cnf--conf=/usr/local/masterha/conf/app1.cnfSeeonlinereference(http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch)fordetails.=head1DESCRIPTIONSeeonlinereference(http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch)fordetails.[root@managerMHA]#[root@managerMHA]#[root@managerMHA]#cat/usr/local/bin/master_ip_online_change#!/usr/bin/envperl#Copyright(C)2011DeNACo.,Ltd.##Thisprogramisfreesoftware;youcanredistributeitand/ormodify#itunderthetermsoftheGNUGeneralPublicLicenseaspublishedby#theFreeSoftwareFoundation;eitherversion2oftheLicense,or#(atyouroption)anylaterversion.##Thisprogramisdistributedinthehopethatitwillbeuseful,#butWITHOUTANYWARRANTY;withouteventheimpliedwarrantyof#MERCHANTABILITYorFITNESSFORAPARTICULARPURPOSE.Seethe#GNUGeneralPublicLicenseformoredetails.##YoushouldhavereceivedacopyoftheGNUGeneralPublicLicense#alongwiththisprogram;ifnot,writetotheFreeSoftware#Foundation,Inc.,#51FranklinStreet,FifthFloor,Boston,MA02110-1301USA##Note:Thisisasamplescriptandisnotcomplete.Modifythescriptbasedonyourenvironment.usestrict;usewarningsFATAL=>'all';useGetopt::Long;useMHA::DBHelper;useMHA::NodeUtil;useTime::HiResqw(sleepgettimeofdaytv_interval);useData::Dumper;my$_tstart;my$_running_interval=0.1;my($command,$orig_master_is_new_slave,$orig_master_host,$orig_master_ip,$orig_master_port,$orig_master_user,$orig_master_password,$orig_master_ssh_user,$new_master_host,$new_master_ip,$new_master_port,$new_master_user,$new_master_password,$new_master_ssh_user,);GetOptions('command=s'=>\$command,'orig_master_is_new_slave'=>\$orig_master_is_new_slave,'orig_master_host=s'=>\$orig_master_host,'orig_master_ip=s'=>\$orig_master_ip,'orig_master_port=i'=>\$orig_master_port,'orig_master_user=s'=>\$orig_master_user,'orig_master_password=s'=>\$orig_master_password,'orig_master_ssh_user=s'=>\$orig_master_ssh_user,'new_master_host=s'=>\$new_master_host,'new_master_ip=s'=>\$new_master_ip,'new_master_port=i'=>\$new_master_port,'new_master_user=s'=>\$new_master_user,'new_master_password=s'=>\$new_master_password,'new_master_ssh_user=s'=>\$new_master_ssh_user,);exit&main();subcurrent_time_us{my($sec,$microsec)=gettimeofday();my$curdate=localtime($sec);return$curdate."".sprintf("%06d",$microsec);}subsleep_until{my$elapsed=tv_interval($_tstart);if($_running_interval>$elapsed){sleep($_running_interval-$elapsed);}}subget_threads_util{my$dbh=shift;my$my_connection_id=shift;my$running_time_threshold=shift;my$type=shift;$running_time_threshold=0unless($running_time_threshold);$type=0unless($type);my@threads;my$sth=$dbh->prepare("SHOWPROCESSLIST");$sth->execute();while(my$ref=$sth->fetchrow_hashref()){my$id=$ref->{Id};my$user=$ref->{User};my$host=$ref->{Host};my$command=$ref->{Command};my$state=$ref->{State};my$query_time=$ref->{Time};my$info=$ref->{Info};$info=~s/^\s*(.*?)\s*$/$1/ifdefined($info);nextif($my_connection_id==$id);nextif(defined($query_time)&&$query_time<$running_time_threshold);nextif(defined($command)&&$commandeq"BinlogDump");nextif(defined($user)&&$usereq"systemuser");nextif(defined($command)&&$commandeq"Sleep"&&defined($query_time)&&$query_time>=1);if($type>=1){nextif(defined($command)&&$commandeq"Sleep");nextif(defined($command)&&$commandeq"Connect");}if($type>=2){nextif(defined($info)&&$info=~m/^select/i);nextif(defined($info)&&$info=~m/^show/i);}push@threads,$ref;}return@threads;}submain{if($commandeq"stop"){##Gracefullykillingconnectionsonthecurrentmaster#1.Setread_only=1onthenewmaster#2.DROPUSERsothatnoappusercanestablishnewconnections#3.Setread_only=1onthecurrentmaster#4.Killcurrentqueries#*Anydatabaseaccessfailurewillresultinscriptdie.my$exit_code=1;eval{##Settingread_only=1onthenewmaster(toavoidaccident)my$new_master_handler=newMHA::DBHelper();#args:hostname,port,user,password,raise_error(die_on_error)_or_not$new_master_handler->connect($new_master_ip,$new_master_port,$new_master_user,$new_master_password,1);printcurrent_time_us()."Setread_onlyonthenewmaster..";$new_master_handler->enable_read_only();if($new_master_handler->is_read_only()){print"ok.\n";}else{die"Failed!\n";}$new_master_handler->disconnect();#Connectingtotheorigmaster,dieifanydatabaseerrorhappensmy$orig_master_handler=newMHA::DBHelper();$orig_master_handler->connect($orig_master_ip,$orig_master_port,$orig_master_user,$orig_master_password,1);##Dropapplicationusersothatnobodycanconnect.Disablingper-sessionbinlogbeforehand$orig_master_handler->disable_log_bin_local();printcurrent_time_us()."Drppingappuserontheorigmaster..\n";#FIXME_xxx_drop_app_user($orig_master_handler);##WaitingforN*100millisecondssothatcurrentconnectionscanexitmy$time_until_read_only=15;$_tstart=[gettimeofday];my@threads=get_threads_util($orig_master_handler->{dbh},$orig_master_handler->{connection_id});while($time_until_read_only>0&&$#threads>=0){if($time_until_read_only%5==0){printf"%sWaitingallrunning%dthreadsaredisconnected..(max%dmilliseconds)\n",current_time_us(),$#threads+1,$time_until_read_only*100;if($#threads<5){printData::Dumper->new([$_])->Indent(0)->Terse(1)->Dump."\n"foreach(@threads);}}sleep_until();$_tstart=[gettimeofday];$time_until_read_only--;@threads=get_threads_util($orig_master_handler->{dbh},$orig_master_handler->{connection_id});}##Settingread_only=1onthecurrentmastersothatnobody(exceptSUPER)canwriteprintcurrent_time_us()."Setread_only=1ontheorigmaster..";$orig_master_handler->enable_read_only();if($orig_master_handler->is_read_only()){print"ok.\n";}else{die"Failed!\n";}##WaitingforM*100millisecondssothatcurrentupdatequeriescancompletemy$time_until_kill_threads=5;@threads=get_threads_util($orig_master_handler->{dbh},$orig_master_handler->{connection_id});while($time_until_kill_threads>0&&$#threads>=0){if($time_until_kill_threads%5==0){printf"%sWaitingallrunning%dqueriesaredisconnected..(max%dmilliseconds)\n",current_time_us(),$#threads+1,$time_until_kill_threads*100;if($#threads<5){printData::Dumper->new([$_])->Indent(0)->Terse(1)->Dump."\n"foreach(@threads);}}sleep_until();$_tstart=[gettimeofday];$time_until_kill_threads--;@threads=get_threads_util($orig_master_handler->{dbh},$orig_master_handler->{connection_id});}##Terminatingallthreadsprintcurrent_time_us()."Killingallapplicationthreads..\n";$orig_master_handler->kill_threads(@threads)if($#threads>=0);printcurrent_time_us()."done.\n";$orig_master_handler->enable_log_bin_local();$orig_master_handler->disconnect();##Afterfinishingthescript,MHAexecutesFLUSHTABLESWITHREADLOCK$exit_code=0;};if($@){warn"GotError:$@\n";exit$exit_code;}exit$exit_code;}elsif($commandeq"start"){##Activatingmasteriponthenewmaster#1.Createappuserwithwriteprivileges#2.Movingbackupscriptifneeded#3.Registernewmaster'siptothecatalogdatabase#Wedon'treturnerroreventhoughactivatingupdatableaccounts/ipfailedsothatwedon'tinterruptslaves'recovery.#Ifexitcodeis0or10,MHAdoesnotabortmy$exit_code=10;eval{my$new_master_handler=newMHA::DBHelper();#args:hostname,port,user,password,raise_error_or_not$new_master_handler->connect($new_master_ip,$new_master_port,$new_master_user,$new_master_password,1);##Setread_only=0onthenewmaster$new_master_handler->disable_log_bin_local();printcurrent_time_us()."Setread_only=0onthenewmaster.\n";$new_master_handler->disable_read_only();##Creatinganappuseronthenewmasterprintcurrent_time_us()."Creatingappuseronthenewmaster..\n";FIXME_xxx_create_app_user($new_master_handler);$new_master_handler->enable_log_bin_local();$new_master_handler->disconnect();##Updatemasteriponthecatalogdatabase,etc$exit_code=0;};if($@){warn"GotError:$@\n";exit$exit_code;}exit$exit_code;}elsif($commandeq"status"){#donothingexit0;}else{&usage();exit1;}}subusage{print"Usage:master_ip_online_change--command=start|stop|status--orig_master_host=host--orig_master_ip=ip--orig_master_port=port--new_master_host=host--new_master_ip=ip--new_master_port=port\n";die;}

二、停止MHA监控程序

masterha_stop--conf=/etc/masterha/app1.cnf

三、进行平滑切换

masterha_master_switch--conf=/etc/masterha/app1.cnf--master_state=alive--new_master_host=192.168.8.57--new_master_port=3306--orig_master_is_new_slave--running_updates_limit=10000

FriOct2614:17:082018-[info]MHA::MasterRotateversion0.58.FriOct2614:17:082018-[info]Startingonlinemasterswitch..FriOct2614:17:082018-[info]FriOct2614:17:082018-[info]*Phase1:ConfigurationCheckPhase..FriOct2614:17:082018-[info]FriOct2614:17:082018-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping.FriOct2614:17:082018-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf..FriOct2614:17:082018-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf..FriOct2614:17:092018-[info]GTIDfailovermode=1FriOct2614:17:092018-[info]CurrentAliveMaster:192.168.8.58(192.168.8.58:3306)FriOct2614:17:092018-[info]AliveSlaves:FriOct2614:17:092018-[info]192.168.8.57(192.168.8.57:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2614:17:092018-[info]GTIDONFriOct2614:17:092018-[info]Replicatingfrom192.168.8.58(192.168.8.58:3306)FriOct2614:17:092018-[info]PrimarycandidateforthenewMaster(candidate_masterisset)FriOct2614:17:092018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2614:17:092018-[info]GTIDONFriOct2614:17:092018-[info]Replicatingfrom192.168.8.58(192.168.8.58:3306)ItisbettertoexecuteFLUSHNO_WRITE_TO_BINLOGTABLESonthemasterbeforeswitching.Isitoktoexecuteon192.168.8.58(192.168.8.58:3306)?(YES/no):yesFriOct2614:17:122018-[info]ExecutingFLUSHNO_WRITE_TO_BINLOGTABLES.Thismaytakelongtime..FriOct2614:17:122018-[info]ok.FriOct2614:17:122018-[info]CheckingMHAisnotmonitoringordoingfailover..FriOct2614:17:122018-[info]Checkingreplicationhealthon192.168.8.57..FriOct2614:17:122018-[info]ok.FriOct2614:17:122018-[info]Checkingreplicationhealthon192.168.8.59..FriOct2614:17:122018-[info]ok.FriOct2614:17:122018-[info]192.168.8.57canbenewmaster.FriOct2614:17:122018-[info]From: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)To:192.168.8.57(192.168.8.57:3306)(newmaster)+--192.168.8.59(192.168.8.59:3306)+--192.168.8.58(192.168.8.58:3306)Startingmasterswitchfrom192.168.8.58(192.168.8.58:3306)to192.168.8.57(192.168.8.57:3306)?(yes/NO):yesFriOct2614:17:162018-[info]Checkingwhether192.168.8.57(192.168.8.57:3306)isokforthenewmaster..FriOct2614:17:162018-[info]ok.FriOct2614:17:162018-[info]192.168.8.58(192.168.8.58:3306):SHOWSLAVESTATUSreturnedemptyresult.Tocheckreplicationfilteringrules,temporarilyexecutingCHANGEMASTERtoadummyhost.FriOct2614:17:162018-[info]192.168.8.58(192.168.8.58:3306):Resettingslavepointingtothedummyhost.FriOct2614:17:162018-[info]**Phase1:ConfigurationCheckPhasecompleted.FriOct2614:17:162018-[info]FriOct2614:17:162018-[info]*Phase2:RejectingupdatesPhase..FriOct2614:17:162018-[info]FriOct2614:17:162018-[info]Executingmasteriponlinechangescripttodisablewriteonthecurrentmaster:FriOct2614:17:162018-[info]/usr/local/bin/master_ip_online_change--command=stop--orig_master_host=192.168.8.58--orig_master_ip=192.168.8.58--orig_master_port=3306--orig_master_user='root'--new_master_host=192.168.8.57--new_master_ip=192.168.8.57--new_master_port=3306--new_master_user='root'--orig_master_ssh_user=root--new_master_ssh_user=root--orig_master_is_new_slave--orig_master_password=xxx--new_master_password=xxxFriOct2614:17:162018363856Setread_onlyonthenewmaster..ok.FriOct2614:17:162018369994Drppingappuserontheorigmaster..FriOct2614:17:162018375886Waitingallrunning2threadsaredisconnected..(max1500milliseconds){'Time'=>'12692','db'=>undef,'Id'=>'2','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'master:38252'}{'Time'=>'12637','db'=>undef,'Id'=>'3','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'slave2:56564'}FriOct2614:17:162018874083Waitingallrunning2threadsaredisconnected..(max1000milliseconds){'Time'=>'12693','db'=>undef,'Id'=>'2','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'master:38252'}{'Time'=>'12638','db'=>undef,'Id'=>'3','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'slave2:56564'}FriOct2614:17:172018375241Waitingallrunning2threadsaredisconnected..(max500milliseconds){'Time'=>'12693','db'=>undef,'Id'=>'2','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'master:38252'}{'Time'=>'12638','db'=>undef,'Id'=>'3','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'slave2:56564'}FriOct2614:17:172018876317Setread_only=1ontheorigmaster..ok.FriOct2614:17:172018878132Waitingallrunning2queriesaredisconnected..(max500milliseconds){'Time'=>'12694','db'=>undef,'Id'=>'2','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'master:38252'}{'Time'=>'12639','db'=>undef,'Id'=>'3','User'=>'repl','State'=>'Masterhassentallbinlogtoslave;waitingformoreupdates','Command'=>'BinlogDumpGTID','Info'=>undef,'Host'=>'slave2:56564'}FriOct2614:17:182018380824Killingallapplicationthreads..FriOct2614:17:182018383181done.FriOct2614:17:182018-[info]ok.FriOct2614:17:182018-[info]Lockingalltablesontheorigmastertorejectupdatesfromeverybody(includingroot):FriOct2614:17:182018-[info]ExecutingFLUSHTABLESWITHREADLOCK..FriOct2614:17:182018-[info]ok.FriOct2614:17:182018-[info]Origmasterbinlog:posismysql-bin.000011:400.FriOct2614:17:182018-[info]Waitingtoexecuteallrelaylogson192.168.8.57(192.168.8.57:3306)..FriOct2614:17:182018-[info]master_pos_wait(mysql-bin.000011:400)completedon192.168.8.57(192.168.8.57:3306).Executed0events.FriOct2614:17:182018-[info]done.FriOct2614:17:182018-[info]Gettingnewmaster'sbinlognameandposition..FriOct2614:17:182018-[info]mysql-bin.000012:194FriOct2614:17:182018-[info]Allotherslavesshouldstartreplicationfromhere.Statementshouldbe:CHANGEMASTERTOMASTER_HOST='192.168.8.57',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='xxx';FriOct2614:17:182018-[info]Executingmasteriponlinechangescripttoallowwriteonthenewmaster:FriOct2614:17:182018-[info]/usr/local/bin/master_ip_online_change--command=start--orig_master_host=192.168.8.58--orig_master_ip=192.168.8.58--orig_master_port=3306--orig_master_user='root'--new_master_host=192.168.8.57--new_master_ip=192.168.8.57--new_master_port=3306--new_master_user='root'--orig_master_ssh_user=root--new_master_ssh_user=root--orig_master_is_new_slave--orig_master_password=xxx--new_master_password=xxxFriOct2614:17:182018735339Setread_only=0onthenewmaster.FriOct2614:17:182018736744Creatingappuseronthenewmaster..GotError:Undefinedsubroutine&main::FIXME_xxx_create_app_usercalledat/usr/local/bin/master_ip_online_changeline246.FriOct2614:17:182018-[warning]Proceeding.FriOct2614:17:182018-[info]FriOct2614:17:182018-[info]*Switchingslavesinparallel..FriOct2614:17:182018-[info]FriOct2614:17:182018-[info]--Slaveswitchonhost192.168.8.59(192.168.8.59:3306)started,pid:4255FriOct2614:17:182018-[info]FriOct2614:17:192018-[info]Logmessagesfrom192.168.8.59...FriOct2614:17:192018-[info]FriOct2614:17:182018-[info]Waitingtoexecuteallrelaylogson192.168.8.59(192.168.8.59:3306)..FriOct2614:17:182018-[info]master_pos_wait(mysql-bin.000011:400)completedon192.168.8.59(192.168.8.59:3306).Executed0events.FriOct2614:17:182018-[info]done.FriOct2614:17:182018-[info]Resettingslave192.168.8.59(192.168.8.59:3306)andstartingreplicationfromthenewmaster192.168.8.57(192.168.8.57:3306)..FriOct2614:17:182018-[info]ExecutedCHANGEMASTER.FriOct2614:17:182018-[info]Slavestarted.FriOct2614:17:192018-[info]Endoflogmessagesfrom192.168.8.59...FriOct2614:17:192018-[info]FriOct2614:17:192018-[info]--Slaveswitchonhost192.168.8.59(192.168.8.59:3306)succeeded.FriOct2614:17:192018-[info]Unlockingalltablesontheorigmaster:FriOct2614:17:192018-[info]ExecutingUNLOCKTABLES..FriOct2614:17:192018-[info]ok.FriOct2614:17:192018-[info]Startingorigmasterasanewslave..FriOct2614:17:192018-[info]Resettingslave192.168.8.58(192.168.8.58:3306)andstartingreplicationfromthenewmaster192.168.8.57(192.168.8.57:3306)..FriOct2614:17:192018-[info]ExecutedCHANGEMASTER.FriOct2614:17:202018-[info]Slavestarted.FriOct2614:17:202018-[info]Allnewslaveserversswitchedsuccessfully.FriOct2614:17:202018-[info]FriOct2614:17:202018-[info]*Phase5:Newmastercleanupphase..FriOct2614:17:202018-[info]FriOct2614:17:202018-[info]192.168.8.57:Resettingslaveinfosucceeded.FriOct2614:17:202018-[info]Switchingmasterto192.168.8.57(192.168.8.57:3306)completedsuccessfully.

四、查看主从库状态

192.168.8.57

mysql>showslavestatus\GEmptyset(0.00sec)mysql>showvariableslike'%read_only%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|innodb_read_only|OFF||read_only|OFF||super_read_only|OFF||transaction_read_only|OFF||tx_read_only|OFF|+-----------------------+-------+

该节点已经变成主库，并且read_only已经关闭。

192.168.8.58

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.57Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000012Read_Master_Log_Pos:194Relay_Log_File:slave1-relay-bin.000002Relay_Log_Pos:367Relay_Master_Log_File:mysql-bin.000012Slave_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-080027c0450d:1-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-6Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)mysql>showvariableslike'%read_only%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|innodb_read_only|OFF||read_only|ON||super_read_only|OFF||transaction_read_only|OFF||tx_read_only|OFF|+-----------------------+-------+

改节点变成了从库，real_only依然开启。

192.168.8.59

mysql>showslavestatus\G***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:192.168.8.57Master_User:replMaster_Port:3306Connect_Retry:60Master_Log_File:mysql-bin.000012Read_Master_Log_Pos:194Relay_Log_File:slave2-relay-bin.000002Relay_Log_Pos:367Relay_Master_Log_File:mysql-bin.000012Slave_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-9,a92f70a4-d5ea-11e8-af28-080027c0450f:1-6Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)mysql>showvariableslike'%read_only%';+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|innodb_read_only|OFF||read_only|ON||super_read_only|OFF||transaction_read_only|OFF||tx_read_only|OFF|+-----------------------+-------+

该节点依然为从库，read_only参数依然开启。

五、检查主从复制状态

查看三个节点数据库数据状态

mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7||t8|+----------------+

在192.168.8.57创建测试表

mysql>createtablet9(idint(6));QueryOK,0rowsaffected(0.33sec)mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7||t8||t9|+----------------+

在192.168.8.58查看数据同步情况

mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7||t8||t9|+----------------+

在192.168.8.59查看数据同步情况

mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2||t3||t4||t5||t6||t7||t8||t9|+----------------+

六、重启监控程序并查看MHA状态

masterha_stop--conf=/etc/masterha/app1.cnf/usr/local/bin/masterha_start.sh

查看MHA日志，正常运行

+--192.168.8.59(192.168.8.59:3306)FriOct2616:10:392018-[info]Checkingmaster_ip_failover_scriptstatus:FriOct2616:10:392018-[info]/usr/local/bin/master_ip_failover--command=status--ssh_user=root--orig_master_host=192.168.8.57--orig_master_ip=192.168.8.57--orig_master_port=3306FriOct2616:10:392018-[info]OK.FriOct2616:10:392018-[warning]shutdown_scriptisnotdefined.FriOct2616:10:392018-[info]Setmasterpinginterval1seconds.FriOct2616:10:392018-[info]Setsecondarycheckscript:/usr/local/bin/masterha_secondary_check-s192.168.8.57-s192.168.8.59FriOct2616:10:392018-[info]Startingpinghealthcheckon192.168.8.57(192.168.8.57:3306)..FriOct2616:10:392018-[info]Ping(SELECT)succeeded,waitinguntilMySQLdoesn'trespond..

到此，关于“MySQL5.7怎么实现一主两从MHA在线手动平滑切换”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！