MySQL基于MHA的高可用架构的搭建

DataBase干货分享 2021-06-25

597

一、MHA架构的介绍

1、简介

　　MHA（Master HA）是一款开源的 MySQL 高可用软件，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发的一套比较成熟的MySQL高可用方案，它为MySQL主从复制架构提供了三种故障转移方式：自动故障转移（主库宕机）、手动故障转移（主库宕机）、手动在线灾备切换（主库正常）。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

2、MHA组成部分

　　MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，所以需要在每服务器上都安装成功，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

　　目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库。

Manager工具包主要包括以下几个工具：

masterha_check_ssh              检查MHA的SSH配置状况
masterha_check_repl             检查MySQL复制状况
masterha_manger                 启动MHA
masterha_check_status           检测当前MHA运行状态
masterha_master_monitor         检测master是否宕机
masterha_master_switch          控制故障转移（自动或者手动）
masterha_conf_host              添加或删除配置的server信息

Node工具包主要包括以下几个工具：

save_binary_logs                保存和复制master的二进制日志
apply_diff_relay_logs           识别差异的中继日志事件并将其差异的事件应用于其他的slave
purge_relay_logs                清除中继日志（不会阻塞SQL线程）

注：这些工具通常由MHA Manager的脚本触发，无需人为操作

3、MHA架构图

4、MHA实现原理

（1）从宕机崩溃的master保存二进制日志事件（binlog events）;
（2）识别含有最新更新的slave；
（3）应用差异的中继日志（relay log）到其他的slave；
（4）应用从master保存的二进制日志事件（binlog events）；
（5）提升一个slave为新的master；
（6）使其他的slave连接新的master进行复制；

二、MHA架构的搭建

1、测试环境IP配置

机器名称	IP	服务器角色	备注
Manager	192.168.232.45	MHA Manager服务器	操作系统redhat7.5
node1	192.168.232.42	MySQL主节点	操作系统redhat7.5；MySQL5.7.22
node2	192.168.232.43	MySQL从节点1	操作系统redhat7.5；MySQL5.7.22
node3	192.168.232.44	MySQL从节点2	操作系统redhat7.5；MySQL5.7.22

2、配置IP映射

在各节点的/etc/hosts文件中配置如下内容：

192.168.232.42 node1
192.168.232.43 node2
192.168.232.44 node3
192.168.232.45 manager

3、配置4台机器ssh免密互通环境

（1）在node1，node2，node3，manager 4台机器上生成秘钥

命令：ssh-keygen -t rsa

注：出现的交互，直接enter就行

（2）将4台机器的公钥都先拷贝到manager机器上

命令：ssh-copy-id 192.168.232.45

注：上面2步操作在管理节点上（192.168.232.45）上也需要操作

（3）查看管理节点机器下的~/.ssh/authorized_keys下是否有4台机器的公钥，并拷贝到另外3台机器

命令：

cat ~/.ssh/authorized_keys 
scp ~/.ssh/authorized_keys root@192.168.232.42:~/.ssh/authorized_keys
scp ~/.ssh/authorized_keys root@192.168.232.43:~/.ssh/authorized_keys
scp ~/.ssh/authorized_keys root@192.168.232.44:~/.ssh/authorized_keys

（4）测试是否可以免密登录

命令：

ssh 192.168.232.42
ssh 192.168.232.43
ssh 192.168.232.44

注：每个节点都测试通过就OK了，最好在其他3个节点都登录一下，因为是拷贝过去的公钥，第一次可能需要交互一下输入yes，下次就不需要了

4、搭建一主多从的复制架构

（1）配置master节点的/etc/my.cnf

[mysqld]
datadir=/mysql/mysql5.7/data
basedir=/mysql/mysql5.7
socket=/tmp/mysql.sock
user=mysql
port=3308
character-set-server=utf8
# skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id = 1 复制集群中的各节点的id，三个节点不能一样
log-bin = mysql-bin 开启二进制日志
relay-log = relay-log 开启中继日志
skip_name_resolve 关闭名称解析（不是必须的）


binlog-do-db=customer 要同步的数据库名称
binlog-ignore-db=mysql 需要忽略的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
auto-increment-increment=10
auto-increment-offset=1
replicate-do-db=customer 
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
log-slave-updates = 1 使得更新的数据写入二进制日志中
[mysqld_safe]
log-error=/mysql/mysql5.7/log/mysqld.log
#pid-file=/mysql/mysql5.7/run/mysqld/mysqld.pid

注：修改完配置文件，需要重启才能生效

（2）配置slave1和slave2节点的/etc/my.cnf

[mysqld]
datadir=/mysql/mysql5.7/data
basedir=/mysql/mysql5.7
socket=/tmp/mysql.sock
user=mysql
port=3308
character-set-server=utf8
# skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


server-id = 2           复制集群中的各节点的id，三个节点不能一样


log-bin = mysql-bin       开启二进制日志
relay-log = relay-log     开启中继日志
binlog_format = row       复制模式使用行模式
relay_log_purge = 0      是否自动清空不再需要的中继日志
skip_name_resolve       关闭名称解析（不是必须的）




read-only=0                 启用只读
binlog-do-db=customer       要同步的数据库名称
binlog-ignore-db=mysql       需要忽略的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
auto-increment-increment=10
auto-increment-offset=1


replicate-do-db=customer
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
log-slave-updates = 1       使得更新的数据写入二进制日志中


[mysqld_safe]
log-error=/mysql/mysql5.7/log/mysqld.log
#pid-file=/mysql/mysql5.7/run/mysqld/mysqld.pid

注：修改完配置文件，需要重启才能生效

（3）在3个节点上都创建用于同步的用户

命令：

mysql> grant replication slave on *.* to 'repl_user1'@'192.168.232.%' identified by '$a123456';
Query OK, 0 rows affected, 1 warning (0.05 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)


mysql> select user,host from user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| root          | %             |
| repl_user1    | 192.168.232.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
+---------------+---------------+
4 rows in set (0.01 sec)


mysql>

注：因为每个节点都有可能成为master，所以3个节点上都需要创建同步的用户，我这里host配置为192.168.232.%意思是192.168.232.网段可以使用该用户。

（4）主节点上创建测试库customer和测试表

mysql> create database customer default character set utf8;
Query OK, 1 row affected (0.44 sec)


mysql> use customer;
Database changed
mysql> create table person (id int(10),username varchar(20),age int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.14 sec)


mysql> insert into person values(1,'jack',19);
Query OK, 1 row affected (0.08 sec)


mysql> select * from person;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    1 | jack     |   19 |
+------+----------+------+
1 row in set (0.00 sec)


mysql>

（5）导出customer库的数据，并copy到其他2个节点

命令：

./mysqldump --master-data -uroot -p customer >/mysql/customer.sql

注：--master-data :加上该选项，

在生成的dump文件中就会将二进制的信息写入到输出文件中，

比如MASTER_LOG_FILE 和 MASTER_LOG_POS

（6）把master节点的dump备份文件拷贝到2个slave节点上并恢复数据库

--1、拷贝到node2和node3
[mysql@node1 mysql]$ scp customer.sql mysql@192.168.232.43:/mysql
mysql@192.168.232.43's password: 
customer.sql                                                       100% 2031   696.1KB/s   00:00    
[mysql@node1 mysql]$ scp customer.sql mysql@192.168.232.44:/mysql
mysql@192.168.232.44's password: 
customer.sql                                                       100% 2031   617.9KB/s   00:00 


--2、node2上恢复数据库
mysql> create database customer default character set utf8;
Query OK, 1 row affected (0.35 sec)


mysql> exit
Bye
[mysql@node2 ~]$ mysql -uroot -p customer < /mysql/customer.sql 
Enter password: 
[mysql@node2 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-log MySQL Community Server (GPL)


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.


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.


mysql> select * from customer.person;
+------+----------+------+
| id | username | age |
+------+----------+------+
| 1 | jack | 19 |
+------+----------+------+
1 row in set (0.01 sec)


mysql>


--3、node3上恢复数据库


mysql> create database customer default character set utf8;
Query OK, 1 row affected (0.07 sec)


mysql> exit
Bye
[mysql@node3 ~]$ mysql -uroot -p customer < /mysql/customer.sql 
Enter password: 
[mysql@node3 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-log MySQL Community Server (GPL)


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.


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.


mysql> select * from customer.person;
+------+----------+------+
| id | username | age |
+------+----------+------+
| 1 | jack | 19 |
+------+----------+------+
1 row in set (0.01 sec)


mysql>

注：slave节点上恢复数据库之前，需要先把库创建好

（7）查看master节点的binary日志名和偏移量

命令：

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 892
     Binlog_Do_DB: customer
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)


mysql>

注：也可以去我们备份出的dump里查看，如果生产上master节点还有业务连进来，就要以dump文件里的File和Position为准了

（8）在2个slave节点开启主从同步

命令：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.232.42',
    -> MASTER_USER='repl_user1',
    -> MASTER_PORT=3308,
    -> MASTER_PASSWORD='$a123456',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=892;
Query OK, 0 rows affected, 2 warnings (0.59 sec)


mysql>

（9）开启2个slave节点的同步，查看slave的状态

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.42
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 892
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
           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: 892
              Relay_Log_Space: 521
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 5eb9dc85-2d68-11eb-acf4-000c29cddf72
             Master_Info_File: /mysql/mysql5.7/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)


mysql>

（10）在master节点插入数据，看slave节点是否同步

--1、master节点
mysql> use customer;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> insert into person values(2,'rose',56);
Query OK, 1 row affected (0.01 sec)


mysql> select * from person;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    1 | jack     |   19 |
|    2 | rose     |   56 |
+------+----------+------+
2 rows in set (0.00 sec)


mysql> 


--2、slave节点
[mysql@node2 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.22-log MySQL Community Server (GPL)


Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.


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.


mysql> select * from customer.person;
+------+----------+------+
| id   | username | age  |
+------+----------+------+
|    1 | jack     |   19 |
|    2 | rose     |   56 |
+------+----------+------+
2 rows in set (0.00 sec)


mysql>


--3、master节点查看slave
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3308 | 1 | 0b8c9b28-3143-11eb-b7bf-000c2927b3e8 |
| 2 | | 3308 | 1 | 2aa26dac-2df4-11eb-b45c-000c29c918c1 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

注：数据已经可以同步，主从架构已经搭建完成。

5、搭建MHA

（1）安装MHA包

* manager节点和node节点都需要安装：mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@node1 cdrom]# cd mysql/mha/
[root@node1 mha]# ll
total 36
-rw-r--r--. 1 mysql mysql 36328 Nov 28 15:57 mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@node1 mha]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining mha4mysql-node-0.58-0.el7.centos.noarch.rpm: mha4mysql-node-0.58-0.el7.centos.noarch
Marking mha4mysql-node-0.58-0.el7.centos.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mha4mysql-node.noarch 0:0.58-0.el7.centos will be installed
--> Finished Dependency Resolution


Dependencies Resolved


=====================================================================================================
 Package           Arch      Version               Repository                                   Size
=====================================================================================================
Installing:
 mha4mysql-node    noarch    0.58-0.el7.centos     mha4mysql-node-0.58-0.el7.centos.noarch    106 k


Transaction Summary
=====================================================================================================
Install  1 Package


Total size: 106 k
Installed size: 106 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mha4mysql-node-0.58-0.el7.centos.noarch                                           1/1 
  Verifying  : mha4mysql-node-0.58-0.el7.centos.noarch                                           1/1 


Installed:
  mha4mysql-node.noarch 0:0.58-0.el7.centos                                                          


Complete!
[root@node1 mha]# rpm -qa | grep mha
mha4mysql-node-0.58-0.el7.centos.noarch
[root@node1 mha]#

注：mha4mysql-node是依赖mariadb-libs和perl-DBD-MySQL这2个包的，系统一般会自带，如果安装mysql的时候删除了，需要使用本地yum源再安装一次即可，错误如下：

* manager节点需另外安装：mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

mha4mysql-manager依赖的包：

perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-2.2.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

注：上面这些依赖安装完成后，

mha4mysql-manager才能安装成功

perl-Log-Dispatch-2.41-2.2.noarch.rpm包安装的时候，

可能还需要安装其他依赖的包，那些包本地yum源中都有，只需要配置好本地yum源即可安装成功，错误如下：

Installing:
perl-Log-Dispatch noarch 2.41-2.2 perl-Log-Dispatch-2.41-2.2.noarch 121 k
Installing for dependencies:
perl-Class-Load noarch 0.20-3.el7 base 27 k
perl-Data-OptList noarch 0.107-9.el7 base 23 k
perl-List-MoreUtils x86_64 0.33-9.el7 base 58 k
perl-MailTools noarch 2.12-2.el7 base 108 k
perl-Module-Implementation noarch 0.06-6.el7 base 17 k
perl-Module-Runtime noarch 0.013-4.el7 base 19 k
perl-Net-SMTP-SSL noarch 1.01-13.el7 base 9.1 k
perl-Package-DeprecationManager noarch 0.13-7.el7 base 19 k
perl-Package-Stash noarch 0.34-2.el7 base 34 k
perl-Package-Stash-XS x86_64 0.26-3.el7 base 31 k
perl-Params-Util x86_64 1.07-6.el7 base 38 k
perl-Params-Validate x86_64 1.08-4.el7 base 69 k
perl-Sub-Install noarch 0.926-6.el7 base 21 k
perl-Sys-Syslog x86_64 0.33-3.el7 base 42 k
perl-Try-Tiny noarch 0.12-2.el7 base 23 k
Transaction Summary
=====================================================================================================
Install 1 Package (+15 Dependent packages)
Total size: 659 k
Total download size: 538 k
Installed size: 1.1 M
Downloading packages:


Error downloading packages:
perl-Sub-Install-0.926-6.el7.noarch: [Errno 256] No more mirrors to try.
perl-Module-Implementation-0.06-6.el7.noarch: [Errno 256] No more mirrors to try.
perl-Params-Validate-1.08-4.el7.x86_64: [Errno 256] No more mirrors to try.
perl-Package-DeprecationManager-0.13-7.el7.noarch: [Errno 256] No more mirrors to try.
perl-Module-Runtime-0.013-4.el7.noarch: [Errno 256] No more mirrors to try.
perl-MailTools-2.12-2.el7.noarch: [Errno 256] No more mirrors to try.
perl-Package-Stash-XS-0.26-3.el7.x86_64: [Errno 256] No more mirrors to try.
perl-Params-Util-1.07-6.el7.x86_64: [Errno 256] No more mirrors to try.
perl-Package-Stash-0.34-2.el7.noarch: [Errno 256] No more mirrors to try.
perl-Sys-Syslog-0.33-3.el7.x86_64: [Errno 256] No more mirrors to try.
perl-Data-OptList-0.107-9.el7.noarch: [Errno 256] No more mirrors to try.
perl-List-MoreUtils-0.33-9.el7.x86_64: [Errno 256] No more mirrors to try.
perl-Net-SMTP-SSL-1.01-13.el7.noarch: [Errno 256] No more mirrors to try.
perl-Try-Tiny-0.12-2.el7.noarch: [Errno 256] No more mirrors to try.
perl-Class-Load-0.20-3.el7.noarch: [Errno 256] No more mirrors to try.


[root@manager rpm]# yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm     
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Examining mha4mysql-manager-0.58-0.el7.centos.noarch.rpm: mha4mysql-manager-0.58-0.el7.centos.noarch
Marking mha4mysql-manager-0.58-0.el7.centos.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mha4mysql-manager.noarch 0:0.58-0.el7.centos will be installed
--> Finished Dependency Resolution


Dependencies Resolved


=====================================================================================================
 Package            Arch    Version               Repository                                    Size
=====================================================================================================
Installing:
 mha4mysql-manager  noarch  0.58-0.el7.centos     mha4mysql-manager-0.58-0.el7.centos.noarch  328 k


Transaction Summary
=====================================================================================================
Install  1 Package


Total size: 328 k
Installed size: 328 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mha4mysql-manager-0.58-0.el7.centos.noarch                                        1/1 
  Verifying  : mha4mysql-manager-0.58-0.el7.centos.noarch                                        1/1 


Installed:
  mha4mysql-manager.noarch 0:0.58-0.el7.centos                                                       


Complete!
[root@manager rpm]#

* 安装包已上传到百度云盘，可自行下载：

链接：https://pan.baidu.com/s/1YQ-1HkSbBbMOjt5SFI9FKw 
提取码：n0fm

（2）在3个节点上创建拥有管理权的用户

命令：

mysql> grant all on *.* to 'mhaadmin'@'192.168.232.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


mysql> select user,host from user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| root          | %             |
| mhaadmin      | 192.168.232.% |
| repl_user1    | 192.168.232.% |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
+---------------+---------------+
5 rows in set (0.00 sec)


mysql>

注：以上命令，3个节点都执行

（3）配置mha.cnf文件

* 配置文件的位置：/etc/mha_master/mha.cnf

* 配置文件的内容如下：

[server default]
user=mhaadmin                     //mha管理用户
password=123456                   //mha管理密码
manager_workdir=/mha              //mha自己的工作路径
manager_log=/mha/log/mha_manager.log     //mha的日志路径
remote_workdir=/mysql/mha            //远程主机的工作目录
ssh_user=root                        //ssh免密登录的用户
repl_user=repl_user1                 //数据库主从复制的用户
repl_password=$a123456               //数据库主从复制用户的密码
ping_interval=1                      //ping间隔时长
[server1]                            //节点1
hostname=192.168.232.42
ssh_port=22
port=3308
master_binlog_dir=/mysql/mysql5.7/data
candidate_master=1                 //将来可不可以成为master的候选节点
[server2]
hostname=192.168.232.43
ssh_port=22
port=3308
master_binlog_dir=/mysql/mysql5.7/data
candidate_master=1
[server3]
hostname=192.168.232.44
ssh_port=22
port=3308
master_binlog_dir=/mysql/mysql5.7/data
candidate_master=1

（4）MHA检测各节点之间ssh互信是否ok

[root@manager mha_master]# masterha_check_ssh -conf=/etc/mha_master/mha.cnf
Wed Dec  2 17:37:21 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec  2 17:37:21 2020 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Dec  2 17:37:21 2020 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Dec  2 17:37:21 2020 - [info] Starting SSH connection tests..
Wed Dec  2 17:37:25 2020 - [debug] 
Wed Dec  2 17:37:22 2020 - [debug]  Connecting via SSH from root@192.168.232.44(192.168.232.44:22) to root@192.168.232.42(192.168.232.42:22)..
Wed Dec  2 17:37:23 2020 - [debug]   ok.
Wed Dec  2 17:37:23 2020 - [debug]  Connecting via SSH from root@192.168.232.44(192.168.232.44:22) to root@192.168.232.43(192.168.232.43:22)..
Wed Dec  2 17:37:24 2020 - [debug]   ok.
Wed Dec  2 17:37:25 2020 - [debug] 
Wed Dec  2 17:37:21 2020 - [debug]  Connecting via SSH from root@192.168.232.42(192.168.232.42:22) to root@192.168.232.44(192.168.232.44:22)..
Wed Dec  2 17:37:22 2020 - [debug]   ok.
Wed Dec  2 17:37:22 2020 - [debug]  Connecting via SSH from root@192.168.232.42(192.168.232.42:22) to root@192.168.232.43(192.168.232.43:22)..
Wed Dec  2 17:37:24 2020 - [debug]   ok.
Wed Dec  2 17:37:26 2020 - [debug] 
Wed Dec  2 17:37:22 2020 - [debug]  Connecting via SSH from root@192.168.232.43(192.168.232.43:22) to root@192.168.232.42(192.168.232.42:22)..
Wed Dec  2 17:37:23 2020 - [debug]   ok.
Wed Dec  2 17:37:23 2020 - [debug]  Connecting via SSH from root@192.168.232.43(192.168.232.43:22) to root@192.168.232.44(192.168.232.44:22)..
Wed Dec  2 17:37:25 2020 - [debug]   ok.
Wed Dec  2 17:37:26 2020 - [info] All SSH connection tests passed successfully.
[root@manager mha_master]#

（5）MHA检测MySQL复制集群连接配置是否OK

[root@manager mha_master]# masterha_check_repl -conf=/etc/mha_master/mha.cnf
Wed Dec  2 17:58:45 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec  2 17:58:45 2020 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Dec  2 17:58:45 2020 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Dec  2 17:58:45 2020 - [info] MHA::MasterMonitor version 0.58.
Wed Dec  2 17:58:46 2020 - [info] GTID failover mode = 0
Wed Dec  2 17:58:46 2020 - [info] Dead Servers:
Wed Dec  2 17:58:46 2020 - [info] Alive Servers:
Wed Dec  2 17:58:46 2020 - [info]   192.168.232.42(192.168.232.42:3308)
Wed Dec  2 17:58:46 2020 - [info]   192.168.232.43(192.168.232.43:3308)
Wed Dec  2 17:58:46 2020 - [info]   192.168.232.44(192.168.232.44:3308)
Wed Dec  2 17:58:46 2020 - [info] Alive Slaves:
Wed Dec  2 17:58:46 2020 - [info]   192.168.232.43(192.168.232.43:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Wed Dec  2 17:58:46 2020 - [info]     Replicating from 192.168.232.42(192.168.232.42:3308)
Wed Dec  2 17:58:46 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Dec  2 17:58:46 2020 - [info]   192.168.232.44(192.168.232.44:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Wed Dec  2 17:58:46 2020 - [info]     Replicating from 192.168.232.42(192.168.232.42:3308)
Wed Dec  2 17:58:46 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Dec  2 17:58:46 2020 - [info] Current Alive Master: 192.168.232.42(192.168.232.42:3308)
Wed Dec  2 17:58:46 2020 - [info] Checking slave configurations..
Wed Dec  2 17:58:46 2020 - [info]  read_only=1 is not set on slave 192.168.232.43(192.168.232.43:3308).
Wed Dec  2 17:58:46 2020 - [info]  read_only=1 is not set on slave 192.168.232.44(192.168.232.44:3308).
Wed Dec  2 17:58:46 2020 - [info] Checking replication filtering settings..
Wed Dec  2 17:58:46 2020 - [info]  binlog_do_db= customer, binlog_ignore_db= information_schema,mysql,performance_schema,sys
Wed Dec  2 17:58:46 2020 - [info]  Replication filtering check ok.
Wed Dec  2 17:58:46 2020 - [info] GTID (with auto-pos) is not supported
Wed Dec  2 17:58:46 2020 - [info] Starting SSH connection tests..
Wed Dec  2 17:58:50 2020 - [info] All SSH connection tests passed successfully.
Wed Dec  2 17:58:50 2020 - [info] Checking MHA Node version..
Wed Dec  2 17:58:51 2020 - [info]  Version check ok.
Wed Dec  2 17:58:51 2020 - [info] Checking SSH publickey authentication settings on the current master..
Wed Dec  2 17:58:52 2020 - [info] HealthCheck: SSH to 192.168.232.42 is reachable.
Wed Dec  2 17:58:52 2020 - [info] Master MHA Node version is 0.58.
Wed Dec  2 17:58:52 2020 - [info] Checking recovery script configurations on 192.168.232.42(192.168.232.42:3308)..
Wed Dec  2 17:58:52 2020 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/mysql/mysql5.7/data --output_file=/mysql/mha/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000002 
Wed Dec  2 17:58:52 2020 - [info]   Connecting to root@192.168.232.42(192.168.232.42:22).. 
  Creating /mysql/mha if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /mysql/mysql5.7/data, up to mysql-bin.000002
Wed Dec  2 17:58:53 2020 - [info] Binlog setting check done.
Wed Dec  2 17:58:53 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Dec  2 17:58:53 2020 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mhaadmin' --slave_host=192.168.232.43 --slave_ip=192.168.232.43 --slave_port=3308 --workdir=/mysql/mha --target_version=5.7.22-log --manager_version=0.58 --relay_log_info=/mysql/mysql5.7/data/relay-log.info  --relay_dir=/mysql/mysql5.7/data/  --slave_pass=xxx
Wed Dec  2 17:58:53 2020 - [info]   Connecting to root@192.168.232.43(192.168.232.43:22).. 
  Checking slave recovery environment settings..
    Opening /mysql/mysql5.7/data/relay-log.info ... ok.
    Relay log found at /mysql/mysql5.7/data, up to relay-log.000002
    Temporary relay log file is /mysql/mysql5.7/data/relay-log.000002
    Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Dec  2 17:58:54 2020 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mhaadmin' --slave_host=192.168.232.44 --slave_ip=192.168.232.44 --slave_port=3308 --workdir=/mysql/mha --target_version=5.7.22-log --manager_version=0.58 --relay_log_info=/mysql/mysql5.7/data/relay-log.info  --relay_dir=/mysql/mysql5.7/data/  --slave_pass=xxx
Wed Dec  2 17:58:54 2020 - [info]   Connecting to root@192.168.232.44(192.168.232.44:22).. 
  Checking slave recovery environment settings..
    Opening /mysql/mysql5.7/data/relay-log.info ... ok.
    Relay log found at /mysql/mysql5.7/data, up to relay-log.000002
    Temporary relay log file is /mysql/mysql5.7/data/relay-log.000002
    Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Dec  2 17:58:55 2020 - [info] Slaves settings check done.
Wed Dec  2 17:58:55 2020 - [info] 
192.168.232.42(192.168.232.42:3308) (current master)
 +--192.168.232.43(192.168.232.43:3308)
 +--192.168.232.44(192.168.232.44:3308)


Wed Dec  2 17:58:55 2020 - [info] Checking replication health on 192.168.232.43..
Wed Dec  2 17:58:55 2020 - [info]  ok.
Wed Dec  2 17:58:55 2020 - [info] Checking replication health on 192.168.232.44..
Wed Dec  2 17:58:55 2020 - [info]  ok.
Wed Dec  2 17:58:55 2020 - [warning] master_ip_failover_script is not defined.
Wed Dec  2 17:58:55 2020 - [warning] shutdown_script is not defined.
Wed Dec  2 17:58:55 2020 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.

可能遇到的错误：

Wed Dec  2 17:39:22 2020 - [info]   Connecting to root@192.168.232.44(192.168.232.44:22).. 
Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
 at /usr/bin/apply_diff_relay_logs line 532.
Wed Dec  2 17:39:22 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Wed Dec  2 17:39:22 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Wed Dec  2 17:39:22 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
Wed Dec  2 17:39:22 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Wed Dec  2 17:39:22 2020 - [info] Got exit code 1 (Not master dead).


MySQL Replication Health is NOT OK!

解决办法：

这个问题是因为没有执行mysqlbinlog的软链接导致的，所以创建软链接即可（三个数据节点都执行）

[root@node1 .ssh]# ln -s /mysql/mysql5.7/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
[root@node1 .ssh]# ln -s /mysql/mysql5.7/bin/mysql /usr/local/bin/mysql

（6）启动MHA（也可以不启动，不启动的话就不能自动故障转移了）

--1、启动mha
[root@manager mha]# nohup masterha_manager --conf=/etc/mha_master/mha.cnf & >/mha/log/mha_manager.log
[1] 2374


--2、查看mha的状态
[root@manager mha]# masterha_check_status --conf=/etc/mha_master/mha.cnf
mha (pid:2374) is running(0:PING_OK), master:192.168.232.42


--3、停止mha
[root@manager mha]# masterha_stop   --conf=/etc/mha_master/mha.cnf
Stopped mha successfully.
[1]+  Exit 1                  nohup masterha_manager --conf=/etc/mha_master/mha.cnf
[root@manager mha]# masterha_check_status --conf=/etc/mha_master/mha.cnf
mha is stopped(2:NOT_RUNNING).

到这一步，整个MySQL的MHA高可用架构已经搭建完成了，接下来我们就测试一下是否可以实现故障转移吧。

三、MHA故障转移测试

1、自动故障转移（主库宕机）

注：自动故障转移，MHA的状态必须是running状态

（1）关闭主节点服务器

因为现在主节点在node1（192.168.232.42）上，直接重启服务器。

（2）查看MHA日志

192.168.232.43(192.168.232.43:3308): Resetting slave info succeeded.
Master failover to 192.168.232.43(192.168.232.43:3308) completed successfully.

注：现在主节点已经变成了node2（192.168.232.43）了

（3）查看新主节点和slave节点的状态

--1、node2节点上查看
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 154
     Binlog_Do_DB: customer
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)


mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3308 |         2 | 0b8c9b28-3143-11eb-b7bf-000c2927b3e8 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)




--2、node3节点上查看
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.43
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys

注：

* 发现192.168.232.43变成了主节点，192.168.232.44变成了从节点，自动转移成功。

* 故障转移成功后，MHA将会自动停止，使用masterha_check_status会看到如下提示：

* 原来的主节点（node1）机器重启后，不会自动和新主同步数据，这个时候最好重新备份一下，重新把该机器加入到现有的主从架构中。

由于我这里是测试环境，没有新增数据，所以直接执行一下change master把node1加入到现有的主从架构里，恢复了

--1、node1上执行change master
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.232.43',
    -> MASTER_USER='repl_user1',
    -> MASTER_PORT=3308,
    -> MASTER_PASSWORD='$a123456',
    -> MASTER_LOG_FILE='mysql-bin.000005',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)


--2、启用复制
mysql> start slave
    -> ;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.43
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys

2、手动故障转移（主库宕机）

注：该场景是在没有启动MHA的情况下，主库宕机了，手动故障转移到其他slave节点。

（1）查看mha监控的状态（mha要是stopped状态）

[root@manager mha]# masterha_check_status --conf=/etc/mha_master/mha.cnf 
mha is stopped(2:NOT_RUNNING).

（2）停止主库，模拟宕机

--1、停止主库
[mysql@node2 ~]$ service mysql stop
Shutting down MySQL............ SUCCESS! 


--2、查看node1从节点的状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 192.168.232.43
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys


--3、查看node3从节点的状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 192.168.232.43
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys

注：2个slave节点的Slave_IO_Running线程的状态已经变成了Connecting状态

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

[root@manager mha]# masterha_master_switch --master_state=dead --conf=/etc/mha_master/mha.cnf  --dead_master_host=192.168.232.43 --dead_master_port=3308 --new_master_host=192.168.232.42 --new_master_port=3308 --ignore_last_failover
--dead_master_ip=<dead_master_ip> is not set. Using 192.168.232.43.
Fri Dec  4 10:25:59 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Dec  4 10:25:59 2020 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Fri Dec  4 10:25:59 2020 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Fri Dec  4 10:25:59 2020 - [info] MHA::MasterFailover version 0.58.
Fri Dec  4 10:25:59 2020 - [info] Starting master failover.
Fri Dec  4 10:25:59 2020 - [info] 
Fri Dec  4 10:25:59 2020 - [info] * Phase 1: Configuration Check Phase..
Fri Dec  4 10:25:59 2020 - [info] 
Fri Dec  4 10:26:01 2020 - [info] GTID failover mode = 0
Fri Dec  4 10:26:01 2020 - [info] Dead Servers:
Fri Dec  4 10:26:01 2020 - [info]   192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:01 2020 - [info] Checking master reachability via MySQL(double check)...
Fri Dec  4 10:26:01 2020 - [info]  ok.
Fri Dec  4 10:26:01 2020 - [info] Alive Servers:
Fri Dec  4 10:26:01 2020 - [info]   192.168.232.42(192.168.232.42:3308)
Fri Dec  4 10:26:01 2020 - [info]   192.168.232.44(192.168.232.44:3308)
Fri Dec  4 10:26:01 2020 - [info] Alive Slaves:
Fri Dec  4 10:26:01 2020 - [info]   192.168.232.42(192.168.232.42:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:26:01 2020 - [info]     Replicating from 192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:01 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Dec  4 10:26:01 2020 - [info]   192.168.232.44(192.168.232.44:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:26:01 2020 - [info]     Replicating from 192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:01 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Master 192.168.232.43(192.168.232.43:3308) is dead. Proceed? (yes/NO): yes
Fri Dec  4 10:26:07 2020 - [info] Starting Non-GTID based failover.
Fri Dec  4 10:26:07 2020 - [info] 
Fri Dec  4 10:26:07 2020 - [info] ** Phase 1: Configuration Check Phase completed.
Fri Dec  4 10:26:07 2020 - [info] 
Fri Dec  4 10:26:07 2020 - [info] * Phase 2: Dead Master Shutdown Phase..
Fri Dec  4 10:26:07 2020 - [info] 
Fri Dec  4 10:26:08 2020 - [info] HealthCheck: SSH to 192.168.232.43 is reachable.
Fri Dec  4 10:26:08 2020 - [info] Forcing shutdown so that applications never connect to the current master..
Fri Dec  4 10:26:08 2020 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Fri Dec  4 10:26:08 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Fri Dec  4 10:26:08 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Fri Dec  4 10:26:08 2020 - [info] 
Fri Dec  4 10:26:08 2020 - [info] * Phase 3: Master Recovery Phase..
Fri Dec  4 10:26:08 2020 - [info] 
Fri Dec  4 10:26:08 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Fri Dec  4 10:26:08 2020 - [info] 
Fri Dec  4 10:26:08 2020 - [info] The latest binary log file/position on all slaves is mysql-bin.000005:154
Fri Dec  4 10:26:08 2020 - [info] Latest slaves (Slaves that received relay log files to the latest):
Fri Dec  4 10:26:08 2020 - [info]   192.168.232.42(192.168.232.42:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:26:08 2020 - [info]     Replicating from 192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:08 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Dec  4 10:26:08 2020 - [info]   192.168.232.44(192.168.232.44:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:26:08 2020 - [info]     Replicating from 192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:08 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Dec  4 10:26:08 2020 - [info] The oldest binary log file/position on all slaves is mysql-bin.000005:154
Fri Dec  4 10:26:08 2020 - [info] Oldest slaves:
Fri Dec  4 10:26:08 2020 - [info]   192.168.232.42(192.168.232.42:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:26:08 2020 - [info]     Replicating from 192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:08 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Dec  4 10:26:08 2020 - [info]   192.168.232.44(192.168.232.44:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:26:08 2020 - [info]     Replicating from 192.168.232.43(192.168.232.43:3308)
Fri Dec  4 10:26:08 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Dec  4 10:26:08 2020 - [info] 
Fri Dec  4 10:26:08 2020 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Fri Dec  4 10:26:08 2020 - [info] 
Fri Dec  4 10:26:09 2020 - [info] Fetching dead master's binary logs..
Fri Dec  4 10:26:09 2020 - [info] Executing command on the dead master 192.168.232.43(192.168.232.43:3308): save_binary_logs --command=save --start_file=mysql-bin.000005  --start_pos=154 --binlog_dir=/mysql/mysql5.7/data --output_file=/mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.58
  Creating /mysql/mha if not exists..    ok.
 Concat binary/relay logs from mysql-bin.000005 pos 154 to mysql-bin.000005 EOF into /mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog ..
 Binlog Checksum enabled
  Dumping binlog format description event, from position 0 to 154.. ok.
  Dumping effective binlog data from /mysql/mysql5.7/data/mysql-bin.000005 position 154 to tail(177).. ok.
 Binlog Checksum enabled
 Concat succeeded.
saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog                 100%  177    84.5KB/s   00:00    
Fri Dec  4 10:26:11 2020 - [info] scp from root@192.168.232.43:/mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog to local:/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog succeeded.
Fri Dec  4 10:26:11 2020 - [info] HealthCheck: SSH to 192.168.232.42 is reachable.
Fri Dec  4 10:26:12 2020 - [info] HealthCheck: SSH to 192.168.232.44 is reachable.
Fri Dec  4 10:26:13 2020 - [info] 
Fri Dec  4 10:26:13 2020 - [info] * Phase 3.3: Determining New Master Phase..
Fri Dec  4 10:26:13 2020 - [info] 
Fri Dec  4 10:26:13 2020 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Fri Dec  4 10:26:13 2020 - [info] All slaves received relay logs to the same position. No need to resync each other.
Fri Dec  4 10:26:13 2020 - [info] 192.168.232.42 can be new master.
Fri Dec  4 10:26:13 2020 - [info] New master is 192.168.232.42(192.168.232.42:3308)
Fri Dec  4 10:26:13 2020 - [info] Starting master failover..
Fri Dec  4 10:26:13 2020 - [info] 
From:
192.168.232.43(192.168.232.43:3308) (current master)
 +--192.168.232.42(192.168.232.42:3308)
 +--192.168.232.44(192.168.232.44:3308)


To:
192.168.232.42(192.168.232.42:3308) (new master)
 +--192.168.232.44(192.168.232.44:3308)


Starting master switch from 192.168.232.43(192.168.232.43:3308) to 192.168.232.42(192.168.232.42:3308)? (yes/NO): yes
Fri Dec  4 10:26:18 2020 - [info] New master decided manually is 192.168.232.42(192.168.232.42:3308)
Fri Dec  4 10:26:18 2020 - [info] 
Fri Dec  4 10:26:18 2020 - [info] * Phase 3.4: New Master Diff Log Generation Phase..
Fri Dec  4 10:26:18 2020 - [info] 
Fri Dec  4 10:26:18 2020 - [info]  This server has all relay logs. No need to generate diff files from the latest slave.
Fri Dec  4 10:26:18 2020 - [info] Sending binlog..
saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog                 100%  177   147.3KB/s   00:00    
Fri Dec  4 10:26:19 2020 - [info] scp from local:/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog to root@192.168.232.42:/mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog succeeded.
Fri Dec  4 10:26:19 2020 - [info] 
Fri Dec  4 10:26:19 2020 - [info] * Phase 3.5: Master Log Apply Phase..
Fri Dec  4 10:26:19 2020 - [info] 
Fri Dec  4 10:26:19 2020 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Fri Dec  4 10:26:19 2020 - [info] Starting recovery on 192.168.232.42(192.168.232.42:3308)..
Fri Dec  4 10:26:19 2020 - [info]  Generating diffs succeeded.
Fri Dec  4 10:26:19 2020 - [info] Waiting until all relay logs are applied.
Fri Dec  4 10:26:19 2020 - [info]  done.
Fri Dec  4 10:26:19 2020 - [info] Getting slave status..
Fri Dec  4 10:26:19 2020 - [info] This slave(192.168.232.42)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000005:154). No need to recover from Exec_Master_Log_Pos.
Fri Dec  4 10:26:19 2020 - [info] Connecting to the target slave host 192.168.232.42, running recover script..
Fri Dec  4 10:26:19 2020 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='mhaadmin' --slave_host=192.168.232.42 --slave_ip=192.168.232.42  --slave_port=3308 --apply_files=/mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog --workdir=/mysql/mha --target_version=5.7.22-log --timestamp=20201204102559 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.58 --slave_pass=xxx
Fri Dec  4 10:26:20 2020 - [info] 
MySQL client version is 5.7.22. Using --binary-mode.
Applying differential binary/relay log files /mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog on 192.168.232.42:3308. This may take long time...
Applying log files succeeded.
Fri Dec  4 10:26:20 2020 - [info]  All relay logs were successfully applied.
Fri Dec  4 10:26:20 2020 - [info] Getting new master's binlog name and position..
Fri Dec  4 10:26:20 2020 - [info]  mysql-bin.000006:154
Fri Dec  4 10:26:20 2020 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.232.42', MASTER_PORT=3308, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154, MASTER_USER='repl_user1', MASTER_PASSWORD='xxx';
Fri Dec  4 10:26:20 2020 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Fri Dec  4 10:26:20 2020 - [info] ** Finished master recovery successfully.
Fri Dec  4 10:26:20 2020 - [info] * Phase 3: Master Recovery Phase completed.
Fri Dec  4 10:26:20 2020 - [info] 
Fri Dec  4 10:26:20 2020 - [info] * Phase 4: Slaves Recovery Phase..
Fri Dec  4 10:26:20 2020 - [info] 
Fri Dec  4 10:26:20 2020 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Fri Dec  4 10:26:20 2020 - [info] 
Fri Dec  4 10:26:20 2020 - [info] -- Slave diff file generation on host 192.168.232.44(192.168.232.44:3308) started, pid: 4477. Check tmp log /mha/192.168.232.44_3308_20201204102559.log if it takes time..
Fri Dec  4 10:26:21 2020 - [info] 
Fri Dec  4 10:26:21 2020 - [info] Log messages from 192.168.232.44 ...
Fri Dec  4 10:26:21 2020 - [info] 
Fri Dec  4 10:26:20 2020 - [info]  This server has all relay logs. No need to generate diff files from the latest slave.
Fri Dec  4 10:26:21 2020 - [info] End of log messages from 192.168.232.44.
Fri Dec  4 10:26:21 2020 - [info] -- 192.168.232.44(192.168.232.44:3308) has the latest relay log events.
Fri Dec  4 10:26:21 2020 - [info] Generating relay diff files from the latest slave succeeded.
Fri Dec  4 10:26:21 2020 - [info] 
Fri Dec  4 10:26:21 2020 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Fri Dec  4 10:26:21 2020 - [info] 
Fri Dec  4 10:26:21 2020 - [info] -- Slave recovery on host 192.168.232.44(192.168.232.44:3308) started, pid: 4479. Check tmp log /mha/192.168.232.44_3308_20201204102559.log if it takes time..
saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog                 100%  177   126.2KB/s   00:00    
Fri Dec  4 10:26:23 2020 - [info] 
Fri Dec  4 10:26:23 2020 - [info] Log messages from 192.168.232.44 ...
Fri Dec  4 10:26:23 2020 - [info] 
Fri Dec  4 10:26:21 2020 - [info] Sending binlog..
Fri Dec  4 10:26:22 2020 - [info] scp from local:/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog to root@192.168.232.44:/mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog succeeded.
Fri Dec  4 10:26:22 2020 - [info] Starting recovery on 192.168.232.44(192.168.232.44:3308)..
Fri Dec  4 10:26:22 2020 - [info]  Generating diffs succeeded.
Fri Dec  4 10:26:22 2020 - [info] Waiting until all relay logs are applied.
Fri Dec  4 10:26:22 2020 - [info]  done.
Fri Dec  4 10:26:22 2020 - [info] Getting slave status..
Fri Dec  4 10:26:22 2020 - [info] This slave(192.168.232.44)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000005:154). No need to recover from Exec_Master_Log_Pos.
Fri Dec  4 10:26:22 2020 - [info] Connecting to the target slave host 192.168.232.44, running recover script..
Fri Dec  4 10:26:22 2020 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user='mhaadmin' --slave_host=192.168.232.44 --slave_ip=192.168.232.44  --slave_port=3308 --apply_files=/mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog --workdir=/mysql/mha --target_version=5.7.22-log --timestamp=20201204102559 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.58 --slave_pass=xxx
Fri Dec  4 10:26:22 2020 - [info] 
MySQL client version is 5.7.22. Using --binary-mode.
Applying differential binary/relay log files /mysql/mha/saved_master_binlog_from_192.168.232.43_3308_20201204102559.binlog on 192.168.232.44:3308. This may take long time...
Applying log files succeeded.
Fri Dec  4 10:26:22 2020 - [info]  All relay logs were successfully applied.
Fri Dec  4 10:26:22 2020 - [info]  Resetting slave 192.168.232.44(192.168.232.44:3308) and starting replication from the new master 192.168.232.42(192.168.232.42:3308)..
Fri Dec  4 10:26:22 2020 - [info]  Executed CHANGE MASTER.
Fri Dec  4 10:26:22 2020 - [info]  Slave started.
Fri Dec  4 10:26:23 2020 - [info] End of log messages from 192.168.232.44.
Fri Dec  4 10:26:23 2020 - [info] -- Slave recovery on host 192.168.232.44(192.168.232.44:3308) succeeded.
Fri Dec  4 10:26:23 2020 - [info] All new slave servers recovered successfully.
Fri Dec  4 10:26:23 2020 - [info] 
Fri Dec  4 10:26:23 2020 - [info] * Phase 5: New master cleanup phase..
Fri Dec  4 10:26:23 2020 - [info] 
Fri Dec  4 10:26:23 2020 - [info] Resetting slave info on the new master..
Fri Dec  4 10:26:23 2020 - [info]  192.168.232.42: Resetting slave info succeeded.
Fri Dec  4 10:26:23 2020 - [info] Master failover to 192.168.232.42(192.168.232.42:3308) completed successfully.
Fri Dec  4 10:26:23 2020 - [info] 


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


mha: MySQL Master failover 192.168.232.43(192.168.232.43:3308) to 192.168.232.42(192.168.232.42:3308) succeeded


Master 192.168.232.43(192.168.232.43:3308) is down!


Check MHA Manager logs at manager for details.


Started manual(interactive) failover.
The latest slave 192.168.232.42(192.168.232.42:3308) has all relay logs for recovery.
Selected 192.168.232.42(192.168.232.42:3308) as a new master.
192.168.232.42(192.168.232.42:3308): OK: Applying all logs succeeded.
192.168.232.44(192.168.232.44:3308): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.232.44(192.168.232.44:3308): OK: Applying all logs succeeded. Slave started, replicating from 192.168.232.42(192.168.232.42:3308)
192.168.232.42(192.168.232.42:3308): Resetting slave info succeeded.
Master failover to 192.168.232.42(192.168.232.42:3308) completed successfully.

注：

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

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

（4）查看新主和slave节点

--1、node1节点上查看
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 154
     Binlog_Do_DB: customer
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)


mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3308 |         1 | 0b8c9b28-3143-11eb-b7bf-000c2927b3e8 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)


--2、node3节点上查看
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.42
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys

注：通过主从的状态发现已经手动故障成功，新的主变成了node1，有一个slave节点是node3。

3、手动在线灾备切换（主库正常）

注：通过第二步的切换，现在主节点已经变成了node1，同样的方式把node2重新添加到现在的主从复制中。

（1）查看mha监控的状态（mha要是stopped状态）

[root@manager mha]# masterha_check_status --conf=/etc/mha_master/mha.cnf                              
 mha is stopped(2:NOT_RUNNING).

如果mha是running状态的话，切换会报错，如下：

Fri Dec  4 10:45:31 2020 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Fri Dec  4 10:45:31 2020 - [info]  ok.
Fri Dec  4 10:45:31 2020 - [info] Checking MHA is not monitoring or doing failover..
Fri Dec  4 10:45:31 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterRotate.pm, ln143] Getting advisory lock failed on the current master. MHA Monitor runs on the current master. Stop MHA Manager/Monitor and try again.
Fri Dec  4 10:45:31 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/bin/masterha_master_switch line 53.

（2）手动在线切换
[root@manager mha]# masterha_master_switch --conf=/etc/mha_master/mha.cnf --master_state=alive --new_master_host=192.168.232.43 --new_master_port=3308 --orig_master_is_new_slave --running_updates_limit=100


Fri Dec  4 10:50:57 2020 - [info] MHA::MasterRotate version 0.58.
Fri Dec  4 10:50:57 2020 - [info] Starting online master switch..
Fri Dec  4 10:50:57 2020 - [info] 
Fri Dec  4 10:50:57 2020 - [info] * Phase 1: Configuration Check Phase..
Fri Dec  4 10:50:57 2020 - [info] 
Fri Dec  4 10:50:57 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Dec  4 10:50:57 2020 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Fri Dec  4 10:50:57 2020 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Fri Dec  4 10:50:58 2020 - [info] GTID failover mode = 0
Fri Dec  4 10:50:58 2020 - [info] Current Alive Master: 192.168.232.42(192.168.232.42:3308)
Fri Dec  4 10:50:58 2020 - [info] Alive Slaves:
Fri Dec  4 10:50:58 2020 - [info]   192.168.232.43(192.168.232.43:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:50:58 2020 - [info]     Replicating from 192.168.232.42(192.168.232.42:3308)
Fri Dec  4 10:50:58 2020 - [info]     Primary candidate for the new Master (candidate_master is set)
Fri Dec  4 10:50:58 2020 - [info]   192.168.232.44(192.168.232.44:3308)  Version=5.7.22-log (oldest major version between slaves) log-bin:enabled
Fri Dec  4 10:50:58 2020 - [info]     Replicating from 192.168.232.42(192.168.232.42:3308)
Fri Dec  4 10:50:58 2020 - [info]     Primary candidate for the new Master (candidate_master is set)


It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.232.42(192.168.232.42:3308)? (YES/no): yes
Fri Dec  4 10:51:00 2020 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Fri Dec  4 10:51:00 2020 - [info]  ok.
Fri Dec  4 10:51:00 2020 - [info] Checking MHA is not monitoring or doing failover..
Fri Dec  4 10:51:00 2020 - [info] Checking replication health on 192.168.232.43..
Fri Dec  4 10:51:00 2020 - [info]  ok.
Fri Dec  4 10:51:00 2020 - [info] Checking replication health on 192.168.232.44..
Fri Dec  4 10:51:00 2020 - [info]  ok.
Fri Dec  4 10:51:00 2020 - [info] 192.168.232.43 can be new master.
Fri Dec  4 10:51:00 2020 - [info] 
From:
192.168.232.42(192.168.232.42:3308) (current master)
 +--192.168.232.43(192.168.232.43:3308)
 +--192.168.232.44(192.168.232.44:3308)


To:
192.168.232.43(192.168.232.43:3308) (new master)
 +--192.168.232.44(192.168.232.44:3308)
 +--192.168.232.42(192.168.232.42:3308)


Starting master switch from 192.168.232.42(192.168.232.42:3308) to 192.168.232.43(192.168.232.43:3308)? (yes/NO): yes
Fri Dec  4 10:51:08 2020 - [info] Checking whether 192.168.232.43(192.168.232.43:3308) is ok for the new master..
Fri Dec  4 10:51:08 2020 - [info]  ok.
Fri Dec  4 10:51:08 2020 - [info] 192.168.232.42(192.168.232.42:3308): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Fri Dec  4 10:51:08 2020 - [info] 192.168.232.42(192.168.232.42:3308): Resetting slave pointing to the dummy host.
Fri Dec  4 10:51:08 2020 - [info] ** Phase 1: Configuration Check Phase completed.
Fri Dec  4 10:51:08 2020 - [info] 
Fri Dec  4 10:51:08 2020 - [info] * Phase 2: Rejecting updates Phase..
Fri Dec  4 10:51:08 2020 - [info] 
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Fri Dec  4 10:51:14 2020 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Fri Dec  4 10:51:14 2020 - [info] Executing FLUSH TABLES WITH READ LOCK..
Fri Dec  4 10:51:14 2020 - [info]  ok.
Fri Dec  4 10:51:14 2020 - [info] Orig master binlog:pos is mysql-bin.000006:154.
Fri Dec  4 10:51:14 2020 - [info]  Waiting to execute all relay logs on 192.168.232.43(192.168.232.43:3308)..
Fri Dec  4 10:51:14 2020 - [info]  master_pos_wait(mysql-bin.000006:154) completed on 192.168.232.43(192.168.232.43:3308). Executed 0 events.
Fri Dec  4 10:51:14 2020 - [info]   done.
Fri Dec  4 10:51:14 2020 - [info] Getting new master's binlog name and position..
Fri Dec  4 10:51:14 2020 - [info]  mysql-bin.000007:154
Fri Dec  4 10:51:14 2020 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.232.43', MASTER_PORT=3308, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154, MASTER_USER='repl_user1', MASTER_PASSWORD='xxx';
Fri Dec  4 10:51:14 2020 - [info] 
Fri Dec  4 10:51:14 2020 - [info] * Switching slaves in parallel..
Fri Dec  4 10:51:14 2020 - [info] 
Fri Dec  4 10:51:14 2020 - [info] -- Slave switch on host 192.168.232.44(192.168.232.44:3308) started, pid: 5263
Fri Dec  4 10:51:14 2020 - [info] 
Fri Dec  4 10:51:15 2020 - [info] Log messages from 192.168.232.44 ...
Fri Dec  4 10:51:15 2020 - [info] 
Fri Dec  4 10:51:14 2020 - [info]  Waiting to execute all relay logs on 192.168.232.44(192.168.232.44:3308)..
Fri Dec  4 10:51:14 2020 - [info]  master_pos_wait(mysql-bin.000006:154) completed on 192.168.232.44(192.168.232.44:3308). Executed 0 events.
Fri Dec  4 10:51:14 2020 - [info]   done.
Fri Dec  4 10:51:14 2020 - [info]  Resetting slave 192.168.232.44(192.168.232.44:3308) and starting replication from the new master 192.168.232.43(192.168.232.43:3308)..
Fri Dec  4 10:51:14 2020 - [info]  Executed CHANGE MASTER.
Fri Dec  4 10:51:14 2020 - [info]  Slave started.
Fri Dec  4 10:51:15 2020 - [info] End of log messages from 192.168.232.44 ...
Fri Dec  4 10:51:15 2020 - [info] 
Fri Dec  4 10:51:15 2020 - [info] -- Slave switch on host 192.168.232.44(192.168.232.44:3308) succeeded.
Fri Dec  4 10:51:15 2020 - [info] Unlocking all tables on the orig master:
Fri Dec  4 10:51:15 2020 - [info] Executing UNLOCK TABLES..
Fri Dec  4 10:51:15 2020 - [info]  ok.
Fri Dec  4 10:51:15 2020 - [info] Starting orig master as a new slave..
Fri Dec  4 10:51:15 2020 - [info]  Resetting slave 192.168.232.42(192.168.232.42:3308) and starting replication from the new master 192.168.232.43(192.168.232.43:3308)..
Fri Dec  4 10:51:15 2020 - [info]  Executed CHANGE MASTER.
Fri Dec  4 10:51:15 2020 - [info]  Slave started.
Fri Dec  4 10:51:15 2020 - [info] All new slave servers switched successfully.
Fri Dec  4 10:51:15 2020 - [info] 
Fri Dec  4 10:51:15 2020 - [info] * Phase 5: New master cleanup phase..
Fri Dec  4 10:51:15 2020 - [info] 
Fri Dec  4 10:51:15 2020 - [info]  192.168.232.43: Resetting slave info succeeded.
Fri Dec  4 10:51:15 2020 - [info] Switching master to 192.168.232.43(192.168.232.43:3308) completed successfully.



（3）查看新主和slave节点

--1、查看主节点（node2）的状态
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000007
         Position: 154
     Binlog_Do_DB: customer
 Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)


mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         1 |      | 3308 |         2 | 5eb9dc85-2d68-11eb-acf4-000c29cddf72 |
|         3 |      | 3308 |         2 | 0b8c9b28-3143-11eb-b7bf-000c2927b3e8 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)


--2、查看slave1（node1）节点的状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.43
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys


--3、查看slave2（node3）节点的状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.232.43
                  Master_User: repl_user1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: customer
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys

在线转移成功！！！

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢！

mysql

文章转载自DataBase干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL基于MHA的高可用架构的搭建

一、MHA架构的介绍

二、MHA架构的搭建

三、MHA故障转移测试

评论