MySQL5.7 MHA测试with GTID
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL5.7 MHA测试with GTID，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含37352字，纯文字阅读大概需要54分钟。
内容图文
我的实验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
标签：第三方 ring check other epo host sock set initial
本文系统来源：https://blog.51cto.com/accole/2520746
内容总结
以上是互联网集市为您收集整理的MySQL5.7 MHA测试with GTID全部内容，希望文章能够帮你解决MySQL5.7 MHA测试with GTID所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】