数据库架构：一主两从

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内容

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313#!/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监控程序

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

三、手工停止主库MySQL进程，模拟故障发生

mysqladmin-uroot-pmysqlshutdown

四、手工故障切换

masterha_master_switch--conf=/etc/masterha/app1.cnf--master_state=dead--dead_master_host=192.168.8.57--dead_master_port=3306--new_master_host=192.168.8.58--new_master_port=3306--ignore_last_failover

--dead_master_ip=<dead_master_ip>isnotset.Using192.168.8.57.FriOct2616:18:052018-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping.FriOct2616:18:052018-[info]Readingapplicationdefaultconfigurationfrom/etc/masterha/app1.cnf..FriOct2616:18:052018-[info]Readingserverconfigurationfrom/etc/masterha/app1.cnf..FriOct2616:18:052018-[info]MHA::MasterFailoverversion0.58.FriOct2616:18:052018-[info]Startingmasterfailover.FriOct2616:18:052018-[info]FriOct2616:18:052018-[info]*Phase1:ConfigurationCheckPhase..FriOct2616:18:052018-[info]FriOct2616:18:072018-[info]GTIDfailovermode=1FriOct2616:18:072018-[info]DeadServers:FriOct2616:18:072018-[info]192.168.8.57(192.168.8.57:3306)FriOct2616:18:072018-[info]CheckingmasterreachabilityviaMySQL(doublecheck)...FriOct2616:18:072018-[info]ok.FriOct2616:18:072018-[info]AliveServers:FriOct2616:18:072018-[info]192.168.8.58(192.168.8.58:3306)FriOct2616:18:072018-[info]192.168.8.59(192.168.8.59:3306)FriOct2616:18:072018-[info]AliveSlaves:FriOct2616:18:072018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2616:18:072018-[info]GTIDONFriOct2616:18:072018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)FriOct2616:18:072018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2616:18:072018-[info]GTIDONFriOct2616:18:072018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)Master192.168.8.57(192.168.8.57:3306)isdead.Proceed?(yes/NO):yesFriOct2616:18:142018-[info]StartingGTIDbasedfailover.FriOct2616:18:142018-[info]FriOct2616:18:142018-[info]**Phase1:ConfigurationCheckPhasecompleted.FriOct2616:18:142018-[info]FriOct2616:18:142018-[info]*Phase2:DeadMasterShutdownPhase..FriOct2616:18:142018-[info]FriOct2616:18:142018-[info]HealthCheck:SSHto192.168.8.57isreachable.FriOct2616:18:152018-[info]Forcingshutdownsothatapplicationsneverconnecttothecurrentmaster..FriOct2616:18:152018-[info]ExecutingmasterIPdeactivationscript:FriOct2616:18:152018-[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=rootFriOct2616:18:152018-[info]done.FriOct2616:18:152018-[warning]shutdown_scriptisnotset.Skippingexplicitshuttingdownofthedeadmaster.FriOct2616:18:152018-[info]*Phase2:DeadMasterShutdownPhasecompleted.FriOct2616:18:152018-[info]FriOct2616:18:152018-[info]*Phase3:MasterRecoveryPhase..FriOct2616:18:152018-[info]FriOct2616:18:152018-[info]*Phase3.1:GettingLatestSlavesPhase..FriOct2616:18:152018-[info]FriOct2616:18:152018-[info]Thelatestbinarylogfile/positiononallslavesismysql-bin.000012:359FriOct2616:18:152018-[info]RetrievedGtidSet:a92f70a4-d5ea-11e8-af28-080027c0450d:10FriOct2616:18:152018-[info]Latestslaves(Slavesthatreceivedrelaylogfilestothelatest):FriOct2616:18:152018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2616:18:152018-[info]GTIDONFriOct2616:18:152018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)FriOct2616:18:152018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2616:18:152018-[info]GTIDONFriOct2616:18:152018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)FriOct2616:18:152018-[info]Theoldestbinarylogfile/positiononallslavesismysql-bin.000012:359FriOct2616:18:152018-[info]RetrievedGtidSet:a92f70a4-d5ea-11e8-af28-080027c0450d:10FriOct2616:18:152018-[info]Oldestslaves:FriOct2616:18:152018-[info]192.168.8.58(192.168.8.58:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2616:18:152018-[info]GTIDONFriOct2616:18:152018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)FriOct2616:18:152018-[info]192.168.8.59(192.168.8.59:3306)Version=5.7.23-log(oldestmajorversionbetweenslaves)log-bin:enabledFriOct2616:18:152018-[info]GTIDONFriOct2616:18:152018-[info]Replicatingfrom192.168.8.57(192.168.8.57:3306)FriOct2616:18:152018-[info]FriOct2616:18:152018-[info]*Phase3.3:DeterminingNewMasterPhase..FriOct2616:18:152018-[info]FriOct2616:18:152018-[info]192.168.8.58canbenewmaster.FriOct2616:18:152018-[info]Newmasteris192.168.8.58(192.168.8.58:3306)FriOct2616:18:152018-[info]Startingmasterfailover..FriOct2616:18:152018-[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)Startingmasterswitchfrom192.168.8.57(192.168.8.57:3306)to192.168.8.58(192.168.8.58:3306)?(yes/NO):yesFriOct2616:18:222018-[info]Newmasterdecidedmanuallyis192.168.8.58(192.168.8.58:3306)FriOct2616:18:222018-[info]FriOct2616:18:222018-[info]*Phase3.3:NewMasterRecoveryPhase..FriOct2616:18:222018-[info]FriOct2616:18:222018-[info]Waitingalllogstobeapplied..FriOct2616:18:222018-[info]done.FriOct2616:18:222018-[info]Gettingnewmaster'sbinlognameandposition..FriOct2616:18:222018-[info]mysql-bin.000011:565FriOct2616:18:222018-[info]Allotherslavesshouldstartreplicationfromhere.Statementshouldbe:CHANGEMASTERTOMASTER_HOST='192.168.8.58',MASTER_PORT=3306,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='xxx';FriOct2616:18:222018-[info]MasterRecoverysucceeded.File:Pos:Exec_Gtid_Set:mysql-bin.000011,565,a92f70a4-d5ea-11e8-af28-080027c0450d:1-10,a92f70a4-d5ea-11e8-af28-080027c0450f:1-6FriOct2616:18:222018-[info]ExecutingmasterIPactivatescript:FriOct2616:18:222018-[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=xxxSetread_only=0onthenewmaster.Creatingappuseronthenewmaster..Undefinedsubroutine&main::FIXME_xxx_create_usercalledat/usr/local/bin/master_ip_failoverline94.FriOct2616:18:222018-[error][/usr/lib/perl5/vendor_perl/MHA/MasterFailover.pm,ln1612]FailedtoactivatemasterIPaddressfor192.168.8.58(192.168.8.58:3306)withreturncode10:0FriOct2616:18:222018-[warning]Proceeding.FriOct2616:18:222018-[info]**Finishedmasterrecoverysuccessfully.FriOct2616:18:222018-[info]*Phase3:MasterRecoveryPhasecompleted.FriOct2616:18:222018-[info]FriOct2616:18:222018-[info]*Phase4:SlavesRecoveryPhase..FriOct2616:18:222018-[info]FriOct2616:18:222018-[info]FriOct2616:18:222018-[info]*Phase4.1:StartingSlavesinparallel..FriOct2616:18:222018-[info]FriOct2616:18:222018-[info]--Slaverecoveryonhost192.168.8.59(192.168.8.59:3306)started,pid:5792.Checktmplog/var/log/masterha/app1/192.168.8.59_3306_20181026161805.logifittakestime..FriOct2616:18:232018-[info]FriOct2616:18:232018-[info]Logmessagesfrom192.168.8.59...FriOct2616:18:232018-[info]FriOct2616:18:222018-[info]Resettingslave192.168.8.59(192.168.8.59:3306)andstartingreplicationfromthenewmaster192.168.8.58(192.168.8.58:3306)..FriOct2616:18:222018-[info]ExecutedCHANGEMASTER.FriOct2616:18:222018-[info]Slavestarted.FriOct2616:18:222018-[info]gtid_wait(a92f70a4-d5ea-11e8-af28-080027c0450d:1-10,a92f70a4-d5ea-11e8-af28-080027c0450f:1-6)completedon192.168.8.59(192.168.8.59:3306).Executed0events.FriOct2616:18:232018-[info]Endoflogmessagesfrom192.168.8.59.FriOct2616:18:232018-[info]--Slaveonhost192.168.8.59(192.168.8.59:3306)started.FriOct2616:18:232018-[info]Allnewslaveserversrecoveredsuccessfully.FriOct2616:18:232018-[info]FriOct2616:18:232018-[info]*Phase5:Newmastercleanupphase..FriOct2616:18:232018-[info]FriOct2616:18:232018-[info]Resettingslaveinfoonthenewmaster..FriOct2616:18:232018-[info]192.168.8.58:Resettingslaveinfosucceeded.FriOct2616:18:232018-[info]Masterfailoverto192.168.8.58(192.168.8.58:3306)completedsuccessfully.FriOct2616:18:232018-[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!CheckMHAManagerlogsatmanagerfordetails.Startedmanual(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.FriOct2616:18:232018-[info]Sendingmail..

五、查看数据库状态

192.168.8.58

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|+-----------------------+-------+

当前节点变成主库，slave进程停止，只读模式关闭

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.000011Read_Master_Log_Pos:565Relay_Log_File:slave2-relay-bin.000002Relay_Log_Pos:414Relay_Master_Log_File:mysql-bin.000011Slave_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:565Relay_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-10,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|+-----------------------+-------+

此节点主库变成192.168.8.58，只读模式不变

六、查看复制状态

当前主库和从库数据状态

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

在主库192.168.8.58创建测试表

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

在从库192.168.8.59查看数据同步情况

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

测试表t10已经同步，复制正常。