文章目录
前言
MHA（Master High Availability）是一套用于 MySQL 数据库的高可用解决方案，能够在主数据库发生故障时自动进行故障切换，确保数据一致性和服务的连续性。
一、环境准备
| 角色 | IP地址 | 主机名 | 说明 |
|---|---|---|---|
| MySQL Master | 192.168.4.51 | mysql-master | 当前主库 |
| MySQL Slave1 | 192.168.4.52 | mysql-slave1 | 候选主库（Candidate Master） |
| MySQL Slave2 | 192.168.4.53 | mysql-slave2 | 从库 |
| MHA Manager | 192.168.4.200 | mha-manager | 管理节点（可部署在任意机器） |
每台虚拟机安装好MySQL（5.7及其以上版本）
关闭防火墙与selinux
为每台虚拟机配置hosts解析
每台虚拟机能上网（为了后面安装依赖和组件）
[root@mha-manager ~]# vim /etc/hosts
[root@mha-manager ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.4.200 mha-manager
192.168.4.51 mysql-master
192.168.4.52 mysql-slave1
192.168.4.53 msyql-slave2
二、MySQL主从搭建
1.配置/etc/my.cnf
配置192.168.4.51 mysql-master
[mysqld]
server-id=51 #服务器唯一标识
log_bin=/var/lib/mysql/mysql-bin # 启用二进制日志，并指定放置地址
binlog_format=row #设置二进制日志格式为行模式，这是MHA强制要求的
log_slave_updates=1 #允许从库记录二进制日志，这是MHA故障切换的关键
配置192.168.4.52 mysql-slave1
[mysqld]
server-id=52
log_bin=/var/lib/mysql/mysql-bin
binlog_format=row
log_slave_updates=1
relay_log_purge=0 #禁止自动删除中继日志
read_only=1 # 设置为只读模式，之后MHA提升为主库自动关闭选项
配置192.168.4.53 mysql-slave2
[mysqld]
server-id=53
log_bin=/var/lib/mysql/mysql-bin
binlog_format=row
log_slave_updates=1
relay_log_purge=0
read_only=1
2.创建授权用户
在master主库上授权
mysql> create user 'repl'@'%' identified by '123qqq...A';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.配置主从
查看master主库的二进制文件和访问点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1007 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
分别在slave1和slave2从库上配置slave
mysql> change master to
master_host='192.168.4.51',
master_user='repl',
master_password='123qqq...A',
master_log_file='mysql-bin.000002',
master_log_pos=1007;
启动slave并查看
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1007
Relay_Log_File: mysql_slave2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 1007
Relay_Log_Space: 534
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: 8e0f2837-ad8a-11f0-a9fb-000c2910cad8
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.01 sec)
其中Slave_IO_Running: Yes和 Slave_SQL_Running: Yes 都为yes代表同步成功
三、MHA安装
- 在 MHA Manager 节点 (200) 上安装 MHA Manager
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
yum install -y epel-release
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-Mail-Sender perl-MIME-Lite perl-Mail-Sendmail
# 安装 MHA Node 包
yum install -y https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
# 安装 MHA Manager 包
yum install -y https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
# 安装额外的 Perl 模块
yum install -y perl-Mail-Sender perl-MIME-Lite perl-Mail-Sendmail
- 在所有 MySQL 节点 (51,52,53) 上安装 MHA Node
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-Mail-Sender perl-MIME-Lite perl-Mail-Sendmail
yum install -y https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
四、配置SSH免密登录
MHA Manager 需要能无密码 SSH 连接到所有 MySQL 节点。
在 MHA Manager 节点 (192.168.4.200) 上执行：
[root@mha-manager ~]# 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:5GtfTHybIHYcO13d5lNd2sUHKzaKycwcgswpAcvWYk4 root@mha-manager
The key's randomart image is:
+---[RSA 2048]----+
|... .oo|
|...+ o +O|
|.E..= . o = o X|
|= .. O + = * +.|
| . S + O o..|
| o = + o.|
| o o o |
| . . . |
| . |
+----[SHA256]-----+
[root@mha-manager ~]# ssh-copy-id root@192.168.4.51
/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.4.51's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@192.168.4.51'"
and check to make sure that only the key(s) you wanted were added.
[root@mha-manager ~]# ssh-copy-id root@192.168.4.52
/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.4.52's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@192.168.4.52'"
and check to make sure that only the key(s) you wanted were added.
[root@mha-manager ~]# ssh-copy-id root@192.168.4.53
/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.4.53's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@192.168.4.53'"
and check to make sure that only the key(s) you wanted were added.
51，52，53要互相免密
按上面的操作分别再51，52，53上做互免
五、配置MHA
1. 创建 MHA 工作目录和配置文件
[root@mha-manager ~]# mkdir -p /etc/mha
[root@mha-manager ~]# mkdir -p /var/log/mha
2. 创建 MHA 配置文件 /etc/mha/app1.cnf
[server default]
# MHA管理账号
user=mha
password=123qqq...A
# SSH用户
ssh_user=root
# 复制账号
repl_user=repl
repl_password=123qqq...A
# 工作目录
manager_workdir=/var/log/mha/app1
remote_workdir=/var/log/mha/app1
[server1]
hostname=192.168.4.51
master_binlog_dir=/var/lib/mysql
candidate_master=1
[server2]
hostname=192.168.4.52
master_binlog_dir=/var/lib/mysql
candidate_master=1 # 明确指定该从库为候选主库
[server3]
hostname=192.168.4.53
master_binlog_dir=/var/lib/mysql
no_master=1 # 明确指定该从库永远不会被提升为主库
编辑的时候去掉#与中文这里只是解释标注，因为可能会引起安全检查不通过
3. 在所有 MySQL 节点上创建 MHA 管理用户
mysql> grant all on *.* to 'mha'@'192.168.4.%' identified by ‘123qqq...A’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
六、启动测试MHA
安全检查ssh
[root@mha-manager ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Mon Oct 20 09:00:09 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 20 09:00:09 2025 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Mon Oct 20 09:00:09 2025 - [info] Reading server configuration from /etc/mha/app1.cnf..
Mon Oct 20 09:00:09 2025 - [info] Starting SSH connection tests..
Mon Oct 20 09:00:10 2025 - [debug]
Mon Oct 20 09:00:09 2025 - [debug] Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.52(192.168.4.52:22)..
Mon Oct 20 09:00:10 2025 - [debug] ok.
Mon Oct 20 09:00:10 2025 - [debug] Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.53(192.168.4.53:22)..
Mon Oct 20 09:00:10 2025 - [debug] ok.
Mon Oct 20 09:00:11 2025 - [debug]
Mon Oct 20 09:00:10 2025 - [debug] Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.51(192.168.4.51:22)..
Mon Oct 20 09:00:10 2025 - [debug] ok.
Mon Oct 20 09:00:10 2025 - [debug] Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.53(192.168.4.53:22)..
Mon Oct 20 09:00:11 2025 - [debug] ok.
Mon Oct 20 09:00:12 2025 - [debug]
Mon Oct 20 09:00:10 2025 - [debug] Connecting via SSH from root@192.168.4.53(192.168.4.53:22) to root@192.168.4.51(192.168.4.51:22)..
Mon Oct 20 09:00:11 2025 - [debug] ok.
Mon Oct 20 09:00:11 2025 - [debug] Connecting via SSH from root@192.168.4.53(192.168.4.53:22) to root@192.168.4.52(192.168.4.52:22)..
Mon Oct 20 09:00:11 2025 - [debug] ok.
Mon Oct 20 09:00:12 2025 - [info] All SSH connection tests passed successfully.
只要显示All SSH connection tests passed successfully.就代表成功
安全检测repl
[root@mha-manager ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
Mon Oct 20 09:43:40 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 20 09:43:40 2025 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Mon Oct 20 09:43:40 2025 - [info] Reading server configuration from /etc/mha/app1.cnf..
Mon Oct 20 09:43:40 2025 - [info] MHA::MasterMonitor version 0.58.
Mon Oct 20 09:43:42 2025 - [info] GTID failover mode = 0
Mon Oct 20 09:43:42 2025 - [info] Dead Servers:
Mon Oct 20 09:43:42 2025 - [info] Alive Servers:
Mon Oct 20 09:43:42 2025 - [info] 192.168.4.51(192.168.4.51:3306)
Mon Oct 20 09:43:42 2025 - [info] 192.168.4.52(192.168.4.52:3306)
Mon Oct 20 09:43:42 2025 - [info] 192.168.4.53(192.168.4.53:3306)
Mon Oct 20 09:43:42 2025 - [info] Alive Slaves:
Mon Oct 20 09:43:42 2025 - [info] 192.168.4.52(192.168.4.52:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Mon Oct 20 09:43:42 2025 - [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Mon Oct 20 09:43:42 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Oct 20 09:43:42 2025 - [info] 192.168.4.53(192.168.4.53:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Mon Oct 20 09:43:42 2025 - [info] Replicating from 192.168.4.51(192.168.4.51:3306)
Mon Oct 20 09:43:42 2025 - [info] Not candidate for the new Master (no_master is set)
Mon Oct 20 09:43:42 2025 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Mon Oct 20 09:43:42 2025 - [info] Checking slave configurations..
Mon Oct 20 09:43:42 2025 - [info] Checking replication filtering settings..
Mon Oct 20 09:43:42 2025 - [info] binlog_do_db= , binlog_ignore_db=
Mon Oct 20 09:43:42 2025 - [info] Replication filtering check ok.
Mon Oct 20 09:43:42 2025 - [info] GTID (with auto-pos) is not supported
Mon Oct 20 09:43:42 2025 - [info] Starting SSH connection tests..
Mon Oct 20 09:43:44 2025 - [info] All SSH connection tests passed successfully.
Mon Oct 20 09:43:44 2025 - [info] Checking MHA Node version..
Mon Oct 20 09:43:45 2025 - [info] Version check ok.
Mon Oct 20 09:43:45 2025 - [info] Checking SSH publickey authentication settings on the current master..
Mon Oct 20 09:43:45 2025 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Mon Oct 20 09:43:45 2025 - [info] Master MHA Node version is 0.58.
Mon Oct 20 09:43:45 2025 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)..
Mon Oct 20 09:43:45 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=mysql-bin.000003
Mon Oct 20 09:43:45 2025 - [info] Connecting to root@192.168.4.51(192.168.4.51:22)..
Creating /var/log/mha/app1 if not exists.. Creating directory /var/log/mha/app1.. done.
ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000003
Mon Oct 20 09:43:45 2025 - [info] Binlog setting check done.
Mon Oct 20 09:43:45 2025 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Oct 20 09:43:45 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
Mon Oct 20 09:43:45 2025 - [info] Connecting to root@192.168.4.52(192.168.4.52: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 mysql-slave1-relay-bin.000011
Temporary relay log file is /var/lib/mysql/mysql-slave1-relay-bin.000011
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.
Mon Oct 20 09:43:46 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
Mon Oct 20 09:43:46 2025 - [info] Connecting to root@192.168.4.53(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 mysql_slave2-relay-bin.000010
Temporary relay log file is /var/lib/mysql/mysql_slave2-relay-bin.000010
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.
Mon Oct 20 09:43:46 2025 - [info] Slaves settings check done.
Mon Oct 20 09:43:46 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)
Mon Oct 20 09:43:46 2025 - [info] Checking replication health on 192.168.4.52..
Mon Oct 20 09:43:46 2025 - [info] ok.
Mon Oct 20 09:43:46 2025 - [info] Checking replication health on 192.168.4.53..
Mon Oct 20 09:43:46 2025 - [info] ok.
Mon Oct 20 09:43:46 2025 - [warning] master_ip_failover_script is not defined.
Mon Oct 20 09:43:46 2025 - [warning] shutdown_script is not defined.
Mon Oct 20 09:43:46 2025 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
只要显示MySQL Replication Health is OK.就代表成功
启动mha
[root@mha-manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf < /dev/null > /var/log/mha/manager.log 2>&1 &
[1] 11093
[root@mha-manager ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:11093) is running(0:PING_OK), master:192.168.4.51
启动 MHA Manager 监控
[root@mha-manager ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf < /dev/null > /var/log/mha/manager.log 2>&1 &
[1] 4407
七、模拟故障切换测试
1. 停止主库 MySQL 服务
在51上停止mysql
[root@mysql-master ~]# systemctl stop mysqld
2. 观察 MHA 日志
[root@mha-manager ~]# tail -f /var/log/mha/manager.log
MHA 会自动检测到主库宕机，并开始故障切换流程。
MHA 会自动将 mysql-slave1（候选主库）提升为新的主库，将 mysql-slave2指向新的主库 mysql-slave1，在原主库 mysql-master的日志中记录二进制日志位置
在52上查看master
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 5377 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在53上查看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: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 5377
Relay_Log_File: mysql_slave2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 5377
Relay_Log_Space: 534
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: f15b876b-ad84-11f0-901b-000c29a32fbd
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)
成功转换
在52机上创建库和表并插入数据（新的master）
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> create table db1.user(id int,name char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into db1.user values(1,'tom');
Query OK, 1 row affected (0.01 sec)
在53机上查看
mysql> select * from db1.user;
+------+------+
| id | name |
+------+------+
| 1 | tom |
+------+------+
1 row in set (0.00 sec)
3.恢复主库
启动MySQL服务
[root@mysql-master ~]# systemctl start mysqld
将其配置为从库
mysql> change master to master_host="192.168.4.52", master_user="repl", master_password="123qqq...A", master_log_file="mysql-bin.000004", master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在线切换主库
[root@host200 ~]# masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.4.51 --orig_master_is_new_slave --running_updates_limit=10000
Tue Oct 21 06:58:46 2025 - [info] MHA::MasterRotate version 0.58.
Tue Oct 21 06:58:46 2025 - [info] Starting online master switch..
Tue Oct 21 06:58:46 2025 - [info]
Tue Oct 21 06:58:46 2025 - [info] * Phase 1: Configuration Check Phase..
Tue Oct 21 06:58:46 2025 - [info]
Tue Oct 21 06:58:46 2025 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 21 06:58:46 2025 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Tue Oct 21 06:58:46 2025 - [info] Reading server configuration from /etc/mha/app1.cnf..
Tue Oct 21 06:58:47 2025 - [info] GTID failover mode = 0
Tue Oct 21 06:58:47 2025 - [info] Current Alive Master: 192.168.4.52(192.168.4.52:3306)
Tue Oct 21 06:58:47 2025 - [info] Alive Slaves:
Tue Oct 21 06:58:47 2025 - [info] 192.168.4.51(192.168.4.51:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Tue Oct 21 06:58:47 2025 - [info] Replicating from 192.168.4.52(192.168.4.52:3306)
Tue Oct 21 06:58:47 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Oct 21 06:58:47 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:58:47 2025 - [info] Replicating from 192.168.4.52(192.168.4.52:3306)
Tue Oct 21 06:58:47 2025 - [info] Not candidate for the new Master (no_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.4.52(192.168.4.52:3306)? (YES/no): yes
Tue Oct 21 06:58:51 2025 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Tue Oct 21 06:58:51 2025 - [info] ok.
Tue Oct 21 06:58:51 2025 - [info] Checking MHA is not monitoring or doing failover..
Tue Oct 21 06:58:51 2025 - [info] Checking replication health on 192.168.4.51..
Tue Oct 21 06:58:51 2025 - [info] ok.
Tue Oct 21 06:58:51 2025 - [info] Checking replication health on 192.168.4.53..
Tue Oct 21 06:58:51 2025 - [info] ok.
Tue Oct 21 06:58:51 2025 - [info] 192.168.4.51 can be new master.
Tue Oct 21 06:58:51 2025 - [info]
From:
192.168.4.52(192.168.4.52:3306) (current master)
+--192.168.4.51(192.168.4.51:3306)
+--192.168.4.53(192.168.4.53:3306)
To:
192.168.4.51(192.168.4.51:3306) (new master)
+--192.168.4.53(192.168.4.53:3306)
+--192.168.4.52(192.168.4.52:3306)
Starting master switch from 192.168.4.52(192.168.4.52:3306) to 192.168.4.51(192.168.4.51:3306)? (yes/NO): yes
Tue Oct 21 06:58:54 2025 - [info] Checking whether 192.168.4.51(192.168.4.51:3306) is ok for the new master..
Tue Oct 21 06:58:54 2025 - [info] ok.
Tue Oct 21 06:58:54 2025 - [info] 192.168.4.52(192.168.4.52:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Tue Oct 21 06:58:54 2025 - [info] 192.168.4.52(192.168.4.52:3306): Resetting slave pointing to the dummy host.
Tue Oct 21 06:58:54 2025 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Oct 21 06:58:54 2025 - [info]
Tue Oct 21 06:58:54 2025 - [info] * Phase 2: Rejecting updates Phase..
Tue Oct 21 06:58:54 2025 - [info]
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Tue Oct 21 06:58:56 2025 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Tue Oct 21 06:58:56 2025 - [info] Executing FLUSH TABLES WITH READ LOCK..
Tue Oct 21 06:58:56 2025 - [info] ok.
Tue Oct 21 06:58:56 2025 - [info] Orig master binlog:pos is mysql-bin.000004:411.
Tue Oct 21 06:58:56 2025 - [info] Waiting to execute all relay logs on 192.168.4.51(192.168.4.51:3306)..
Tue Oct 21 06:58:56 2025 - [info] master_pos_wait(mysql-bin.000004:411) completed on 192.168.4.51(192.168.4.51:3306). Executed 0 events.
Tue Oct 21 06:58:56 2025 - [info] done.
Tue Oct 21 06:58:56 2025 - [info] Getting new master's binlog name and position..
Tue Oct 21 06:58:56 2025 - [info] mysql-bin.000006:154
Tue Oct 21 06:58:56 2025 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.4.51', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Tue Oct 21 06:58:56 2025 - [info]
Tue Oct 21 06:58:56 2025 - [info] * Switching slaves in parallel..
Tue Oct 21 06:58:56 2025 - [info]
Tue Oct 21 06:58:56 2025 - [info] -- Slave switch on host 192.168.4.53(192.168.4.53:3306) started, pid: 4550
Tue Oct 21 06:58:56 2025 - [info]
Tue Oct 21 06:58:57 2025 - [info] Log messages from 192.168.4.53 ...
Tue Oct 21 06:58:57 2025 - [info]
Tue Oct 21 06:58:56 2025 - [info] Waiting to execute all relay logs on 192.168.4.53(192.168.4.53:3306)..
Tue Oct 21 06:58:56 2025 - [info] master_pos_wait(mysql-bin.000004:411) completed on 192.168.4.53(192.168.4.53:3306). Executed 0 events.
Tue Oct 21 06:58:56 2025 - [info] done.
Tue Oct 21 06:58:56 2025 - [info] Resetting slave 192.168.4.53(192.168.4.53:3306) and starting replication from the new master 192.168.4.51(192.168.4.51:3306)..
Tue Oct 21 06:58:56 2025 - [info] Executed CHANGE MASTER.
Tue Oct 21 06:58:56 2025 - [info] Slave started.
Tue Oct 21 06:58:57 2025 - [info] End of log messages from 192.168.4.53 ...
Tue Oct 21 06:58:57 2025 - [info]
Tue Oct 21 06:58:57 2025 - [info] -- Slave switch on host 192.168.4.53(192.168.4.53:3306) succeeded.
Tue Oct 21 06:58:57 2025 - [info] Unlocking all tables on the orig master:
Tue Oct 21 06:58:57 2025 - [info] Executing UNLOCK TABLES..
Tue Oct 21 06:58:57 2025 - [info] ok.
Tue Oct 21 06:58:57 2025 - [info] Starting orig master as a new slave..
Tue Oct 21 06:58:57 2025 - [info] Resetting slave 192.168.4.52(192.168.4.52:3306) and starting replication from the new master 192.168.4.51(192.168.4.51:3306)..
Tue Oct 21 06:58:57 2025 - [info] Executed CHANGE MASTER.
Tue Oct 21 06:58:57 2025 - [info] Slave started.
Tue Oct 21 06:58:57 2025 - [info] All new slave servers switched successfully.
Tue Oct 21 06:58:57 2025 - [info]
Tue Oct 21 06:58:57 2025 - [info] * Phase 5: New master cleanup phase..
Tue Oct 21 06:58:57 2025 - [info]
Tue Oct 21 06:58:57 2025 - [info] 192.168.4.51: Resetting slave info succeeded.
Tue Oct 21 06:58:57 2025 - [info] Switching master to 192.168.4.51(192.168.4.51:3306) completed successfully.
在51主库上创建库表并插入数据测试是否恢复成功
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
mysql> create table db2.user(id int ,name char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into db2.user values(1,'dangking');
Query OK, 1 row affected (0.03 sec)
在从库里查看
mysql> select * from db2.user;
+------+----------+
| id | name |
+------+----------+
| 1 | dangking |
+------+----------+
1 row in set (0.00 sec)
至此恢复成功
总结
通过以上步骤，您就成功部署了一个高可用的 MySQL 集群。MHA 会自动监控主库状态并在故障时自动进行故障转移，确保数据库服务的高可用性。
扫一扫
7万+
到【灌水乐园】发言
