MySQL 集群（MySQL Cluster）

Ne0inhk

22 Mar 2026 — 51 min read

MySQL 集群（MySQL Cluster）本质是为了解决单节点 MySQL 的 性能瓶颈（高并发）、可用性风险（单点故障）和 数据可靠性（数据丢失）问题，通过多台服务器协同工作，将数据分散 / 复制存储、请求分散处理，最终实现：

高可用（HA）：单个节点故障不影响整体服务；
高扩展（Scalability）：可通过增加节点提升处理能力；
数据一致性：集群内数据保持同步（不同架构一致性级别不同）

Mysql的源码编译

下载安装包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.3.0.tar.gz

源码编译

[root@mysql-node1 ~]# dnf install cmake3 gcc git bison openssl-devel ncurses-devel systemd-devel rpcgen.x86_64 libtirpc-devel-1.3.3-9.el9.x86_64.rpm gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc -y  [root@mysql-node1 ~]# tar zxf mysql-boost-8.3.0.tar.gz [root@mysql-node1 mysql-8.3.0]# mkdir build [root@mysql-node1 mysql-8.3.0]# cd build/  [root@mysql-node1 build]# cmake3 .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=bundled -DWITH_SSL=system -DWITH_DEBUG=OFF   [root@mysql-node1 build]# make

部署mysql

[root@mysql-node1 build]# make install [root@mysql-node1 build]# cd /usr/local/mysql/  [root@mysql-node1 mysql]# vim ~/.bash_profile # .bash_profile  # Get the aliases and functions if [ -f ~/.bashrc ]; then       . ~/.bashrc fi  # User specific environment and startup programs export PATH=$PATH:/usr/local/mysql/bin #设置mysql运行环境的环境变量   [root@mysql-node1 mysql]# source ~/.bash_profile  [root@mysql-node1 mysql]# useradd -r -s /sbin/nologin -M mysql  [root@mysql-node1 mysql]# mkdir -p /data/mysql [root@mysql-node1 mysql]# chown mysql.mysql /data/mysql/  [root@mysql-node1 ~]# vim /etc/my.cnf [mysqld] datadir=/data/mysql socket=/data/mysql/mysql.sock

mysql数据结构初始化

[root@mysql-node1 ~]# mysqld --initialize --user=mysql

启动mysql

[root@mysql-node1 support-files]# dnf install initscripts.x86_64 -y  [root@mysql-node1 support-files]# cd /usr/local/mysql/support-files/ [root@mysql-node1 support-files]# cp -p mysql.server /etc/init.d/mysqld  [root@mysql-node1 support-files]# /etc/init.d/mysqld start Starting MySQL.Logging to '/data/mysql/mysql-node1.err'. . SUCCESS!  #开机启动 [root@mysql-node1 support-files]# chkconfig --level 35 mysqld on

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. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component?  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'. This ensures that someone cannot guess at the root password from the network.  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. This is also intended only for testing, and should be removed before moving into a production environment.   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success.  - Removing privileges on test database... Success.  Reloading the privilege tables will ensure that all changes made so far will take effect immediately.  Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success.  All done!

根据已经配置好的MySQL主机快速配置其他主机

# 在第一台机器上执行 [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  # 创建mysql用户 [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 2026-02-26T02:38:25.571900Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start. 2026-02-26T02:38:25.573384Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.3.0) initializing of server in progress as process 28876 2026-02-26T02:38:25.580523Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2026-02-26T02:38:25.779854Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2026-02-26T02:38:26.835299Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: howD-bTM.6rd    #记住密码howD-bTM.6rd 2026-02-26T02:38:27.978294Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end.  # 复制启动脚本 [root@mysql-node2 ~]# cd /usr/local/mysql/support-files/ [root@mysql-node2 support-files]# cp mysql.server /etc/init.d/mysqld  # 安装initscripts（如果需要） [root@mysql-node2 ~]# dnf install -y initscripts-10.11.8-4.el9.x86_64  # 启动MySQL [root@mysql-node2 ~]# /etc/init.d/mysqld start  # 设置开机启动 [root@mysql-node2 ~]# chkconfig --level 35 mysqld on  [root@node3 support-files]# 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. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component?  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  ... skipping. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.  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'. This ensures that someone cannot guess at the root password from the network.  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. This is also intended only for testing, and should be removed before moving into a production environment.   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success.  - Removing privileges on test database... Success.  Reloading the privilege tables will ensure that all changes made so far will take effect immediately.  Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success.  All done!

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: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.3.0 Source distribution  Copyright (c) 2000, 2024, Oracle and/or its affiliates.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  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> select User from mysql.user; +------------------+ | User             | +------------------+ | lee             | | mysql.infoschema | | mysql.session   | | mysql.sys       | | root             | +------------------+ 5 rows in set (0.00 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)   #在其他主机中 [root@mysql-node2 ~]# mysql -ulee -plee -h172.25.254.10 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.3.0 Source distribution  Copyright (c) 2000, 2024, Oracle and/or its affiliates.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  mysql> quit

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: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.3.0 Source distribution  Copyright (c) 2000, 2024, Oracle and/or its affiliates.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  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> 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.测试

[root@mysql-node1 ~]# mysql -uroot -lee #在master中建立库 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql             | | performance_schema | | sys               | +--------------------+ 4 rows in set (0.00 sec)  mysql> create database timinglee; Query OK, 1 row affected (0.00 sec)  mysql> SHOW DATABASES; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql             | | performance_schema | | sys               | | timinglee         | +--------------------+ 5 rows in set (0.00 sec)  [root@mysql-node2 ~]# mysql -uroot -plee #在slave主机中可以实现数据同步 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql             | | performance_schema | | sys               | | timinglee         | +--------------------+ 5 rows in set (0.00 sec)

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

#模拟一主一从中已经存在数据情况 [root@mysql-node1 ~]# mysql -uroot -plee 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)  mysql> SELECT * FROM hjw.userlist; +-------+------+ | name | pass | +-------+------+ | user1 | 123 | +-------+------+ 1 row in set (0.01 sec)   #加入新从库时需要手动拉平数据 [root@mysql-node1 ~]# mysqldump -uroot -p hjw > hjw.sql [root@mysql-node1 ~]# scp timinglee.sql [email protected]:/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> 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 |             |                 |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set, 1 warning (0.00 sec)    [root@mysql-node3 ~]# mysql -uroot -phjw 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 线程无关

这个延迟复制不是 i/o 线程过段时间来复制，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 mysql: [Warning] Using a password on the command line interface can be insecure.                   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 | +------+------+ | hjw | hjw | +------+------+ 1 row in set (0.00 sec)    #在未被延迟的slave数据库中查看是否数据操作动作被同步 mysql> select * from hjw.userlist; +------+------+ | name | pass | +------+------+ | hjw | hjw | +------+------+ 1 row in set (0.00 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）时，就被认为是慢查询，这个 SQL 语句就是需要优化的
慢查询被记录在慢查询日志里
慢查询日志默认是不开启的
如果需要优化 SQL 语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

#慢查询日志是否开启 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 | +---------------------+----------------------------+ 3 rows in set (0.02 sec)    #开启慢查询日志 mysql> SET GLOBAL slow_query_log=ON; Query OK, 0 rows affected (0.02 sec)  mysql> SHOW variables like "slow%"; +---------------------+----------------------------+ | Variable_name       | Value                     | +---------------------+----------------------------+ | slow_launch_time   | 2                         | | slow_query_log     | ON                         | | slow_query_log_file | /data/mysql/node3-slow.log | +---------------------+----------------------------+ 3 rows in set (0.00 sec)   #检测慢查询日志 mysql> SHOW VARIABLES like "long%";    #慢查询阈值 +-----------------+-----------+ | Variable_name   | Value     | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.01 sec)  mysql> SET long_query_time=4; Query OK, 0 rows affected (0.00 sec)  mysql> SHOW VARIABLES like "long%"; +-----------------+----------+ | Variable_name   | Value   | +-----------------+----------+ | long_query_time | 4.000000 | +-----------------+----------+ 1 row in set (0.00 sec)  mysql> select sleep (3); #不会生成慢查询日志 +-----------+ | sleep (3) | +-----------+ |         0 | +-----------+ 1 row in set (3.00 sec)  mysql> select sleep (4); +-----------+ | sleep (4) | +-----------+ |         0 | +-----------+ 1 row in set (4.00 sec)  mysql> SHOW VARIABLES LIKE 'slow_query_log_file';  #查看生成的慢查询日志目录 +---------------------+----------------------------+ | Variable_name       | Value                     | +---------------------+----------------------------+ | slow_query_log_file | /data/mysql/node3-slow.log | +---------------------+----------------------------+ 1 row in set (0.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

那么为接管 master 的主机继续充当 slave 角色并会指向到新的 master 上，作为其 slave

这时候按照之前的配置我们需要知道新的 master 上的 pos 的 id，但是我们无法确定新的 master 和 slave 之间差多少

当激活 GITD 之后

当 master 出现问题后，slave2 和 master 的数据最接近，会被作为新的 master

slave1 指向新的 master，但是他不会去检测新的 master 的 pos id，只需要继续读取自己 gtid_next 即可

#在master和slave中默认gtid模式是未开启的 mysql> show variables like '%gtid%'; +----------------------------------+-----------+ | Variable_name                   | Value     | +----------------------------------+-----------+ | binlog_gtid_simple_recovery     | ON       | | enforce_gtid_consistency         | OFF       | | gtid_executed                   |           | | gtid_executed_compression_period | 0         | | gtid_mode                       | OFF       | | gtid_next                       | AUTOMATIC | | gtid_owned                       |           | | gtid_purged                     |           | | session_track_gtids             | OFF       | +----------------------------------+-----------+ 9 rows in set (0.01 sec)   #在所有主机中加入参数 [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             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: 158             Relay_Log_Space: 586             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: 10                 Master_UUID: c8f5b42a-12b8-11f1-b001-000c29827403             Master_Info_File: mysql.slave_master_info                   SQL_Delay: 0         SQL_Remaining_Delay: NULL     Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 10                 Master_Bind:     Last_IO_Error_Timestamp:     Last_SQL_Error_Timestamp:               Master_SSL_Crl:           Master_SSL_Crlpath:           Retrieved_Gtid_Set:           Executed_Gtid_Set:               Auto_Position: 1         Replicate_Rewrite_DB:                 Channel_Name:           Master_TLS_Version:       Master_public_key_path:       Get_master_public_key: 0           Network_Namespace: 1 row in set, 1 warning (0.00 sec)  ERROR: No query specified

多线程回放

默认情况下 slave 中使用的是 sql 单线程回放

在 master 中时多用户读写，如果使用 sql 单线程回放那么会造成组从延迟严重

#在slave主机中默认回方日志时使用单线程回放 mysql> show processlist;

#开启多线程回放日志（在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）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（Relay log）。
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

步骤 1：Master 将写操作记录到二进制日志（binlog）。

步骤 2：Slave 将 Master 的 binary log events 拷贝到它的中继日志（relay log）；

步骤 3：Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化的，而且重启后从接入点开始复制。

具体操作

1.slaves 端中设置了 master 端的 ip，用户，日志，和日志的 Position，通过这些信息取得 master 的认证及信息

2.master 端在设定好 binlog 启动后会开启 binlog dump 的线程

3.master 端的 binlog dump 把二进制的更新发送到 slave 端的

4.slave 端开启两个线程，一个是 I/O 线程，一个是 sql 线程，

i/o 线程用于接收 master 端的二进制日志，此线程会在本地打开 relaylog 中继日志，并且保存到本地磁盘
sql 线程读取本地 relog 中继日志进行回放

5.什么时候我们需要多个 slave？

当读取的而操作远远高与写操作时。我们采用一主多从架构

数据库外层接入负载均衡层并搭配高可用机制

架构缺陷

主从架构采用的是异步机制

master 更新完成后直接发送二进制日志到 slave，但是 slaves 是否真正保存了数据 master 端不会检测

master 端直接保存二进制日志到磁盘

当 master 端到 slave 端的网络出现问题时或者 master 端直接挂掉，二进制日志可能根本没有到达 slave

master 出现问题 slave 端接管 master，这个过程中数据就丢失了

这样的问题出现就无法达到数据的强一致性，零数据丢失

半同步模式

用户线程写入完成后 master 中的 dump 会把日志推送到 slave 端
slave 中的 io 线程接收后保存到 relaylog 中继日志
保存完成后 slave 向 master 端返回 ack
在未接受到 slave 的 ack 时 master 端时不做提交的，一直处于等待当收到 ack 后提交到存储引擎
在 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 mysql> SHOW STATUS LIKE 'Rpl_semi_sync%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec) #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> 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_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (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) #测试： #在主库中 mysql> INSERT INTO hjw.userlist values ('user1','123'); Query OK, 1 row affected (10.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.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_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 2 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 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_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+ 14 rows 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.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 & 300 ~]# /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> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec)  mysql> START SLAVE IO_THREAD; 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. If you answer 'no' below, you will enter an interactive dialog for each configuration option instead.  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      ######################################## Warning: Permanently added '172.25.254.20' (ED25519) to the list of known hosts. mha4mysql-node-0.58-0.el7.centos.noarch.rpm                     100%   35KB  28.2MB/s   00:00 Verifying...                          ######################################## 准备中...                          ######################################## 正在升级/安装... mha4mysql-node-0.58-0.el7.centos      ######################################## Warning: Permanently added '172.25.254.30' (ED25519) to the list of known hosts. mha4mysql-node-0.58-0.el7.centos.noarch.rpm                     100%   35KB  20.1MB/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 199 #sub parse_mysql_major_version($) { 200 # my $str = shift; 201 # my $result = sprintf( '%03d%03d', $str =~ m/(\d+)/g ); 202 # return $result; 203 #}  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 # master_ip_failover_script= /script/masterha/master_ip_failover # shutdown_script= /script/masterha/power_manager # report_script= /script/masterha/send_report # master_ip_online_change_script= /script/masterha/master_ip_online_change [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 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri Feb 27 16:23:20 2026 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Fri Feb 27 16:23:20 2026 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Fri Feb 27 16:23:20 2026 - [info] Starting SSH connection tests.. Fri Feb 27 16:23:21 2026 - [debug] Fri Feb 27 16:23:20 2026 - [debug] Connecting via SSH from [email protected](172.25.254.10:22) to [email protected](172.25.254.20:22).. Fri Feb 27 16:23:20 2026 - [debug]   ok. Fri Feb 27 16:23:20 2026 - [debug] Connecting via SSH from [email protected](172.25.254.10:22) to [email protected](172.25.254.30:22).. Fri Feb 27 16:23:20 2026 - [debug]   ok. Fri Feb 27 16:23:21 2026 - [debug] Fri Feb 27 16:23:20 2026 - [debug] Connecting via SSH from [email protected](172.25.254.20:22) to [email protected](172.25.254.10:22).. Fri Feb 27 16:23:21 2026 - [debug]   ok. Fri Feb 27 16:23:21 2026 - [debug] Connecting via SSH from [email protected](172.25.254.20:22) to [email protected](172.25.254.30:22).. Fri Feb 27 16:23:21 2026 - [debug]   ok. Fri Feb 27 16:23:22 2026 - [debug] Fri Feb 27 16:23:21 2026 - [debug] Connecting via SSH from [email protected](172.25.254.30:22) to [email protected](172.25.254.10:22).. Fri Feb 27 16:23:21 2026 - [debug]   ok. Fri Feb 27 16:23:21 2026 - [debug] Connecting via SSH from [email protected](172.25.254.30:22) to [email protected](172.25.254.20:22).. Fri Feb 27 16:23:21 2026 - [debug]   ok. Fri Feb 27 16:23:22 2026 - [info] All SSH connection tests passed successfully. Use of uninitialized value in exit at /usr/bin/masterha_check_ssh line 44.     [root@mha mha4mysql-manager-0.58]# masterha_check_repl --conf=/etc/masterha/app1.cnf Fri Feb 27 16:23:50 2026 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri Feb 27 16:23:50 2026 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Fri Feb 27 16:23:50 2026 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Fri Feb 27 16:23:50 2026 - [info] MHA::MasterMonitor version 0.58. Fri Feb 27 16:23:51 2026 - [info] GTID failover mode = 1 Fri Feb 27 16:23:51 2026 - [info] Dead Servers: Fri Feb 27 16:23:51 2026 - [info] Alive Servers: Fri Feb 27 16:23:51 2026 - [info]   172.25.254.10(172.25.254.10:3306) Fri Feb 27 16:23:51 2026 - [info]   172.25.254.20(172.25.254.20:3306) Fri Feb 27 16:23:51 2026 - [info]   172.25.254.30(172.25.254.30: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 (oldest major version between slaves) log-bin:enabled Fri Feb 27 16:23:51 2026 - [info]     GTID ON Fri Feb 27 16:23:51 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Fri Feb 27 16:23:51 2026 - [info]     Primary candidate for the new Master (candidate_master is set) Fri Feb 27 16:23:51 2026 - [info]   172.25.254.30(172.25.254.30:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Fri Feb 27 16:23:51 2026 - [info]     GTID ON Fri Feb 27 16:23:51 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Fri Feb 27 16:23:51 2026 - [info]     Not candidate for the new Master (no_master is set) 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] Checking slave configurations.. Fri Feb 27 16:23:51 2026 - [info]  read_only=1 is not set on slave 172.25.254.20(172.25.254.20:3306). Fri Feb 27 16:23:51 2026 - [info]  read_only=1 is not set on slave 172.25.254.30(172.25.254.30:3306). Fri Feb 27 16:23:51 2026 - [info] Checking replication filtering settings.. Fri Feb 27 16:23:51 2026 - [info]  binlog_do_db= , binlog_ignore_db= Fri Feb 27 16:23:51 2026 - [info] Replication filtering check ok. Fri Feb 27 16:23:51 2026 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Fri Feb 27 16:23:51 2026 - [info] Checking SSH publickey authentication settings on the current master.. Fri Feb 27 16:23:51 2026 - [info] HealthCheck: SSH to 172.25.254.10 is reachable. Fri Feb 27 16:23:51 2026 - [info] 172.25.254.10(172.25.254.10:3306) (current master) +--172.25.254.20(172.25.254.20:3306) +--172.25.254.30(172.25.254.30:3306)  Fri Feb 27 16:23:51 2026 - [info] Checking replication health on 172.25.254.20.. Fri Feb 27 16:23:51 2026 - [info] ok. Fri Feb 27 16:23:51 2026 - [info] Checking replication health on 172.25.254.30.. Fri Feb 27 16:23:51 2026 - [info] ok. Fri Feb 27 16:23:51 2026 - [warning] master_ip_failover_script is not defined. Fri Feb 27 16:23:51 2026 - [warning] shutdown_script is not defined. Fri Feb 27 16:23:51 2026 - [info] Got exit code 0 (Not master dead).  MySQL Replication Health is OK.

二.集群切换操作

手动切换

master无故障切换

#默认状态 [root@mysql-node2 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 10 mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 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.000002         Read_Master_Log_Pos: 1968               Relay_Log_File: mysql-node2-relay-bin.000002               Relay_Log_Pos: 2185  [root@mysql-node3 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 15 mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 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.000002         Read_Master_Log_Pos: 1968               Relay_Log_File: mysql-node3-relay-bin.000002               Relay_Log_Pos: 2185       Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB:         Replicate_Ignore_DB: [root@mysql-node3 ~]#   #执行切换，把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] MHA::MasterRotate version 0.58. Sat Mar  7 10:21:01 2026 - [info] Starting online master switch.. Sat Mar  7 10:21:01 2026 - [info] Sat Mar  7 10:21:01 2026 - [info] * Phase 1: Configuration Check Phase.. Sat Mar  7 10:21:01 2026 - [info] Sat Mar  7 10:21:01 2026 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Mar  7 10:21:01 2026 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Sat Mar  7 10:21:01 2026 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Sat Mar  7 10:21:02 2026 - [info] GTID failover mode = 1 Sat Mar  7 10:21:02 2026 - [info] Current Alive Master: 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:21:02 2026 - [info] Alive Slaves: Sat Mar  7 10:21:02 2026 - [info]   172.25.254.20(172.25.254.20:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:21:02 2026 - [info]     GTID ON Sat Mar  7 10:21:02 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:21:02 2026 - [info]     Primary candidate for the new Master (candidate_master is set) Sat Mar  7 10:21:02 2026 - [info]   172.25.254.30(172.25.254.30:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:21:02 2026 - [info]     GTID ON Sat Mar  7 10:21:02 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:21:02 2026 - [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 172.25.254.10(172.25.254.10:3306)? (YES/no): yes     #输入内容 Sat Mar  7 10:21:28 2026 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Sat Mar  7 10:21:28 2026 - [info] ok. Sat Mar  7 10:21:28 2026 - [info] Checking MHA is not monitoring or doing failover.. Sat Mar  7 10:21:28 2026 - [info] Checking replication health on 172.25.254.20.. Sat Mar  7 10:21:28 2026 - [info] ok. Sat Mar  7 10:21:28 2026 - [info] Checking replication health on 172.25.254.30.. Sat Mar  7 10:21:28 2026 - [info] ok. Sat Mar  7 10:21:28 2026 - [info] 172.25.254.20 can be new master. Sat Mar  7 10:21:28 2026 - [info] From: 172.25.254.10(172.25.254.10:3306) (current master) +--172.25.254.20(172.25.254.20:3306) +--172.25.254.30(172.25.254.30:3306)  To: 172.25.254.20(172.25.254.20:3306) (new master) +--172.25.254.30(172.25.254.30:3306) +--172.25.254.10(172.25.254.10:3306)  Starting master switch from 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306)? (yes/NO): yes #输入内容 Sat Mar  7 10:21:34 2026 - [info] Checking whether 172.25.254.20(172.25.254.20:3306) is ok for the new master.. Sat Mar  7 10:21:34 2026 - [info] ok. Sat Mar  7 10:21:34 2026 - [info] 172.25.254.10(172.25.254.10:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Sat Mar  7 10:21:34 2026 - [info] 172.25.254.10(172.25.254.10:3306): Resetting slave pointing to the dummy host. Sat Mar  7 10:21:34 2026 - [info] ** Phase 1: Configuration Check Phase completed. Sat Mar  7 10:21:34 2026 - [info] Sat Mar  7 10:21:34 2026 - [info] * Phase 2: Rejecting updates Phase.. Sat Mar  7 10:21:34 2026 - [info] Sat Mar  7 10:21:34 2026 - [info] Executing master ip online change script to disable write on the current master: Sat Mar  7 10:21:34 2026 - [info]   /etc/masterha/scripts/master_ip_online_change --command=stop --orig_master_host=172.25.254.10 --orig_master_ip=172.25.254.10 --orig_master_port=3306 --orig_master_user='root' --new_master_host=172.25.254.20 --new_master_ip=172.25.254.20 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx    *************************************************************** Disabling the VIP - 172.25.254.100/24 on old master: 172.25.254.10 ***************************************************************    Error: ipv4: Address not found. Sat Mar  7 10:21:34 2026 - [info] ok. Sat Mar  7 10:21:34 2026 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Sat Mar  7 10:21:34 2026 - [info] Executing FLUSH TABLES WITH READ LOCK.. Sat Mar  7 10:21:34 2026 - [info] ok. Sat Mar  7 10:21:34 2026 - [info] Orig master binlog:pos is mysql-bin.000002:1968. Sat Mar  7 10:21:34 2026 - [info] Waiting to execute all relay logs on 172.25.254.20(172.25.254.20:3306).. Sat Mar  7 10:21:34 2026 - [info] master_pos_wait(mysql-bin.000002:1968) completed on 172.25.254.20(172.25.254.20:3306). Executed 0 events. Sat Mar  7 10:21:34 2026 - [info]   done. Sat Mar  7 10:21:34 2026 - [info] Getting new master's binlog name and position.. Sat Mar  7 10:21:34 2026 - [info] mysql-bin.000002:2337 Sat Mar  7 10:21:34 2026 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='lee', MASTER_PASSWORD='xxx'; Sat Mar  7 10:21:34 2026 - [info] Executing master ip online change script to allow write on the new master: Sat Mar  7 10:21:34 2026 - [info]   /etc/masterha/scripts/master_ip_online_change --command=start --orig_master_host=172.25.254.10 --orig_master_ip=172.25.254.10 --orig_master_port=3306 --orig_master_user='root' --new_master_host=172.25.254.20 --new_master_ip=172.25.254.20 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root   --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx    *************************************************************** Enabling the VIP - 172.25.254.100/24 on new master: 172.25.254.20 ***************************************************************    Sat Mar  7 10:21:34 2026 - [info] ok. Sat Mar  7 10:21:34 2026 - [info] Sat Mar  7 10:21:34 2026 - [info] * Switching slaves in parallel.. Sat Mar  7 10:21:34 2026 - [info] Sat Mar  7 10:21:34 2026 - [info] -- Slave switch on host 172.25.254.30(172.25.254.30:3306) started, pid: 1654 Sat Mar  7 10:21:34 2026 - [info] Sat Mar  7 10:21:35 2026 - [info] Log messages from 172.25.254.30 ... Sat Mar  7 10:21:35 2026 - [info] Sat Mar  7 10:21:34 2026 - [info] Waiting to execute all relay logs on 172.25.254.30(172.25.254.30:3306).. Sat Mar  7 10:21:34 2026 - [info] master_pos_wait(mysql-bin.000002:1968) completed on 172.25.254.30(172.25.254.30:3306). Executed 0 events. Sat Mar  7 10:21:34 2026 - [info]   done. Sat Mar  7 10:21:34 2026 - [info] Resetting slave 172.25.254.30(172.25.254.30:3306) and starting replication from the new master 172.25.254.20(172.25.254.20:3306).. Sat Mar  7 10:21:34 2026 - [info] Executed CHANGE MASTER. Sat Mar  7 10:21:34 2026 - [info] Slave started. Sat Mar  7 10:21:35 2026 - [info] End of log messages from 172.25.254.30 ... Sat Mar  7 10:21:35 2026 - [info] Sat Mar  7 10:21:35 2026 - [info] -- Slave switch on host 172.25.254.30(172.25.254.30:3306) succeeded. Sat Mar  7 10:21:35 2026 - [info] Unlocking all tables on the orig master: Sat Mar  7 10:21:35 2026 - [info] Executing UNLOCK TABLES.. Sat Mar  7 10:21:35 2026 - [info] ok. Sat Mar  7 10:21:35 2026 - [info] Starting orig master as a new slave.. Sat Mar  7 10:21:35 2026 - [info] Resetting slave 172.25.254.10(172.25.254.10:3306) and starting replication from the new master 172.25.254.20(172.25.254.20:3306).. Sat Mar  7 10:21:35 2026 - [info] Executed CHANGE MASTER. Sat Mar  7 10:21:35 2026 - [info] Slave started. Sat Mar  7 10:21:35 2026 - [info] All new slave servers switched successfully. Sat Mar  7 10:21:35 2026 - [info] Sat Mar  7 10:21:35 2026 - [info] * Phase 5: New master cleanup phase.. Sat Mar  7 10:21:35 2026 - [info] Sat Mar  7 10:21:35 2026 - [info]  172.25.254.20: Resetting slave info succeeded. Sat Mar  7 10:21:35 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 mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                 Master_Host: 172.25.254.20                 Master_User: lee                 Master_Port: 3306               Connect_Retry: 60             Master_Log_File: mysql-bin.000002         Read_Master_Log_Pos: 2337               Relay_Log_File: mysql-node1-relay-bin.000002               Relay_Log_Pos: 742       Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB:         Replicate_Ignore_DB:   [root@mysql-node3 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 15 mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                 Master_Host: 172.25.254.20                 Master_User: lee                 Master_Port: 3306               Connect_Retry: 60             Master_Log_File: mysql-bin.000002         Read_Master_Log_Pos: 2337               Relay_Log_File: mysql-node3-relay-bin.000002               Relay_Log_Pos: 742       Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB:         Replicate_Ignore_DB:

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 --dead_master_ip=<dead_master_ip> is not set. Using 172.25.254.10. Sat Mar  7 10:26:01 2026 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Mar  7 10:26:01 2026 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Sat Mar  7 10:26:01 2026 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Sat Mar  7 10:26:01 2026 - [info] MHA::MasterFailover version 0.58. Sat Mar  7 10:26:01 2026 - [info] Starting master failover. Sat Mar  7 10:26:01 2026 - [info] Sat Mar  7 10:26:01 2026 - [info] * Phase 1: Configuration Check Phase.. Sat Mar  7 10:26:01 2026 - [info] Sat Mar  7 10:26:02 2026 - [info] GTID failover mode = 1 Sat Mar  7 10:26:02 2026 - [info] Dead Servers: Sat Mar  7 10:26:02 2026 - [info]   172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:02 2026 - [info] Checking master reachability via MySQL(double check)... Sat Mar  7 10:26:02 2026 - [info] ok. Sat Mar  7 10:26:02 2026 - [info] Alive Servers: Sat Mar  7 10:26:02 2026 - [info]   172.25.254.20(172.25.254.20:3306) Sat Mar  7 10:26:02 2026 - [info]   172.25.254.30(172.25.254.30:3306) Sat Mar  7 10:26:02 2026 - [info] Alive Slaves: Sat Mar  7 10:26:02 2026 - [info]   172.25.254.20(172.25.254.20:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:26:02 2026 - [info]     GTID ON Sat Mar  7 10:26:02 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:02 2026 - [info]     Primary candidate for the new Master (candidate_master is set) Sat Mar  7 10:26:02 2026 - [info]   172.25.254.30(172.25.254.30:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:26:02 2026 - [info]     GTID ON Sat Mar  7 10:26:02 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:02 2026 - [info]     Not candidate for the new Master (no_master is set) Master 172.25.254.10(172.25.254.10:3306) is dead. Proceed? (yes/NO): yes #输入内容 Sat Mar  7 10:26:08 2026 - [info] Starting GTID based failover. Sat Mar  7 10:26:08 2026 - [info] Sat Mar  7 10:26:08 2026 - [info] ** Phase 1: Configuration Check Phase completed. Sat Mar  7 10:26:08 2026 - [info] Sat Mar  7 10:26:08 2026 - [info] * Phase 2: Dead Master Shutdown Phase.. Sat Mar  7 10:26:08 2026 - [info] Sat Mar  7 10:26:08 2026 - [info] HealthCheck: SSH to 172.25.254.10 is reachable. Sat Mar  7 10:26:09 2026 - [info] Forcing shutdown so that applications never connect to the current master.. Sat Mar  7 10:26:09 2026 - [info] Executing master IP deactivation script: Sat Mar  7 10:26:09 2026 - [info]   /etc/masterha/scripts/master_ip_failover --orig_master_host=172.25.254.10 --orig_master_ip=172.25.254.10 --orig_master_port=3306 --command=stopssh --ssh_user=root   IN SCRIPT TEST====/sbin/ip addr del 172.25.254.100/24 dev eth0==/sbin/ip addr add 172.25.254.100/24 dev eth0===  Disabling the VIP on old master: 172.25.254.10 Sat Mar  7 10:26:09 2026 - [info]  done. Sat Mar  7 10:26:09 2026 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Sat Mar  7 10:26:09 2026 - [info] * Phase 2: Dead Master Shutdown Phase completed. Sat Mar  7 10:26:09 2026 - [info] Sat Mar  7 10:26:09 2026 - [info] * Phase 3: Master Recovery Phase.. Sat Mar  7 10:26:09 2026 - [info] Sat Mar  7 10:26:09 2026 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Sat Mar  7 10:26:09 2026 - [info] Sat Mar  7 10:26:09 2026 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:2295 Sat Mar  7 10:26:09 2026 - [info] Latest slaves (Slaves that received relay log files to the latest): Sat Mar  7 10:26:09 2026 - [info]   172.25.254.20(172.25.254.20:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:26:09 2026 - [info]     GTID ON Sat Mar  7 10:26:09 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:09 2026 - [info]     Primary candidate for the new Master (candidate_master is set) Sat Mar  7 10:26:09 2026 - [info]   172.25.254.30(172.25.254.30:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:26:09 2026 - [info]     GTID ON Sat Mar  7 10:26:09 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:09 2026 - [info]     Not candidate for the new Master (no_master is set) Sat Mar  7 10:26:09 2026 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:2295 Sat Mar  7 10:26:09 2026 - [info] Oldest slaves: Sat Mar  7 10:26:09 2026 - [info]   172.25.254.20(172.25.254.20:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:26:09 2026 - [info]     GTID ON Sat Mar  7 10:26:09 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:09 2026 - [info]     Primary candidate for the new Master (candidate_master is set) Sat Mar  7 10:26:09 2026 - [info]   172.25.254.30(172.25.254.30:3306)  Version=8.3.0 (oldest major version between slaves) log-bin:enabled Sat Mar  7 10:26:09 2026 - [info]     GTID ON Sat Mar  7 10:26:09 2026 - [info]     Replicating from 172.25.254.10(172.25.254.10:3306) Sat Mar  7 10:26:09 2026 - [info]     Not candidate for the new Master (no_master is set) Sat Mar  7 10:26:09 2026 - [info] Sat Mar  7 10:26:09 2026 - [info] * Phase 3.3: Determining New Master Phase.. Sat Mar  7 10:26:09 2026 - [info] Sat Mar  7 10:26:09 2026 - [info] 172.25.254.20 can be new master. Sat Mar  7 10:26:09 2026 - [info] New master is 172.25.254.20(172.25.254.20:3306) Sat Mar  7 10:26:09 2026 - [info] Starting master failover.. Sat Mar  7 10:26:09 2026 - [info] From: 172.25.254.10(172.25.254.10:3306) (current master) +--172.25.254.20(172.25.254.20:3306) +--172.25.254.30(172.25.254.30:3306)  To: 172.25.254.20(172.25.254.20:3306) (new master) +--172.25.254.30(172.25.254.30:3306)  Starting master switch from 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306)? (yes/NO): yes #输入内容 Sat Mar  7 10:26:13 2026 - [info] New master decided manually is 172.25.254.20(172.25.254.20:3306) Sat Mar  7 10:26:13 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] * Phase 3.3: New Master Recovery Phase.. Sat Mar  7 10:26:13 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] Waiting all logs to be applied.. Sat Mar  7 10:26:13 2026 - [info]   done. Sat Mar  7 10:26:13 2026 - [info] Getting new master's binlog name and position.. Sat Mar  7 10:26:13 2026 - [info] mysql-bin.000002:2337 Sat Mar  7 10:26:13 2026 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='lee', MASTER_PASSWORD='xxx'; Sat Mar  7 10:26:13 2026 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000002, 2337, 1b73d49c-19c8-11f1-a11b-000c29e84b64:1, b9652041-19c7-11f1-a4c3-000c29f4a60c:1-7 Sat Mar  7 10:26:13 2026 - [info] Executing master IP activate script: Sat Mar  7 10:26:13 2026 - [info]   /etc/masterha/scripts/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.25.254.10 --orig_master_ip=172.25.254.10 --orig_master_port=3306 --new_master_host=172.25.254.20 --new_master_ip=172.25.254.20 --new_master_port=3306 --new_master_user='root'   --new_master_password=xxx Unknown option: new_master_user Unknown option: new_master_password   IN SCRIPT TEST====/sbin/ip addr del 172.25.254.100/24 dev eth0==/sbin/ip addr add 172.25.254.100/24 dev eth0===  Enabling the VIP - 172.25.254.100/24 on the new master - 172.25.254.20 Sat Mar  7 10:26:13 2026 - [info] OK. Sat Mar  7 10:26:13 2026 - [info] Setting read_only=0 on 172.25.254.20(172.25.254.20:3306).. Sat Mar  7 10:26:13 2026 - [info] ok. Sat Mar  7 10:26:13 2026 - [info] ** Finished master recovery successfully. Sat Mar  7 10:26:13 2026 - [info] * Phase 3: Master Recovery Phase completed. Sat Mar  7 10:26:13 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] * Phase 4: Slaves Recovery Phase.. Sat Mar  7 10:26:13 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] * Phase 4.1: Starting Slaves in parallel.. Sat Mar  7 10:26:13 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] -- Slave recovery on host 172.25.254.30(172.25.254.30:3306) started, pid: 1681. Check tmp log /etc/masterha/172.25.254.30_3306_20260307102601.log if it takes time.. Sat Mar  7 10:26:14 2026 - [info] Sat Mar  7 10:26:14 2026 - [info] Log messages from 172.25.254.30 ... Sat Mar  7 10:26:14 2026 - [info] Sat Mar  7 10:26:13 2026 - [info] Resetting slave 172.25.254.30(172.25.254.30:3306) and starting replication from the new master 172.25.254.20(172.25.254.20:3306).. Sat Mar  7 10:26:13 2026 - [info] Executed CHANGE MASTER. Sat Mar  7 10:26:13 2026 - [info] Slave started. Sat Mar  7 10:26:13 2026 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln974] gtid_wait(1b73d49c-19c8-11f1-a11b-000c29e84b64:1, b9652041-19c7-11f1-a4c3-000c29f4a60c:1-7) returned NULL on 172.25.254.30(172.25.254.30:3306). Maybe SQL thread was aborted? Sat Mar  7 10:26:14 2026 - [info] End of log messages from 172.25.254.30. Sat Mar  7 10:26:14 2026 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln2045] Master failover to 172.25.254.20(172.25.254.20:3306) done, but recovery on slave partially failed. Sat Mar  7 10:26:14 2026 - [info]  ----- Failover Report -----  app1: MySQL Master failover 172.25.254.10(172.25.254.10:3306) to 172.25.254.20(172.25.254.20:3306)  Master 172.25.254.10(172.25.254.10:3306) is down!  Check MHA Manager logs at mha for details.  Started manual(interactive) failover. Invalidated master IP address on 172.25.254.10(172.25.254.10:3306) Selected 172.25.254.20(172.25.254.20:3306) as a new master. 172.25.254.20(172.25.254.20:3306): OK: Applying all logs succeeded. 172.25.254.20(172.25.254.20:3306): OK: Activated master IP address. 172.25.254.30(172.25.254.30:3306): ERROR: Failed on waiting gtid exec set on master. Master failover to 172.25.254.20(172.25.254.20:3306) done, but recovery on slave partially failed.   #查看切换信息 [root@mysql-node3 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 15 mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                 Master_Host: 172.25.254.20                 Master_User: lee                 Master_Port: 3306               Connect_Retry: 60             Master_Log_File: mysql-bin.000002         Read_Master_Log_Pos: 2337               Relay_Log_File: mysql-node3-relay-bin.000002               Relay_Log_Pos: 422       Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB:         Replicate_Ignore_DB:   #故障恢复 #当出现故障切换后，mha主机中会出现切换锁文件，当文件存在后不能再次执行切换 [root@mha ~]# ls /etc/masterha/ app1.cnf app1.failover.complete mha.log scripts | #锁文件    [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 Starting MySQL. SUCCESS!  mysql -uroot -phjw -e "CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='lee', MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;"  [root@mysql-node1 ~]# mysql -uroot -phjw -e "start slave;" [root@mysql-node1 ~]# mysql -uroot -phjw -e "show slave status\G;" | head -n 15 mysql: [Warning] Using a password on the command line interface can be insecure. *************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                 Master_Host: 172.25.254.20                 Master_User: lee                 Master_Port: 3306               Connect_Retry: 60             Master_Log_File: mysql-bin.000002         Read_Master_Log_Pos: 2337               Relay_Log_File: mysql-node1-relay-bin.000002               Relay_Log_Pos: 422       Relay_Master_Log_File: mysql-bin.000002             Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB:         Replicate_Ignore_DB:

自动切换

注意：

在主配置文件中：vim /etc/masterha/app1.cnf

监控ip要有mysql服务

#为了方便观察建议开启两个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 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000   link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00   inet 127.0.0.1/8 scope host lo       valid_lft forever preferred_lft forever   inet6 ::1/128 scope host       valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000   link/ether 00:0c:29:e8:4b:64 brd ff:ff:ff:ff:ff:ff   altname enp3s0   altname ens160   inet 172.25.254.20/24 brd 172.25.254.255 scope global noprefixroute eth0       valid_lft forever preferred_lft forever   inet 172.25.254.100/24 scope global secondary eth0       valid_lft forever preferred_lft forever   inet6 fe80::f8be:d443:72d7:d336/64 scope link noprefixroute       valid_lft forever preferred_lft forever