MySQL 搭建MHA高可用架构
| 环境 |
OS: Centos 7.6 Mysql: mysql-5.7.26-linux-glibc2.12-x86_64 MHA:https://github.com/yoshinorim/ 采用源编译安装 |
|||||||
| 节点 (机器间免密互通,MHA要求) | 安装软件 | 角色 | ||||||
| node101：192.168.6.101 | mysql、MHA node | Mysql Master、MHA Node | ||||||
| node102：192.168.6.102 | mysql、MHA node | Mysql Slave、MHA Node | ||||||
| node103：192.168.6.103 | mysql、MHA node | Mysql Slave、MHA Node | ||||||
|
node104：192.168.6.104 vip:192.168.6.123 |
MHA manager、MHA node | MHA Master、MHA Node | ||||||
一、安装Perl语言环境
范围：所有节点
1.安装CPAN，CPAN是perl第三方代码库
yum install -y perl-CPAN*
2.安装perl DBD-MySQL模块
yum install -y perl-DBD-MySQL*
3.使用cpan安装Install模块
cpan -i Module::Install
reboot 重启 否则不生效
安装方法1：使用cpanm wget http://xrl.us/cpanm -O /usr/bin/cpanm; chmod +x /usr/bin/cpanm cpanm Module::Install 安装方法2：使用perl -MCPAN -e shell [root@node104 ~]#perl -MCPAN -e shell #获得帮助 cpan>help #列出CPAN上所有模块的列表 cpan>m #安装模块，自动完成Net::Server模块从下载到安装的全过程。 cpan>install Net::Server #退出 cpan>quit 安装方法3： [root@node104 ~]# cpan -i Net::Server
4.安装ifconfig centos7默认没有ifconfig命令
yum install -y net-tools.x86_64
二、安装MHA node节点
范围：node101、node102、node103、node104(包括管理节点)
1.采用源码方式安装，下载MHA node
Code-->Download ZIP-->mha4mysql-node-master.zip-->上传Linux-->unzip解压
2.编译安装
cd mha4mysql-node-master
perl Makefile.PL && make && make install
三、安装MHA manager
范围：node104
1.采用源码方式安装，下载MHA manager
Code-->Download ZIP-->mha4mysql-master-master.zip-->上传Linux-->unzip解压
2.安装依赖的perl模块
yum install -y perl-Params-Validate yum install -y perl-Config-Tiny yum install -y perl-Log-Dispatch yum install -y perl-Parallel-ForkManager yum install -y perl-Time-HiRes
3.编译安装
cd mha4mysql-master-master
perl Makefile.PL && make && make install
四、配置MHA manager
范围：node104
1.配置MHA manager
#创建配置目录
mkdir -p /usr/local/mha && mkdir -p /etc/mha
#编辑配置文件 用于指导manager和其他node节点通信，有几个node就配置几个server,相同配置放在server default
vi /etc/mha/mha.conf
[server default] user=root password=123456 repl_user=backup_user repl_password=backup_123 manager_workdir=/usr/local/mha manager_log=/usr/local/mha/manager.log remote_workdir=/usr/local/mha ssh_user=root ping_interval=1 master_ip_failover_script=/usr/local/scripts/master_ip_failover master_ip_online_change_script=/usr/local/scripts/master_ip_online_change [server1] hostname=node101 ssh_port=22 port=3306 master_binlog_dir=/usr/local/mysql/data candidate_master=1 [server2] hostname=node102 ssh_port=22 port=3306 master_binlog_dir=/usr/local/mysql/data candidate_master=1 [server3] hostname=node103 ssh_port=22 port=3306 master_binlog_dir=/usr/local/mysql/data candidate_master=1
参考：mha参数配置详解
2.配置master_ip_failover切换脚本
#创建脚本目录
mkdir -p /usr/local/scripts
#vi master_ip_failover
#!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command,$ssh_user,$orig_master_host,$orig_master_ip,$orig_master_port,$new_master_host,$new_master_ip,$new_master_port,$new_master_user,$new_master_password ); my $vip = '192.168.6.123/24'; my $key = '0'; my $ssh_start_vip = "ifconfig ens33:$key $vip"; my $ssh_stop_vip = "ifconfig ens33:$key down"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if($command eq "stop" || $command eq "stopssh"){ my $exit_code=1; eval{ print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code=0; }; if($@){ warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif($command eq "start"){ my $exit_code = 10; eval{ print "Enabling the VIP - $vip on the new master - $new_master_host \n" ; &start_vip(); $exit_code=0; }; if($@){ warn $@; exit $exit_code; } exit $exit_code; } elsif ($command eq "status"){ print "Checking the Status of the script.. OK \n"; exit 0; } else{ &usage(); exit 1; } } sub start_vip(){ `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } sub stop_vip() { return 0 unless($ssh_user); `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|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"; }
3.编辑master_ip_online_change的脚本
#vi master_ip_online_change
#!/usr/bin/env perl 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 $vip="192.168.6.123/24"; my $if="ens33"; my $ssh_start_vip="ifconfig ens33:$key $vip"; my $ssh_stop_vip="ifconfig ens33:$key down"; my $exit_code=0; 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 drop_vip{ my $output = `ssh -oConnectTimeout=15 -oConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`; } sub add_vip{ my $output = `ssh -oConnectTimeout=15 -oConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`; } sub current_time_us{ my ($sec,$microsec)=gettimeofday(); my $curdate=localtime($sec); return $curdate." ".sprintf("%06d",$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->{Commond}; 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) && $commandeq "Binlog Dump"); next if(defined($user) && $user eq "systemuser"); next if(defined($command) && $command eq "Sleep" && defined($query_time) && $query_time >= 1); 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.Setread=1 on the new master #2.DROPUSER so that no app user can establish new connections #3.Setread_only=1 on the current master #4.Killcurrent queries #* Anydatabase 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); printcurrent_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); ## Dropapplication user so that nobody can connect. Disabling per-session binlogbeforehand $orig_master_handler->disable_log_bin_local(); # printcurrent_time_us()." Droping app user on the orig master..\n"; printcurrent_time_us()." drop vip $vip..\n"; #drop_app_user($orig_master_handler); $drop_vip(); ## Waiting for N*100 milliseconds so that current connections can exit my $time_util_read_only=15; $_tstart=[gettimeofday]; my @threads=get_threads_util($orig_master_handler->{dbh},$orig_master_handler->{connection_id}); while($time_util_read_only > 0 && $#threads>=0){ if($time_util_read_only%5==0){ printf "%s Waiting all running %d threads are disconnected..(max %d milliseconds)\n",current_time_us(),$#threads+1,$time_util_read_only*100; if($#threads<5){ print Data::Dumper->new([$_])->Indent(0)->Terse(1)->Dump."\n"; foreach(@threads); } } } sleep_until(); $_tstart=[gettimeofday]; $time_util_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 printcurrent_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_util_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 printcurrent_time_us()." Killing all application threads..\n"; $orig_master_handler->enable_log_bin_local(); $orig_master_handler->disconnect(); ## Afterfinishing 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 retrun error even though activating update table 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(); printcurrent_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 #printcurrent_time_us()." Creating app user on the new master..\n"; printcurrent_time_us()." Add vip $vip on $if..\n"; #create_app_user($new_master_handler); $add_vip(); $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"){ # donothing 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; }
3.赋予执行权限
chmod +x master_ip_failover
chmod +x master_ip_online_change
4.主库机器添加VIP虚拟地址
命令：ip addr add xxx.xxx.xxx.xxx dev eth0,这里xxx.xxx.xxx.xxx是自定义的一个vip地址
[root@node101 bin]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:5d:b9:f6 brd ff:ff:ff:ff:ff:ff inet 192.168.6.101/24 brd 192.168.6.255 scope global noprefixroute ens33 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe5d:b9f6/64 scope link valid_lft forever preferred_lft forever [root@node101 bin]# ip addr add 192.168.6.123/24 dev ens33 [root@node101 bin]# ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:5d:b9:f6 brd ff:ff:ff:ff:ff:ff inet 192.168.6.101/24 brd 192.168.6.255 scope global noprefixroute ens33 valid_lft forever preferred_lft forever inet 192.168.6.123/24 scope global secondary ens33 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe5d:b9f6/64 scope link valid_lft forever preferred_lft forever
5.检测
执行SSH检测命令，如果检测全部OK 那么就代表安装完毕了
[root@node104 mha]# /root/mha4mysql-manager-master/bin/masterha_check_ssh --conf=/etc/mha/mha.conf Sun Sep 26 17:57:21 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Sep 26 17:57:21 2021 - [info] Reading application default configuration from /etc/mha/mha.conf.. Sun Sep 26 17:57:21 2021 - [info] Reading server configuration from /etc/mha/mha.conf.. Sun Sep 26 17:57:21 2021 - [info] Starting SSH connection tests.. Sun Sep 26 17:57:22 2021 - [debug] Sun Sep 26 17:57:21 2021 - [debug] Connecting via SSH from root@node101(192.168.6.101:22) to root@node102(192.168.6.102:22).. Sun Sep 26 17:57:21 2021 - [debug] ok. Sun Sep 26 17:57:21 2021 - [debug] Connecting via SSH from root@node101(192.168.6.101:22) to root@node103(192.168.6.103:22).. Sun Sep 26 17:57:22 2021 - [debug] ok. Sun Sep 26 17:57:23 2021 - [debug] Sun Sep 26 17:57:22 2021 - [debug] Connecting via SSH from root@node103(192.168.6.103:22) to root@node101(192.168.6.101:22).. Sun Sep 26 17:57:22 2021 - [debug] ok. Sun Sep 26 17:57:22 2021 - [debug] Connecting via SSH from root@node103(192.168.6.103:22) to root@node102(192.168.6.102:22).. Sun Sep 26 17:57:23 2021 - [debug] ok. Sun Sep 26 17:57:23 2021 - [debug] Sun Sep 26 17:57:21 2021 - [debug] Connecting via SSH from root@node102(192.168.6.102:22) to root@node101(192.168.6.101:22).. Sun Sep 26 17:57:22 2021 - [debug] ok. Sun Sep 26 17:57:22 2021 - [debug] Connecting via SSH from root@node102(192.168.6.102:22) to root@node103(192.168.6.103:22).. Sun Sep 26 17:57:23 2021 - [debug] ok. Sun Sep 26 17:57:23 2021 - [info] All SSH connection tests passed successfully.
报错：/root/perl5/lib/perl5/MHA/SSHCheck.pm line 148. 可能的原因是 mha.conf中有空格或特殊字符
解决措施：
(1)有空格去掉空格
(2)yum install -y dos2unix
dos2unix mha.conf
检测主从架构，如果检测结果全部正常 那么就代表没问题了
[root@node104 bin]# /root/mha4mysql-manager-master/bin/masterha_check_repl --conf=/etc/mha/mha.conf Mon Sep 27 10:00:15 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Sep 27 10:00:15 2021 - [info] Reading application default configuration from /etc/mha/mha.conf.. Mon Sep 27 10:00:15 2021 - [info] Reading server configuration from /etc/mha/mha.conf.. Mon Sep 27 10:00:15 2021 - [info] MHA::MasterMonitor version 0.58. Mon Sep 27 10:00:16 2021 - [info] GTID failover mode = 0 Mon Sep 27 10:00:16 2021 - [info] Dead Servers: Mon Sep 27 10:00:16 2021 - [info] Alive Servers: Mon Sep 27 10:00:16 2021 - [info] node101(192.168.6.101:3306) Mon Sep 27 10:00:16 2021 - [info] node102(192.168.6.102:3306) Mon Sep 27 10:00:16 2021 - [info] node103(192.168.6.103:3306) Mon Sep 27 10:00:16 2021 - [info] Alive Slaves: Mon Sep 27 10:00:16 2021 - [info] node102(192.168.6.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 10:00:16 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 10:00:16 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 10:00:16 2021 - [info] node103(192.168.6.103:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 10:00:16 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 10:00:16 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 10:00:16 2021 - [info] Current Alive Master: node101(192.168.6.101:3306) Mon Sep 27 10:00:16 2021 - [info] Checking slave configurations.. Mon Sep 27 10:00:16 2021 - [info] read_only=1 is not set on slave node102(192.168.6.102:3306). Mon Sep 27 10:00:16 2021 - [warning] relay_log_purge=0 is not set on slave node102(192.168.6.102:3306). Mon Sep 27 10:00:16 2021 - [info] read_only=1 is not set on slave node103(192.168.6.103:3306). Mon Sep 27 10:00:16 2021 - [warning] relay_log_purge=0 is not set on slave node103(192.168.6.103:3306). Mon Sep 27 10:00:16 2021 - [info] Checking replication filtering settings.. Mon Sep 27 10:00:16 2021 - [info] binlog_do_db= , binlog_ignore_db= Mon Sep 27 10:00:16 2021 - [info] Replication filtering check ok. Mon Sep 27 10:00:16 2021 - [info] GTID (with auto-pos) is not supported Mon Sep 27 10:00:16 2021 - [info] Starting SSH connection tests.. Mon Sep 27 10:00:19 2021 - [info] All SSH connection tests passed successfully. Mon Sep 27 10:00:19 2021 - [info] Checking MHA Node version.. Mon Sep 27 10:00:19 2021 - [info] Version check ok. Mon Sep 27 10:00:19 2021 - [info] Checking SSH publickey authentication settings on the current master.. Mon Sep 27 10:00:20 2021 - [info] HealthCheck: SSH to node101 is reachable. Mon Sep 27 10:00:20 2021 - [info] Master MHA Node version is 0.58. Mon Sep 27 10:00:20 2021 - [info] Checking recovery script configurations on node101(192.168.6.101:3306).. Mon Sep 27 10:00:20 2021 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/usr/local/mha/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin101.000001 Mon Sep 27 10:00:20 2021 - [info] Connecting to root@192.168.6.101(node101:22).. Creating /usr/local/mha if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /usr/local/mysql/data, up to mysql-bin101.000001 Mon Sep 27 10:00:21 2021 - [info] Binlog setting check done. Mon Sep 27 10:00:21 2021 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Mon Sep 27 10:00:21 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=node102 --slave_ip=192.168.6.102 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.26-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx Mon Sep 27 10:00:21 2021 - [info] Connecting to root@192.168.6.102(node102:22).. Checking slave recovery environment settings.. Opening /usr/local/mysql/data/relay-log.info ... ok. Relay log found at /usr/local/mysql/data, up to node102-relay-bin.000002 Temporary relay log file is /usr/local/mysql/data/node102-relay-bin.000002 Checking if super_read_only is defined and turned on.. not present or turned off, ignoring. Testing mysql connection and privileges.. mysql: [Warning] Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Sep 27 10:00:21 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=node103 --slave_ip=192.168.6.103 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.26-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx Mon Sep 27 10:00:21 2021 - [info] Connecting to root@192.168.6.103(node103:22).. Checking slave recovery environment settings.. Opening /usr/local/mysql/data/relay-log.info ... ok. Relay log found at /usr/local/mysql/data, up to node103-relay-bin.000002 Temporary relay log file is /usr/local/mysql/data/node103-relay-bin.000002 Checking if super_read_only is defined and turned on.. not present or turned off, ignoring. Testing mysql connection and privileges.. mysql: [Warning] Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Sep 27 10:00:22 2021 - [info] Slaves settings check done. Mon Sep 27 10:00:22 2021 - [info] node101(192.168.6.101:3306) (current master) +--node102(192.168.6.102:3306) +--node103(192.168.6.103:3306) Mon Sep 27 10:00:22 2021 - [info] Checking replication health on node102.. Mon Sep 27 10:00:22 2021 - [info] ok. Mon Sep 27 10:00:22 2021 - [info] Checking replication health on node103.. Mon Sep 27 10:00:22 2021 - [info] ok. Mon Sep 27 10:00:22 2021 - [info] Checking master_ip_failover_script status: Mon Sep 27 10:00:22 2021 - [info] /usr/local/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=node101 --orig_master_ip=192.168.6.101 --orig_master_port=3306 IN SCRIPT TEST====ifconfig eth33:0 down==ifconfig eth33:0 192.168.6.123/24=== Mon Sep 27 10:00:22 2021 - [info] OK. Mon Sep 27 10:00:22 2021 - [warning] shutdown_script is not defined. Mon Sep 27 10:00:22 2021 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
报错1：Can't exec "mysqlbinlog": No such file or directory at /root/perl5/lib/perl5/MHA/BinlogManager.pm line 106.
解决方案：所有节点执行：ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog && ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
报错2：[error][/root/perl5/lib/perl5/MHA/Server.pm, ln490] Slave IO thread is not running on node101(192.168.6.101:3306)
node101是主库，不能开启slave;
解决方案：主库执行 stop slave; reset slave all;
报错3：[error][/root/perl5/lib/perl5/MHA/MasterMonitor.pm, ln364] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf
解决方案：两个从库上开启二进制日志即可,注意logbin名字不能相同，修改完成后 要重置slave;
6.启动MHA manager节点
#启动 [root@node104 bin]# nohup /root/mha4mysql-manager-master/bin/masterha_manager --conf=/etc/mha/mha.conf > /tmp/mha_manager.log < /dev/null 2>&1 & [1] 7776 #验证是否启动成功 [root@node104 bin]# /root/mha4mysql-manager-master/bin/masterha_check_status --conf=/etc/mha/mha.conf mha (pid:7776) is running(0:PING_OK), master:node101
7.测试数据库高可用
停掉主库,然后从库会自动获取主库机器上的VIP,同时从库会被转换为新的主库，其他从库也会指向新的主库，这些都是MHA自动给你完成的，然后可以把宕机的主库重启启动，然后把他配置为从库，指向新的主库就可以了。
切换前主库初始状态：
mysql> show master status; +---------------------+----------+--------------+------------------+----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------+----------+--------------+------------------+----------------------------------------------+ | mysql-bin101.000005 | 154 | | | dc7ac5be-15d0-11ec-a1d1-000c295db9f6:1-25569 | +---------------------+----------+--------------+------------------+----------------------------------------------+ 1 row in set (0.02 sec) mysql> select * from information_schema.processlist as p where p.command = 'Binlog Dump' ORDER BY ID; +----+-------------+---------------+------+-------------+------+---------------------------------------------------------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+-------------+---------------+------+-------------+------+---------------------------------------------------------------+------+ | 3 | backup_user | node102:57184 | NULL | Binlog Dump | 21 | Master has sent all binlog to slave; waiting for more updates | NULL | | 4 | backup_user | node103:57304 | NULL | Binlog Dump | 5 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+-------------+---------------+------+-------------+------+---------------------------------------------------------------+------+ 2 rows in set (0.02 sec)
关掉主库：
[root@node101 ~]# systemctl stop mysql
查看mha日志：
[root@node101 ~]# tail -50f /usr/local/mha/manager.log Mon Sep 27 15:06:08 2021 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) Mon Sep 27 15:06:08 2021 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/usr/local/mha/save_binary_logs_test --manager_version=0.58 --binlog_prefix=mysql-bin101 Mon Sep 27 15:06:08 2021 - [info] HealthCheck: SSH to node101 is reachable. Mon Sep 27 15:06:09 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.6.101' (111)) Mon Sep 27 15:06:09 2021 - [warning] Connection failed 2 time(s).. Mon Sep 27 15:06:10 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.6.101' (111)) Mon Sep 27 15:06:10 2021 - [warning] Connection failed 3 time(s).. Mon Sep 27 15:06:11 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.6.101' (111)) Mon Sep 27 15:06:11 2021 - [warning] Connection failed 4 time(s).. Mon Sep 27 15:06:11 2021 - [warning] Master is not reachable from health checker! Mon Sep 27 15:06:11 2021 - [warning] Master node101(192.168.6.101:3306) is not reachable! Mon Sep 27 15:06:11 2021 - [warning] SSH is reachable. Mon Sep 27 15:06:11 2021 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mha.conf again, and trying to connect to all servers to check server status.. Mon Sep 27 15:06:11 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Sep 27 15:06:11 2021 - [info] Reading application default configuration from /etc/mha/mha.conf.. Mon Sep 27 15:06:11 2021 - [info] Reading server configuration from /etc/mha/mha.conf.. Mon Sep 27 15:06:12 2021 - [info] GTID failover mode = 0 Mon Sep 27 15:06:12 2021 - [info] Dead Servers: Mon Sep 27 15:06:12 2021 - [info] node101(192.168.6.101:3306) Mon Sep 27 15:06:12 2021 - [info] Alive Servers: Mon Sep 27 15:06:12 2021 - [info] node102(192.168.6.102:3306) Mon Sep 27 15:06:12 2021 - [info] node103(192.168.6.103:3306) Mon Sep 27 15:06:12 2021 - [info] Alive Slaves: Mon Sep 27 15:06:12 2021 - [info] node102(192.168.6.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:12 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:12 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:12 2021 - [info] node103(192.168.6.103:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:12 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:12 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:12 2021 - [info] Checking slave configurations.. Mon Sep 27 15:06:12 2021 - [info] read_only=1 is not set on slave node102(192.168.6.102:3306). Mon Sep 27 15:06:12 2021 - [warning] relay_log_purge=0 is not set on slave node102(192.168.6.102:3306). Mon Sep 27 15:06:12 2021 - [info] read_only=1 is not set on slave node103(192.168.6.103:3306). Mon Sep 27 15:06:12 2021 - [warning] relay_log_purge=0 is not set on slave node103(192.168.6.103:3306). Mon Sep 27 15:06:12 2021 - [info] Checking replication filtering settings.. Mon Sep 27 15:06:12 2021 - [info] Replication filtering check ok. Mon Sep 27 15:06:12 2021 - [info] Master is down! Mon Sep 27 15:06:12 2021 - [info] Terminating monitoring script. Mon Sep 27 15:06:12 2021 - [info] Got exit code 20 (Master dead). Mon Sep 27 15:06:12 2021 - [info] MHA::MasterFailover version 0.58. Mon Sep 27 15:06:12 2021 - [info] Starting master failover. Mon Sep 27 15:06:12 2021 - [info] Mon Sep 27 15:06:12 2021 - [info] * Phase 1: Configuration Check Phase.. Mon Sep 27 15:06:12 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] GTID failover mode = 0 Mon Sep 27 15:06:13 2021 - [info] Dead Servers: Mon Sep 27 15:06:13 2021 - [info] node101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Checking master reachability via MySQL(double check)... Mon Sep 27 15:06:13 2021 - [info] ok. Mon Sep 27 15:06:13 2021 - [info] Alive Servers: Mon Sep 27 15:06:13 2021 - [info] node102(192.168.6.102:3306) Mon Sep 27 15:06:13 2021 - [info] node103(192.168.6.103:3306) Mon Sep 27 15:06:13 2021 - [info] Alive Slaves: Mon Sep 27 15:06:13 2021 - [info] node102(192.168.6.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:13 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:13 2021 - [info] node103(192.168.6.103:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:13 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:13 2021 - [info] Starting Non-GTID based failover. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] ** Phase 1: Configuration Check Phase completed. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] * Phase 2: Dead Master Shutdown Phase.. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] Forcing shutdown so that applications never connect to the current master.. Mon Sep 27 15:06:13 2021 - [info] Executing master IP deactivation script: Mon Sep 27 15:06:13 2021 - [info] /usr/local/scripts/master_ip_failover --orig_master_host=node101 --orig_master_ip=192.168.6.101 --orig_master_port=3306 --command=stopssh --ssh_user=root IN SCRIPT TEST====ifconfig ens33:0 down==ifconfig ens33:0 192.168.6.123/24=== Disabling the VIP on old master: node101 SIOCSIFFLAGS: Cannot assign requested address Mon Sep 27 15:06:13 2021 - [info] done. Mon Sep 27 15:06:13 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Mon Sep 27 15:06:13 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] * Phase 3: Master Recovery Phase.. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] The latest binary log file/position on all slaves is mysql-bin101.000005:154 Mon Sep 27 15:06:13 2021 - [info] Latest slaves (Slaves that received relay log files to the latest): Mon Sep 27 15:06:13 2021 - [info] node102(192.168.6.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:13 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:13 2021 - [info] node103(192.168.6.103:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:13 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:13 2021 - [info] The oldest binary log file/position on all slaves is mysql-bin101.000005:154 Mon Sep 27 15:06:13 2021 - [info] Oldest slaves: Mon Sep 27 15:06:13 2021 - [info] node102(192.168.6.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:13 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:13 2021 - [info] node103(192.168.6.103:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:13 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:13 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Mon Sep 27 15:06:13 2021 - [info] Mon Sep 27 15:06:13 2021 - [info] Fetching dead master's binary logs.. Mon Sep 27 15:06:13 2021 - [info] Executing command on the dead master node101(192.168.6.101:3306): save_binary_logs --command=save --start_file=mysql-bin101.000005 --start_pos=154 --binlog_dir=/usr/local/mysql/data --output_file=/usr/local/mha/saved_master_binlog_from_node101_3306_20210927150612.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.58 Creating /usr/local/mha if not exists.. ok. Concat binary/relay logs from mysql-bin101.000005 pos 154 to mysql-bin101.000005 EOF into /usr/local/mha/saved_master_binlog_from_node101_3306_20210927150612.binlog .. Binlog Checksum enabled Dumping binlog format description event, from position 0 to 154.. ok. No need to dump effective binlog data from /usr/local/mysql/data/mysql-bin101.000005 (pos starts 154, filesize 154). Skipping. Binlog Checksum enabled /usr/local/mha/saved_master_binlog_from_node101_3306_20210927150612.binlog has no effective data events. Event not exists. Mon Sep 27 15:06:14 2021 - [info] Additional events were not found from the orig master. No need to save. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] * Phase 3.3: Determining New Master Phase.. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] Finding the latest slave that has all relay logs for recovering other slaves.. Mon Sep 27 15:06:14 2021 - [info] All slaves received relay logs to the same position. No need to resync each other. Mon Sep 27 15:06:14 2021 - [info] Searching new master from slaves.. Mon Sep 27 15:06:14 2021 - [info] Candidate masters from the configuration file: Mon Sep 27 15:06:14 2021 - [info] node102(192.168.6.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:14 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:14 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:14 2021 - [info] node103(192.168.6.103:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled Mon Sep 27 15:06:14 2021 - [info] Replicating from 192.168.6.101(192.168.6.101:3306) Mon Sep 27 15:06:14 2021 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 27 15:06:14 2021 - [info] Non-candidate masters: Mon Sep 27 15:06:14 2021 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Mon Sep 27 15:06:14 2021 - [info] New master is node102(192.168.6.102:3306) Mon Sep 27 15:06:14 2021 - [info] Starting master failover.. Mon Sep 27 15:06:14 2021 - [info] From: node101(192.168.6.101:3306) (current master) +--node102(192.168.6.102:3306) +--node103(192.168.6.103:3306) To: node102(192.168.6.102:3306) (new master) +--node103(192.168.6.103:3306) Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] * Phase 3.4: New Master Diff Log Generation Phase.. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] * Phase 3.5: Master Log Apply Phase.. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed. Mon Sep 27 15:06:14 2021 - [info] Starting recovery on node102(192.168.6.102:3306).. Mon Sep 27 15:06:14 2021 - [info] This server has all relay logs. Waiting all logs to be applied.. Mon Sep 27 15:06:14 2021 - [info] done. Mon Sep 27 15:06:14 2021 - [info] All relay logs were successfully applied. Mon Sep 27 15:06:14 2021 - [info] Getting new master's binlog name and position.. Mon Sep 27 15:06:14 2021 - [info] mysql-bin102.000001:154 Mon Sep 27 15:06:14 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='node102 or 192.168.6.102', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin102.000001', MASTER_LOG_POS=154, MASTER_USER='backup_user', MASTER_PASSWORD='xxx'; Mon Sep 27 15:06:14 2021 - [info] Executing master IP activate script: Mon Sep 27 15:06:14 2021 - [info] /usr/local/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=node101 --orig_master_ip=192.168.6.101 --orig_master_port=3306 --new_master_host=node102 --new_master_ip=192.168.6.102 --new_master_port=3306 --new_master_user='root' --new_master_password=xxx Unknown option: new_master_user Unknown option: new_master_password IN SCRIPT TEST====ifconfig ens33:0 down==ifconfig ens33:0 192.168.6.123/24=== Enabling the VIP - 192.168.6.123/24 on the new master - node102 Mon Sep 27 15:06:14 2021 - [info] OK. Mon Sep 27 15:06:14 2021 - [info] ** Finished master recovery successfully. Mon Sep 27 15:06:14 2021 - [info] * Phase 3: Master Recovery Phase completed. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] * Phase 4: Slaves Recovery Phase.. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Mon Sep 27 15:06:14 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] -- Slave diff file generation on host node103(192.168.6.103:3306) started, pid: 8976. Check tmp log /usr/local/mha/node103_3306_20210927150612.log if it takes time.. Mon Sep 27 15:06:15 2021 - [info] Mon Sep 27 15:06:15 2021 - [info] Log messages from node103 ... Mon Sep 27 15:06:15 2021 - [info] Mon Sep 27 15:06:14 2021 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Mon Sep 27 15:06:15 2021 - [info] End of log messages from node103. Mon Sep 27 15:06:15 2021 - [info] -- node103(192.168.6.103:3306) has the latest relay log events. Mon Sep 27 15:06:15 2021 - [info] Generating relay diff files from the latest slave succeeded. Mon Sep 27 15:06:15 2021 - [info] Mon Sep 27 15:06:15 2021 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Mon Sep 27 15:06:15 2021 - [info] Mon Sep 27 15:06:15 2021 - [info] -- Slave recovery on host node103(192.168.6.103:3306) started, pid: 8978. Check tmp log /usr/local/mha/node103_3306_20210927150612.log if it takes time.. Mon Sep 27 15:06:16 2021 - [info] Mon Sep 27 15:06:16 2021 - [info] Log messages from node103 ... Mon Sep 27 15:06:16 2021 - [info] Mon Sep 27 15:06:15 2021 - [info] Starting recovery on node103(192.168.6.103:3306).. Mon Sep 27 15:06:15 2021 - [info] This server has all relay logs. Waiting all logs to be applied.. Mon Sep 27 15:06:15 2021 - [info] done. Mon Sep 27 15:06:15 2021 - [info] All relay logs were successfully applied. Mon Sep 27 15:06:15 2021 - [info] Resetting slave node103(192.168.6.103:3306) and starting replication from the new master node102(192.168.6.102:3306).. Mon Sep 27 15:06:15 2021 - [info] Executed CHANGE MASTER. Mon Sep 27 15:06:15 2021 - [info] Slave started. Mon Sep 27 15:06:16 2021 - [info] End of log messages from node103. Mon Sep 27 15:06:16 2021 - [info] -- Slave recovery on host node103(192.168.6.103:3306) succeeded. Mon Sep 27 15:06:16 2021 - [info] All new slave servers recovered successfully. Mon Sep 27 15:06:16 2021 - [info] Mon Sep 27 15:06:16 2021 - [info] * Phase 5: New master cleanup phase.. Mon Sep 27 15:06:16 2021 - [info] Mon Sep 27 15:06:16 2021 - [info] Resetting slave info on the new master.. Mon Sep 27 15:06:16 2021 - [info] node102: Resetting slave info succeeded. Mon Sep 27 15:06:16 2021 - [info] Master failover to node102(192.168.6.102:3306) completed successfully. Mon Sep 27 15:06:16 2021 - [info] ----- Failover Report ----- mha: MySQL Master failover node101(192.168.6.101:3306) to node102(192.168.6.102:3306) succeeded Master node101(192.168.6.101:3306) is down! Check MHA Manager logs at node104:/usr/local/mha/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on node101(192.168.6.101:3306) The latest slave node102(192.168.6.102:3306) has all relay logs for recovery. Selected node102(192.168.6.102:3306) as a new master. node102(192.168.6.102:3306): OK: Applying all logs succeeded. node102(192.168.6.102:3306): OK: Activated master IP address. node103(192.168.6.103:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. node103(192.168.6.103:3306): OK: Applying all logs succeeded. Slave started, replicating from node102(192.168.6.102:3306) node102(192.168.6.102:3306): Resetting slave info succeeded. Master failover to node102(192.168.6.102:3306) completed successfully.
切换后：
#node102:
mysql> select * from information_schema.processlist as p where p.command = 'Binlog Dump' ORDER BY ID; +----+-------------+---------------+------+-------------+------+---------------------------------------------------------------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+-------------+---------------+------+-------------+------+---------------------------------------------------------------+------+ | 71 | backup_user | node103:49882 | NULL | Binlog Dump | 298 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+-------------+---------------+------+-------------+------+---------------------------------------------------------------+------+ 1 row in set (0.02 sec) mysql> show master status; +---------------------+----------+--------------+------------------+----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------+----------+--------------+------------------+----------------------------------------------+ | mysql-bin102.000001 | 154 | | | dc7ac5be-15d0-11ec-a1d1-000c295db9f6:1-25569 | +---------------------+----------+--------------+------------------+----------------------------------------------+ 1 row in set (0.03 sec)
#node103：
mysql> show SLAVE STATUS; +----------------------------------+---------------+-------------+-------------+---------------+---------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+----------------------------------------------+---------------+----------------------+--------------+--------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | +----------------------------------+---------------+-------------+-------------+---------------+---------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+----------------------------------------------+---------------+----------------------+--------------+--------------------+ | Waiting for master to send event | 192.168.6.102 | backup_user | 3306 | 60 | mysql-bin102.000001 | 154 | node103-relay-bin.000002 | 323 | mysql-bin102.000001 | Yes | Yes | | | | | | | 0 | | 0 | 154 | 532 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 102 | 3abe35cd-1c0d-11ec-a939-005056256212 | /usr/local/mysql/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | dc7ac5be-15d0-11ec-a1d1-000c295db9f6:1-25569 | 0 | | | | +----------------------------------+---------------+-------------+-------------+---------------+---------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+----------------------------------------------+---------------+----------------------+--------------+--------------------+ 1 row in set (0.03 sec)
根据结果来看，node102变成master,node103仍然为slave连接node102。
参考：
学习技术不是用来写HelloWorld和Demo的，而是要用来解决线上系统的真实问题的.
