MySQL8.0 mha 安装部署及主从切换测试

MySQL8.0 mha 安装部署及主从切换测试

1.安装mha依赖包(每台服务器都需要)

yum -y install epel-release

yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN

2.安装mha ：每台服务器都需要

wget https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

安装manager节点，只在manager上安装，这里在node3上执行

wget https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3.配置管理节点

mkdir /etc/mha

mkdir /home/mysql_mha

vim /etc/mha/mysql_mha.cnf

[server default]

#mha访问数据库的账号与密码

user=mha

password=123456

#指定mha的工作目录

manager_workdir=/home/mysql_mha

#指定管理日志路径

manager_log=/home/mysql_mha/manager.log

#指定mha在远程节点上的工作目录

remote_workdir=/home/mysql_mha

#可以使用ssh登入的用户

ssh_user=root

ssh_port=22

#指定主从复制的mysq用户和密码

repl_user=repl

repl_password=123456

#指定检测间隔时间

ping_interval=1

#指定master节点存放binlog的日志文件的目录

master_binlog_dir=/data/mysql/log

#指定一个脚本，该脚本实现了在主从切换之后，将虚拟ip漂移到新的master上

master_ip_failover_script=/usr/bin/master_ip_failover

#指定用于二次检查节点状态的节点

secondary_check_script= /usr/bin/masterha_secondary_check -s 172.17.0.35 -s 172.17.0.29 -s 172.17.0.56

#配置集群中的节点

[server1]

hostname=172.17.0.35

port=3306

ssh_user=root

#指定该节点可以参与master选举

candidate_master=1

[server2]

hostname=172.17.0.29

#指定该节点可以参与master选举

port=3308

ssh_user=root

candidate_master=1

[server3]

hostname=172.17.0.56

port=3307

ssh_user=root

##指定该节点不参与master选举 该节点也是监控节点，所以关闭master候选

no_master=1

4.编辑vip配置文件

vim /usr/bin/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 = ‘172.17.0.251/24’; # Virtual IP

my $key = “1”;

my sshstartvip="/sbin/ifconfigeth0:ssh_start_vip = "/sbin/ifconfig eth0:sshs​tartv​ip="/sbin/ifconfigeth0:key $vip";

my sshstopvip="/sbin/ifconfigeth0:ssh_stop_vip = "/sbin/ifconfig eth0:sshs​topv​ip="/sbin/ifconfigeth0:key down";

GetOptions(

‘command=s’ => $command,

‘ssh_user=s’ => $ssh_user,

‘orig_master_host=s’ => $orig_master_host,

‘orig_master_ip=s’ => $orig_master_ip,

‘orig_master_port=i’ => $orig_master_port,

‘new_master_host=s’ => $new_master_host,

‘new_master_ip=s’ => $new_master_ip,

‘new_master_port=i’ => $new_master_port,

);

exit &main();

sub main {

print “\n\nIN SCRIPT TEST====sshstopvip==ssh_stop_vip==sshs​topv​ip==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 “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” ) {

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 “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”;

ssh $ssh_user\@$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 $ssh_user\@$new_master_host \" $ssh_start_vip \";

}

A simple system call that disable the VIP on the old_master

sub stop_vip() {

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”;

}

添加权限：

chmod +x /usr/bin/master_ip_failover

5.在其他节点创建mha目录

mkdir /home/mysql_mha

6.在master节点设置vip：

ifconfig eth0:1 172.17.0.251/24

7.在manager进行检测工作，检测ssh免密和mysql主从同步

masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf

Mon Jan 22 01:18:03 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon Jan 22 01:18:03 2024 - [info] Reading application default configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 01:18:03 2024 - [info] Reading server configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 01:18:03 2024 - [info] Starting SSH connection tests…

Mon Jan 22 01:18:08 2024 - [debug]

Mon Jan 22 01:18:03 2024 - [debug] Connecting via SSH from root@172.17.0.35(172.17.0.35:22) to root@172.17.0.29(172.17.0.29:22)…

Mon Jan 22 01:18:06 2024 - [debug] ok.

Mon Jan 22 01:18:06 2024 - [debug] Connecting via SSH from root@172.17.0.35(172.17.0.35:22) to root@172.17.0.56(172.17.0.56:22)…

Mon Jan 22 01:18:08 2024 - [debug] ok.

Mon Jan 22 01:18:10 2024 - [debug]

Mon Jan 22 01:18:04 2024 - [debug] Connecting via SSH from root@172.17.0.56(172.17.0.56:22) to root@172.17.0.35(172.17.0.35:22)…

Mon Jan 22 01:18:07 2024 - [debug] ok.

Mon Jan 22 01:18:07 2024 - [debug] Connecting via SSH from root@172.17.0.56(172.17.0.56:22) to root@172.17.0.29(172.17.0.29:22)…

Mon Jan 22 01:18:10 2024 - [debug] ok.

Mon Jan 22 01:18:10 2024 - [debug]

Mon Jan 22 01:18:03 2024 - [debug] Connecting via SSH from root@172.17.0.29(172.17.0.29:22) to root@172.17.0.35(172.17.0.35:22)…

Mon Jan 22 01:18:07 2024 - [debug] ok.

Mon Jan 22 01:18:07 2024 - [debug] Connecting via SSH from root@172.17.0.29(172.17.0.29:22) to root@172.17.0.56(172.17.0.56:22)…

Mon Jan 22 01:18:10 2024 - [debug] ok.

Mon Jan 22 01:18:10 2024 - [info] All SSH connection tests passed successfully.

[root@node3 mysql_mha]# masterha_check_ssh --conf=/etc/mha/mysql_mha.cnf

Mon Jan 22 04:11:07 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon Jan 22 04:11:07 2024 - [info] Reading application default configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 04:11:07 2024 - [info] Reading server configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 04:11:07 2024 - [info] Starting SSH connection tests…

Mon Jan 22 04:11:14 2024 - [debug]

Mon Jan 22 04:11:08 2024 - [debug] Connecting via SSH from root@172.17.0.56(172.17.0.56:22) to root@172.17.0.35(172.17.0.35:22)…

Mon Jan 22 04:11:12 2024 - [debug] ok.

Mon Jan 22 04:11:12 2024 - [debug] Connecting via SSH from root@172.17.0.56(172.17.0.56:22) to root@172.17.0.29(172.17.0.29:22)…

Mon Jan 22 04:11:13 2024 - [debug] ok.

Mon Jan 22 04:11:15 2024 - [debug]

Mon Jan 22 04:11:08 2024 - [debug] Connecting via SSH from root@172.17.0.29(172.17.0.29:22) to root@172.17.0.35(172.17.0.35:22)…

Mon Jan 22 04:11:12 2024 - [debug] ok.

Mon Jan 22 04:11:12 2024 - [debug] Connecting via SSH from root@172.17.0.29(172.17.0.29:22) to root@172.17.0.56(172.17.0.56:22)…

Mon Jan 22 04:11:14 2024 - [debug] ok.

Mon Jan 22 04:11:17 2024 - [debug]

Mon Jan 22 04:11:07 2024 - [debug] Connecting via SSH from root@172.17.0.35(172.17.0.35:22) to root@172.17.0.29(172.17.0.29:22)…

Mon Jan 22 04:11:15 2024 - [debug] ok.

Mon Jan 22 04:11:15 2024 - [debug] Connecting via SSH from root@172.17.0.35(172.17.0.35:22) to root@172.17.0.56(172.17.0.56:22)…

Mon Jan 22 04:11:16 2024 - [debug] ok.

Mon Jan 22 04:11:17 2024 - [info] All SSH connection tests passed successfully.

[root@node3 mysql_mha]# masterha_check_repl --conf=/etc/mha/mysql_mha.cnf

[root@node3 ~]# masterha_check_repl --conf=/etc/mha/mysql_mha.cnf

Mon Jan 22 13:29:37 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon Jan 22 13:29:37 2024 - [info] Reading application default configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 13:29:37 2024 - [info] Reading server configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 13:29:37 2024 - [info] MHA::MasterMonitor version 0.58.

Mon Jan 22 13:29:38 2024 - [info] GTID failover mode = 1

Mon Jan 22 13:29:38 2024 - [info] Dead Servers:

Mon Jan 22 13:29:38 2024 - [info] Alive Servers:

Mon Jan 22 13:29:38 2024 - [info] 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 13:29:38 2024 - [info] 172.17.0.29(172.17.0.29:3308)

Mon Jan 22 13:29:38 2024 - [info] 172.17.0.56(172.17.0.56:3307)

Mon Jan 22 13:29:38 2024 - [info] Alive Slaves:

Mon Jan 22 13:29:38 2024 - [info] 172.17.0.29(172.17.0.29:3308) Version=8.0.26 (oldest major version between slaves) log-bin:enabled

Mon Jan 22 13:29:38 2024 - [info] GTID ON

Mon Jan 22 13:29:38 2024 - [info] Replicating from 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 13:29:38 2024 - [info] Primary candidate for the new Master (candidate_master is set)

Mon Jan 22 13:29:38 2024 - [info] 172.17.0.56(172.17.0.56:3307) Version=8.0.26 (oldest major version between slaves) log-bin:enabled

Mon Jan 22 13:29:38 2024 - [info] GTID ON

Mon Jan 22 13:29:38 2024 - [info] Replicating from 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 13:29:38 2024 - [info] Not candidate for the new Master (no_master is set)

Mon Jan 22 13:29:38 2024 - [info] Current Alive Master: 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 13:29:38 2024 - [info] Checking slave configurations…

Mon Jan 22 13:29:38 2024 - [info] read_only=1 is not set on slave 172.17.0.29(172.17.0.29:3308).

Mon Jan 22 13:29:38 2024 - [info] read_only=1 is not set on slave 172.17.0.56(172.17.0.56:3307).

Mon Jan 22 13:29:38 2024 - [info] Checking replication filtering settings…

Mon Jan 22 13:29:38 2024 - [info] binlog_do_db= , binlog_ignore_db=

Mon Jan 22 13:29:38 2024 - [info] Replication filtering check ok.

Mon Jan 22 13:29:38 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Mon Jan 22 13:29:38 2024 - [info] Checking SSH publickey authentication settings on the current master…

Authorized only. All activity will be monitored and reported

Mon Jan 22 13:29:38 2024 - [info] HealthCheck: SSH to 172.17.0.35 is reachable.

Mon Jan 22 13:29:38 2024 - [info]

172.17.0.35(172.17.0.35:3306) (current master)

±-172.17.0.29(172.17.0.29:3308)

±-172.17.0.56(172.17.0.56:3307)

Mon Jan 22 13:29:38 2024 - [info] Checking replication health on 172.17.0.29…

Mon Jan 22 13:29:38 2024 - [info] ok.

Mon Jan 22 13:29:38 2024 - [info] Checking replication health on 172.17.0.56…

Mon Jan 22 13:29:38 2024 - [info] ok.

Mon Jan 22 13:29:38 2024 - [info] Checking master_ip_failover_script status:

Mon Jan 22 13:29:38 2024 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.17.0.35 --orig_master_ip=172.17.0.35 --orig_master_port=3306

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.17.0.251/24===

Checking the Status of the script… OK

Authorized only. All activity will be monitored and reported

Mon Jan 22 13:29:38 2024 - [info] OK.

Mon Jan 22 13:29:38 2024 - [warning] shutdown_script is not defined.

Mon Jan 22 13:29:38 2024 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

8.启动前检查

[root@node3 ~]# masterha_check_status --conf=/etc/mha/mysql_mha.cnf

mysql_mha is stopped(2:NOT_RUNNING).

15.在manager节点上启动mha服务

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf --remove_dead_master_conf --ignore_last_failover /home/mysql_mha/manager.log 2>&1 &

n Jan 22 04:18:47 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon Jan 22 04:18:47 2024 - [info] Reading application default configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 04:18:47 2024 - [info] Reading server configuration from /etc/mha/mysql_mha.cnf…

Mon Jan 22 04:18:47 2024 - [info] MHA::MasterMonitor version 0.58.

Mon Jan 22 04:18:48 2024 - [info] GTID failover mode = 1

Mon Jan 22 04:18:48 2024 - [info] Dead Servers:

Mon Jan 22 04:18:48 2024 - [info] Alive Servers:

Mon Jan 22 04:18:48 2024 - [info] 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 04:18:48 2024 - [info] 172.17.0.29(172.17.0.29:3306)

Mon Jan 22 04:18:48 2024 - [info] 172.17.0.56(172.17.0.56:3306)

Mon Jan 22 04:18:48 2024 - [info] Alive Slaves:

Mon Jan 22 04:18:48 2024 - [info] 172.17.0.29(172.17.0.29:3306) Version=8.0.25 (oldest major version between slaves) log-bin:enabled

Mon Jan 22 04:18:48 2024 - [info] GTID ON

Mon Jan 22 04:18:48 2024 - [info] Replicating from 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 04:18:48 2024 - [info] Primary candidate for the new Master (candidate_master is set)

Mon Jan 22 04:18:48 2024 - [info] 172.17.0.56(172.17.0.56:3306) Version=8.0.25 (oldest major version between slaves) log-bin:enabled

Mon Jan 22 04:18:48 2024 - [info] GTID ON

Mon Jan 22 04:18:48 2024 - [info] Replicating from 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 04:18:48 2024 - [info] Not candidate for the new Master (no_master is set)

Mon Jan 22 04:18:48 2024 - [info] Current Alive Master: 172.17.0.35(172.17.0.35:3306)

Mon Jan 22 04:18:48 2024 - [info] Checking slave configurations…

Mon Jan 22 04:18:48 2024 - [info] read_only=1 is not set on slave 172.17.0.29(172.17.0.29:3306).

Mon Jan 22 04:18:48 2024 - [info] read_only=1 is not set on slave 172.17.0.56(172.17.0.56:3306).

Mon Jan 22 04:18:48 2024 - [info] Checking replication filtering settings…

Mon Jan 22 04:18:48 2024 - [info] binlog_do_db= , binlog_ignore_db=

Mon Jan 22 04:18:48 2024 - [info] Replication filtering check ok.

Mon Jan 22 04:18:48 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

Mon Jan 22 04:18:48 2024 - [info] Checking SSH publickey authentication settings on the current master…

Mon Jan 22 04:18:49 2024 - [info] HealthCheck: SSH to 172.17.0.35 is reachable.

Mon Jan 22 04:18:49 2024 - [info]

172.17.0.35(172.17.0.35:3306) (current master)

±-172.17.0.29(172.17.0.29:3306)

±-172.17.0.56(172.17.0.56:3306)

Mon Jan 22 04:18:49 2024 - [info] Checking master_ip_failover_script status:

Mon Jan 22 04:18:49 2024 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.17.0.35 --orig_master_ip=172.17.0.35 --orig_master_port=3306

IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.17.0.251/24===

Checking the Status of the script… OK

Mon Jan 22 04:18:49 2024 - [info] OK.

Mon Jan 22 04:18:49 2024 - [warning] shutdown_script is not defined.

Mon Jan 22 04:18:49 2024 - [info] Set master ping interval 1 seconds.

Mon Jan 22 04:18:49 2024 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 172.17.0.35 -s 172.17.0.29 -s 172.17.0.56 --user=root --master_host=172.17.0.35 --master_ip=172.17.0.35 --master_port=3306

Mon Jan 22 04:18:49 2024 - [info] Starting ping health check on 172.17.0.35(172.17.0.35:3306)…

Mon Jan 22 04:18:50 2024 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond…

停止mha

#masterha_stop --conf=/etc/mha/mysql_mha.cnf

9.测试

a.在node1上停止mysql进程

systemctl stop mysqld

b.关注日志

/home/mysql_mha/manager.log

c.在172.17.0.35上查看vip的情况，vip已经切换到在172.17.0.29上查看vip的情况上。

ip a

d.尝试登录数据库

slave1:172.17.0.29

mysql -uroot -p123456 -h 172.17.0.29 -P3308

[root@node2 ~]# mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 38

Server version: 8.0.26 MySQL Community Server - GPL

Copyright © 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

root@db 13:41: [(none)]> show master status \G;

*************************** 1. row ***************************

File: binlog-mysql.000003

Position: 416

Binlog_Do_DB:


Binlog_Ignore_DB:

Executed_Gtid_Set: 28219385-b80e-11ee-9b76-fa163ecc23fa:1-9

1 row in set (0.00 sec)

ERROR:

No query specified

10.恢复故障机

在原主节点启动mysql：systemctl start mysqld

指定主从，将故障机的主节点指向新的主

172.17.0.35上操作

change master to master_host=“172.17.0.29”,master_user=“repl”,master_password=“123456”,master_port=3308,master_auto_position=1;

start slave;

show slave status\G

172.17.0.29上操作

修改/etc/mha/mysql_mha.cnf

secondary_check_script=/usr/bin/masterha_secondary_check -s master-ip -s slave1-ip -s slave2-ip --user=root --master_host=master-ip --master_ip=master-ip --master_port=3306

secondary_check_script=/usr/bin/masterha_secondary_check -s 172.17.0.29 -s 172.17.0.35 -s 172.17.0.56 --user=root --master_host=172.17.0.29 --master_ip=172.17.0.29 --master_port=3308

stop slave;

reset slave all;

show slave status \G;

11.启动mha：

在monitor上启动mha进程：（172.17.0.56）

nohup masterha_manager --conf=/etc/mha/mysql_mha.cnf --remove_dead_master_conf --ignore_last_failover /home/mysql_mha/manager.log 2>&1 &

在monitor上检查状态：（172.17.0.56）

/usr/bin/masterha_check_status --conf=/etc/mha/mysql_mha.cnf

查看日志：（172.17.0.56）

less /home/mysql_mha/manager.log

12.停止主库，模拟宕机

node2主库：172.17.0.29

node1从1：172.17.0.35

node3从2：172.17.0.56

1).停止node2MySQL服务

[root@node2 init.d]# service mysqld stop

Shutting down MySQL… SUCCESS!

2).查看node1从节点状态

root@db 14:53: [(none)]> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Reconnecting after a failed source event read

Master_Host: 172.17.0.29

Master_User: repl

Master_Port: 3308

Connect_Retry: 60

Master_Log_File: binlog-mysql.000003

Read_Master_Log_Pos: 416

Relay_Log_File: yby-wg-pub-tsf-tes-0015-relay-bin.000003

Relay_Log_Pos: 637

Relay_Master_Log_File: binlog-mysql.000003

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:


Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 416

Relay_Log_Space: 3066

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL


Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master 'repl@172.17.0.29:3308' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '172.17.0.29:3308' (111)

Last_SQL_Errno: 0

Last_SQL_Error:


Replicate_Ignore_Server_Ids:

Master_Server_Id: 293306

Master_UUID: 28219385-b80e-11ee-9b76-fa163ecc23fa

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 240122 14:53:05

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 28219385-b80e-11ee-9b76-fa163ecc23fa:1-9

Executed_Gtid_Set: 28219385-b80e-11ee-9b76-fa163ecc23fa:1:4:6-9

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:


1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

3).查看node3从节点状态

root@db 14:52: [(none)]> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Reconnecting after a failed source event read

Master_Host: 172.17.0.29

Master_User: repl

Master_Port: 3308

Connect_Retry: 60

Master_Log_File: binlog-mysql.000003

Read_Master_Log_Pos: 416

Relay_Log_File: node3-relay-bin.000003

Relay_Log_Pos: 637

Relay_Master_Log_File: binlog-mysql.000003

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:


Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 416

Relay_Log_Space: 3066

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL


Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master 'repl@172.17.0.29:3308' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '172.17.0.29:3308' (111)

Last_SQL_Errno: 0

Last_SQL_Error:


Replicate_Ignore_Server_Ids:

Master_Server_Id: 293306

Master_UUID: 28219385-b80e-11ee-9b76-fa163ecc23fa

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 240122 14:53:05

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 28219385-b80e-11ee-9b76-fa163ecc23fa:1-9

Executed_Gtid_Set: 28219385-b80e-11ee-9b76-fa163ecc23fa:1:4:6-9,


8ea0ded3-b80f-11ee-a946-fa163e74bccb:1-7

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:


1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

此时，2 个 slave 节点的 Slave_IO_Running 线程的状态已经变成了 Connecting 状态

手动故障转移到其他 slave 节点

//node03 上操作:

[root@node3 ~]# ll /usr/bin/masterha_master_switch

-rwxr-xr-x 1 root root 2373 Mar 23 2018 /usr/bin/masterha_master_switch

[root@node3 ~]# /usr/bin/masterha_master_switch --master_state=dead --conf=/etc/mha/mysql_mha.cnf --dead_master_host=172.17.0.29 --dead_master_port=3308 --new_master_host=172.17.0.35 --new_master_port=3306 --ignore_last_failover

注意：

• 要加上-ignore_last_failover 参数，否则会报错

• 转移的过程中，有 2 次交互式的提示，第一次是让确认现在的主节点是否已经 dead，第二次是

确认是否要转移到某个节点。

以上为测试环境实验，生产环境谨慎操作。