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

目录

自动failover

MHA：

MHA 服务

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

实验项目IP地址配置：

MHA下载地址

项目步骤： 

一、修改主机名

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

7.7、manager节点后台开启MHA

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

8.1、模拟master宕机，停掉master

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

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

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

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

9.1、原master开启mysqld

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

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

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

自动failover

自动故障切换（Automatic Failover）是一种系统设计和配置策略，旨在在出现故障时自动将服务从一个失败的节点转移到另一个健康的节点，以保持系统的可用性。自动故障切换通常用于分布式系统、数据库集群、高可用性架构等场景，以减少系统停机时间并确保业务连续性。

MHA：

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

查看日志信息

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

slave2也已经选择slave1作为master

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

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

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

九、原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 

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就算搭建成功了！文章来源地址https://www.toymoban.com/news/detail-648170.html

到了这里，关于Mysql 搭建MHA高可用架构，实现自动failover，完成主从切换的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！