MySQL5.7怎么实现一主两从MHA在线手动平滑切换
发表于：2025-11-10 作者：千家信息网编辑
千家信息网最后更新 2025年11月10日，这篇文章主要介绍"MySQL5.7怎么实现一主两从MHA在线手动平滑切换"，在日常操作中，相信很多人在MySQL5.7怎么实现一主两从MHA在线手动平滑切换问题上存在疑惑，小编查阅了各式资料，整理出简
千家信息网最后更新 2025年11月10日MySQL5.7怎么实现一主两从MHA在线手动平滑切换
这篇文章主要介绍"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/env perl# Copyright (C) 2011 DeNA Co.,Ltd.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; either version 2 of the License, or# (at your option) any later version.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc.,# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USAuse strict;use warnings FATAL => 'all';use Getopt::Long qw(:config pass_through);use Pod::Usage;use MHA::MasterMonitor;use MHA::MasterFailover;use MHA::MasterRotate;use MHA::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_switch version $MHA::ManagerConst::VERSION.\n"; exit 0;}if ($help) { pod2usage(0);}if ( $master_state eq "dead" ) { $exit_code = MHA::MasterFailover::main(@ARGV);}elsif ( $master_state eq "alive" ) { $exit_code = MHA::MasterRotate::main(@ARGV);}else { pod2usage(1);}exit $exit_code;# ############################################################################# Documentation# ############################################################################=pod=head1 NAMEmasterha_master_switch - Switching MySQL master server to one of other slave servers=head1 SYNOPSIS# For master failovermasterha_master_switch --master_state=dead --global_conf=/etc/masterha_default.cnf --conf=/usr/local/masterha/conf/app1.cnf --dead_master_host=host1 # For online master switchmasterha_master_switch --master_state=alive --global_conf=/etc/masterha_default.cnf --conf=/usr/local/masterha/conf/app1.cnfSee online reference (http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch) for details.=head1 DESCRIPTIONSee online reference (http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch) for details.[root@manager MHA]# [root@manager MHA]# [root@manager MHA]# cat /usr/local/bin/master_ip_online_change#!/usr/bin/env perl# Copyright (C) 2011 DeNA Co.,Ltd.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; either version 2 of the License, or# (at your option) any later version.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc.,# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA## Note: This is a sample script and is not complete. Modify the script based on your environment.use strict;use warnings FATAL => 'all';use Getopt::Long;use MHA::DBHelper;use MHA::NodeUtil;use Time::HiRes qw( sleep gettimeofday tv_interval );use Data::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();sub current_time_us { my ( $sec, $microsec ) = gettimeofday(); my $curdate = localtime($sec); return $curdate . " " . sprintf( "d", $microsec );}sub sleep_until { my $elapsed = tv_interval($_tstart); if ( $_running_interval > $elapsed ) { sleep( $_running_interval - $elapsed ); }}sub get_threads_util { my $dbh = shift; my $my_connection_id = shift; my $running_time_threshold = shift; my $type = shift; $running_time_threshold = 0 unless ($running_time_threshold); $type = 0 unless ($type); my @threads; my $sth = $dbh->prepare("SHOW PROCESSLIST"); $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/ if defined($info); next if ( $my_connection_id == $id ); next if ( defined($query_time) && $query_time < $running_time_threshold ); next if ( defined($command) && $command eq "Binlog Dump" ); next if ( defined($user) && $user eq "system user" ); next if ( defined($command) && $command eq "Sleep" && defined($query_time) && $query_time >= 1 ); if ( $type >= 1 ) { next if ( defined($command) && $command eq "Sleep" ); next if ( defined($command) && $command eq "Connect" ); } if ( $type >= 2 ) { next if ( defined($info) && $info =~ m/^select/i ); next if ( defined($info) && $info =~ m/^show/i ); } push @threads, $ref; } return @threads;}sub main { if ( $command eq "stop" ) { ## Gracefully killing connections on the current master # 1. Set read_only= 1 on the new master # 2. DROP USER so that no app user can establish new connections # 3. Set read_only= 1 on the current master # 4. Kill current queries # * Any database access failure will result in script die. my $exit_code = 1; eval { ## Setting read_only=1 on the new master (to avoid accident) my $new_master_handler = new MHA::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 ); print current_time_us() . " Set read_only on the new master.. "; $new_master_handler->enable_read_only(); if ( $new_master_handler->is_read_only() ) { print "ok.\n"; } else { die "Failed!\n"; } $new_master_handler->disconnect(); # Connecting to the orig master, die if any database error happens my $orig_master_handler = new MHA::DBHelper(); $orig_master_handler->connect( $orig_master_ip, $orig_master_port, $orig_master_user, $orig_master_password, 1 ); ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand $orig_master_handler->disable_log_bin_local(); print current_time_us() . " Drpping app user on the orig master..\n"; #FIXME_xxx_drop_app_user($orig_master_handler); ## Waiting for N * 100 milliseconds so that current connections can exit my $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"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n", current_time_us(), $#threads + 1, $time_until_read_only * 100; if ( $#threads < 5 ) { print Data::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} ); } ## Setting read_only=1 on the current master so that nobody(except SUPER) can write print current_time_us() . " Set read_only=1 on the orig master.. "; $orig_master_handler->enable_read_only(); if ( $orig_master_handler->is_read_only() ) { print "ok.\n"; } else { die "Failed!\n"; } ## Waiting for M * 100 milliseconds so that current update queries can complete my $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"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n", current_time_us(), $#threads + 1, $time_until_kill_threads * 100; if ( $#threads < 5 ) { print Data::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} ); } ## Terminating all threads print current_time_us() . " Killing all application threads..\n"; $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 ); print current_time_us() . " done.\n"; $orig_master_handler->enable_log_bin_local(); $orig_master_handler->disconnect(); ## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { ## Activating master ip on the new master # 1. Create app user with write privileges # 2. Moving backup script if needed # 3. Register new master's ip to the catalog database# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.# If exit code is 0 or 10, MHA does not abort my $exit_code = 10; eval { my $new_master_handler = new MHA::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 ); ## Set read_only=0 on the new master $new_master_handler->disable_log_bin_local(); print current_time_us() . " Set read_only=0 on the new master.\n"; $new_master_handler->disable_read_only(); ## Creating an app user on the new master print current_time_us() . " Creating app user on the new master..\n"; FIXME_xxx_create_app_user($new_master_handler); $new_master_handler->enable_log_bin_local(); $new_master_handler->disconnect(); ## Update master ip on the catalog database, etc $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { # do nothing exit 0; } else { &usage(); exit 1; }}sub usage { 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
Fri Oct 26 14:17:08 2018 - [info] MHA::MasterRotate version 0.58.Fri Oct 26 14:17:08 2018 - [info] Starting online master switch..Fri Oct 26 14:17:08 2018 - [info] Fri Oct 26 14:17:08 2018 - [info] * Phase 1: Configuration Check Phase..Fri Oct 26 14:17:08 2018 - [info] Fri Oct 26 14:17:08 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Fri Oct 26 14:17:08 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..Fri Oct 26 14:17:08 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..Fri Oct 26 14:17:09 2018 - [info] GTID failover mode = 1Fri Oct 26 14:17:09 2018 - [info] Current Alive Master: 192.168.8.58(192.168.8.58:3306)Fri Oct 26 14:17:09 2018 - [info] Alive Slaves:Fri Oct 26 14:17:09 2018 - [info] 192.168.8.57(192.168.8.57:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabledFri Oct 26 14:17:09 2018 - [info] GTID ONFri Oct 26 14:17:09 2018 - [info] Replicating from 192.168.8.58(192.168.8.58:3306)Fri Oct 26 14:17:09 2018 - [info] Primary candidate for the new Master (candidate_master is set)Fri Oct 26 14:17:09 2018 - [info] 192.168.8.59(192.168.8.59:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabledFri Oct 26 14:17:09 2018 - [info] GTID ONFri Oct 26 14:17:09 2018 - [info] Replicating from 192.168.8.58(192.168.8.58:3306)It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.8.58(192.168.8.58:3306)? (YES/no): yesFri Oct 26 14:17:12 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..Fri Oct 26 14:17:12 2018 - [info] ok.Fri Oct 26 14:17:12 2018 - [info] Checking MHA is not monitoring or doing failover..Fri Oct 26 14:17:12 2018 - [info] Checking replication health on 192.168.8.57..Fri Oct 26 14:17:12 2018 - [info] ok.Fri Oct 26 14:17:12 2018 - [info] Checking replication health on 192.168.8.59..Fri Oct 26 14:17:12 2018 - [info] ok.Fri Oct 26 14:17:12 2018 - [info] 192.168.8.57 can be new master.Fri Oct 26 14:17:12 2018 - [info] From:192.168.8.58(192.168.8.58:3306) (current master) +--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) (new master) +--192.168.8.59(192.168.8.59:3306) +--192.168.8.58(192.168.8.58:3306)Starting master switch from 192.168.8.58(192.168.8.58:3306) to 192.168.8.57(192.168.8.57:3306)? (yes/NO): yesFri Oct 26 14:17:16 2018 - [info] Checking whether 192.168.8.57(192.168.8.57:3306) is ok for the new master..Fri Oct 26 14:17:16 2018 - [info] ok.Fri Oct 26 14:17:16 2018 - [info] 192.168.8.58(192.168.8.58:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.Fri Oct 26 14:17:16 2018 - [info] 192.168.8.58(192.168.8.58:3306): Resetting slave pointing to the dummy host.Fri Oct 26 14:17:16 2018 - [info] ** Phase 1: Configuration Check Phase completed.Fri Oct 26 14:17:16 2018 - [info] Fri Oct 26 14:17:16 2018 - [info] * Phase 2: Rejecting updates Phase..Fri Oct 26 14:17:16 2018 - [info] Fri Oct 26 14:17:16 2018 - [info] Executing master ip online change script to disable write on the current master:Fri Oct 26 14:17:16 2018 - [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=xxxFri Oct 26 14:17:16 2018 363856 Set read_only on the new master.. ok.Fri Oct 26 14:17:16 2018 369994 Drpping app user on the orig master..Fri Oct 26 14:17:16 2018 375886 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds){'Time' => '12692','db' => undef,'Id' => '2','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'master:38252'}{'Time' => '12637','db' => undef,'Id' => '3','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'slave2:56564'}Fri Oct 26 14:17:16 2018 874083 Waiting all running 2 threads are disconnected.. (max 1000 milliseconds){'Time' => '12693','db' => undef,'Id' => '2','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'master:38252'}{'Time' => '12638','db' => undef,'Id' => '3','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'slave2:56564'}Fri Oct 26 14:17:17 2018 375241 Waiting all running 2 threads are disconnected.. (max 500 milliseconds){'Time' => '12693','db' => undef,'Id' => '2','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'master:38252'}{'Time' => '12638','db' => undef,'Id' => '3','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'slave2:56564'}Fri Oct 26 14:17:17 2018 876317 Set read_only=1 on the orig master.. ok.Fri Oct 26 14:17:17 2018 878132 Waiting all running 2 queries are disconnected.. (max 500 milliseconds){'Time' => '12694','db' => undef,'Id' => '2','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'master:38252'}{'Time' => '12639','db' => undef,'Id' => '3','User' => 'repl','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'slave2:56564'}Fri Oct 26 14:17:18 2018 380824 Killing all application threads..Fri Oct 26 14:17:18 2018 383181 done.Fri Oct 26 14:17:18 2018 - [info] ok.Fri Oct 26 14:17:18 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):Fri Oct 26 14:17:18 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..Fri Oct 26 14:17:18 2018 - [info] ok.Fri Oct 26 14:17:18 2018 - [info] Orig master binlog:pos is mysql-bin.000011:400.Fri Oct 26 14:17:18 2018 - [info] Waiting to execute all relay logs on 192.168.8.57(192.168.8.57:3306)..Fri Oct 26 14:17:18 2018 - [info] master_pos_wait(mysql-bin.000011:400) completed on 192.168.8.57(192.168.8.57:3306). Executed 0 events.Fri Oct 26 14:17:18 2018 - [info] done.Fri Oct 26 14:17:18 2018 - [info] Getting new master's binlog name and position..Fri Oct 26 14:17:18 2018 - [info] mysql-bin.000012:194Fri Oct 26 14:17:18 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.8.57', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';Fri Oct 26 14:17:18 2018 - [info] Executing master ip online change script to allow write on the new master:Fri Oct 26 14:17:18 2018 - [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=xxxFri Oct 26 14:17:18 2018 735339 Set read_only=0 on the new master.Fri Oct 26 14:17:18 2018 736744 Creating app user on the new master..Got Error: Undefined subroutine &main::FIXME_xxx_create_app_user called at /usr/local/bin/master_ip_online_change line 246.Fri Oct 26 14:17:18 2018 - [warning] Proceeding.Fri Oct 26 14:17:18 2018 - [info] Fri Oct 26 14:17:18 2018 - [info] * Switching slaves in parallel..Fri Oct 26 14:17:18 2018 - [info] Fri Oct 26 14:17:18 2018 - [info] -- Slave switch on host 192.168.8.59(192.168.8.59:3306) started, pid: 4255Fri Oct 26 14:17:18 2018 - [info] Fri Oct 26 14:17:19 2018 - [info] Log messages from 192.168.8.59 ...Fri Oct 26 14:17:19 2018 - [info] Fri Oct 26 14:17:18 2018 - [info] Waiting to execute all relay logs on 192.168.8.59(192.168.8.59:3306)..Fri Oct 26 14:17:18 2018 - [info] master_pos_wait(mysql-bin.000011:400) completed on 192.168.8.59(192.168.8.59:3306). Executed 0 events.Fri Oct 26 14:17:18 2018 - [info] done.Fri Oct 26 14:17:18 2018 - [info] Resetting slave 192.168.8.59(192.168.8.59:3306) and starting replication from the new master 192.168.8.57(192.168.8.57:3306)..Fri Oct 26 14:17:18 2018 - [info] Executed CHANGE MASTER.Fri Oct 26 14:17:18 2018 - [info] Slave started.Fri Oct 26 14:17:19 2018 - [info] End of log messages from 192.168.8.59 ...Fri Oct 26 14:17:19 2018 - [info] Fri Oct 26 14:17:19 2018 - [info] -- Slave switch on host 192.168.8.59(192.168.8.59:3306) succeeded.Fri Oct 26 14:17:19 2018 - [info] Unlocking all tables on the orig master:Fri Oct 26 14:17:19 2018 - [info] Executing UNLOCK TABLES..Fri Oct 26 14:17:19 2018 - [info] ok.Fri Oct 26 14:17:19 2018 - [info] Starting orig master as a new slave..Fri Oct 26 14:17:19 2018 - [info] Resetting slave 192.168.8.58(192.168.8.58:3306) and starting replication from the new master 192.168.8.57(192.168.8.57:3306)..Fri Oct 26 14:17:19 2018 - [info] Executed CHANGE MASTER.Fri Oct 26 14:17:20 2018 - [info] Slave started.Fri Oct 26 14:17:20 2018 - [info] All new slave servers switched successfully.Fri Oct 26 14:17:20 2018 - [info] Fri Oct 26 14:17:20 2018 - [info] * Phase 5: New master cleanup phase..Fri Oct 26 14:17:20 2018 - [info] Fri Oct 26 14:17:20 2018 - [info] 192.168.8.57: Resetting slave info succeeded.Fri Oct 26 14:17:20 2018 - [info] Switching master to 192.168.8.57(192.168.8.57:3306) completed successfully.四、查看主从库状态
192.168.8.57
mysql> show slave status \GEmpty set (0.00 sec)mysql> show variables like '%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> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.57 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 194 Relay_Log_File: slave1-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 575 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 57 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450d Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_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-6 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)mysql> show variables like '%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> show slave status \G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.57 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 194 Relay_Log_File: slave2-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 575 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 57 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450d Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_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-6 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)mysql> show variables like '%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> show tables;+----------------+| Tables_in_test |+----------------+| t1 || t2 || t3 || t4 || t5 || t6 || t7 || t8 |+----------------+
在192.168.8.57创建测试表
mysql> create table t9(id int(6));Query OK, 0 rows affected (0.33 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1 || t2 || t3 || t4 || t5 || t6 || t7 || t8 || t9 |+----------------+
在192.168.8.58查看数据同步情况
mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1 || t2 || t3 || t4 || t5 || t6 || t7 || t8 || t9 |+----------------+
在192.168.8.59查看数据同步情况
mysql> show tables;+----------------+| 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)Fri Oct 26 16:10:39 2018 - [info] Checking master_ip_failover_script status:Fri Oct 26 16:10:39 2018 - [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=3306 Fri Oct 26 16:10:39 2018 - [info] OK.Fri Oct 26 16:10:39 2018 - [warning] shutdown_script is not defined.Fri Oct 26 16:10:39 2018 - [info] Set master ping interval 1 seconds.Fri Oct 26 16:10:39 2018 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 192.168.8.57 -s 192.168.8.59Fri Oct 26 16:10:39 2018 - [info] Starting ping health check on 192.168.8.57(192.168.8.57:3306)..Fri Oct 26 16:10:39 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
到此，关于"MySQL5.7怎么实现一主两从MHA在线手动平滑切换"的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！
平滑
切换
数据
手动
在线
状态
节点
学习
监控程序
主从
情况
数据库
更多
程序
同步
帮助
监控
实用
接下来
三个
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
武汉大学网络安全学院官网
三门峡网络技术优化平台
一元夺宝软件开发
安徽服务器硬盘性能
数据库英文简称
苹果软件开发者名称怎么改
数据库集合的概念
途锦说债互联网科技有限公司
湖南数据库安全箱推荐厂家
鲲鹏基础软件开发
数据库还原之后名字还是之前的
苹果自家数据库
ivms4200考勤数据库同步
软件开发降本增效措施
戴尔服务器没有带外管理口
卫生网络安全讲话
ug服务器版出现许可证被占用
我的世界离线服务器注册需要验证
全球三根主服务器
公共网络安全应急处置预案
公司网络安全区域
虎眼科技软件开发制作
网络安全关键技术主要涉及有
手机为什么连不上服务器
天下3鼎立山河服务器啥时候开的
服务器没找到路径
软件开发报价流程
服务器处理中
软件开发常态
西门子数据库打不开
