一、准备
安装mha 架构的mysql
mysql使用 5.7.27
测试资源：
172.21.27.29
172.21.27.30 master
172.21.27.31
二、master部署
- 路径配置
- 环境配置
- mysql安装
- my.cnf
- Systemd配置
- 初始化mysql
- 启动mysql
- 配置root密码
三、slave01部署
- 路径配置
cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.27-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
mkdir -p /data/mysql/
touch /var/log/mysqld.log
mkdir -p /var/lib/mysql/
cd /data/mysql/
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /var/lib/mysql/
chown -R mysql:mysql /var/log/mysqld.log
./bin/mysqld --initialize --user=mysql --datadir=/data/mysql/data
# generated for root@localhost : *vqdaUAN9szq
# ./bin/mysqld_safe --datadir=/data/disk1/mysql/data &
- mysql用户配置
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /var/lib/mysql/
chown -R mysql:mysql /var/log/mysqld.log
- mysql安装
cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.27-linux-glibc2.12-x86_64 mysql
- my.cnf
cat > /etc/my.cnf << EOF
[mysqld]
super_read_only = ON
report-host=172.21.27.30
user = mysql
port = 3306
# 主从复制配置
server_id=4
relay-log=mysql-relay-bin
log-slave-updates=ON
gtid-mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
log_bin=/data/mysql/mysql-bin
binlog-format = row
expire_logs_days=7
max_binlog_size = 500M
datadir=/data/mysql/data/
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
# pid-file= /var/ run/mysqld/mysqld. pid
character_set_server=utf8
EOF
- Systemd配置
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.7 Database Server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
- 初始化mysql
./bin/mysqld --initialize --user=mysql --datadir=/data/mysql/data
- 启动mysql
systemctl daemon-reload && systemctl start mysqld
# 配置环境变量
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
- 配置root密码
# 重置 local root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
create user 'root'@'%' identified by '123';
grant all on * . * to root with grant option;
FLUSH PRIVILEGES;
EXIT;
# 创建 % root的密码
create user 'root'@'%' identified by '123';
grant all on * . * to root with grant option;
flush privileges;
show grants for 'root'@'%';
四、slave02部署
- 路径配置
- 环境配置
- mysql安装
- my.cnf
- Systemd配置
- 初始化mysql
- 启动mysql
- 配置root密码
五、主从配置
#
# 全备数据
mysqldump -h172.21.27.30 -uroot -p'123' --single-transaction --all-databases --flush-logs > ./all_backup_01.sql
#
mysql -h127.0.0.1 -uroot -p'123'
mysql -h172.21.27.30 -uroot -p'123'
mysql -h172.21.27.29 -uroot -p'123'
#
SHOW VARIABLES LIKE '%read_only';
# slave上配置复制信息
set global read_only=0;
#
flush logs;
# 配置主从信息
# CHANGE MASTER TO MASTER_HOST='172.21.27.30' ,MASTER_USER='replica' ,MASTER_PASSWORD='Asdf@1234',master_auto_position=1;
CHANGE MASTER TO MASTER_HOST='172.21.27.30' ,MASTER_USER='root' ,MASTER_PASSWORD='123',master_auto_position=1;
# 启动slave，并启动复制
start slave;
六、mha组件安装与配置
-
安装1个manager
# 只需要在一个节点上安装mha4mysql-manager-0.57-0.el7.noarch.rpm
yum install -y mha4mysql-manager-0.57-0.el7.noarch.rpm
-
安装3个node
# 安装node ，在3个节点上都要安装
yum install -y mha4mysql-node-0.57-0.el7.noarch.rpm
-
初始化路径
#
mkdir -p /etc/mha_master
-
manager 中的default 配置
#
vi /etc/masterha_default.cnf
[server default]
user=root
password=123
manager_workdir=/etc/mha_master
manager_log=/etc/mha_master/manager.log
remote_workdir=/etc/mha_master
ssh_user=root
repl_user=root
repl_password=123
-
manager中的mha配置
#
vi /etc/mha_master/mha.cnf
[server default]
user=root
password=123
manager_workdir=/etc/mha_master
manager_log=/etc/mha_master/manager.log
remote_workdir=/etc/mha_master
ssh_user=root
repl_user=root
repl_password=123
ping_interval=1
[server1]
hostname=172.21.27.30
ssh_port=22
candidate_master=1
[server2]
hostname=172.21.27.29
ssh_port=22
candidate_master=1
[server3]
hostname=172.21.27.31
ssh_port=22
candidate_master=0
-
验证mha的ssh
# 验证ssh连通性
/usr/bin/masterha_check_ssh --conf=/etc/mha_master/mha.cnf
# 验证mysql的服务状态
masterha_check_repl --conf=/etc/mha_master/mha.cnf
-
启动mha
# 启动mha
nohup masterha_manager --conf=/etc/mha_master/mha.cnf &> /etc/mha_master/manager.log &
[root@xfzcvmmw185022 mha_master]# nohup masterha_manager --conf=/etc/mha_master/mha.cnf &> /etc/mha_master/manager.log &
[1] 20914
[root@xfzcvmmw185022 mha_master]# masterha_check_status --conf=/etc/mha_master/mha.cnf
mha (pid:20914) is running(0:PING_OK), master:172.21.27.31
[root@xfzcvmmw185022 mha_master]#
[root@xfzcvmmw185022 mha_master]#
-
观察mha的日志
Wed Apr 23 11:51:20 2025 - [info] Reading default configuration from /etc/masterha_default.cnf..
Wed Apr 23 11:51:20 2025 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Apr 23 11:51:20 2025 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Apr 23 11:51:20 2025 - [info] MHA::MasterMonitor version 0.57.
Wed Apr 23 11:51:21 2025 - [info] GTID failover mode = 1
Wed Apr 23 11:51:21 2025 - [info] Dead Servers:
Wed Apr 23 11:51:21 2025 - [info] Alive Servers:
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.29(172.21.27.29:3306)
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:51:21 2025 - [info] Alive Slaves:
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.29(172.21.27.29:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:51:21 2025 - [info] GTID ON
Wed Apr 23 11:51:21 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:51:21 2025 - [info] GTID ON
Wed Apr 23 11:51:21 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:51:21 2025 - [info] Current Alive Master: 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] Checking slave configurations..
Wed Apr 23 11:51:21 2025 - [info] Checking replication filtering settings..
Wed Apr 23 11:51:21 2025 - [info] binlog_do_db= , binlog_ignore_db=
Wed Apr 23 11:51:21 2025 - [info] Replication filtering check ok.
Wed Apr 23 11:51:21 2025 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Wed Apr 23 11:51:21 2025 - [info] Checking SSH publickey authentication settings on the current master..
Wed Apr 23 11:51:21 2025 - [warning] HealthCheck: SSH to 172.21.27.30 is NOT reachable.
Wed Apr 23 11:51:21 2025 - [info]
172.21.27.30(172.21.27.30:3306) (current master)
+--172.21.27.29(172.21.27.29:3306)
+--172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:51:21 2025 - [warning] master_ip_failover_script is not defined.
Wed Apr 23 11:51:21 2025 - [warning] shutdown_script is not defined.
Wed Apr 23 11:51:21 2025 - [info] Set master ping interval 1 seconds.
Wed Apr 23 11:51:21 2025 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability fro
m two or more routes.
Wed Apr 23 11:51:21 2025 - [info] Starting ping health check on 172.21.27.30(172.21.27.30:3306)..
Wed Apr 23 11:51:21 2025 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
-
检查mysql master状态
#
# 启动成功后检查 master状态
masterha_check_status --conf=/etc/mha_master/mha.cnf
七、验证
- 在manager上tail mha日志
#
tail -f /etc/mha_master/manager.log
- 将master 27.30 的mysql kill掉
kill -9 [mysql pid]
-
观察日志
-
日志输出
nohup: 忽略输入
Wed Apr 23 11:51:20 2025 - [info] Reading default configuration from /etc/masterha_default.cnf..
Wed Apr 23 11:51:20 2025 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Apr 23 11:51:20 2025 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Apr 23 11:51:20 2025 - [info] MHA::MasterMonitor version 0.57.
Wed Apr 23 11:51:21 2025 - [info] GTID failover mode = 1
Wed Apr 23 11:51:21 2025 - [info] Dead Servers:
Wed Apr 23 11:51:21 2025 - [info] Alive Servers:
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.29(172.21.27.29:3306)
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:51:21 2025 - [info] Alive Slaves:
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.29(172.21.27.29:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:51:21 2025 - [info] GTID ON
Wed Apr 23 11:51:21 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:51:21 2025 - [info] GTID ON
Wed Apr 23 11:51:21 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:51:21 2025 - [info] Current Alive Master: 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:51:21 2025 - [info] Checking slave configurations..
Wed Apr 23 11:51:21 2025 - [info] Checking replication filtering settings..
Wed Apr 23 11:51:21 2025 - [info] binlog_do_db= , binlog_ignore_db=
Wed Apr 23 11:51:21 2025 - [info] Replication filtering check ok.
Wed Apr 23 11:51:21 2025 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Wed Apr 23 11:51:21 2025 - [info] Checking SSH publickey authentication settings on the current master..
Wed Apr 23 11:51:21 2025 - [warning] HealthCheck: SSH to 172.21.27.30 is NOT reachable.
Wed Apr 23 11:51:21 2025 - [info]
172.21.27.30(172.21.27.30:3306) (current master)
+--172.21.27.29(172.21.27.29:3306)
+--172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:51:21 2025 - [warning] master_ip_failover_script is not defined.
Wed Apr 23 11:51:21 2025 - [warning] shutdown_script is not defined.
Wed Apr 23 11:51:21 2025 - [info] Set master ping interval 1 seconds.
Wed Apr 23 11:51:21 2025 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Wed Apr 23 11:51:21 2025 - [info] Starting ping health check on 172.21.27.30(172.21.27.30:3306)..
Wed Apr 23 11:51:21 2025 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Wed Apr 23 11:53:29 2025 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Wed Apr 23 11:53:29 2025 - [info] Executing SSH check script: exit 0
Wed Apr 23 11:53:29 2025 - [warning] HealthCheck: SSH to 172.21.27.30 is NOT reachable.
Wed Apr 23 11:53:30 2025 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.21.27.30' (111))
Wed Apr 23 11:53:30 2025 - [warning] Connection failed 2 time(s)..
Wed Apr 23 11:53:31 2025 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.21.27.30' (111))
Wed Apr 23 11:53:31 2025 - [warning] Connection failed 3 time(s)..
Wed Apr 23 11:53:32 2025 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.21.27.30' (111))
Wed Apr 23 11:53:32 2025 - [warning] Connection failed 4 time(s)..
Wed Apr 23 11:53:32 2025 - [warning] Master is not reachable from health checker!
Wed Apr 23 11:53:32 2025 - [warning] Master 172.21.27.30(172.21.27.30:3306) is not reachable!
Wed Apr 23 11:53:32 2025 - [warning] SSH is NOT reachable.
Wed Apr 23 11:53:32 2025 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha_master/mha.cnf again, and trying to connect to all servers to check server status..
Wed Apr 23 11:53:32 2025 - [info] Reading default configuration from /etc/masterha_default.cnf..
Wed Apr 23 11:53:32 2025 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Apr 23 11:53:32 2025 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Apr 23 11:53:33 2025 - [info] GTID failover mode = 1
Wed Apr 23 11:53:33 2025 - [info] Dead Servers:
Wed Apr 23 11:53:33 2025 - [info] 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:33 2025 - [info] Alive Servers:
Wed Apr 23 11:53:33 2025 - [info] 172.21.27.29(172.21.27.29:3306)
Wed Apr 23 11:53:33 2025 - [info] 172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:53:33 2025 - [info] Alive Slaves:
Wed Apr 23 11:53:33 2025 - [info] 172.21.27.29(172.21.27.29:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:33 2025 - [info] GTID ON
Wed Apr 23 11:53:33 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:33 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:33 2025 - [info] GTID ON
Wed Apr 23 11:53:33 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:33 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:53:33 2025 - [info] Checking slave configurations..
Wed Apr 23 11:53:33 2025 - [info] Checking replication filtering settings..
Wed Apr 23 11:53:33 2025 - [info] Replication filtering check ok.
Wed Apr 23 11:53:33 2025 - [info] Master is down!
Wed Apr 23 11:53:33 2025 - [info] Terminating monitoring script.
Wed Apr 23 11:53:33 2025 - [info] Got exit code 20 (Master dead).
Wed Apr 23 11:53:33 2025 - [info] MHA::MasterFailover version 0.57.
Wed Apr 23 11:53:33 2025 - [info] Starting master failover.
Wed Apr 23 11:53:33 2025 - [info]
Wed Apr 23 11:53:33 2025 - [info] * Phase 1: Configuration Check Phase..
Wed Apr 23 11:53:33 2025 - [info]
Wed Apr 23 11:53:34 2025 - [info] GTID failover mode = 1
Wed Apr 23 11:53:34 2025 - [info] Dead Servers:
Wed Apr 23 11:53:34 2025 - [info] 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:34 2025 - [info] Checking master reachability via MySQL(double check)...
Wed Apr 23 11:53:34 2025 - [info] ok.
Wed Apr 23 11:53:34 2025 - [info] Alive Servers:
Wed Apr 23 11:53:34 2025 - [info] 172.21.27.29(172.21.27.29:3306)
Wed Apr 23 11:53:34 2025 - [info] 172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:53:34 2025 - [info] Alive Slaves:
Wed Apr 23 11:53:34 2025 - [info] 172.21.27.29(172.21.27.29:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:34 2025 - [info] GTID ON
Wed Apr 23 11:53:34 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:34 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:34 2025 - [info] GTID ON
Wed Apr 23 11:53:34 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:34 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:53:34 2025 - [info] Starting GTID based failover.
Wed Apr 23 11:53:34 2025 - [info]
Wed Apr 23 11:53:34 2025 - [info] ** Phase 1: Configuration Check Phase completed.
Wed Apr 23 11:53:34 2025 - [info]
Wed Apr 23 11:53:34 2025 - [info] * Phase 2: Dead Master Shutdown Phase..
Wed Apr 23 11:53:34 2025 - [info]
Wed Apr 23 11:53:34 2025 - [info] Forcing shutdown so that applications never connect to the current master..
Wed Apr 23 11:53:34 2025 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Wed Apr 23 11:53:34 2025 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Wed Apr 23 11:53:35 2025 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] * Phase 3: Master Recovery Phase..
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] The latest binary log file/position on all slaves is mysql-bin.000003:451
Wed Apr 23 11:53:35 2025 - [info] Retrieved Gtid Set: 14b4dd95-e843-11ef-b6ea-005056ac7e76:1-6
Wed Apr 23 11:53:35 2025 - [info] Latest slaves (Slaves that received relay log files to the latest):
Wed Apr 23 11:53:35 2025 - [info] 172.21.27.29(172.21.27.29:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:35 2025 - [info] GTID ON
Wed Apr 23 11:53:35 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:35 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:35 2025 - [info] GTID ON
Wed Apr 23 11:53:35 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:35 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:53:35 2025 - [info] The oldest binary log file/position on all slaves is mysql-bin.000003:451
Wed Apr 23 11:53:35 2025 - [info] Retrieved Gtid Set: 14b4dd95-e843-11ef-b6ea-005056ac7e76:1-6
Wed Apr 23 11:53:35 2025 - [info] Oldest slaves:
Wed Apr 23 11:53:35 2025 - [info] 172.21.27.29(172.21.27.29:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:35 2025 - [info] GTID ON
Wed Apr 23 11:53:35 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:35 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:35 2025 - [info] GTID ON
Wed Apr 23 11:53:35 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:35 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] * Phase 3.3: Determining New Master Phase..
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] Searching new master from slaves..
Wed Apr 23 11:53:35 2025 - [info] Candidate masters from the configuration file:
Wed Apr 23 11:53:35 2025 - [info] 172.21.27.31(172.21.27.31:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled
Wed Apr 23 11:53:35 2025 - [info] GTID ON
Wed Apr 23 11:53:35 2025 - [info] Replicating from 172.21.27.30(172.21.27.30:3306)
Wed Apr 23 11:53:35 2025 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Apr 23 11:53:35 2025 - [info] Non-candidate masters:
Wed Apr 23 11:53:35 2025 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Wed Apr 23 11:53:35 2025 - [info] New master is 172.21.27.31(172.21.27.31:3306)
Wed Apr 23 11:53:35 2025 - [info] Starting master failover..
Wed Apr 23 11:53:35 2025 - [info]
From:
172.21.27.30(172.21.27.30:3306) (current master)
+--172.21.27.29(172.21.27.29:3306)
+--172.21.27.31(172.21.27.31:3306)
To:
172.21.27.31(172.21.27.31:3306) (new master)
+--172.21.27.29(172.21.27.29:3306)
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] * Phase 3.3: New Master Recovery Phase..
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] Waiting all logs to be applied..
Wed Apr 23 11:53:35 2025 - [info] done.
Wed Apr 23 11:53:35 2025 - [info] Replicating from the latest slave 172.21.27.29(172.21.27.29:3306) and waiting to apply..
Wed Apr 23 11:53:35 2025 - [info] Waiting all logs to be applied on the latest slave..
Wed Apr 23 11:53:35 2025 - [info] Resetting slave 172.21.27.31(172.21.27.31:3306) and starting replication from the new master 172.21.27.29(172.21.27.29:3306)..
Wed Apr 23 11:53:35 2025 - [info] Executed CHANGE MASTER.
Wed Apr 23 11:53:35 2025 - [info] Slave started.
Wed Apr 23 11:53:35 2025 - [info] Waiting to execute all relay logs on 172.21.27.31(172.21.27.31:3306)..
Wed Apr 23 11:53:35 2025 - [info] master_pos_wait(mysql-bin.000001:1386) completed on 172.21.27.31(172.21.27.31:3306). Executed 1 events.
Wed Apr 23 11:53:35 2025 - [info] done.
Wed Apr 23 11:53:35 2025 - [info] done.
Wed Apr 23 11:53:35 2025 - [info] Getting new master's binlog name and position..
Wed Apr 23 11:53:35 2025 - [info] mysql-bin.000002:403
Wed Apr 23 11:53:35 2025 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.21.27.31', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='root', MASTER_PASSWORD='xxx';
Wed Apr 23 11:53:35 2025 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000002, 403, 14b4dd95-e843-11ef-b6ea-005056ac7e76:1-6
Wed Apr 23 11:53:35 2025 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Wed Apr 23 11:53:35 2025 - [info] Setting read_only=0 on 172.21.27.31(172.21.27.31:3306)..
Wed Apr 23 11:53:35 2025 - [info] ok.
Wed Apr 23 11:53:35 2025 - [info] ** Finished master recovery successfully.
Wed Apr 23 11:53:35 2025 - [info] * Phase 3: Master Recovery Phase completed.
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] * Phase 4: Slaves Recovery Phase..
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] * Phase 4.1: Starting Slaves in parallel..
Wed Apr 23 11:53:35 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] -- Slave recovery on host 172.21.27.29(172.21.27.29:3306) started, pid: 11621. Check tmp log /etc/mha_master/172.21.27.29_3306_20250423115333.log if it takes time..
Wed Apr 23 11:53:36 2025 - [info]
Wed Apr 23 11:53:36 2025 - [info] Log messages from 172.21.27.29 ...
Wed Apr 23 11:53:36 2025 - [info]
Wed Apr 23 11:53:35 2025 - [info] Resetting slave 172.21.27.29(172.21.27.29:3306) and starting replication from the new master 172.21.27.31(172.21.27.31:3306)..
Wed Apr 23 11:53:35 2025 - [info] Executed CHANGE MASTER.
Wed Apr 23 11:53:35 2025 - [info] Slave started.
Wed Apr 23 11:53:35 2025 - [info] gtid_wait(14b4dd95-e843-11ef-b6ea-005056ac7e76:1-6) completed on 172.21.27.29(172.21.27.29:3306). Executed 0 events.
Wed Apr 23 11:53:36 2025 - [info] End of log messages from 172.21.27.29.
Wed Apr 23 11:53:36 2025 - [info] -- Slave on host 172.21.27.29(172.21.27.29:3306) started.
Wed Apr 23 11:53:36 2025 - [info] All new slave servers recovered successfully.
Wed Apr 23 11:53:36 2025 - [info]
Wed Apr 23 11:53:36 2025 - [info] * Phase 5: New master cleanup phase..
Wed Apr 23 11:53:36 2025 - [info]
Wed Apr 23 11:53:36 2025 - [info] Resetting slave info on the new master..
Wed Apr 23 11:53:36 2025 - [info] 172.21.27.31: Resetting slave info succeeded.
Wed Apr 23 11:53:36 2025 - [info] Master failover to 172.21.27.31(172.21.27.31:3306) completed successfully.
Wed Apr 23 11:53:36 2025 - [info]
----- Failover Report -----
mha: MySQL Master failover 172.21.27.30(172.21.27.30:3306) to 172.21.27.31(172.21.27.31:3306) succeeded
Master 172.21.27.30(172.21.27.30:3306) is down!
Check MHA Manager logs at xfzcvmmw185022:/etc/mha_master/manager.log for details.
Started automated(non-interactive) failover.
Selected 172.21.27.31(172.21.27.31:3306) as a new master.
172.21.27.31(172.21.27.31:3306): OK: Applying all logs succeeded.
172.21.27.29(172.21.27.29:3306): OK: Slave started, replicating from 172.21.27.31(172.21.27.31:3306)
172.21.27.31(172.21.27.31:3306): Resetting slave info succeeded.
Master failover to 172.21.27.31(172.21.27.31:3306) completed successfully.
- mha路径下会生成 mha.failover.complete，该文件影响下次回切，需要改名
- 结论
mha可以实现自动ha能力，但是有个问题，是否支持不停的检测切换master？
八、补充信息
- mha参考 www.cnblogs.com/keerya/p/78…
- mha部署mysql5.7.27
- 主从修复
# 切换binlog
flush logs;
# 主从库上都执行
reset master
#
stop slave;
# 配置主从信息
# CHANGE MASTER TO MASTER_HOST='172.21.27.30' ,MASTER_USER='replica' ,MASTER_PASSWORD='Asdf@1234',master_auto_position=1;
CHANGE MASTER TO MASTER_HOST='172.21.27.30' ,MASTER_USER='root' ,MASTER_PASSWORD='123',master_auto_position=1;
# 启动slave，并启动复制
start slave;
show slave status\G
