MHA 搭建部署
MHA（Master High Availability）是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，能在最大程度上保证数据的一致性，且整个故障转移过程对应用程序完全透明。
1.准备工作
MHA由两部分组成：MHA Manager(管理节点)和MHA Node(数据节点,运行在每台mysql服务器上)
搭建MHA要求最少三台数据库服务器，一主二从，即一台充当master、一台充当备用master、另外一台充当从库，然后把Manager装在第3台从库上
MHA Manager会定时探测master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master
- MHA 切换过程：
1.从宕机崩溃的master保存二进制日志事件(binlog events)
2.识别含有最新更新的slave
3.应用差异的中继日志(relay log)到其他的slave
4.应用从master保存的二进制日志事件(binlog events)
5.提升一个slave为新的master
6.使其他的slave连接新的master进行复制
- 机器规划：
| ip地址 | 主机名 | ssh port | OS版本 | MySQL版本 | sql port | server-id | 备注 |
|---|---|---|---|---|---|---|---|
| 192.168.31.101 | mysql101 | 23245 | Centos 7.9 x64 | 5.7.44 | 3306 | 311013306 | Master，读+写 |
| 192.168.31.102 | mysql102 | 23245 | Centos 7.9 x64 | 5.7.44 | 3306 | 311023306 | Slave1，备选master，只读 |
| 192.168.31.103 | mysql103 | 23245 | Centos 7.9 x64 | 5.7.44 | 3306 | 311033306 | Slave2 + Manager，只读 |
- [所有节点]：初始化工作
#.配置DNS和时区
cat /etc/resolv.conf | grep "^nameserver" || echo "nameserver 223.5.5.5" > /etc/resolv.conf
timedatectl set-timezone Asia/Shanghai
yum install -y -q ntpdate
/usr/sbin/ntpdate -u ntp.aliyun.com
#.配置阿里云yum源
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.$(date +%Y%m%d%H%M%S)
curl -sL http://mirrors.aliyun.com/repo/Centos-7.repo -o /etc/yum.repos.d/CentOS-Base.repo
yum makecache
#.安装基础软件
yum install -y -q wget ntpdate lrzsz telnet zip unzip net-tools make
#.关闭iptables及防火墙
iptables -F
systemctl stop firewalld.service
systemctl disable firewalld.service
#.更改机器名
newhost=`ifconfig -a|grep inet|grep -v 127.0.0.1|grep -v inet6|awk '{print $2}'|tail -1|awk -F'.' '{print "mysql"$4}'`
hostnamectl set-hostname $newhost
#.关闭SELinux
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
setenforce 0
#.更改ssh默认端口为23245
sed -i "s/#Port/Port/" /etc/ssh/sshd_config
sed -i '/^Port/c Port 23245' /etc/ssh/sshd_config
grep "^Port" /etc/ssh/sshd_config
systemctl restart sshd.service
#.退出重连(更换了ssh端口)
exit
- [所有节点]：所有节点之间的免密登录（包括本机to本机）
#.使用 ssh-keygen 生成ssh免密登录公私钥（一路回车即可）
cat ~/.ssh/id_rsa.pub | grep ssh-rsa || ssh-keygen -t rsa
#.将公钥 ~/.ssh/id_rsa.pub 上传到目标服务器，实现免密登录
#.ssh-copy-id的时候也要自己to自己,否则会报错
ssh-copy-id -p 23245 root@192.168.31.101
ssh-copy-id -p 23245 root@192.168.31.102
ssh-copy-id -p 23245 root@192.168.31.103
#.测试一下
ssh -p23245 root@192.168.31.102
2.安装 mysql 5.7
- [所有节点]：安装 MySQL 5.7.44
#.开始安装
cd /opt
wget -c http://iso.sqlfans.cn/mysql/percona-server-5.7.44-48.tar.gz
wget -c http://iso.sqlfans.cn/mysql/config/5.7/my_innodb.cnf
wget -c http://iso.sqlfans.cn/mysql/install_percona_5744.sh
bash install_percona_5744.sh /opt /data 3306
#.登录测试
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"select host,user,plugin,account_locked from mysql.user;"
- [所有节点]：确认已启用 GTID 及 半同步复制，确认 gtid_mode、rpl_semi_sync_master_enabled、rpl_semi_sync_slave_enabled 均为 on
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"show variables like 'gtid_mode';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock -e"show variables like 'rpl_semi_sync_%_enabled';"
3.搭建 mysql 主从复制
- [任一节点]：配置mysql主从复制
#.定义节点信息
workdir=/data
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
sql_port=3306
#.1.[任一节点]-在主库上创建复制专用账户
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"DROP USER IF EXISTS 'dba_repl'@'%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"CREATE USER IF NOT EXISTS 'dba_repl'@'%' IDENTIFIED with mysql_native_password BY 'MA6RuouuZZn4x_Hd';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"GRANT SELECT,RELOAD,FILE,SUPER,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'dba_repl'@'%';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"FLUSH PRIVILEGES;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"select user,host,plugin,account_locked from mysql.user;"
#.2.[任一节点]-备份mysql主库的所有数据库
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port -e"CREATE DATABASE IF NOT EXISTS xxx_001;"
mysqldump --single-transaction --master-data=2 --flush-logs --set-gtid-purged=on --triggers --events --routines --skip-tz-utc -udba_admin -pcf_rB1NKCzbaQuPH -h $node1 -P $sql_port --all-databases > ${workdir}/alldb.2025.sql
#.3.[任一节点]-针对从库1-启动主从
#.注意mysql 5.7.44的 SET @@GLOBAL.GTID_PURGED 在文件的结尾
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "reset master;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port < ${workdir}/alldb.2025.sql
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "show databases;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"STOP SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"RESET SLAVE ALL;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"RESET MASTER;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"`tail -n 50 ${workdir}/alldb.2025.sql | grep GTID_PURGED`"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"CHANGE MASTER TO MASTER_HOST='${node1}',MASTER_PORT=${sql_port},MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"START SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
#.4.[任一节点]-针对从库2-启动主从
#.注意mysql 5.7.44的 SET @@GLOBAL.GTID_PURGED 在文件的结尾
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "reset master;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port < ${workdir}/alldb.2025.sql
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "show databases;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"STOP SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"RESET SLAVE ALL;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"RESET MASTER;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"`tail -n 50 ${workdir}/alldb.2025.sql | grep GTID_PURGED`"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"CHANGE MASTER TO MASTER_HOST='${node1}',MASTER_PORT=${sql_port},MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"START SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
- 注意 Replicate_Do_DB 和 Replicate_Ignore_DB 设置必须相同。MHA在启动时候会检测过滤规则，如果过滤规则不同，MHA不启动监控和故障转移
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e"SHOW SLAVE STATUS \G;" | egrep "(Replicate_Do_DB|Replicate_Ignore_DB)"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e"SHOW SLAVE STATUS \G;" | egrep "(Replicate_Do_DB|Replicate_Ignore_DB)"
4.搭建 mha 集群
- 1.[所有节点]：安装 MHA Node 组件
#.所有节点(包括Manager)安装mha核心包
rpm -q epel-release || yum -y install epel-release
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-MakeMaker perl-CPAN
#.所有节点(包括Manager)安装mha node组件
cd /opt/
wget -c http://iso.sqlfans.cn/mysql/mha/mha57.zip
unzip mha57.zip
cd mha
rpm -q python-ecdsa || rpm -Uvh python-ecdsa-0.13-1.noarch.rpm
rpm -q mha4mysql-node || rpm -Uvh mha4mysql-node-0.57-0.el7.noarch.rpm
- 2.[节点2+节点3]：为2台从库设置 relay log 定期清理
#.定义节点信息
workdir=/data
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
sql_port=3306
#.为2台slave设置read_only，之所以没有写进配置文件，是因为slave随时会提升为master
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "set global read_only=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "set global read_only=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "show variables like 'read_only';"
#.为2台slave设置relay log清理方式。默认情况下，从库的中继日志会在SQL线程执行完毕后自动删除
#.但MHA在发生切换的过程中，从库的恢复过程依赖于relay log的相关信息，因此需要禁用中继日志的自动删除功能，而采用手动清除relay log的方式
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "set global relay_log_purge=0;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "set global relay_log_purge=0;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "show variables like 'relay_log_purge';"
#.创建清理relay log的脚本文件，并添加到crontab定期执行
rm -f /opt/mha_purge_relay_log.sh
cat > /opt/mha_purge_relay_log.sh <<EOF
#!/bin/bash
/usr/bin/purge_relay_logs --user=dba_admin --password=cf_rB1NKCzbaQuPH --port=$sql_port --disable_relay_log_purge --workdir=/data >> /opt/mha_purge_relay_log.log 2>&1
EOF
chmod u+x /opt/mha_purge_relay_log.sh
crontab -l | grep mha_purge_relay_log || echo "* 5 * * * /bin/bash /opt/mha_purge_relay_log.sh" >> /var/spool/cron/`whoami`
#.测试一下：执行purge_relay_logs删除中继日志不会阻塞SQL线程
sh /opt/mha_purge_relay_log.sh
ll /data/mysql_$sql_port/binlog
#.确保mysqlbinlog版本及路径
mysqlbinlog --version
type mysqlbinlog
- 3.[节点3]：安装 MHA Manager 组件
#.只在Manager节点上安装mha manager(注意有依赖,安装按顺序)
cd /opt/mha
rpm -q mha4mysql-manager || rpm -Uvh mha4mysql-manager-0.57-0.el7.noarch.rpm
dbzr=`rpm -qa | grep ^bzr`
dparamiko=`rpm -qa | grep python-paramiko`
dcrypto=`rpm -qa | grep python-crypto`
rpm -e $dbzr > /dev/null 2>&1
rpm -e $dparamiko > /dev/null 2>&1
rpm -e $dcrypto > /dev/null 2>&1
rpm -Uvh python-pycrypto-2.6.1-1.x86_64.rpm
rpm -Uvh python-paramiko-1.12.4-1.x86_64.rpm
rpm -Uvh python-pymysql-0.6.7-1.noarch.rpm
rpm -Uvh python-mha_helper-0.4.2-1.noarch.rpm
yum install -y bzr
- 4.[节点3]：修改 MHA 配置文件
#.1.定义节点信息
node1=192.168.31.101
node2=192.168.31.102
node3=192.168.31.103
mha_vip=192.168.31.201
net_name=ens33
sql_port=3306
my_ssh_user=root
my_ssh_port=23245
my_user=dba_admin
my_password=cf_rB1NKCzbaQuPH
my_repl_user=dba_repl
my_repl_password=MA6RuouuZZn4x_Hd
#.2.创建工作目录
mkdir -p /etc/mha
mkdir -p /etc/mha-helper
mkdir -p /var/log/mha
#.3.调整配置文件 mha-helper.conf
cat > /etc/mha-helper/mha-helper.conf <<EOF
[default]
requires_sudo=yes
requires_arping=yes
vip_type=metal
writer_vip_cidr=$mha_vip/24
super_read_only=no
report_email=78667417@qq.com
smtp_host=smtp.qq.com
kill_after_timeout=5
cluster_interface=$net_name
[$node1]
cluster_interface=$net_name
[$node2]
cluster_interface=$net_name
[$node3]
cluster_interface=$net_name
EOF
#.确认内容
cat /etc/mha-helper/mha-helper.conf | grep -Ev '^$|#'
#.4.调整配置文件 masterha_default.cnf
cat > /etc/mha/masterha_default.cnf <<EOF
[server default]
user=$my_user
password=$my_password
repl_user=$my_repl_user
repl_password=$my_repl_password
ssh_user=$my_ssh_user
ssh_port=$my_ssh_port
manager_workdir=/var/log/mha/
manager_log=/var/log/mha/cluster.log
remote_workdir=/var/log/mha/
master_binlog_dir=/data/mysql_$sql_port/binlog
master_ip_failover_script=/usr/bin/master_ip_hard_failover_helper
master_ip_online_change_script=/usr/bin/master_ip_online_failover_helper
report_script=/usr/bin/master_failover_report
secondary_check_script=masterha_secondary_check -s $node2 -s $node3 --user=$my_user --master_ip=$node1 --Master_Port=$sql_port
[server1]
hostname=$node1
port=$sql_port
candidate_master=1
[server2]
hostname=$node2
port=$sql_port
candidate_master=1
[server3]
hostname=$node3
port=$sql_port
no_master=1
[binlog1]
hostname=$node1
EOF
#.确认内容
cat /etc/mha/masterha_default.cnf | grep -Ev '^$|#'
#.5.调整配置文件(修改ssh_port否则vip不会漂移).
sed -i "s/ssh_port = 22/ssh_port = $my_ssh_port/g" /usr/bin/masterha_secondary_check
cat /usr/bin/masterha_secondary_check | grep "^\$ssh_port"
#.6.再次确认2个slave节点relay_log_purge为off及read_only为on
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "show variables like 'relay_log_purge';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "show variables like 'relay_log_purge';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node2 -P $sql_port -e "show variables like 'read_only';"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h $node3 -P $sql_port -e "show variables like 'read_only';"
- 5.[节点3]：执行三项MHA检查
#.1.检查Manger到所有Node节点的SSH连接状态，正常返回 All SSH connection tests passed successfully.
/usr/bin/masterha_check_ssh --conf=/etc/mha/masterha_default.cnf
#.2.主库启动vip后，查看整个集群的状态，正常返回 MySQL Replication Health is OK.
#.如果执行 masterha_check_repl 报错 The VIP was not found on host 192.168.31.101 则参考如下2行注释的命令：
# ssh root@192.168.31.101 -p23245 "/sbin/ip addr add 192.168.31.201/24 dev ens33"
# ssh root@192.168.31.101 -p23245 "/sbin/ip addr | grep 192"
/usr/bin/masterha_check_repl --conf=/etc/mha/masterha_default.cnf
#.3.创建manager启动脚本(不然会遇到bug)
rm -f /var/log/mha/start_monitor.sh
cat > /var/log/mha/start_monitor.sh <<EOF
#!/bin/bash
/usr/bin/nohup /usr/bin/masterha_manager --conf=/etc/mha/masterha_default.cnf > /var/log/mha/mha_manager.log </dev/null 2>&1 &
EOF
chmod u+x /var/log/mha/start_monitor.sh
#.4.启动manager进程
sh /var/log/mha/start_monitor.sh
#.5.查看manager状态，正常返回 masterha_default is running(0:PING_OK), master:192.168.31.101
/usr/bin/masterha_check_status --conf=/etc/mha/masterha_default.cnf
- 6.[任一节点]：通过 vip 测试访问 mysql 实例
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.201 -P 3306 -e "show databases;"
5.灾难演练
5.1.手动切换
- 1.[任一节点]：ping vip 地址，实测即使发生 failover 也不会有丢包，如下所示：
[root@mysql102 mha]# ping 192.168.31.201
64 bytes from 192.168.31.201: icmp_seq=93 ttl=64 time=0.428 ms
64 bytes from 192.168.31.201: icmp_seq=94 ttl=64 time=0.391 ms
64 bytes from 192.168.31.201: icmp_seq=95 ttl=64 time=1.49 ms
64 bytes from 192.168.31.201: icmp_seq=96 ttl=64 time=1.91 ms
64 bytes from 192.168.31.201: icmp_seq=97 ttl=64 time=1.57 ms
- 2.[Manager节点]：停止与failover后，候选master 即 node2 变为主库
[root@mysql103 ~]# /usr/bin/masterha_stop --conf=/etc/mha/masterha_default.cnf
Stopped masterha_default successfully.
[root@mysql103 ~]# /usr/bin/mysql_online_failover -c /etc/mha/masterha_default.cnf
......
Thu Oct 16 15:55:33 2025 - [info] 192.168.31.102: Resetting slave info succeeded.
Thu Oct 16 15:55:33 2025 - [info] Switching master to 192.168.31.102(192.168.31.102:3306) completed successfully.
- 3.[节点2]：确认新的主节点已监听vip
[root@mysql101 mha]# ip a | grep 192
inet 192.168.31.101/24 brd 192.168.31.255 scope global noprefixroute ens33
[root@mysql102 mha]# ip a | grep 192
inet 192.168.31.102/24 brd 192.168.31.255 scope global noprefixroute ens33
inet 192.168.31.201/24 scope global secondary ens33
- 4.[Manager节点]：发生切换之后，务必先修改 masterha_default.cnf（调整master_ip地址及server1/server2/binlog1的顺序），再启动manager进程。
#.检查 masterha_check_repl 报错 Binlog setting check failed!
[root@mysql103 ~]# /usr/bin/masterha_check_repl --conf=/etc/mha/masterha_default.cnf
......
Thu Oct 16 15:56:33 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln161] Binlog setting check failed!
Thu Oct 16 15:56:33 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln267] Binlog server configuration failed.
Thu Oct 16 15:56:33 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Thu Oct 16 15:56:33 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Thu Oct 16 15:56:33 2025 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
#.调整master_ip地址及server1/server2/binlog1的顺序
master_old=192.168.31.101
master_new=192.168.31.102
master_tmp=127.0.0.1
sed -i "s/$master_old/$master_tmp/" /etc/mha/masterha_default.cnf
sed -i "s/$master_new/$master_old/" /etc/mha/masterha_default.cnf
sed -i "s/$master_tmp/$master_new/" /etc/mha/masterha_default.cnf
#.修改后如下所示：
[root@mysql103 ~]# cat /etc/mha/masterha_default.cnf | egrep "(check|server|hostname|binlog)"
[server default]
master_binlog_dir=/data/mysql_3306/binlog
secondary_check_script=masterha_secondary_check -s 192.168.31.101 -s 192.168.31.103 --user=dba_admin --master_ip=192.168.31.102 --Master_Port=3306
[server1]
hostname=192.168.31.102
[server2]
hostname=192.168.31.101
[server3]
hostname=192.168.31.103
[binlog1]
hostname=192.168.31.102
- 5.[Manager节点]：执行三项MHA检查，确认 masterha_default is running
#.检查 masterha_check_ssh 成功
[root@mysql103 ~]# /usr/bin/masterha_check_ssh --conf=/etc/mha/masterha_default.cnf
Thu Oct 16 16:27:48 2025 - [info] All SSH connection tests passed successfully.
#.检查 masterha_check_repl 成功
[root@mysql103 ~]# /usr/bin/masterha_check_repl --conf=/etc/mha/masterha_default.cnf
MySQL Replication Health is OK.
#.检查 masterha_check_status 报错 masterha_default is stopped
[root@mysql103 ~]# /usr/bin/masterha_check_status --conf=/etc/mha/masterha_default.cnf
masterha_default is stopped(2:NOT_RUNNING).
#.启动manager进程
[root@mysql103 ~]# sh /var/log/mha/start_monitor.sh
#.再次检查 masterha_check_status 成功
[root@mysql103 ~]# /usr/bin/masterha_check_status --conf=/etc/mha/masterha_default.cnf
masterha_default (pid:15786) is running(0:PING_OK), master:192.168.31.102
- 6.[任一节点]：查看主从同步是否异常
#.执行建库测试
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"create database if not exists xxx_002;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.102 -P 3306 -e"create database if not exists xxx_002;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.201 -P 3306 -e"show databases;"
#.确认主从状态
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"SHOW SLAVE STATUS \G;" | grep "Running:"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.102 -P 3306 -e"SHOW SLAVE STATUS \G;" | grep "Running:"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.103 -P 3306 -e"SHOW SLAVE STATUS \G;" | grep "Running:"
- 7.[Manager节点]：查看启动日志
[root@mysql103 ~]# tail -n20 /var/log/mha/cluster.log | grep failover
masterha_default: MySQL Master failover 192.168.31.101(192.168.31.101:3306) to 192.168.31.102(192.168.31.102:3306) succeeded
Started automated(non-interactive) failover.
Master failover to 192.168.31.102(192.168.31.102:3306) completed successfully.
5.2.模拟主库宕机
- 1.[任一节点]：ping vip 地址，实测即使发生 failover 也不会有丢包，如下所示：
[root@mysql103 mha]# ping 192.168.31.201
64 bytes from 192.168.31.201: icmp_seq=23 ttl=64 time=0.039 ms
64 bytes from 192.168.31.201: icmp_seq=24 ttl=64 time=0.017 ms
64 bytes from 192.168.31.201: icmp_seq=25 ttl=64 time=1.42 ms
64 bytes from 192.168.31.201: icmp_seq=26 ttl=64 time=1.44 ms
64 bytes from 192.168.31.201: icmp_seq=27 ttl=64 time=0.539 ms
64 bytes from 192.168.31.201: icmp_seq=28 ttl=64 time=0.519 ms
64 bytes from 192.168.31.201: icmp_seq=29 ttl=64 time=0.466 ms
- 2.[主节点]：杀掉mysql进程，模拟服务器宕机
[root@mysql101 mha]# mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 shutdown
- 3.[候选master节点]：发现 vip 已从老的主库（即节点1）飘逸到新的主库（即节点2）
[root@mysql101 mha]# ip a | grep 192
inet 192.168.31.101/24 brd 192.168.31.255 scope global noprefixroute ens33
[root@mysql102 mha]# ip a | grep 192
inet 192.168.31.102/24 brd 192.168.31.255 scope global noprefixroute ens33
inet 192.168.31.201/24 scope global secondary ens33
- 4.[Manager节点]：查看启动日志
[root@mysql103 ~]# tail -n20 /var/log/mha/cluster.log | grep failover
masterha_default: MySQL Master failover 192.168.31.101(192.168.31.101:3306) to 192.168.31.102(192.168.31.102:3306) succeeded
Started automated(non-interactive) failover.
Master failover to 192.168.31.102(192.168.31.102:3306) completed successfully.
- 5.[Manager节点]：执行三项MHA检查，确认 masterha_default is running
#.检查 masterha_check_ssh 成功
[root@mysql103 ~]# /usr/bin/masterha_check_ssh --conf=/etc/mha/masterha_default.cnf
Fri Oct 17 10:41:32 2025 - [info] All SSH connection tests passed successfully.
#.检查 masterha_check_repl 报错 There are 2 non-slave servers! MHA manages at most one non-slave server.
[root@mysql103 mha]# /usr/bin/masterha_check_repl --conf=/etc/mha/masterha_default.cnf
......
Fri Oct 17 10:42:32 2025 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln653] There are 2 non-slave servers! MHA manages at most one non-slave server. Check configurations.
Fri Oct 17 10:42:32 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Fri Oct 17 10:42:32 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Fri Oct 17 10:42:32 2025 - [info] Got exit code 1 (Not master dead).
#.确认主从状态，发现节点2为主库、节点3的主从正常，但节点1的主从复制已丢失
[root@mysql103 mha]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
[root@mysql103 mha]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.102 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
[root@mysql103 mha]# mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.103 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#.执行如下语句为节点1重新配置主从同步
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"CHANGE MASTER TO MASTER_HOST='192.168.31.102',MASTER_PORT=3306,MASTER_USER='dba_repl',MASTER_PASSWORD='MA6RuouuZZn4x_Hd',Master_Auto_Position=1;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"START SLAVE;"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P 3306 -e"SHOW SLAVE STATUS \G;" | grep "Running:"
#.再次检查 masterha_check_repl 成功
[root@mysql103 ~]# /usr/bin/masterha_check_repl --conf=/etc/mha/masterha_default.cnf
MySQL Replication Health is OK.
#.再次检查 masterha_check_status 报错 masterha_default is stopped
[root@mysql103 ~]# /usr/bin/masterha_check_status --conf=/etc/mha/masterha_default.cnf
masterha_default is stopped(2:NOT_RUNNING).
#.启动manager进程
[root@mysql103 ~]# sh /var/log/mha/start_monitor.sh
#.再次检查 masterha_check_status 成功
[root@mysql103 ~]# /usr/bin/masterha_check_status --conf=/etc/mha/masterha_default.cnf
masterha_default (pid:18367) is running(0:PING_OK), master:192.168.31.102
6.遇到的问题
- 问题1：在 Centos 7.9 执行 masterha_check_ssh 报错 SSH connection from
root@192.168.31.102(192.168.31.102:23245) toroot@192.168.31.101(192.168.31.101:23245) failed!
在3台机器上都要配置免密登录，并包括本机to本机
- 问题2：在 Centos 7.9 执行 masterha_check_repl 报错 ImportError: No module named mha_helper.config_helper
\cp -r /usr/lib/python2.6/site-packages/* /usr/lib/python2.7/site-packages
- 问题3：在 Centos 7.9 执行 masterha_check_repl 报错 ImportError: No module named Crypto
yum -y install gcc python-devel python-pip
pip install -i https://pypi.doubanio.com/simple/ --trusted-host=pypi.doubanio.com paramiko
- 问题4：在 Centos 7.9 执行 masterha_check_repl 报错 The VIP was not found on host 192.168.31.101
#.手动为节点1添加vip
ssh root@192.168.31.101 -p23245 "/sbin/ip addr add 192.168.31.201/24 dev ens33"
ssh root@192.168.31.101 -p23245 "/sbin/ip addr | grep 192"
#.添加vip：/sbin/ip addr add 192.168.31.201/24 dev ens33
#.删除vip：/sbin/ip addr delete 192.168.31.201/24 dev ens33
- 问题5：在 Centos 7.9 执行 masterha_check_repl 报错 There are 2 non-slave servers! MHA manages at most one non-slave server.
#.确认主从状态，发现有一个从库的主从复制已丢失
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.101 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.102 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"
mysql -udba_admin -pcf_rB1NKCzbaQuPH -h 192.168.31.103 -P $sql_port -e"SHOW SLAVE STATUS \G;" | grep "Running:"