| 环境 | 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 Slave、MHA Node | |
| node103：192.168.6.103 | ||
| 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 View Code
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
四、配置MHA manager
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 [email protected](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 [email protected](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 [email protected](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。
参考：
MHA
教你最新版本VIP模式搭建
mha4mysql-manager Installation
学习技术不是用来写HelloWorld和Demo的，而是要用来解决线上系统的真实问题的.