MySQL 主从复制与高可用架构实战

MySQL 的安全初始化

[root@mysql-node1 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password:
VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security...
Press y|Y for Yes, any other key for No: no
Using existing password for root. Change the password for root ? ((Press y|Y for Yes, any other key for No) : no
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success.
Normally, root should only be allowed to connect from 'localhost'...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success.
By default, MySQL comes with a database named 'test' that anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success.
All done!

快速配置其他主机

# 在第一台机器上执行
[root@mysql-node1 ~]# cd /usr/local/
[root@mysql-node1 local]# tar czf mysql.tar.gz mysql/
# 传输到第二台机器
[root@mysql-node1 local]# scp mysql.tar.gz root@mysql-node2:/usr/local/
[root@mysql-node1 local]# scp /etc/my.cnf root@mysql-node2:/etc/
# 在第二台机器上执行
[root@mysql-node2 ~]# cd /usr/local/
[root@mysql-node2 local]# tar zxf mysql.tar.gz
[root@mysql-node2 local]# useradd -r -s /sbin/nologin -M mysql
[root@mysql-node2 local]# mkdir -p /data/mysql
[root@mysql-node2 local]# chown mysql.mysql /data/mysql/
[root@mysql-node2 local]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bash_profile
[root@mysql-node2 local]# source ~/.bash_profile
[root@mysql-node2 ~]# mysqld --initialize --user=mysql
# 记住密码
[root@mysql-node2 ~]# cd /usr/local/mysql/support-files/
[root@mysql-node2 support-files]# cp mysql.server /etc/init.d/mysqld
[root@mysql-node2 ~]# dnf install -y initscripts-10.11.8-4.el9.x86_64
[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# chkconfig --level 35 mysqld on
[root@node3 support-files]# mysql_secure_installation
# ... (同上安全初始化流程)

MySQL 集群实战--主从复制

1. 编写 my.cnf 主配置文件

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
log-bin=mysql-bin

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
log-bin=mysql-bin

[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
log-bin=mysql-bin

#在三台主机中重启数据库
[root@mysql-node1~3 ~]# /etc/init.d/mysqld restart

2. 建立同步时需要用到的数据库账号

[root@mysql-node1 ~]# mysql -uroot -plee mysql
mysql> SHOW VARIABLES LIKE 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> create user lee@'%' identified with mysql_native_password by 'lee';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT replication slave ON *.* to lee@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR lee@'%';
+---------------------------------------------+
| Grants for lee@%                            |
+---------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `lee`@`%` |
+---------------------------------------------+
1 row in set (0.00 sec)

3. 配置数据库一主一从

#在 master 中查看日志文件名称及 id
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      659 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

#在 slave 主机中
[root@mysql-node2 ~]# mysql -uroot -plee mysql
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_USER='lee',MASTER_PASSWORD='lee',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=659;
Query OK, 0 rows affected, 8 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
           Slave_IO_State: Waiting for source to send event
              Master_Host: 172.25.254.10
              Master_User: lee
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000001
       Read_Master_Log_Pos: 659
        Relay_Log_File: mysql-node2-relay-bin.000002
          Relay_Log_Pos: 328
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes #数据同步成功
       Slave_SQL_Running: Yes #通过同步过来的数据做日志回访成功

4. 测试

#在 master 中建立库
mysql> create database timinglee;
Query OK, 1 row affected (0.00 sec)

#在 slave 主机中可以实现数据同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| timinglee          |
+--------------------+
5 rows in set (0.00 sec)

5. 向当前一主一从中加入新的数据库

#模拟一主一从中已经存在数据情况
mysql> CREATE TABLE hjw.userlist (
    -> name VARCHAR(10) not null,
    -> pass VARCHAR(50) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hjw.userlist values ('user1','123');
Query OK, 1 row affected (0.00 sec)

#加入新从库时需要手动拉平数据
[root@mysql-node1 ~]# mysqldump -uroot -p hjw > hjw.sql
[root@mysql-node1 ~]# scp hjw.sql root@172.25.254.30:/root/

[root@mysql-node3 ~]# mysql -uroot -phjw -e "create database hjw;"
[root@mysql-node3 ~]# mysql -uroot -phjw hjw < hjw.sql

[root@mysql-node3 ~]# mysql -uroot -phjw mysql
mysql> select * from hjw.userlist;
+-------+------+
| name  | pass |
+-------+------+
| user1 | 123  |
+-------+------+
1 row in set (0.00 sec)

6. 将新库加入主从结构中

#在 master 中查看日志的 id
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1367 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

[root@mysql-node3 ~]# mysql -uroot -phjw mysql
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_USER='lee',MASTER_PASSWORD='lee',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1367;
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
           Slave_IO_State: Waiting for source to send event
              Master_Host: 172.25.254.10
              Master_User: lee
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000001
       Read_Master_Log_Pos: 1415
        Relay_Log_File: mysql-node3-relay-bin.000002
          Relay_Log_Pos: 328
    Relay_Master_Log_File: mysql-bin.000001
         Slave_IO_Running: Yes
       Slave_SQL_Running: Yes

7. 测试一主两从

#在 master 中建立数据
mysql> INSERT INTO hjw.userlist values ('user2','123');

#在新加入的 slave 中查看信息
mysql> select * from hjw.userlist;
+-------+------+
| name  | pass |
+-------+------+
| user1 | 123  |
| user2 | 123  |
+-------+------+
2 rows in set (0.00 sec)

MySQL 主从架构中的使用技巧及优化

1. 延迟复制

延迟复制时用来控制 sql 线程的，和 i/o 线程无关。是日志已经保存在 slave 端了，那个 sql 要等多久进行回放。

#在指定需要延迟同步的 slave 主机中，如果主机中安装数据库的版本是 8 以上
mysql> STOP REPLICA;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_DELAY=60;
mysql> START REPLICA;

[root@mysql-node2 ~]# mysql -uroot -phjw -e "show slave status\G;" | grep SQL_Delay
SQL_Delay: 60

#在 master 主机中对数据进行更改
mysql> delete from hjw.userlist where name='user1';
Query OK, 1 row affected (0.01 sec)

#在被设定延迟复制的主机中查看动作是否被同步
mysql> select * from hjw.userlist;
+-------+------+
| name  | pass |
+-------+------+
| user1 | 123  |
| hjw   | hjw  |
+-------+------+
2 rows in set (0.00 sec)

#等待延迟时间过后再次查看
mysql> select * from hjw.userlist;
+------+------++
| name | pass ||
+------+------++
| hjw  | hjw  ||
+------+------++
1 row in set (0.00 sec)

2. 慢查询日志

• 慢查询，顾名思义，执行很慢的查询
• 当执行 SQL 超过 long_query_time 参数设定的时间阈值（默认 10s）时，就被认为是慢查询
• 慢查询日志默认是不开启的

#慢查询日志是否开启
mysql> SHOW variables like "slow%";
+---------------------+---------------------------+
| Variable_name       | Value                     |
+---------------------+---------------------------+
| slow_launch_time    | 2                         |
| slow_query_log      | OFF                       |
| slow_query_log_file | /data/mysql/node3-slow.log|
+---------------------+---------------------------+

#开启慢查询日志
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.02 sec)

#检测慢查询日志
mysql> SHOW VARIABLES like "long%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> SET long_query_time=4;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep (4);
+-----------+
| sleep (4) |
+-----------+
|         0 |
+-----------+
1 row in set (4.00 sec)

mysql> quit
Bye
[root@node3 ~]# cat /data/mysql/node3-slow.log
/usr/local/mysql/bin/mysqld, Version: 8.3.0 (Source distribution). started with: Tcp port: 3306 Unix socket: /data/mysql/mysql.sock Time Id Command Argument
# Time: 2026-02-27T02:14:19.853151Z
# User@Host: root[root] @ localhost [] Id:   21
# Query_time: 4.000810 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1772158455;
select sleep (4);

3. GTID 模式

当为启用 GTID 时我们要考虑的问题

在 master 端的写入时多用户读写，在 slave 端的复制时单线程日志回放，所以 slave 端一定会延迟与 master 端。这种延迟在 slave 端的延迟可能会不一致，当 master 挂掉后 slave 接管，一般会挑选一个和 master 延迟日志最接近的充当新的 master。

当激活 GTID 之后

当 master 出现问题后，slave2 和 master 的数据最接近，会被作为新的 master。slave1 指向新的 master，但是他不会去检测新的 master 的 pos id，只需要继续读取自己 gtid_next 即可。

#在所有主机中加入参数
[root@mysql-node1~3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql socket=/data/mysql/mysql.sock
#开启主从服务
server-id=10 log-bin=mysql-bin
#开启 GTI 模式
gtid_mode=ON
enforce-gtid-consistency=ON

[root@mysql-node1~3 ~]# /etc/init.d/mysqld restart

#在三台主机中分别查看 GTID 模式是否开启
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed                    |           |
| gtid_executed_compression_period | 0         |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.00 sec)

#在从库中停止 slave 功能；
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='lee', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
mysql> start slave ;
mysql> show slave status\G;
*************************** 1. row ***************************
           Slave_IO_State: Waiting for source to send event
              Master_Host: 172.25.254.10
              Master_User: lee
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000003
       Read_Master_Log_Pos: 158
        Relay_Log_File: node3-relay-bin.000002
          Relay_Log_Pos: 375
    Relay_Master_Log_File: mysql-bin.000003
         Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
         Auto_Position: 1

多线程回放

默认情况下 slave 中使用的是 sql 单线程回放。在 master 中时多用户读写，如果使用 sql 单线程回放那么会造成组从延迟严重。

#开启多线程回放日志（在 slave 主中）
[root@mysql-node2 ~]# vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
relay_log_recovery=ON

[root@mysql-node2 ~]# /etc/init.d/mysqld restart
#查看更改生效信息
mysql> show processlist;

此时 sql 线程转化为协调线程，16 个 worker 负责处理 sql 协调线程发送过来的处理请求。

原理刨析

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。

• 二进制日志转储线程（Binlog dump thread）：是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库。
• 从库 I/O 线程：会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（Relay log）。
• 从库 SQL 线程：会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

1. Master 将写操作记录到二进制日志（binlog）。
2. Slave 将 Master 的 binary log events 拷贝到它的中继日志（relay log）；
3. Slave 重做中继日志中的事件，将改变应用到自己的数据库中。

架构缺陷

主从架构采用的是异步机制。master 更新完成后直接发送二进制日志到 slave，但是 slaves 是否真正保存了数据 master 端不会检测。当 master 端到 slave 端的网络出现问题时或者 master 端直接挂掉，二进制日志可能根本没有到达 slave。master 出现问题 slave 端接管 master，这个过程中数据就丢失了。这样的问题出现就无法达到数据的强一致性，零数据丢失。

半同步模式

1. 用户线程写入完成后 master 中的 dump 会把日志推送到 slave 端
2. slave 中的 io 线程接收后保存到 relaylog 中继日志
3. 保存完成后 slave 向 master 端返回 ack
4. 在未接受到 slave 的 ack 时 master 端时不做提交的，一直处于等待当收到 ack 后提交到存储引擎
5. 在 5.6 版本中用到的时 after_commit 模式，after_commit 模式时先提交在等待 ack 返回后输出 ok

#在所有主机中操作
#master
[root@mysql-node1~3 ~]# vim /etc/my.cnf
rpl_semi_sync_master_enabled=1

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';
+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE        |
+---------------------+---------------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

#所有主机重启 mysql
[root@mysql-node3 ~]# /etc/init.d/mysqld restart

#slave
[root@mysql-node1~3 ~]# vim /etc/my.cnf
rpl_semi_sync_slave_enabled=1

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

#测试：
#在主库中
mysql> INSERT INTO hjw.userlist values ('user1','123');
Query OK, 1 row affected (10.01 sec)

#查看从库是否收到数据
mysql> select * from hjw.userlist;
+-------+------+
| name  | pass |
+-------+------+
| hjw   | hjw  |
| user1 | 123  |
+-------+------+
2 rows in set (0.00 sec)

#模拟 ack 故障 在所有 slave 主机中
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#在主库写入数据
mysql> INSERT INTO hjw.userlist values ('user2','hjw');
Query OK, 1 row affected (0.01 sec)

#有延迟时间
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_no_tx                 | 2     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

#恢复故障 在所有 slave 主机中
mysql> start SLAVE IO_THREAD;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#从查看数据是否收到
mysql> select * from hjw.userlist;
+-------+------+
| name  | pass |
+-------+------+
| hjw   | hjw  |
| user1 | 123  |
| user2 | hjw  |
+-------+------+
3 rows in set (0.00 sec)

#查看半同步是否重新开启 会自动切换
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_status                | ON    |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

MySQL-MHA 高可用集群

环境搭建

#在 master 节点中
[root@mysql-node10 ~]# /etc/init.d/mysqld stop
[root@mysql-node10 ~]# rm -fr /data/mysql/*
[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0

[root@mysql-node10 ~]# mysqld --user mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
[root@mysql-node10 ~]# mysql_secure_installation
[root@mysql-node10 ~]# mysql -p
mysql> create user repl@'%' identified with mysql_native_password by 'lee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

#在 slave1 和 slave2 中
[root@mysql-node20 & 30 ~]# /etc/init.d/mysqld stop
[root@mysql-node20 & 30 ~]# rm -fr /data/mysql/*
[root@mysql-node20 & 30 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0

[root@mysql-node20 & 30 ~]# mysqld --user mysql --initialize
[root@mysql-node20 & 30 ~]# /etc/init.d/mysqld start
[root@mysql-node20 & 30 ~]# mysql_secure_installation
[root@mysql-node20 & 30 ~]# mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

配置 Mha-manager

[root@mha ~]# unzip MHA-7.zip
[root@mha ~]# cd MHA-7/
[root@mha MHA-7]# dnf install perl perl-DBD-MySQL perl-CPAN -y
[root@mha MHA-7]# cpan
Loading internal logger. Log::Log4perl recommended for better logging
CPAN.pm requires configuration, but most of it can be done automatically.
Would you like to configure as much as possible automatically? [yes] yes
cpan[1]> install Config::Tiny
cpan[2]> install Log::Dispatch
cpan[3]> install Mail::Sender
Specify defaults for Mail::Sender? (y/N) y
Default encoding of message bodies (N)one, (Q)uoted-printable, (B)ase64: n
cpan[4]> install Parallel::ForkManager
cpan[5]>exit
[root@mha MHA-7]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps

在 slave 中安装相应软件

[root@mha MHA-7]# for i in 10 20 30 > do > scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm [email protected].$i:/mnt > ssh -l root 172.25.254.$i "rpm -ivh /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm --nodeps" > done
Warning: Permanently added '172.25.254.10' (ED25519) to the list of known hosts.
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100%   35KB  16.9MB/s  00:00
Verifying... 
准备中... 
正在升级/安装... 
mha4mysql-node-0.58-0.el7.centos

修改 MHA-Manager 中的检测代码

[root@mha MHA-7]# vim /usr/share/perl5/vendor_perl/MHA/NodeUtil.pm
sub parse_mysql_major_version($)
{
    my $str = shift;
    my @nums = $str =~ m/(\d+)/g;
    my $result = sprintf( '%03d%03d', $nums[0]//0, $nums[1]//0);
    return $result;
}

为 MHA 建立远程登录用户

#在 master 主机中
mysql> create user root@'%' identified with mysql_native_password by 'lee';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON *.* TO root@'%';
Query OK, 0 rows affected (0.00 sec)

生成 MHA-manager 的配置文件模板

[root@mha mha4mysql-manager-0.58]# mkdir /etc/masterha/ -p
[root@mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mha MHA-7]# cd mha4mysql-manager-0.58
[root@mha mha4mysql-manager-0.58]# mkdir /etc/masterha/ -p
[root@mha mha4mysql-manager-0.58]# cat samples/conf/masterha_default.cnf samples/conf/app1.cnf > /etc/masterha/app1.cnf

修改配置文件

[root@mha mha4mysql-manager-0.58]# vim /etc/masterha/app1.cnf
[server default]
user=root
password=hjw
ssh_user=root
repl_user=lee
repl_password=lee
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s 172.25.254.2 ping_interval=3

[server default]
manager_workdir=/etc/masterha
manager_log=/etc/masterha/mha.log

[server1]
hostname=172.25.254.10
candidate_master=1
check_repl_delay=0

[server2]
hostname=172.25.254.20
candidate_master=1
check_repl_delay=0

[server3]
hostname=172.25.254.30
no_master=1

检测环境

[root@mha mha4mysql-manager-0.58]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Fri Feb 27 16:23:20 2026 - [info] All SSH connection tests passed successfully.

[root@mha mha4mysql-manager-0.58]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Fri Feb 27 16:23:50 2026 - [info] MySQL Replication Health is OK.
Fri Feb 27 16:23:51 2026 - [info] Current Alive Master: 172.25.254.10(172.25.254.10:3306)
Fri Feb 27 16:23:51 2026 - [info] Alive Slaves:
Fri Feb 27 16:23:51 2026 - [info]   172.25.254.20(172.25.254.20:3306) Version=8.3.0 log-bin:enabled GTID ON
Fri Feb 27 16:23:51 2026 - [info]   172.25.254.30(172.25.254.30:3306) Version=8.3.0 log-bin:enabled GTID ON

二、集群切换操作

手动切换

master 无故障切换

#默认状态
[root@mysql-node2 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 10
Slave_IO_State: Waiting for source to send event
Master_Host: 172.25.254.10

#执行切换，把 master 切换到 20
[root@mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Sat Mar 7 10:21:01 2026 - [info] Starting online master switch..
Sat Mar 7 10:21:01 2026 - [info] Phase 1: Configuration Check Phase..
Sat Mar 7 10:21:28 2026 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES.
Sat Mar 7 10:21:34 2026 - [info] Switching master to 172.25.254.20(172.25.254.20:3306) completed successfully.

#查看集群状态
[root@mysql-node1 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 15
Master_Host: 172.25.254.20
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

master 故障后切换

[root@mha ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.10 --dead_master_port=3306 --new_master_host=172.25.254.20 --new_master_port=3306 --ignore_last_failover
Sat Mar 7 10:26:01 2026 - [info] Starting master failover.
Sat Mar 7 10:26:08 2026 - [info] New master decided manually is 172.25.254.20(172.25.254.20:3306)
Sat Mar 7 10:26:13 2026 - [info] Finished master recovery successfully.

#故障恢复
[root@mha ~]# rm -fr /etc/masterha/app1.failover.complete
[root@mysql-node2 ~]# mysql -uroot -phjw -e "reset slave;"
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node1 ~]# mysql -uroot -phjw -e "CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='lee', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;"
[root@mysql-node1 ~]# mysql -uroot -phjw -e "start slave;"

自动切换

#为了方便观察建议开启两个 shell
[root@mha ~]# > /etc/masterha/*.log
[root@mha ~]# watch -n 1 cat /etc/masterha/mha.log

#开启自动切换功能
[root@mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf &
[root@mha ~]# jobs
[1]+ 运行中 masterha_manager --conf=/etc/masterha/app1.cnf &

#模拟故障
[root@mysql-node1 ~]# /etc/init.d/mysqld stop

三、VIP 功能及 VIP 的启动切换

[root@mha ~]# unzip MHA-7.zip
[root@mha ~]# ll MHA-7/master_ip_*
rw-r--r-- 1 root root 2156 1 月 14 2021 MHA-7/master_ip_failover
rw-r--r-- 1 root root 3813 1 月 14 2021 MHA-7/master_ip_online_change

[root@mha ~]# mkdir /etc/masterha/scripts
[root@mha ~]# cp MHA-7/master_ip_* /etc/masterha/scripts

[root@mha ~]# vim /etc/masterha/app1.cnf
master_ip_failover_script= /etc/masterha/scripts/master_ip_failover
master_ip_online_change_script= /etc/masterha/scripts/master_ip_online_change

[root@mha ~]# vim /etc/masterha/scripts/master_ip_failover
my $vip = '172.25.254.100/24';

[root@mha ~]# vim /etc/masterha/scripts/master_ip_online_change
my $vip = '172.25.254.100/24';

[root@mysql-node1 ~]# ip a a 172.25.254.100/24 dev eth0

#测试：
[root@mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf &
[root@mha ~]# jobs
[1]+ 运行中 masterha_manager --conf=/etc/masterha/app1.cnf &

#关闭 mysql master
[root@mysql-node1 ~]# /etc/init.d/mysqld stop

[root@mysql-node2 ~]# ip a
inet 172.25.254.20/24 brd 172.25.254.255 scope global noprefixroute eth0
inet 172.25.254.100/24 scope global secondary eth0