Mysql 搭建MHA高可用架构，实现自动failover，完成主从切换

MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL主从复制架构提供了 automating master failover （自动化主故障转移）功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新数据的 slave 节点成为新的master 节点，在此期间，MHA 会通过于其它从节点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能，即按需切换 master/slave 节点。　

参考：MYSQL高可用架构之MHA实战一数据库主从配置（真实可用）_51CTO博客_mysql数据库主从搭建

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_mb643815027e44d的技术博客_51CTO博客

MHA 服务

MHA 服务有两种角色， MHA Manager(管理节点)和 MHA Node(数据节点)：

MHA Manager： 通常单独部署在一台独立机器上管理多个 master/slave 集群(组)，每个 master/slave 集群称作一个 application，用来管理统筹整个集群。

MHA node： 运行在每台 MySQL 服务器上(master/slave/manager)，它通过监控具备解析和清理

logs 功能的脚本来加快故障转移：主要是接收管理节点所发出指令的代理，代理需要运行在每一个 mysql 节点上。简单讲 node 就是用来收集从节点服务器上所生成的 bin-log 。对比打算提升为新的主节点之上的从节点的是否拥有并完成操作，如果没有发给新主节点在本地应用后提升为主节点。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.7的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的 slave服务器上，因此可以保证所有节点的数据一致性。

由上图我们可以看出，每个复制组内部和 Manager 之间都需要ssh实现无密码互连，只有这样，在 Master 出故障时， Manager 才能顺利的连接进去，实现主从切换功能。
[root@mysql-1 ~]# ps aux|grep mysql
root       3269  0.0  0.1 115536  1688 ?        S    8月12   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
mysql      3506  0.6 27.1 1603328 270316 ?      Sl   8月12   5:28 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_error.log --open-files-limit=8192 --pid-file=/data/mysql/localhost.localdomain.pid --socket=/data/mysql/mysql.sock --port=3306
root      20706  0.0  0.1 115536  1700 ?        S    04:21   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql-1.pid
mysql     20945  1.7 19.2 975992 191756 ?       Sl   04:21   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_error.log --open-files-limit=8192 --pid-file=/data/mysql/mysql-1.pid --socket=/data/mysql/mysql.sock --port=3306
root      21042  0.0  0.1 115408  1648 ?        Ss   04:22   0:00 /bin/sh /etc/rc.d/init.d/mysqld start
root      21047  0.0  0.1 115536  1700 ?        S    04:22   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql-1.pid
mysql     21286  2.2 19.2 975992 191820 ?       Sl   04:22   0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_error.log --open-files-limit=8192 --pid-file=/data/mysql/mysql-1.pid --socket=/data/mysql/mysql.sock --port=3306
root      21340  0.0  0.0 112824   988 pts/1    R+   04:22   0:00 grep --color=auto mysql

项目：搭建Mysql主从复制、MHA高可用架构

实验项目IP地址配置：

mha_manager:

manager：192.168.2.141 #用于监控管理

vip：192.168.2.227

mha_node:

master：192.168.2.150 #开启 bin-log relay-log

slave-1：192.168.2.151 #开启 bin-log relay-log

slave-2：192.168.2.152 #开启 bin-log relay-log

MHA下载地址

mha4mysql-manager-0.58.tar.gz：

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz：

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz

mha4mysql安装包集合

链接：https://pan.baidu.com/s/1cyM1syv8NjwOW8ExR0E21Q?pwd=z52d
提取码：z52d

[root@mha_manager ~]#  ls
anaconda-ks.cfg  mha4mysql-manager-0.58.tar.gz  mha4mysql-node-0.58.tar.gz
[root@mha_manager ~]#

项目步骤：

一、修改主机名

[root@web-3 ~]# hostnamectl set-hostname mha_manager
[root@web-3 ~]# su -
上一次登录：五 8月 11 13:28:25 CST 2023从 192.168.2.7pts/0 上
[root@mha_manager ~]#

二、编写一键安装mha node脚本和一键安装mha mangaer脚本，并执行安装

一键安装mha node脚本

[root@mha_manager ~]# cat onekey_install_mha_node.sh 
#查看可以安装或者已安装的rpm包，并且作缓存
yum list

#下载epel源
yum install epel-release --nogpgcheck -y

#下载依赖包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN

#软件包mha4mysql-node-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58

#编译安装
perl Makefile.PL
make && make install

一键安装mha mangaer脚本

[root@mha_manager ~]# cat onekey_install_mha_manager.sh 
#查看可以安装或者已安装的rpm包，并且作缓存
yum list

#下载epel源
yum install epel-release --nogpgcheck -y

#下载依赖包
yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN

#软件包mha4mysql-manager-0.58.tar.gz放入/root目录下
cd ~
tar zxvf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58

#编译安装
perl Makefile.PL
make && make install

三、搭建Mysql主从复制集群（注意所有的Mysql主从复制机器都需要打开二进制日志，可以实现自动故障切换）

参考：Mysql - 配置Mysql主从复制-keepalived高可用-读写分离集群_Claylpf的博客-CSDN博客

四、将安装包 mha4mysql-node和脚本一键安装mha node脚本传输给Mysql主从复制集群，并运行脚本安装（下载依赖的时候最好翻墙下载）

4.1、mha_manger发送一键安装mha_node的脚本给mysql主从复制集群

[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.150:~
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.150' (ECDSA) to the list of known hosts.
root@192.168.2.150's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   745.0KB/s   00:00    
[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.151:~
The authenticity of host '192.168.2.151 (192.168.2.151)' can't be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.151' (ECDSA) to the list of known hosts.
root@192.168.2.151's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   287.5KB/s   00:00    
[root@mha_manager ~]# scp onekey_install_mha_node.sh root@192.168.2.152:~
The authenticity of host '192.168.2.152 (192.168.2.152)' can't be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.152' (ECDSA) to the list of known hosts.
root@192.168.2.152's password: 
onekey_install_mha_node.sh                                                                                                                                 100%  481   397.8KB/s   00:00    
[root@mha_manager ~]#

4.2、mha_manger上需要配置有mha4mysql-manager-0.58.tar.gz和mha4mysql-node-0.58.tar.gz安装包和对应的一键安装脚本，首先执行一键安装mha4mysql-node-0.58的脚本

[root@mha_manager ~]# bash onekey_install_mha_node.sh

再执行一键安装mha4mysql-manager-0.58的脚本

[root@mha_manager ~]# bash onekey_install_mha_manager.sh

最后输出如下所示表示成功

Appending installation info to /root/perl5/lib/perl5/x86_64-linux-thread-multi/perllocal.pod

4.3、mysql主从复制集群运行一键安装mha4mysql-node-0.58的脚本

[root@mysql-1 ~]# bash onekey_install_mha_node.sh 

[root@mysql-2 ~]# bash onekey_install_mha_node.sh 

[root@mysql-3 ~]# bash onekey_install_mha_node.sh

注：下载依赖包perl的时候如果无法成功下载，可以尝试转换源为：The Comprehensive Perl Archive Network - www.cpan.org ，并翻墙下载

五、所有服务器互相建立免密通道

参考：Linux - SSH服务 - SCP - 免密通道建立_linux ssh服务状态_Claylpf的博客-CSDN博客

5.1、mha manager对所有mysql服务器建立免密通道

[root@mha_manager .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:36631NGvhLwX3HXPFgkfo8t/C0g+k59hqkGi1cn0/cA root@mha_manager
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|             . o |
|         .    + +|
|        + o o. =o|
|       oS= o.Eoo*|
|      o o.ooo==.*|
|     .   ..*=+++.|
|          .oBo=o.|
|         .o++=..o|
+----[SHA256]-----+
[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.150
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.150's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'root@192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.

[root@mha_manager .ssh]# 

[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.151

[root@mha_manager .ssh]# ssh-copy-id root@192.168.2.152

5.2、master对slave1、slave2建立免密通道

[root@mysql-1 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:rB6Rg0nbJCHYxWxuBafl4HDB8+1RuuOpHC9/5LYRTAI root@mysql-1
The key's randomart image is:
+---[RSA 2048]----+
| oo=BoE          |
|. .=*B..  .      |
|   o=+o..o.      |
|   .oB.o++       |
|   .+ =.Soo      |
|       ++ ..     |
|      +. =.      |
|     o.+o +.     |
|      ++oo..     |
+----[SHA256]-----+
[root@mysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.151's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.2.151'"
and check to make sure that only the key(s) you wanted were added.

[root@mysql-1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.152's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.2.152'"
and check to make sure that only the key(s) you wanted were added.

[root@mysql-1 ~]#

5.3、slave1对master、slave2建立免密通道

[root@mysql-2 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:MMCE8STghhwmha65CVG/w3/9k8/T96sFfcr75CFMTGs root@mysql-2
The key's randomart image is:
+---[RSA 2048]----+
|o*+=+            |
|B ++..           |
|o= .. o      .   |
|o.  .  o    o o  |
|.o . .  S    E ..|
|+   +       +....|
|.o   o   .   +o+.|
|o     . . . o.+++|
|       .   ..+=+B|
+----[SHA256]-----+
[root@mysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.150's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.

[root@mysql-2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.152
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.152 (192.168.2.152)' can't be established.
ECDSA key fingerprint is SHA256:t7FSFcUpEOJYIGkZo1HvvfqhsezGEz7WEScc4KTgQDU.
ECDSA key fingerprint is MD5:7c:68:1c:c3:aa:a5:34:b7:f7:4b:18:0b:93:fb:a6:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.152's password: 
Permission denied, please try again.
root@192.168.2.152's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.2.152'"
and check to make sure that only the key(s) you wanted were added.

[root@mysql-2 ~]#

5.4、slave2对master、slave1建立免密通道

[root@mysql-3 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:m6F9WyLFkNnweKy2ERj3LflPDHqU5ZUL+S8FpCbXhtw root@mysql-3
The key's randomart image is:
+---[RSA 2048]----+
|      . o    .+ o|
|       + X + @.o |
|      . * X @ E..|
|         * O + o.|
|        S + o o..|
|       + B . o. .|
|      . * o . .. |
|         o +     |
|          .      |
+----[SHA256]-----+
[root@mysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.150
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.150 (192.168.2.150)' can't be established.
ECDSA key fingerprint is SHA256:rUDllK9IdVfMva40nDGHGyHLkpuXrHJyRHRPuLbkkv8.
ECDSA key fingerprint is MD5:6d:46:aa:d1:48:87:92:8b:14:ca:d2:18:af:3b:89:51.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.150's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.2.150'"
and check to make sure that only the key(s) you wanted were added.

[root@mysql-3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.151
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.2.151 (192.168.2.151)' can't be established.
ECDSA key fingerprint is SHA256:3SsW//YjcK0UTRAlQkOUcqMcFMaQEhZ1xRSUgHRs/JQ.
ECDSA key fingerprint is MD5:58:8e:3f:27:fb:f5:4e:83:56:70:e6:fd:f7:d0:9d:17.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.2.151's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.2.151'"
and check to make sure that only the key(s) you wanted were added.

[root@mysql-3 ~]#

六、在Mysql的主从复制服务器里，配置mha相关信息

6.1、所有mysql服务器（master、slave1、slave2）将mysql命令和mysqlbinlog二进制文件操作命令软链接到/usr/sbin，方便manager管理节点，因为`/usr/sbin/` 目录下可以被直接调用。

[root@mysql-1 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-1 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

[root@mysql-2 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-2 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

[root@mysql-3 ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@mysql-3 ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

6.2、所有mysql服务器新建允许manager访问的授权用户mha，密码123456

root@(none) 17:21  mysql>grant all on *.* to 'mha'@'192.168.2.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@(none) 17:21  mysql>grant all on *.* to 'mha'@'192.168.2.150' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@(none) 17:22  mysql>grant all on *.* to 'mha'@'192.168.2.151' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@(none) 17:22  mysql>grant all on *.* to 'mha'@'192.168.2.152' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@(none) 17:22  mysql>select user,host from mysql.user;
+---------------+---------------+
| user          | host          |
+---------------+---------------+
| claylpf       | %             |
| sc_slave      | %             |
| mha           | 192.168.2.%   |
| mha           | 192.168.2.150 |
| mha           | 192.168.2.151 |
| mha           | 192.168.2.152 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
9 rows in set (0.00 sec)

root@(none) 17:22  mysql>

七、在mha manager节点上配置好相关脚本、管理节点服务器

7.1、mha manager节点上复制相关脚本到/usr/local/bin下

[root@mha_manager ~]# cp -rp /root/mha4mysql-manager-0.58/samples/scripts/ /usr/local/bin/
[root@mha_manager ~]# cd /usr/local/bin/
[root@mha_manager bin]# ls
scripts
[root@mha_manager bin]# 
[root@mha_manager bin]# cd scripts/
[root@mha_manager scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@mha_manager scripts]#

7.2、复制自动切换时vip管理的脚本到/usr/local/bin下

[root@mha_manager scripts]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[root@mha_manager scripts]# ls
master_ip_failover  master_ip_online_change  power_manager  send_report
[root@mha_manager scripts]# cd ..

7.3、修改master_ip_failover文件内容，配置vip（只配置vip(192.168.2.227)相关参数，其他默认不修改）

[root@mha_manager bin]# ls
master_ip_failover  scripts
[root@mha_manager bin]# >/usr/local/bin/master_ip_failover  #清空文件内容，复制以下内容
[root@mha_manager bin]# vim master_ip_failover 
[root@mha_manager bin]# 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.2.227';								#指定vip的地址，自己指定
my $brdc = '192.168.2.255';								#指定vip的广播地址
my $ifdev = 'ens33';										#指定vip绑定的网卡
my $key = '1';												#指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";		#代表此变量值为ifconfig ens33:1 192.168.2.227
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";		#代表此变量值为ifconfig ens33:1 192.168.2.227 down
my $exit_code = 0;											#指定退出状态码为0
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
## 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";
}

[root@mha_manager bin]#

7.4、创建 MHA 软件目录并复制配置文件，使用app1.cnf配置文件来管理 mysql 节点服务器，配置文件一般放在/etc/目录下

注意：注释只是提示用，编辑配置文件时最好不要加注释，否则很可能会出错

[root@mha_manager bin]# mkdir /etc/masterha
[root@mha_manager bin]# cp /root/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[root@mha_manager bin]# cd /etc/masterha/
[root@mha_manager masterha]# ls
app1.cnf
[root@mha_manager masterha]# >app1.cnf #清空原有内容
[root@mha_manager masterha]# vim app1.cnf 

[server default]
manager_log=/var/log/masterha/app1/manager.log　　　　　  #manager日志
manager_workdir=/var/log/masterha/app1.log　　　　		#manager工作目录
master_binlog_dir=/data/mysql/　　　　　　　　 　#master保存binlog的位置，这里的路径要与master里配置的binlog的路径一致，以便MHA能找到
master_ip_failover_script=/usr/local/bin/master_ip_failover　          　#设置自动failover时候的切换脚本，也就是上面的那个脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change　　#设置手动切换时候的切换脚本
user=mha					#设置监控用户mha
password=123456			#设置mysql中mha用户的密码，这个密码是前文中创建监控用户的那个密码
ping_interval=1				#设置监控主库，发送ping包的时间间隔1秒，默认是3秒，尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp			#设置远端mysql在发生切换时binlog的保存位置
repl_user=slave			#设置复制用户的用户slave
repl_password=123456		#设置复制用户slave的密码
report_script=/usr/local/send_report　　　　　#设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152	#指定检查的从服务器IP地址
shutdown_script=""			#设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用）
ssh_user=root				#设置ssh的登录用户名

[server1]
#master
hostname=192.168.2.150 
port=3306

[server2]
#slave1
hostname=192.168.2.151
port=3306
candidate_master=1
#设置为候选master，设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中最新的slave

check_repl_delay=0
#默认情况下如果一个slave落后master 超过100M的relay logs的话，MHA将不会选择该slave作为一个新的master， 因为对于这个slave的恢复需要花费很长时间；通过设置check_repl_delay=0，MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

[server3]
#slave2
hostname=192.168.2.152
port=3306

7.5、master服务器上手工开启vip

[root@mysql-1 ~]# ifconfig ens33:1 192.168.2.227/24
[root@mysql-1 ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:61:50:77 brd ff:ff:ff:ff:ff:ff
    inet 192.168.2.150/24 brd 192.168.2.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.2.227/24 brd 192.168.2.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe61:5077/64 scope link 
       valid_lft forever preferred_lft forever
[root@mysql-1 ~]#

7.6、测试：manager节点上测试ssh免密通道，如果正常最后会输出successfully

[root@mha_manager masterha]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Mon May  8 11:50:00 2023 - [info] All SSH connection tests passed successfully.

注意是否每台mysql间都建立了ssh免密通道，否则会报错

如果报错，思考是否软链接建立好了？或者主从复制搭建正确了

在 manager 节点上测试 mysql 主从连接情况，最后出现 MySQL Replication Health is OK 字样说明正常。

[root@mha_manager masterha]# masterha_check_repl -conf=/etc/masterha/app1.cnf
MySQL Replication Health is OK.

7.7、manager节点后台开启MHA

[root@mha_manager masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 5085

查看 MHA 状态，可以看到当前的 master 是 Mysql1 节点。

[root@mha_manager masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:5085) is running(0:PING_OK), master:192.168.2.150

查看MHA日志，看到当前matser是192.168.2.150

[root@mha_manager masterha]# cat /var/log/masterha/app1/manager.log | grep "current master"
Mon May  14 11:57:07 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.2.150(192.168.2.150:3306) (current master)

查看mha进程

[root@mha_manager bin]# ps aux|grep manager
root       5085  0.1  4.5 299656 21992 pts/0    S    11:57   0:12 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover
root      14939  0.0  0.2 112824   984 pts/0    S+   14:39   0:00 grep --color=auto manager

八、故障转移效果测试，模拟matser宕机，指定slave1成为新的master

manager节点监控日志记录（实时监控）

[root@mha_manager bin]# tail -f /var/log/masterha/app1/manager.log

8.1、模拟master宕机，停掉master

[root@mysql-1 mysql]# service mysqld stop

8.2、查看自动故障检测的效果

查看vip是否漂移到了slave1

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_MySQL_11

查看日志信息

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_MySQL_12

日志显示，master已经切换到了192.168.2.151（slave1）

slave2也已经选择slave1作为master

8.3、查看/etc/masterha/app1.cnf文件是否发生改变

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_mysql_13

发现原来的server1配置被删除了

8.4、再来看看slave2的master_info信息（确定master服务转移到了salve1上）

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_MySQL_14

九、原master故障修复（原master转为slave，指向slave1）

9.1、原master开启mysqld

[root@mysql-1 ~]# service mysqld start

9.2、修复主从，原master修改master_info指向新的master（原slave1）

在mysql-2（slave1）上进行操作

root@(none) 17:18  mysql>change master to master_host='192.168.2.151',master_user='slave',master_password='123456',master_port=3306,master_auto_position=1;
root@(none) 17:19  mysql>start slave;
root@(none) 17:19  mysql>show slave status\G;

9.3、在 manager 节点上修改配置文件/etc/masterha/app1.cnf（再把这个记录添加进去，因为master宕机后原来的server1会被自动删除）

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/data/mysql/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=slave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.2.151 -s 192.168.2.152
shutdown_script=""
ssh_user=root
user=mha

[server1]
hostname=192.168.2.151   #原slave1的IP地址
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.2.150   #原master的IP地址
port=3306

[server3]
hostname=192.168.2.152   #原slave2的IP地址
port=3306

基于mycat2+mha+keepalived的半同步主从复制MySQL cluster_mysql_16

9.4、重启mha manager，并检查此时的master

[root@mha_manager ~]# masterha_stop --conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+  退出 1                nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1
[root@mha_manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & 
[1] 20022
[root@mha_manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:20022) is running(0:PING_OK), master:192.168.2.151

master已经从192.168.2.150切换到了192.168.2.151

并且原来的192.168.2.150（原master）变成了slave1，并从192.168.2.151（原slave1）拿二进制日志了

至此，mha就算搭建成功了！

Claylpf

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
Mysql 搭建MHA高可用架构，实现自动failover，完成主从切换

如果报错，多看日志/var/log/masterha/app1/manager.log注意是否每台mysql间都建立了ssh免密通道，否则会报错如果报错，思考是否软链接建立好了？或者主从复制搭建正确了在 manager 节点上测试 mysql 主从连接情况，最后出现 MySQL Replication Health is OK 字样说明正常。
复制链接

扫一扫