基于CentOS7搭建MHA集群

1. MHA架构

1.1 简要概述

MHA 是一种 MySQL 主从架构高可用方案，由日本 DeNA 公司开发，旨在最大限度地减少主库宕机时的数据丢失与服务中断时间。MHA 通过 自动检测主库的故障，在主库宕机后，自动选择最合适的从库提升为新主库，并在最短时间内恢复复制结构，实现 MySQL 服务的快速恢复。

1.2 MHA主要主键

MHA Manager（管理节点）

部署在一台独立的管理服务器上。
负责监控主库的状态，一旦检测到主库宕机：
- 自动分析各个从库的 binlog；
- 选举出一个最合适的从库作为新主库；
- 自动调整复制关系。

MHA Node（数据节点）

部署在所有 MySQL 主从服务器上。
提供如 保存和应用 binlog 等功能。
Manager 借助 Node 完成主从切换和数据补全操作。

1.3 MHA的主要功能

功能	说明
主库自动故障检测	定期检测主库的存活状态
自动故障转移（Failover）	主库宕机时，自动选择最优从库提升为主库
数据一致性保障	在新主库提升前，从宕机主库中尽可能提取最新的 binlog，减少数据丢失
快速切换	故障切换通常在 10–30 秒内完成
在线主从切换（Switch Over）	可以在维护时手动或自动切换主从角色

1.4 工作原理简述

定期监控主库存活状态

MHA Manager 定时 ping 主库或尝试连接数据库，判断其是否可用。
检测主库故障

当检测到主库不可达时，进入故障切换流程。
确定新主库

MHA 从现有从库中选出 延迟最小、数据最完整 的从库作为新主库。
修复复制结构

让其他从库重新指向新主库，恢复复制。
同步 binlog

如果旧主库还有未同步的 binlog，MHA 会尽可能从中提取并应用到

1.5 原理架构图

1.6 MHA的优势与局限

优势：

自动化主库切换；
切换速度快；
数据丢失最小；
可与传统 MySQL 复制机制兼容；
无需修改应用端（仅需 VIP 或中间件切换）。

局限：

仅适用于主从复制结构（不支持多主）；
需要独立部署管理节点；
故障转移期间仍有短暂中断；
对存储引擎、复制延迟要求较高。

1.7 运用场景

场景类别	应用领域	MHA 作用
企业生产数据库	ERP、CRM 系统	保证主库故障时业务持续运行
电商业务系统	订单、库存库	自动切换主从，减少交易失败
互联网应用	网站、APP 后端	快速恢复数据库服务
金融支付系统	交易、账务数据库	减少数据丢失，保障一致性
大数据分析	日志与报表库	确保主库故障后任务不中断
测试演练环境	DBA 运维演练	验证自动故障切换流程

2. MHA配置过程

2.1 环境准备

角色	主机名	IP 地址	说明
MHA Manager	mha-manager	192.168.4.50	仅安装 MHA Manager
Master	master	192.168.4.51	MySQL 主库
Slave	slave1	192.168.4.52	从库1（要开启binlog日志）
Slave	slave2	192.168.4.53	从库2（要开启binlog日志）

操作系统：CentOS7
MySQL版本：MySQL5.5.17
SSH免密登录：Manager 节点需要能免密 SSH 登录到各 MySQL 节点；各 MySQL 节点间也要能互相免密 SSH

为什么从数据库开启binlog日志？

在 MHA 集群中，从库开启 binlog 是为了在主库故障切换时能顺利晋升为新主库并继续带动其他从库复制。
当主库宕机后，MHA 会从剩余从库中选出同步点最新的一台作为新主库，这时其他从库需要依赖该从库的 binlog 确定新的复制位置。
因此，从库开启 binlog：

保证切换后可作为新主库产生新的 binlog；
让其他从库能从新的主库继续复制；
便于 MHA 对比同步点、补齐数据差异。
如果从库未启用 binlog，即使被晋升为主库，也无法维持后续复制链条。
所以 MHA 官方要求所有节点都必须开启 binlog。

2.2 实战演示

所有主机关闭防火墙SELinux

systemctl stop firewalld
setenforce 0   #临时设置

#永久设置,修改/etc/selinux/config,修改为permissive或者disabled
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

所有主机配置/etc/hosts文件

[root@master ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.4.50 mha-manager
192.168.4.51 master
192.168.4.52 slave1
192.168.4.53 slave2


#所有主机的/etc/hosts文件都配置为这样

192.168.4.50 mha-manager
192.168.4.51 master
192.168.4.52 slave1
192.168.4.53 slave2

SSH免密登录配置

MHA Manager 节点需能 SSH 到所有 MySQL 节点。
每个数据库节点也需能互相 SSH（用于日志比对）

在mha-manager节点执行

[root@mha-manager ~]# ssh-keygen   #交互式生成密钥
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):    
Created directory '/root/.ssh'.
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:wGgUWVr9/RSs87d3pfwysej3nzKyJlwkzAe435Pdyk8 root@mha-manager
The key's randomart image is:
+---[RSA 2048]----+
|    o+o..    .   |
|   ..= ...    o  |
|    + o +... . . |
|   .   o +.o+ .  |
|        S = o=.  |
|         . = .+.o|
|        . . oo.Eo|
|         o o.=O.+|
|          oo+.+BB|
+----[SHA256]-----+

[root@mha-manager ~]# ssh-copy-id [email protected]  #传递公钥
[root@mha-manager ~]# ssh-copy-id [email protected]
[root@mha-manager ~]# ssh-copy-id [email protected]

其他数据库节点也执行以上命令，注意ssh-copy-id后面的IP地址有不同

配置主从复制

master主机配置过程

修改master主机的mysqld的主配置文件（/etc/my.cnf中配置）

[mysqld]
server_id=51
log_bin=master

重启mysqld

[root@master ~]# systemctl restart mysqld

#检查服务是否正常起来,mysqld的端口号为3306
[root@master ~]# ss -antulp | grep 3306
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=11447,fd=32))

登录 MySQL，创建复制账户

mysql> CREATE USER 'lisi'@'%' IDENTIFIED BY '123qqq...A';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'lisi'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%';
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT user, host FROM mysql.user WHERE user = 'lisi';
+------+--------------+
| user | host         |
+------+--------------+
| lisi | %            |
| lisi | 192.168.4.50 |
+------+--------------+
2 rows in set (0.01 sec)

mysql> DROP USER IF EXISTS 'lisi'@'192.168.4.50';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'lisi'@'%';
+-------------------------------------------+
| Grants for lisi@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

从库配置

注意这一步slave1和slave2主机都要配置，配置步骤相同

修改主配置文件/etc/my.cnf

#slave2
[mysqld]
server_id=52   #这里的server_id不能重复
relay-log = relay-log    #设置中继日志,从库接收主库的 binlog（主二进制日志）后，会将其写入本地 
                    中继日志中,然后 SQL 线程从中继日志读取并执行，保持数据一致
relay_log_purge = 0     #中继日志不会被自动清除，方便排查或恢复
read_only = 1            #设置实例为只读模式，禁止普通用户写入数据
expire_logs_days = 7         #设置日志过期时间
binlog_format = row              #binlog日志记录模式
log-bin = mysql-bin                    #开启binlog日志


#slave1
[mysqld]
server_id=53
relay-log = relay-log
relay_log_purge = 0
read_only = 1
expire_logs_days = 7
binlog_format = row
log-bin = mysql-bin

重启服务

[root@slave2 ~]# systemctl restart mysqld

[root@slave1 ~]# systemctl restart mysqld

指定主服务信息

mysql> change master to master_host='192.168.4.51',master_user='lisi',master_password='123qqq...A',master_log_file="master.000001",master_log_pos=437;
 
#两台主机都要执行

开启slave

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

检查slave状态观察Slave_IO_Running和Slave_SQL_Running都为YES

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.51
                  Master_User: lisi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000001
          Read_Master_Log_Pos: 437
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: master.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 437
              Relay_Log_Space: 525
              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: 51
                  Master_UUID: c35d0999-add9-11f0-bde4-000c2906928d
             Master_Info_File: /var/lib/mysql/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)

ERROR: 
No query specified

安装MHA

所有节点安装Node组件

yum install -y epel-release
yum install -y mha4mysql-node perl-DBD-MySQL perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-Tiny

依赖安装错误

执行yum install -y epel-release命令，提示依赖安装失败

[root@slave1 ~]# yum install -y epel-release
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
No package epel-release available.
Error: Nothing to do

我的解决办法是给所有主机配置双网卡，新添加的网卡配置为nat模式，确保能联网

首先安装阿里云镜像

curl -o /etc/yum.repos.d/CentOS-Base-aliyun.repo http://mirrors.aliyun.com/repo/Centos-7.repo

之后直接执行下载操作

MHA Manager 节点安装 Manager 组件

[root@mha-manager ~]# yum install -y mha4mysql-manager

配置MHA

在 Manager 节点创建目录

[root@mha-manager ~]# mkdir -p /etc/mha
[root@mha-manager ~]# mkdir -p /var/log/mha/app1

配置 /etc/mha/app1.cnf

[root@mha-manager ~]# cat /etc/mha/app1.cnf
[server default]
user=mha
password=123qqq...A
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
remote_workdir=/var/log/mha/app1
ssh_user=root
repl_user=lisi
repl_password=123qqq...A
ping_interval=3
master_binlog_dir=/var/lib/mysql
check_repl_delay=0

[server1]
hostname=192.168.4.51
port=3306
candidate_master=1

[server2]
hostname=192.168.4.52
port=3306
candidate_master=1

[server3]
hostname=192.168.4.53
port=3306
no_master=1

创建MHA用户

在每个 MySQL 节点执行：

mysql> CREATE USER 'mha'@'%' IDENTIFIED BY '123qqq...A';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mha'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION CLIENT ON *.* TO 'mha'@'%';
Query OK, 0 rows affected (0.01 sec)

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

检查配置

检查 SSH

[root@mha-manager ~]# vim /etc/mha/app1.cnf
[root@mha-manager ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Tue Oct 21 04:46:33 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 21 04:46:33 2025 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Tue Oct 21 04:46:33 2025 - [info] Reading server configuration from /etc/mha/app1.cnf..
Tue Oct 21 04:46:33 2025 - [info] Starting SSH connection tests..
Tue Oct 21 04:46:34 2025 - [debug] 
Tue Oct 21 04:46:33 2025 - [debug]  Connecting via SSH from [email protected](192.168.4.51:22) to [email protected](192.168.4.52:22)..
Tue Oct 21 04:46:34 2025 - [debug]   ok.
Tue Oct 21 04:46:34 2025 - [debug]  Connecting via SSH from [email protected](192.168.4.51:22) to [email protected](192.168.4.53:22)..
Tue Oct 21 04:46:34 2025 - [debug]   ok.
Tue Oct 21 04:46:35 2025 - [debug] 
Tue Oct 21 04:46:34 2025 - [debug]  Connecting via SSH from [email protected](192.168.4.52:22) to [email protected](192.168.4.51:22)..
Tue Oct 21 04:46:34 2025 - [debug]   ok.
Tue Oct 21 04:46:34 2025 - [debug]  Connecting via SSH from [email protected](192.168.4.52:22) to [email protected](192.168.4.53:22)..
Tue Oct 21 04:46:35 2025 - [debug]   ok.
Tue Oct 21 04:46:36 2025 - [debug] 
Tue Oct 21 04:46:34 2025 - [debug]  Connecting via SSH from [email protected](192.168.4.53:22) to [email protected](192.168.4.51:22)..
Tue Oct 21 04:46:35 2025 - [debug]   ok.
Tue Oct 21 04:46:35 2025 - [debug]  Connecting via SSH from [email protected](192.168.4.53:22) to [email protected](192.168.4.52:22)..
Tue Oct 21 04:46:35 2025 - [debug]   ok.
Tue Oct 21 04:46:36 2025 - [info] All SSH connection tests passed successfully.

检查复制状态

[root@mha-manager ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
Tue Oct 21 06:18:51 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 21 06:18:51 2025 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Tue Oct 21 06:18:51 2025 - [info] Reading server configuration from /etc/mha/app1.cnf..
Tue Oct 21 06:18:51 2025 - [info] MHA::MasterMonitor version 0.58.
Tue Oct 21 06:18:52 2025 - [info] GTID failover mode = 0
Tue Oct 21 06:18:52 2025 - [info] Dead Servers:
Tue Oct 21 06:18:52 2025 - [info] Alive Servers:
Tue Oct 21 06:18:52 2025 - [info]   192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:18:52 2025 - [info]   192.168.4.52(192.168.4.52:3306)
Tue Oct 21 06:18:52 2025 - [info]   192.168.4.53(192.168.4.53:3306)
Tue Oct 21 06:18:52 2025 - [info] Alive Slaves:
Tue Oct 21 06:18:52 2025 - [info]   192.168.4.52(192.168.4.52:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Oct 21 06:18:52 2025 - [info]     Replicating from 192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:18:52 2025 - [info]     Primary candidate for the new Master (candidate_master is set)
Tue Oct 21 06:18:52 2025 - [info]   192.168.4.53(192.168.4.53:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Oct 21 06:18:52 2025 - [info]     Replicating from 192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:18:52 2025 - [info]     Not candidate for the new Master (no_master is set)
Tue Oct 21 06:18:52 2025 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:18:52 2025 - [info] Checking slave configurations..
Tue Oct 21 06:18:52 2025 - [info] Checking replication filtering settings..
Tue Oct 21 06:18:52 2025 - [info]  binlog_do_db= , binlog_ignore_db= 
Tue Oct 21 06:18:52 2025 - [info]  Replication filtering check ok.
Tue Oct 21 06:18:52 2025 - [info] GTID (with auto-pos) is not supported
Tue Oct 21 06:18:52 2025 - [info] Starting SSH connection tests..
Tue Oct 21 06:18:55 2025 - [info] All SSH connection tests passed successfully.
Tue Oct 21 06:18:55 2025 - [info] Checking MHA Node version..
Tue Oct 21 06:18:56 2025 - [info]  Version check ok.
Tue Oct 21 06:18:56 2025 - [info] Checking SSH publickey authentication settings on the current master..
Tue Oct 21 06:18:56 2025 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Tue Oct 21 06:18:57 2025 - [info] Master MHA Node version is 0.58.
Tue Oct 21 06:18:57 2025 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)..
Tue Oct 21 06:18:57 2025 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/var/log/mha/app1/save_binary_logs_test --manager_version=0.58 --start_file=master.000005 
Tue Oct 21 06:18:57 2025 - [info]   Connecting to [email protected](192.168.4.51:22).. 
  Creating /var/log/mha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to master.000005
Tue Oct 21 06:18:57 2025 - [info] Binlog setting check done.
Tue Oct 21 06:18:57 2025 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Oct 21 06:18:57 2025 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.4.52 --slave_ip=192.168.4.52 --slave_port=3306 --workdir=/var/log/mha/app1 --target_version=5.7.17-log --manager_version=0.58 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Tue Oct 21 06:18:57 2025 - [info]   Connecting to [email protected](192.168.4.52:22).. 
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to relay-log.000002
    Temporary relay log file is /var/lib/mysql/relay-log.000002
    Checking if super_read_only is defined and turned on..DBI connect(';host=192.168.4.52;port=3306','mha',...) failed: Access denied for user 'mha'@'slave1' (using password: YES) at /usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 239.
Tue Oct 21 06:18:58 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Tue Oct 21 06:18:58 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Tue Oct 21 06:18:58 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
Tue Oct 21 06:18:58 2025 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Oct 21 06:18:58 2025 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!
[root@mha-manager ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
Tue Oct 21 06:21:36 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 21 06:21:36 2025 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Tue Oct 21 06:21:36 2025 - [info] Reading server configuration from /etc/mha/app1.cnf..
Tue Oct 21 06:21:36 2025 - [info] MHA::MasterMonitor version 0.58.
Tue Oct 21 06:21:37 2025 - [info] GTID failover mode = 0
Tue Oct 21 06:21:37 2025 - [info] Dead Servers:
Tue Oct 21 06:21:37 2025 - [info] Alive Servers:
Tue Oct 21 06:21:37 2025 - [info]   192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:21:37 2025 - [info]   192.168.4.52(192.168.4.52:3306)
Tue Oct 21 06:21:37 2025 - [info]   192.168.4.53(192.168.4.53:3306)
Tue Oct 21 06:21:37 2025 - [info] Alive Slaves:
Tue Oct 21 06:21:37 2025 - [info]   192.168.4.52(192.168.4.52:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Oct 21 06:21:37 2025 - [info]     Replicating from 192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:21:37 2025 - [info]     Primary candidate for the new Master (candidate_master is set)
Tue Oct 21 06:21:37 2025 - [info]   192.168.4.53(192.168.4.53:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Oct 21 06:21:37 2025 - [info]     Replicating from 192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:21:37 2025 - [info]     Not candidate for the new Master (no_master is set)
Tue Oct 21 06:21:37 2025 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Tue Oct 21 06:21:37 2025 - [info] Checking slave configurations..
Tue Oct 21 06:21:37 2025 - [info] Checking replication filtering settings..
Tue Oct 21 06:21:37 2025 - [info]  binlog_do_db= , binlog_ignore_db= 
Tue Oct 21 06:21:37 2025 - [info]  Replication filtering check ok.
Tue Oct 21 06:21:37 2025 - [info] GTID (with auto-pos) is not supported
Tue Oct 21 06:21:37 2025 - [info] Starting SSH connection tests..
Tue Oct 21 06:21:40 2025 - [info] All SSH connection tests passed successfully.
Tue Oct 21 06:21:40 2025 - [info] Checking MHA Node version..
Tue Oct 21 06:21:41 2025 - [info]  Version check ok.
Tue Oct 21 06:21:41 2025 - [info] Checking SSH publickey authentication settings on the current master..
Tue Oct 21 06:21:41 2025 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Tue Oct 21 06:21:42 2025 - [info] Master MHA Node version is 0.58.
Tue Oct 21 06:21:42 2025 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)..
Tue Oct 21 06:21:42 2025 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/var/log/mha/app1/save_binary_logs_test --manager_version=0.58 --start_file=master.000005 
Tue Oct 21 06:21:42 2025 - [info]   Connecting to [email protected](192.168.4.51:22).. 
  Creating /var/log/mha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to master.000005
Tue Oct 21 06:21:42 2025 - [info] Binlog setting check done.
Tue Oct 21 06:21:42 2025 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Oct 21 06:21:42 2025 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.4.52 --slave_ip=192.168.4.52 --slave_port=3306 --workdir=/var/log/mha/app1 --target_version=5.7.17-log --manager_version=0.58 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Tue Oct 21 06:21:42 2025 - [info]   Connecting to [email protected](192.168.4.52:22).. 
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to relay-log.000002
    Temporary relay log file is /var/lib/mysql/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.
Tue Oct 21 06:21:43 2025 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.4.53 --slave_ip=192.168.4.53 --slave_port=3306 --workdir=/var/log/mha/app1 --target_version=5.7.17-log --manager_version=0.58 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Tue Oct 21 06:21:43 2025 - [info]   Connecting to [email protected](192.168.4.53:22).. 
Creating directory /var/log/mha/app1.. done.
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to relay-log.000002
    Temporary relay log file is /var/lib/mysql/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.
Tue Oct 21 06:21:43 2025 - [info] Slaves settings check done.
Tue Oct 21 06:21:43 2025 - [info] 
192.168.4.51(192.168.4.51:3306) (current master)
 +--192.168.4.52(192.168.4.52:3306)
 +--192.168.4.53(192.168.4.53:3306)

Tue Oct 21 06:21:43 2025 - [info] Checking replication health on 192.168.4.52..
Tue Oct 21 06:21:43 2025 - [info]  ok.
Tue Oct 21 06:21:43 2025 - [info] Checking replication health on 192.168.4.53..
Tue Oct 21 06:21:43 2025 - [info]  ok.
Tue Oct 21 06:21:43 2025 - [warning] master_ip_failover_script is not defined.
Tue Oct 21 06:21:43 2025 - [warning] shutdown_script is not defined.
Tue Oct 21 06:21:43 2025 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

两者都返回 OK 表示配置正常。

启动 MHA 管理进程

[root@mha-manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover > /var/log/mha/manager.log 2>&1 &
[1] 2634

查看状态

[root@mha-manager ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:2634) is running(0:PING_OK), master:192.168.4.51

验证自动切换

模拟主库宕机：

[root@master ~]# systemctl stop mysqld

查看日志：

[root@mha-manager ~]# tail -f /var/log/mha/app1/manager.log
app1: MySQL Master failover 192.168.4.51(192.168.4.51:3306) to 192.168.4.52(192.168.4.52:3306) succeeded

Master 192.168.4.51(192.168.4.51:3306) is down!

Check MHA Manager logs at mha-manager:/var/log/mha/app1/manager.log for details.

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

故障排错

1.当遇到依赖包安装不了的情况就要想去下载网络中的镜像，但这前提是配置一个可以连网的网卡。

2. 执行masterha_check_repl --conf=/etc/mha/app1.cnf后，出现Access denied for user 'mha'@'slave1' (using password: YES)，说明监控用户授权出现了问题，解决办法是执行DROP USER IF EXISTS 'mha'@'%';再继续重新创建监控用户并授权

3. 从服务器一定要设置只读模式，防止主库在修改，从库也在修改造成主从数据库的数据不一致的情况

这时候就有一个问题了，从库设置为只读模式，那么当主库宕机后选出一个从库最为新的主库，如果还是只读模式那么数据如何插入呢？

当从库晋升为新的主库会执行以下命令解除只读模式

SET GLOBAL read_only=0;
SET GLOBAL super_read_only=0;

创建数据库和表插入数据验证

master库

在master中创建mha_test数据库

mysql> create database mha_test;
Query OK, 1 row affected (0.00 sec)

创建表

mysql> create table mha_test.mha_verify( id INT AUTO_INCREMENT PRIMARY KEY, hostname VARCHAR(100) NOT NULL, server_id INT NOT NULL, role VARCHAR(50) NOT NULL, test_data VARCHAR(255), created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

插入数据

mysql> INSERT INTO mha_test.mha_verify (hostname, server_id, role, test_data)  VALUES (@@hostname, @@server_id, 'Master', 'Initial data from master');
Query OK, 1 row affected (0.00 sec)

在从主机上查询

模拟主库宕机

主库变为主机名为slave2的机器

slave2主机

创建数据库和表

mysql> create database kpop;
Query OK, 1 row affected (0.00 sec)


mysql> create table kpop.team( name char(15),company char(10));
Query OK, 0 rows affected (0.00 sec)

插入数据

mysql> insert into kpop.team values("svt","HYBE");
Query OK, 1 row affected (0.00 sec)

slave2查看数据

重启master主机的MySQL服务

[root@master ~]# systemctl start mysqld

进入数据库查看是否有新创建的数据库

这时候会发现,重启数据库后无法同步新主库创建的内容,这时候要手动同步数据,将master作为从库与slave1新主库进行同步

在slave1主机执行

mysql> grant replication slave on *.* to lisi@'%' identified by '123qqq...A';
Query OK, 0 rows affected, 1 warning (0.00 sec)

在master主机上执行

mysql> change master to master_host="192.168.4.52",master_user="lisi",master_password='123qqq...A',master_log_file='mysql-bin.000001',master_log_pos=1047;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.52
                  Master_User: lisi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1047
               Relay_Log_File: master-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1047
              Relay_Log_Space: 528
              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: 52
                  Master_UUID: 5a940450-ada5-11f0-9a63-000c29a85b20
             Master_Info_File: /var/lib/mysql/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)

ERROR: 
No query specified

但是这是时候master主机的数据和slave1新主库的数据不一样需要手动执行mysqldump导出再导入master主机的数据库

本文地址：https://www.limyvps.com/news/26_41984.html

上一篇：Azure 免费虚拟机 Centos8 安装 Nginx, PostgreSQL, Django

下一篇：OSSEC搭建部署(ossec-server(CentOS7.X)和ossec-agent(CentOS7.X))

基于CentOS7搭建MHA集群

1. MHA架构

1.1 简要概述

1.2 MHA主要主键

MHA Manager（管理节点）

MHA Node（数据节点）

1.3 MHA的主要功能

1.4 工作原理简述

1.5 原理架构图

1.6 MHA的优势与局限

1.7 运用场景

2. MHA配置过程

2.1 环境准备

为什么从数据库开启binlog日志？

2.2 实战演示

所有主机关闭防火墙SELinux

所有主机配置/etc/hosts文件

SSH免密登录配置

配置主从复制

master主机配置过程

从库配置

安装MHA

依赖安装错误

配置MHA

创建MHA用户

检查配置

启动 MHA 管理进程

验证自动切换

故障排错

创建数据库和表插入数据验证

master库

模拟主库宕机

slave2主机

重启master主机的MySQL服务

最新文章

linux 搭建packetix *** server

Centos6.4 安装ossec 2.7（2）

Xshell 7远程连接Linux系统

个人博客搭建

電子商務成功實例--Reebok

热门文章

为何选择香港VPS服务器作为证券行业的优选方案

如何使用ChatGPT API训练自定义知识库AI聊天机器人

Ubuntu 20.04下用Let‘s Encrypt为Nginx配置HTTPS完整指南

DAY24：信息搜集

网络安全观察攻击类型分布

标签列表

服务器租用

站群服务器

高防服务器

关于我们