MySQL5.7 MHA测试with GTID

我的实验MHA

环境准备:

192.168.199.101 manager
192.168.199.102 node01
192.168.199.103 node02
192.168.199.104 node03

主机环境

[root@manager ~]# uname -a
Linux manager 3.10.0-1062.12.1.el7.x86_64 #1 SMP Tue Feb 4 23:02:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

###步骤1, 准备MySQL5.7
#本文预先安装了MySQL5.7到/opt/mysql7

cd /data/
mkdir MHANODE01
mkdir {data,log,tmp}
useradd mysql
echo "111111" | passwd mysql --stdin
chown -R mysql:mysql /data/MHANODE01/
#初始化数据库,初始化密码需要到日志里找
/opt/mysql7/bin/mysqld --initialize --user=mysql --basedir=/opt/mysql7 --datadir=/data/MHANODE01/data --explicit_defaults_for_timestamp
#建立my.cnf,核心参数:
[client]
default-character-set = utf8
port = 3306
socket = /data/MHANODE01/mysql.sock

[mysqld]
server-id = 1
collation-server = utf8_unicode_ci
init-connect = ‘SET NAMES utf8‘
character-set-server = utf8
port        = 3306
socket        = /data/MHANODE01/mysql.sock
datadir = /data/MHANODE01/data
log-error = /data/MHANODE01/mysql.err
pid-file = /data/MHANODE01/mysql.pid

gtid_mode=on
#auto-increment-increment = 2
#auto-increment-offset = 2
sync_binlog = 1
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1
enforce-gtid-consistency=on
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin = /data/MHANODE01/data/mysql-bin
relay_log = /data/MHANODE01/data/relay-bin
##cascaded replication for slave to write binlog.
log_slave_updates = 1
#read-only=1
binlog_format = row
slow_query_log = 1
slow_query_log_file = /data/MHANODE01/log/slowquery.log
long_query_time = 1
general_log = off
general_log_file = /data/MHANODE01/log/general.log
#skip-grant-tables
#rpl_semi_sync_slave_enabled = 1

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

#建立错误日志文件

touch /data/MHANODE02/mysql.err
chown mysql:mysql /data/MHANODE02/mysql.err
#启动数据库
/opt/mysql7/bin/mysqld_safe --defaults-file=‘/data/MHANODE01/my.cnf‘ --user=mysql &

#首次登陆数据库,并更改密码:

/opt/mysql7/bin/mysql -uroot -p -S ‘/data/MHANODE01/mysql.sock‘

#登陆后更改密码,并登出,重新登入

set password=password(‘111111‘);
flush privileges;
exit
/opt/mysql7/bin/mysql -uroot -p -S ‘/data/MHANODE01/mysql.sock‘

###以上第一步建立数据库,另外两个节点同样设置,只是需要注意server-id必须不同,可以设置2,3...
###需要放开3306端口的tcp和udp访问,或者直接关闭防火墙:

systemctl stop firewalld
systemctl disable firewalld

###设置好3*立数据库后,设置一主多从的复制:
#主库上:

grant replication slave, replication client on *.* to replica@‘192.168.199.%‘;
mysqldump -uroot -p111111 -S /data/MHANODE01/mysql.sock --single-transaction --master-data=2 --opt -A > /tmp/fullnode01.sql

scp /tmp/fullnode01.sql node02:/tmp/
scp /tmp/fullnode01.sql node03:/tmp/

#从库上:

mysql -uroot -p111111 -S /data/MHANODE02/mysql.sock < /tmp/fullnode01.sql
##登录MySQL

reset master;
 change  master to master_host=‘192.168.199.102‘,master_port=3306,master_user=‘replica‘,master_password=‘111111‘,master_auto_position=1;
 start slave;

#检查slave状态

show salve status\G

#对于一主多从的复制,详细情况请参阅其他主题

###安装MHA的过程
#所有节点,包含manager节点,都需要安装mha4mysql-node包

[root@localhost ~]# mkdir /usr/local/src/mha4mysql-node
[root@localhost ~]# cd /usr/local/src/mha4mysql-node
[root@localhost mha4mysql-node]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

cpan Module::Install
perl -MCPAN -e "install Class::Load"
yum install -y perl-Mail-Sender perl-Email-Date-Format perl-MIME-Types perl-MIME-Lite perl-Parallel-ForkManager perl-Mail-Sendmail 
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-YAML-Tiny perl-PAR-Dist perl-Module-ScanDeps perl-Module-CoreList perl-Module-Build perl-CPAN perl-YAML perl-CPANPLUS perl-File-Remove perl-Module-Install
yum install -y perl-DBD-MySQL perl-DBI mysql-libs

yum -y install cpanm
cpanm Module::Runtime

tar -zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL 
make && make install

#manager 节点上安装mha4mysql-manager-0.58

mkdir /usr/local/src/mha4mysql-manager
cd /usr/local/src/mha4mysql-manager
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
tar -zxvf mha4mysql-manager-0.58.tar.gz 
cd mha4mysql-manager-0.58
perl Makefile.PL 
make && make install

#如果安装都没出错,则可以配置管理节点文件

mkdir /etc/masterha
 cd /etc/masterha/
 vim app1.cnf
[server default]
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log
#mysql用户和密码
password=111111
user=mha
#监控主库,发送ping包的时间间隔,默认是3秒,尝试3次不成功,则自动进行切换操作
ping_interval=3
#复制用户
repl_password=111111
repl_user=replica
#report_script=/usr/local/send_report
#通过第三方机器确认目标主库是否存活,不是必须的,就算没有也是能用
secondary_check_script=masterha_secondary_check -s 192.168.199.101 
#故障自动切换VIP调用脚本,不是必须的,就算没有也是能用,
master_ip_failover_script=/etc/masterha/scripts/master_ip_failover

#ssh用户
ssh_user=root
ssh_port=22
[server1]
hostname=192.168.199.102
candidate_master=1
[server2]
hostname=192.168.199.103
candidate_master=1 

[server3] 
hostname=192.168.199.104
candidate_master=1 

#vip脚本文件

#!/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
);

my $vip = ‘192.168.199.105/24‘;  # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;

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();

[root@manager scripts]# 
[root@manager scripts]# 
[root@manager scripts]# cat 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
);

my $vip = ‘192.168.199.105/24‘;  # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;

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" ) {

        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
        # If you manage master ip address at global catalog database,
        # invalidate orig_master_ip here.
        my $exit_code = 1;
        eval {
            print "\n\n\n***************************************************************\n";
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";
            print "***************************************************************\n\n\n\n";
&stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@\n";
            exit $exit_code;
        }
        exit $exit_code;
}
elsif ( $command eq "start" ) {

        # all arguments are passed.
        # If you manage master ip address at global catalog database,
        # activate new_master_ip here.
        # You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
        eval {
            print "\n\n\n***************************************************************\n";
            print "Enabling the VIP - $vip on new master: $new_master_host \n";
            print "***************************************************************\n\n\n\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";
        `ssh $orig_master_host \" $ssh_start_vip \"`;
        exit 0;
}
else {
&usage();
        exit 1;
}
}

# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $new_master_host " $ssh_start_vip "`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $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=po
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";
}

#测试脚本文件:
#启动

./master_ip_failover --command=start  --new_master_host=192.168.199.105

#停止

./master_ip_failover --command=stop --orig_master_host=192.168.199.102

#检查状态

./master_ip_failover --command=status --orig_master_host=192.168.199.102

###测试
#启动

masterha_manager --conf=/etc/masterha/app1.cnf &

#查看日志

tail -f /etc/masterha/manager.log

#停止

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

###测试fail-over
#启动

masterha_manager --conf=/etc/masterha/app1.cnf &

#master上杀死MYSQL进程

killall mysqld

#日志过程

Sun Aug 16 19:34:10 2020 - [info] Checking master_ip_failover_script status:
Sun Aug 16 19:34:10 2020 - [info]   /etc/masterha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.199.102 --orig_master_ip=192.168.199.102 --orig_master_port=3306 
Checking the Status of the script.. OK 
Sun Aug 16 19:34:10 2020 - [info]  OK.
Sun Aug 16 19:34:10 2020 - [warning] shutdown_script is not defined.
Sun Aug 16 19:34:10 2020 - [info] Set master ping interval 3 seconds.
Sun Aug 16 19:34:10 2020 - [info] Set secondary check script: masterha_secondary_check -s 192.168.199.101
Sun Aug 16 19:34:10 2020 - [info] Starting ping health check on 192.168.199.102(192.168.199.102:3306)..
Sun Aug 16 19:34:22 2020 - [warning] Got error when monitoring master:  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 489.
Sun Aug 16 19:34:22 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln491] Target master‘s advisory lock is already held by someone. Please check whether you monitor the same master from multiple monitoring processes.
Sun Aug 16 19:34:22 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln511] Error happened on health checking.  at /usr/local/bin/masterha_manager line 50.
Sun Aug 16 19:34:22 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Sun Aug 16 19:34:22 2020 - [info] Got exit code 1 (Not master dead).
Sun Aug 16 19:35:10 2020 - [info] MHA::MasterFailover version 0.58.
Sun Aug 16 19:35:10 2020 - [info] Starting master failover.
Sun Aug 16 19:35:10 2020 - [info] 
Sun Aug 16 19:35:10 2020 - [info] * Phase 1: Configuration Check Phase..
Sun Aug 16 19:35:10 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] GTID failover mode = 1
Sun Aug 16 19:35:12 2020 - [info] Dead Servers:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info] Checking master reachability via MySQL(double check)...
Sun Aug 16 19:35:12 2020 - [info]  ok.
Sun Aug 16 19:35:12 2020 - [info] Alive Servers:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)
Sun Aug 16 19:35:12 2020 - [info] Alive Slaves:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info] Starting GTID based failover.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 2: Dead Master Shutdown Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] Forcing shutdown so that applications never connect to the current master..
Sun Aug 16 19:35:12 2020 - [info] Executing master IP deactivation script:
Sun Aug 16 19:35:12 2020 - [info]   /etc/masterha/scripts/master_ip_failover --orig_master_host=192.168.199.102 --orig_master_ip=192.168.199.102 --orig_master_port=3306 --command=stopssh --ssh_user=root  

***************************************************************
Disabling the VIP - 192.168.199.105/24 on old master: 192.168.199.102
***************************************************************

Sun Aug 16 19:35:12 2020 - [info]  done.
Sun Aug 16 19:35:12 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Aug 16 19:35:12 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3: Master Recovery Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:3766
Sun Aug 16 19:35:12 2020 - [info] Retrieved Gtid Set: e30e52c8-df91-11ea-8231-0050563df028:9-14
Sun Aug 16 19:35:12 2020 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:3766
Sun Aug 16 19:35:12 2020 - [info] Retrieved Gtid Set: e30e52c8-df91-11ea-8231-0050563df028:9-14
Sun Aug 16 19:35:12 2020 - [info] Oldest slaves:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3.3: Determining New Master Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] Searching new master from slaves..
Sun Aug 16 19:35:12 2020 - [info]  Candidate masters from the configuration file:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]  Non-candidate masters:
Sun Aug 16 19:35:12 2020 - [info]  Searching from candidate_master slaves which have received the latest relay log events..
Sun Aug 16 19:35:12 2020 - [info] New master is 192.168.199.103(192.168.199.103:3306)
Sun Aug 16 19:35:12 2020 - [info] Starting master failover..
Sun Aug 16 19:35:12 2020 - [info] 
From:
192.168.199.102(192.168.199.102:3306) (current master)
 +--192.168.199.103(192.168.199.103:3306)
 +--192.168.199.104(192.168.199.104:3306)

To:
192.168.199.103(192.168.199.103:3306) (new master)
 +--192.168.199.104(192.168.199.104:3306)
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3.3: New Master Recovery Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info]  Waiting all logs to be applied.. 
Sun Aug 16 19:35:12 2020 - [info]   done.
Sun Aug 16 19:35:12 2020 - [info] Getting new master‘s binlog name and position..
Sun Aug 16 19:35:12 2020 - [info]  mysql-bin.000001:1341
Sun Aug 16 19:35:12 2020 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=‘192.168.199.103‘, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘replica‘, MASTER_PASSWORD=‘xxx‘;
Sun Aug 16 19:35:12 2020 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 1341, e30e52c8-df91-11ea-8231-0050563df028:1-14
Sun Aug 16 19:35:12 2020 - [info] Executing master IP activate script:
Sun Aug 16 19:35:12 2020 - [info]   /etc/masterha/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.199.102 --orig_master_ip=192.168.199.102 --orig_master_port=3306 --new_master_host=192.168.199.103 --new_master_ip=192.168.199.103 --new_master_port=3306 --new_master_user=‘mha‘   --new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password

***************************************************************
Enabling the VIP - 192.168.199.105/24 on new master: 192.168.199.103 
***************************************************************

Sun Aug 16 19:35:12 2020 - [info]  OK.
Sun Aug 16 19:35:12 2020 - [info] ** Finished master recovery successfully.
Sun Aug 16 19:35:12 2020 - [info] * Phase 3: Master Recovery Phase completed.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 4: Slaves Recovery Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 4.1: Starting Slaves in parallel..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] -- Slave recovery on host 192.168.199.104(192.168.199.104:3306) started, pid: 11958. Check tmp log /etc/masterha/192.168.199.104_3306_20200816193510.log if it takes time..
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:14 2020 - [info] Log messages from 192.168.199.104 ...
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info]  Resetting slave 192.168.199.104(192.168.199.104:3306) and starting replication from the new master 192.168.199.103(192.168.199.103:3306)..
Sun Aug 16 19:35:12 2020 - [info]  Executed CHANGE MASTER.
Sun Aug 16 19:35:13 2020 - [info]  Slave started.
Sun Aug 16 19:35:13 2020 - [info]  gtid_wait(e30e52c8-df91-11ea-8231-0050563df028:1-14) completed on 192.168.199.104(192.168.199.104:3306). Executed 0 events.
Sun Aug 16 19:35:14 2020 - [info] End of log messages from 192.168.199.104.
Sun Aug 16 19:35:14 2020 - [info] -- Slave on host 192.168.199.104(192.168.199.104:3306) started.
Sun Aug 16 19:35:14 2020 - [info] All new slave servers recovered successfully.
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:14 2020 - [info] * Phase 5: New master cleanup phase..
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:14 2020 - [info] Resetting slave info on the new master..
Sun Aug 16 19:35:14 2020 - [info]  192.168.199.103: Resetting slave info succeeded.
Sun Aug 16 19:35:14 2020 - [info] Master failover to 192.168.199.103(192.168.199.103:3306) completed successfully.
Sun Aug 16 19:35:14 2020 - [info] 

----- Failover Report -----

app1: MySQL Master failover 192.168.199.102(192.168.199.102:3306) to 192.168.199.103(192.168.199.103:3306) succeeded

Master 192.168.199.102(192.168.199.102:3306) is down!

Check MHA Manager logs at manager:/etc/masterha/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.199.102(192.168.199.102:3306)
Selected 192.168.199.103(192.168.199.103:3306) as a new master.
192.168.199.103(192.168.199.103:3306): OK: Applying all logs succeeded.
192.168.199.103(192.168.199.103:3306): OK: Activated master IP address.
192.168.199.104(192.168.199.104:3306): OK: Slave started, replicating from 192.168.199.103(192.168.199.103:3306)
192.168.199.103(192.168.199.103:3306): Resetting slave info succeeded.
Master failover to 192.168.199.103(192.168.199.103:3306) completed successfully.
^C
[root@manager masterha]# tail -f /etc/masterha/manager.log ^C
[root@manager masterha]# more /etc/masterha/manager.log
Sun Aug 16 19:34:08 2020 - [info] MHA::MasterMonitor version 0.58.
Sun Aug 16 19:34:09 2020 - [info] GTID failover mode = 1
Sun Aug 16 19:34:09 2020 - [info] Dead Servers:
Sun Aug 16 19:34:09 2020 - [info] Alive Servers:
Sun Aug 16 19:34:09 2020 - [info]   192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:34:09 2020 - [info]   192.168.199.103(192.168.199.103:3306)
Sun Aug 16 19:34:09 2020 - [info]   192.168.199.104(192.168.199.104:3306)
Sun Aug 16 19:34:09 2020 - [info] Alive Slaves:
Sun Aug 16 19:34:09 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:34:09 2020 - [info]     GTID ON
Sun Aug 16 19:34:09 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:34:09 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:34:09 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:34:09 2020 - [info]     GTID ON
Sun Aug 16 19:34:09 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:34:09 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:34:09 2020 - [info] Current Alive Master: 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:34:09 2020 - [info] Checking slave configurations..
Sun Aug 16 19:34:09 2020 - [info]  read_only=1 is not set on slave 192.168.199.103(192.168.199.103:3306).
Sun Aug 16 19:34:09 2020 - [info]  read_only=1 is not set on slave 192.168.199.104(192.168.199.104:3306).
Sun Aug 16 19:34:09 2020 - [info] Checking replication filtering settings..
Sun Aug 16 19:34:09 2020 - [info]  binlog_do_db= , binlog_ignore_db= 
Sun Aug 16 19:34:09 2020 - [info]  Replication filtering check ok.
Sun Aug 16 19:34:09 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package check
ing.
Sun Aug 16 19:34:09 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sun Aug 16 19:34:10 2020 - [info] HealthCheck: SSH to 192.168.199.102 is reachable.
Sun Aug 16 19:34:10 2020 - [info] 
192.168.199.102(192.168.199.102:3306) (current master)
 +--192.168.199.103(192.168.199.103:3306)
 +--192.168.199.104(192.168.199.104:3306)

Sun Aug 16 19:34:10 2020 - [info] Checking master_ip_failover_script status:
Sun Aug 16 19:34:10 2020 - [info]   /etc/masterha/scripts/master_ip_failover --command=status --ssh_user=roo
t --orig_master_host=192.168.199.102 --orig_master_ip=192.168.199.102 --orig_master_port=3306 
Checking the Status of the script.. OK 
Sun Aug 16 19:34:10 2020 - [info]  OK.
Sun Aug 16 19:34:10 2020 - [warning] shutdown_script is not defined.
Sun Aug 16 19:34:10 2020 - [info] Set master ping interval 3 seconds.
Sun Aug 16 19:34:10 2020 - [info] Set secondary check script: masterha_secondary_check -s 192.168.199.101
Sun Aug 16 19:34:10 2020 - [info] Starting ping health check on 192.168.199.102(192.168.199.102:3306)..
Sun Aug 16 19:34:22 2020 - [warning] Got error when monitoring master:  at /usr/local/share/perl5/MHA/Master
Monitor.pm line 489.
Sun Aug 16 19:34:22 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln491] Target master‘s advis
ory lock is already held by someone. Please check whether you monitor the same master from multiple monitori
ng processes.
Sun Aug 16 19:34:22 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln511] Error happened on hea
lth checking.  at /usr/local/bin/masterha_manager line 50.
Sun Aug 16 19:34:22 2020 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on mon
itoring servers.
Sun Aug 16 19:34:22 2020 - [info] Got exit code 1 (Not master dead).
Sun Aug 16 19:35:10 2020 - [info] MHA::MasterFailover version 0.58.
Sun Aug 16 19:35:10 2020 - [info] Starting master failover.
Sun Aug 16 19:35:10 2020 - [info] 
Sun Aug 16 19:35:10 2020 - [info] * Phase 1: Configuration Check Phase..
Sun Aug 16 19:35:10 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] GTID failover mode = 1
Sun Aug 16 19:35:12 2020 - [info] Dead Servers:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info] Checking master reachability via MySQL(double check)...
Sun Aug 16 19:35:12 2020 - [info]  ok.
Sun Aug 16 19:35:12 2020 - [info] Alive Servers:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)
Sun Aug 16 19:35:12 2020 - [info] Alive Slaves:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info] Starting GTID based failover.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 2: Dead Master Shutdown Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] Forcing shutdown so that applications never connect to the current master.
.
Sun Aug 16 19:35:12 2020 - [info] Executing master IP deactivation script:
Sun Aug 16 19:35:12 2020 - [info]   /etc/masterha/scripts/master_ip_failover --orig_master_host=192.168.199.
102 --orig_master_ip=192.168.199.102 --orig_master_port=3306 --command=stopssh --ssh_user=root  

***************************************************************
Disabling the VIP - 192.168.199.105/24 on old master: 192.168.199.102
***************************************************************

Sun Aug 16 19:35:12 2020 - [info]  done.
Sun Aug 16 19:35:12 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead
 master.
Sun Aug 16 19:35:12 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3: Master Recovery Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:3766
Sun Aug 16 19:35:12 2020 - [info] Retrieved Gtid Set: e30e52c8-df91-11ea-8231-0050563df028:9-14
Sun Aug 16 19:35:12 2020 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:3766
Sun Aug 16 19:35:12 2020 - [info] Retrieved Gtid Set: e30e52c8-df91-11ea-8231-0050563df028:9-14
Sun Aug 16 19:35:12 2020 - [info] Oldest slaves:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3.3: Determining New Master Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] Searching new master from slaves..
Sun Aug 16 19:35:12 2020 - [info]  Candidate masters from the configuration file:
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.103(192.168.199.103:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]   192.168.199.104(192.168.199.104:3306)  Version=5.7.28-log (oldest major 
version between slaves) log-bin:enabled
Sun Aug 16 19:35:12 2020 - [info]     GTID ON
Sun Aug 16 19:35:12 2020 - [info]     Replicating from 192.168.199.102(192.168.199.102:3306)
Sun Aug 16 19:35:12 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Aug 16 19:35:12 2020 - [info]  Non-candidate masters:
Sun Aug 16 19:35:12 2020 - [info]  Searching from candidate_master slaves which have received the latest rel
ay log events..
Sun Aug 16 19:35:12 2020 - [info] New master is 192.168.199.103(192.168.199.103:3306)
Sun Aug 16 19:35:12 2020 - [info] Starting master failover..
Sun Aug 16 19:35:12 2020 - [info] 
From:
192.168.199.102(192.168.199.102:3306) (current master)
 +--192.168.199.103(192.168.199.103:3306)
 +--192.168.199.104(192.168.199.104:3306)

To:
192.168.199.103(192.168.199.103:3306) (new master)
 +--192.168.199.104(192.168.199.104:3306)
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 3.3: New Master Recovery Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info]  Waiting all logs to be applied.. 
Sun Aug 16 19:35:12 2020 - [info]   done.
Sun Aug 16 19:35:12 2020 - [info] Getting new master‘s binlog name and position..
Sun Aug 16 19:35:12 2020 - [info]  mysql-bin.000001:1341
Sun Aug 16 19:35:12 2020 - [info]  All other slaves should start replication from here. Statement should be:
 CHANGE MASTER TO MASTER_HOST=‘192.168.199.103‘, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=‘repl
ica‘, MASTER_PASSWORD=‘xxx‘;
Sun Aug 16 19:35:12 2020 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 1341,
 e30e52c8-df91-11ea-8231-0050563df028:1-14
Sun Aug 16 19:35:12 2020 - [info] Executing master IP activate script:
Sun Aug 16 19:35:12 2020 - [info]   /etc/masterha/scripts/master_ip_failover --command=start --ssh_user=root
 --orig_master_host=192.168.199.102 --orig_master_ip=192.168.199.102 --orig_master_port=3306 --new_master_ho
st=192.168.199.103 --new_master_ip=192.168.199.103 --new_master_port=3306 --new_master_user=‘mha‘   --new_ma
ster_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password

***************************************************************
Enabling the VIP - 192.168.199.105/24 on new master: 192.168.199.103 
***************************************************************

Sun Aug 16 19:35:12 2020 - [info]  OK.
Sun Aug 16 19:35:12 2020 - [info] ** Finished master recovery successfully.
Sun Aug 16 19:35:12 2020 - [info] * Phase 3: Master Recovery Phase completed.
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 4: Slaves Recovery Phase..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] * Phase 4.1: Starting Slaves in parallel..
Sun Aug 16 19:35:12 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info] -- Slave recovery on host 192.168.199.104(192.168.199.104:3306) started, p
id: 11958. Check tmp log /etc/masterha/192.168.199.104_3306_20200816193510.log if it takes time..
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:14 2020 - [info] Log messages from 192.168.199.104 ...
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:12 2020 - [info]  Resetting slave 192.168.199.104(192.168.199.104:3306) and starting replic
ation from the new master 192.168.199.103(192.168.199.103:3306)..
Sun Aug 16 19:35:12 2020 - [info]  Executed CHANGE MASTER.
Sun Aug 16 19:35:13 2020 - [info]  Slave started.
Sun Aug 16 19:35:13 2020 - [info]  gtid_wait(e30e52c8-df91-11ea-8231-0050563df028:1-14) completed on 192.168
.199.104(192.168.199.104:3306). Executed 0 events.
Sun Aug 16 19:35:14 2020 - [info] End of log messages from 192.168.199.104.
Sun Aug 16 19:35:14 2020 - [info] -- Slave on host 192.168.199.104(192.168.199.104:3306) started.
Sun Aug 16 19:35:14 2020 - [info] All new slave servers recovered successfully.
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:14 2020 - [info] * Phase 5: New master cleanup phase..
Sun Aug 16 19:35:14 2020 - [info] 
Sun Aug 16 19:35:14 2020 - [info] Resetting slave info on the new master..
Sun Aug 16 19:35:14 2020 - [info]  192.168.199.103: Resetting slave info succeeded.
Sun Aug 16 19:35:14 2020 - [info] Master failover to 192.168.199.103(192.168.199.103:3306) completed success
fully.
Sun Aug 16 19:35:14 2020 - [info] 

----- Failover Report -----

app1: MySQL Master failover 192.168.199.102(192.168.199.102:3306) to 192.168.199.103(192.168.199.103:3306) s
ucceeded

Master 192.168.199.102(192.168.199.102:3306) is down!

Check MHA Manager logs at manager:/etc/masterha/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.199.102(192.168.199.102:3306)
Selected 192.168.199.103(192.168.199.103:3306) as a new master.
192.168.199.103(192.168.199.103:3306): OK: Applying all logs succeeded.
192.168.199.103(192.168.199.103:3306): OK: Activated master IP address.
192.168.199.104(192.168.199.104:3306): OK: Slave started, replicating from 192.168.199.103(192.168.199.103:3
306)
192.168.199.103(192.168.199.103:3306): Resetting slave info succeeded.
Master failover to 192.168.199.103(192.168.199.103:3306) completed successfully.
[root@manager masterha]# 
[root@manager masterha]# 

MySQL5.7 MHA测试with GTID

上一篇:mysql的使用和远程连接


下一篇:Mysql8.0主从配置