MySQL高可用篇之MHA集群

MySQL⾼可⽤篇之MHA集群
很多⼩伙伴反映说⽹上的MHA教程甚⾄收费的课程⾥的MHA教程都存在坑，不少教程只是搭建完成了，是否真的能在主库宕机时⾃动切换不得⽽知，鉴于此情况，简单写了⼀个MHA集群的搭建步骤。

由于搭建的次数（本⽂篇幅相对较长，建议收藏，如对你有帮助，帮忙⽂末点个推荐或关注公众号：数据库⼲货铺，谢谢）
1 准备⼯作
1.1 修改主机名
vim /etc/hosts
# 添加对应主机192.168.28.128 mha1192.168.28.131 mha2192.168.28.132 mha3
1.2 关闭防⽕墙及修改selinux
# 关闭防⽕墙
systemctl stop firewalldsystemctl disable firewalld # 关闭⾃启动
# 修改selinux
vim /etc/sysconfig/selinux
SELINUX=disabled # 设置为disabled
1.3 部署⼀套1主2从的MySQL集群
创建主从可以参考 MySQL主从搭建
注意必须有如下参数
server-id=1 # 每个节点不能相同
log-bin=/data/mysql3306/logs/mysql-bin
relay-log=/data/mysql3306/logs/relay-log
skip-name-resolve # 建议加上⾮必须项
#read_only = ON # 从库开启，主库关闭只读
relay_log_purge = 0 # 关闭⾃动清理中继⽇志
log_slave_updates = 1 # 从库通过binlog更新的数据写进从库⼆进制⽇志中，必加，否则切换后可能丢失数据
创建mha管理账号
# 特别注意： mha的密码不要出现特殊字符，否则后⾯⽆法切换主库create user mha@'192.168.28.%' identified by 'MHAadmin123';create user mha@'localhost' identified by 'MHAadmin123';grant all on *.* to mha@'192.168.28.%';grant all on *.* to mha@'localh 1.4 配置互信
MHA管理节点上执⾏（但建议每台主机均执⾏，便于切换管理节点及集群间维护，但注意主机安全），包含本机到本机的互信
ssh-keygen
ssh-copy-id-i~/.ssh/**********************.28.128
ssh-copy-id-i~/.ssh/**********************.28.131
ssh-copy-id-i~/.ssh/**********************.28.132
配置完成后记得测试⼀下是否配置成功（必须测试）
***************.28.128
***************.28.131
***************.28.132
ssh root@mha1
ssh root@mha2
ssh root@mha3
2 MHA⼯具部署
2.1 安装MHA相关依赖包
yum install perl-DBI perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-Params-Validate perl-DateTime -y
yum install perl-ExtUtils-Embed -y
yum install cpan -y
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
注意： MySQL数据库安装时不建议⽤rpm包⽅式安装，否则此处部分包可能有冲突
2.2 安装MHA 管理及node节点
# 所有节点均需安装
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#管理节点需安装（其他节点也可以安装）
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
如果以上安装包未安装全，则会出现类似下⾯的错误，如出现可以调整yum源或找下载好的同学获取
[root@mha3 local]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
error: Failed dependencies:
perl(Log::Dispatch) is needed by mha4mysql-manager-0.58-0.el7.centos.noarch
perl(Log::Dispatch::File) is needed by mha4mysql-manager-0.58-0.el7.centos.noarch
perl(Log::Dispatch::Screen) is needed by mha4mysql-manager-0.58-0.el7.centos.noarch
perl(Parallel::ForkManager) is needed by mha4mysql-manager-0.58-0.el7.centos.noarch
2.3 配置mha
创建配置⽂件路径、⽇志⽂件路径
mkdir -p /etc/masterha
mkdir -p /var/log/masterha/app1
创建mha配置⽂件
vim /etc/masterha/app1.confuser=mha[server default]manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/app1.logmaster_ip_failover_script=/usr/bin/master_ip_failover
master_ip_online_change_script=/usr/bin/master_ip_online_change
##mysql⽤户名和密码user=mha
password=MHAadmin123
password=MHAadmin123
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=3remote_workdir=/tmp
report_script=/usr/bin/send_report# secondary_check_script 可以不加
# secondary_check_script=/usr/bin/masterha_secondary_check -s mha2 -s mha3 --user=mha --master_host=mha1 --master_ip=192.168.28.128 --master_port=3306 --password=MHAadmin123
shutdown_script=""
report_script=""[server1]hostname=192.168.28.128master_binlog_dir=/data/mysql3306/logs
candidate_master=1
[server2]hostname=192.168.28.131master_binlog_dir=/data/mysql3306/logs
candidate_master=1check_repl_delay=0
[server3]hostname=192.168.28.132master_binlog_dir=/data/mysql3306/logs
no_master=1
配置两个重要的脚本 master_ip_failover 、 master_ip_online_change
/usr/bin/master_ip_failover
vim /usr/bin/master_ip_failover
#!/usr/bin/env perluse 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
);
my $vip = '192.168.28.199/24';
my $if = 'ens33';
my $ssh_start_vip = "/sbin/ip addr add $vip dev $if";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev $if";
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"; }
/usr/bin/master_ip_online_change
vim /usr/bin/master_ip_online_change
#!/usr/bin/env perluse 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
#);
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,
);
my $vip = '192.168.28.199/24';
my $if = 'ens33';
my $ssh_start_vip = "/sbin/ip addr add $vip dev $if";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev $if";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev $if";
my $ssh_user = "root";
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,
'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 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 --ssh-user=user --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"; }
2.4 相关检测
检测互信
检查各节点互信是否正常，类似于之前的检查，此处有脚本实现检查
[root@mha3 app1]# masterha_check_ssh --conf=/etc/masterha/app1.conf
Sun May 24 17:33:08 2020 - [warning] Global configuration file /etc/masterha_f not found. Skipping.
Sun May 24 17:33:08 2020 - [info] Reading application default configuration from /etc/masterha/app1.conf..
Sun May 24 17:33:08 2020 - [info] Reading server configuration from /etc/masterha/app1.conf..
Sun May 24 17:33:08 2020 - [info] Starting SSH connection tests..
Sun May 24 17:33:12 2020 - [debug]
SunMay2417:33:082020-[debug]********************************.28.131(192.168.28.131:22)**************.28.128(192.168.28.128:22)..
Sun May 24 17:33:10 2020 - [debug] ok.
SunMay2417:33:102020-[debug]********************************.28.131(192.168.28.131:22)**************.28.132(192.168.28.132:22)..
Sun May 24 17:33:12 2020 - [debug] ok.
Sun May 24 17:33:12 2020 - [debug]
SunMay2417:33:082020-[debug]********************************.28.128(192.168.28.128:22)**************.28.131(192.168.28.131:22)..
Sun May 24 17:33:09 2020 - [debug] ok.
SunMay2417:33:092020-[debug]********************************.28.128(192.168.28.128:22)**************.28.132(192.168.28.132:22)..
Sun May 24 17:33:12 2020 - [debug] ok.
Sun May 24 17:33:13 2020 - [debug]
SunMay2417:33:092020-[debug]********************************.28.132(192.168.28.132:22)**************.28.128(192.168.28.128:22)..
Sun May 24 17:33:11 2020 - [debug] ok.
SunMay2417:33:112020-[debug]********************************.28.132(192.168.28.132:22)**************.28.131(192.168.28.131:22)..
Sun May 24 17:33:13 2020 - [debug] ok.
Sun May 24 17:33:13 2020 - [info] All SSH connection tests passed successfully.
检查复制集群是否正常
如按照我之前的步骤配置，则此处会有如下异常
masterha_check_repl --conf=/etc/masterha/app1.conf
SunMay2417:34:022020-[info]************************.28.131(192.168.28.131:22)..
Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
at /usr/bin/apply_diff_relay_logs line 532.
报错信息很明确，找不到mysqlbinlog命令,处理⽅式⽐较简单，做个软连接即可
ln -s /usr/local/mysql5.7/bin/mysql /usr/bin/ ln -s /usr/local/mysql5.7/bin/mysqlbinlog /usr/bin/
再进⾏检测
[root@mha3 app1]# masterha_check_repl --conf=/etc/masterha/app1.conf
Sun May 24 17:34:41 2020 - [warning] Global configuration file /etc/masterha_f not found. Skipping.
Sun May 24 17:34:41 2020 - [info] Reading application default configuration from /etc/masterha/app1.conf..
Sun May 24 17:34:41 2020 - [info] Reading server configuration from /etc/masterha/app1.conf..
Sun May 24 17:34:41 2020 - [info] MHA::MasterMonitor version 0.58.
Sun May 24 17:34:42 2020 - [info] GTID failover mode = 0Sun May 24 17:34:42 2020 - [info] Dead Servers:
Sun May 24 17:34:42 2020 - [info] Alive Servers:
Sun May 24 17:34:42 2020 - [info] 192.168.28.128(192.168.28.128:3306)
Sun May 24 17:34:42 2020 - [info] 192.168.28.131(192.168.28.131:3306)
Sun May 24 17:34:42 2020 - [info] 192.168.28.132(192.168.28.132:3306)
Sun May 24 17:34:42 2020 - [info] Alive Slaves:
Sun May 24 17:34:42 2020 - [info] 192.168.28.131(192.168.28.131:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 17:34:42 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 17:34:42 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sun May 24 17:34:42 2020 - [info] 192.168.28.132(192.168.28.132:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 17:34:42 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 17:34:42 2020 - [info] Not candidate for the new Master (no_master is set)
Sun May 24 17:34:42 2020 - [info] Current Alive Master: 192.168.28.128(192.168.28.128:3306)
Sun May 24 17:34:42 2020 - [info] Checking slave configurations..
Sun May 24 17:34:42 2020 - [info] Checking replication filtering settings..
Sun May 24 17:34:42 2020 - [info] binlog_do_db= , binlog_ignore_db=
Sun May 24 17:34:42 2020 - [info] Replication filtering check ok.
Sun May 24 17:34:42 2020 - [info] GTID (with auto-pos) is not supported
Sun May 24 17:34:42 2020 - [info] Starting SSH connection tests..
Sun May 24 17:34:48 2020 - [info] All SSH connection tests passed successfully.
Sun May 24 17:34:48 2020 - [info] Checking MHA Node version..
Sun May 24 17:34:49 2020 - [info] Version check ok.
Sun May 24 17:34:49 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sun May 24 17:34:50 2020 - [info] HealthCheck: SSH to 192.168.28.128 is reachable.
Sun May 24 17:34:51 2020 - [info] Master MHA Node version is 0.58.
Sun May 24 17:34:51 2020 - [info] Checking recovery script configurations on 192.168.28.128(192.168.28.128:3306)..
Sun May 24 17:34:51 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql3306/data --output_file=/tmp/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000012
SunMay2417:34:512020-[info]************************.28.128(192.168.28.128:22)..
Creating /tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql3306/data, up to mysql-bin.000012Sun May 24 17:34:52 2020 - [info] Binlog setting check done.
Sun May 24 17:34:52 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sun May 24 17:34:52 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.28.131 --slave_ip=192.168.28.131 --slave_port=3306 --workdir=/tmp --target_version=5.7.25-28-log --manager_version=0.5 SunMay2417:34:522020-[info]************************.28.131(192.168.28.131:22)..
Checking slave recovery environment settings..
Opening /data/mysql3306/data/ ... ok.
Relay log found at /data/mysql3306/data, up to relay-log.000003
Temporary relay log file is /data/mysql3306/data/relay-log.000003 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.
Sun May 24 17:34:53 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.28.132 --slave_ip=192.168.28.132 --slave_port=3306 --workdir=/tmp --target_version=5.7.25-28-log --manager_version=0.5 SunMay2417:34:532020-[info]************************.28.132(192.168.28.132:22)..
Checking slave recovery environment settings..
Opening /data/mysql3306/data/ ... ok.
Relay log found at /data/mysql3306/data, up to relay-log.000003
Temporary relay log file is /data/mysql3306/data/relay-log.000003 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.
Sun May 24 17:34:54 2020 - [info] Slaves settings check done.
Sun May 24 17:34:54 2020 - [info]
192.168.28.128(192.168.28.128:3306) (current master)
+--192.168.28.131(192.168.28.131:3306)
+--192.168.28.132(192.168.28.132:3306)
Sun May 24 17:34:54 2020 - [info] Checking replication health on 192.168.28.131..
Sun May 24 17:34:54 2020 - [info] ok.
Sun May 24 17:34:54 2020 - [info] Checking replication health on 192.168.28.132..
Sun May 24 17:34:54 2020 - [info] ok.
Sun May 24 17:34:54 2020 - [info] Checking master_ip_failover_script status:
Sun May 24 17:34:54 2020 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.28.128 --orig_master_ip=192.168.28.128 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ip addr del 192.168.28.199/24 dev ens33==/sbin/ip addr add 192.168.28.199/24 dev ens33===Checking the Status of the script.. OK
Sun May 24 17:34:54 2020 - [info] OK.
Sun May 24 17:34:54 2020 - [warning] shutdown_script is not defined.
Sun May 24 17:34:54 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
看到 "MySQL Replication Health is OK." 代表检测通过。

3 MHA测试
3.1 开启MHA服务
开启MHA服务的脚本如下，也可以写成脚本或服务
nohup masterha_manager --conf=/etc/masterha/app1.conf < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
开启服务后，⽇志如下，与集群检测类似
Sun May 24 18:31:54 2020 - [info] MHA::MasterMonitor version 0.58.
Sun May 24 18:31:55 2020 - [info] GTID failover mode = 0Sun May 24 18:31:55 2020 - [info] Dead Servers:
Sun May 24 18:31:55 2020 - [info] Alive Servers:
Sun May 24 18:31:55 2020 - [info] 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:31:55 2020 - [info] 192.168.28.131(192.168.28.131:3306)
Sun May 24 18:31:55 2020 - [info] 192.168.28.132(192.168.28.132:3306)
Sun May 24 18:31:55 2020 - [info] Alive Slaves:
Sun May 24 18:31:55 2020 - [info] 192.168.28.131(192.168.28.131:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 18:31:55 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:31:55 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sun May 24 18:31:55 2020 - [info] 192.168.28.132(192.168.28.132:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 18:31:55 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:31:55 2020 - [info] Not candidate for the new Master (no_master is set)
Sun May 24 18:31:55 2020 - [info] Current Alive Master: 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:31:55 2020 - [info] Checking slave configurations..
Sun May 24 18:31:55 2020 - [info] Checking replication filtering settings..
Sun May 24 18:31:55 2020 - [info] binlog_do_db= , binlog_ignore_db=Sun May 24 18:31:55 2020 - [info] Replication filtering check ok.
Sun May 24 18:31:55 2020 - [info] GTID (with auto-pos) is not supported
Sun May 24 18:31:55 2020 - [info] Starting SSH connection tests..
Sun May 24 18:32:01 2020 - [info] All SSH connection tests passed successfully.
Sun May 24 18:32:01 2020 - [info] Checking MHA Node version..
Sun May 24 18:32:03 2020 - [info] Version check ok.
Sun May 24 18:32:03 2020 - [info] Version check ok.
Sun May 24 18:32:03 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sun May 24 18:32:03 2020 - [info] HealthCheck: SSH to 192.168.28.128 is reachable.
Sun May 24 18:32:04 2020 - [info] Master MHA Node version is 0.58.
Sun May 24 18:32:04 2020 - [info] Checking recovery script configurations on 192.168.28.128(192.168.28.128:3306)..
Sun May 24 18:32:04 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql3306/data --output_file=/tmp/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000013
SunMay2418:32:042020-[info]************************.28.128(192.168.28.128:22)..
Creating /tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql3306/data, up to mysql-bin.000013Sun May 24 18:32:05 2020 - [info] Binlog setting check done.
Sun May 24 18:32:05 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sun May 24 18:32:05 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.28.131 --slave_ip=192.168.28.131 --slave_port=3306 --workdir=/tmp --target_version=5.7.25-28-log --manager_version=0.5 SunMay2418:32:052020-[info]************************.28.131(192.168.28.131:22)..
Checking slave recovery environment settings..
Opening /data/mysql3306/data/ ... ok.
Relay log found at /data/mysql3306/data, up to relay-log.000005
Temporary relay log file is /data/mysql3306/data/relay-log.000005 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.
Sun May 24 18:32:06 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.28.132 --slave_ip=192.168.28.132 --slave_port=3306 --workdir=/tmp --target_version=5.7.25-28-log --manager_version=0.5 SunMay2418:32:062020-[info]************************.28.132(192.168.28.132:22)..
Checking slave recovery environment settings..
Opening /data/mysql3306/data/ ... ok.
Relay log found at /data/mysql3306/data, up to relay-log.000005
Temporary relay log file is /data/mysql3306/data/relay-log.000005 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.
Sun May 24 18:32:07 2020 - [info] Slaves settings check done.
Sun May 24 18:32:07 2020 - [info]
192.168.28.128(192.168.28.128:3306) (current master)
+--192.168.28.131(192.168.28.131:3306)
+--192.168.28.132(192.168.28.132:3306)
Sun May 24 18:32:07 2020 - [info] Checking master_ip_failover_script status:
Sun May 24 18:32:07 2020 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.28.128 --orig_master_ip=192.168.28.128 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ip addr del 192.168.28.199/24 dev ens33==/sbin/ip addr add 192.168.28.199/24 dev ens33===Checking the Status of the script.. OK
Sun May 24 18:32:08 2020 - [info] OK.
Sun May 24 18:32:08 2020 - [warning] shutdown_script is not defined.
Sun May 24 18:32:08 2020 - [info] Set master ping interval 3 seconds.
Sun May 24 18:32:08 2020 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Sun May 24 18:32:08 2020 - [info] Starting ping health check on 192.168.28.128(192.168.28.128:3306)..
Sun May 24 18:32:08 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
3.2 测试⾃动切换
模拟主库数据库down
主库执⾏shutdown
mysql> shutdown;
观察⽇志：
⽇志中⼤致的流程是检测到主库（192.168.28.128:3306）不可⽤-->连续试探3次（次数可⾃定义）-->检测进群中剩余存活的节点-->从备选主节点中选择⼀个节点为主节点-->漂移VIP⾄新的主节点（如果原主节点系统Sun May 24 18:35:56 2020 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sun May 24 18:35:56 2020 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql3306/data --output_file=/tmp/save_binary_logs_test --manager_version=0.58 --binlog_prefix=mysql-bin
Sun May 24 18:35:56 2020 - [info] HealthCheck: SSH to 192.168.28.128 is reachable.
Sun May 24 18:35:59 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.28.128' (111))
Sun May 24 18:35:59 2020 - [warning] Connection failed 2 time(s)..
Sun May 24 18:36:02 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.28.128' (111))
Sun May 24 18:36:02 2020 - [warning] Connection failed 3 time(s)..
Sun May 24 18:36:05 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.28.128' (111))
Sun May 24 18:36:05 2020 - [warning] Connection failed 4 time(s)..
Sun May 24 18:36:05 2020 - [warning] Master is not reachable from health checker!
Sun May 24 18:36:05 2020 - [warning] Master 192.168.28.128(192.168.28.128:3306) is not reachable!
Sun May 24 18:36:05 2020 - [warning] SSH is reachable.
Sun May 24 18:36:05 2020 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_f and /etc/masterha/app1.conf again, and trying to connect to all servers to check server status..
Sun May 24 18:36:05 2020 - [warning] Global configuration file /etc/masterha_f not found. Skipping.
Sun May 24 18:36:05 2020 - [info] Reading application default configuration from /etc/masterha/app1.conf..
Sun May 24 18:36:05 2020 - [info] Reading server configuration from /etc/masterha/app1.conf..
Sun May 24 18:36:06 2020 - [info] GTID failover mode = 0
Sun May 24 18:36:06 2020 - [info] Dead Servers:
Sun May 24 18:36:06 2020 - [info] 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:36:06 2020 - [info] Alive Servers:
Sun May 24 18:36:06 2020 - [info] 192.168.28.131(192.168.28.131:3306)
Sun May 24 18:36:06 2020 - [info] 192.168.28.132(192.168.28.132:3306)
Sun May 24 18:36:06 2020 - [info] Alive Slaves:
Sun May 24 18:36:06 2020 - [info] 192.168.28.131(192.168.28.131:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 18:36:06 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:36:06 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sun May 24 18:36:06 2020 - [info] 192.168.28.132(192.168.28.132:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 18:36:06 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:36:06 2020 - [info] Not candidate for the new Master (no_master is set)
Sun May 24 18:36:06 2020 - [info] Checking slave configurations..
Sun May 24 18:36:06 2020 - [info] Checking replication filtering settings..
Sun May 24 18:36:06 2020 - [info] Replication filtering check ok.
Sun May 24 18:36:06 2020 - [info] Master is down!
Sun May 24 18:36:06 2020 - [info] Terminating monitoring script.
Sun May 24 18:36:06 2020 - [info] Got exit code 20 (Master dead).
Sun May 24 18:36:06 2020 - [info] MHA::MasterFailover version 0.58.
Sun May 24 18:36:06 2020 - [info] Starting master failover.
Sun May 24 18:36:06 2020 - [info]
Sun May 24 18:36:06 2020 - [info] * Phase 1: Configuration Check Phase..
Sun May 24 18:36:06 2020 - [info]
Sun May 24 18:36:07 2020 - [info] GTID failover mode = 0
Sun May 24 18:36:07 2020 - [info] Dead Servers:
Sun May 24 18:36:07 2020 - [info] 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:36:07 2020 - [info] Checking master reachability via MySQL(double check)...
Sun May 24 18:36:07 2020 - [info] ok.
Sun May 24 18:36:07 2020 - [info] Alive Servers:
Sun May 24 18:36:07 2020 - [info] 192.168.28.131(192.168.28.131:3306)
Sun May 24 18:36:07 2020 - [info] 192.168.28.132(192.168.28.132:3306)
Sun May 24 18:36:07 2020 - [info] Alive Slaves:
Sun May 24 18:36:07 2020 - [info] 192.168.28.131(192.168.28.131:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 18:36:07 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:36:07 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sun May 24 18:36:07 2020 - [info] 192.168.28.132(192.168.28.132:3306) Version=5.7.25-28-log (oldest major version between slaves) log-bin:enabled
Sun May 24 18:36:07 2020 - [info] Replicating from 192.168.28.128(192.168.28.128:3306)
Sun May 24 18:36:07 2020 - [info] Not candidate for the new Master (no_master is set)
Sun May 24 18:36:07 2020 - [info] Starting Non-GTID based failover.
Sun May 24 18:36:07 2020 - [info]。