MySQL 的源码编译安装、主从复制配置、GTID 模式、半同步复制以及基于 MHA 的高可用集群搭建。内容涵盖从基础环境准备到延迟复制、慢查询日志优化，再到故障切换与 VIP 漂移的完整流程，旨在解决单节点性能瓶颈与数据可靠性问题，实现高可用架构。
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
[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
[root@mysql-node1 ~]# mysqld --initialize --user=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
[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
# ... (同上安全初始化流程)
[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
[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)
#在 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 #通过同步过来的数据做日志回访成功
#在 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)
#模拟一主一从中已经存在数据情况
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)
#在 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
#在 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)
延迟复制时用来控制 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)
#慢查询日志是否开启
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);
当为启用 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 个线程来操作，一个主库线程，两个从库线程。
复制三步骤
主从架构采用的是异步机制。master 更新完成后直接发送二进制日志到 slave，但是 slaves 是否真正保存了数据 master 端不会检测。当 master 端到 slave 端的网络出现问题时或者 master 端直接挂掉，二进制日志可能根本没有到达 slave。master 出现问题 slave 端接管 master，这个过程中数据就丢失了。这样的问题出现就无法达到数据的强一致性，零数据丢失。
#在所有主机中操作
#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)
#在 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)
[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
[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
[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;
}
#在 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)
[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
#默认状态
[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
[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
[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
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML转Markdown 互为补充。 在线工具，Markdown转HTML在线工具，online