[ mysql 集群部署-运维文档]
MySQL 8.4 主从复制 · 命令速查
环境：ssh pi → ~/DockerV/mysql8-cluster/replication
节点：mysql-source 3311 · mysql-replica1 3312 · mysql-replica2 3313
密码统一 123456
准备：先跑这 3 行（每次 ssh 进去后跑一次）
m() { sudo docker exec "$1" mysql -uroot -p123456 -e "$2" 2>&1 | grep -v "Using a password"; }
mall() { for c in mysql-source mysql-replica1 mysql-replica2; do echo "--- $c ---"; m $c "$1"; done; }
mrep() { for c in mysql-replica1 mysql-replica2; do echo "--- $c ---"; m $c "$1"; done; }
之后：m mysql-source "SQL" 发给主库，mrep "SQL" 发给两个从库，mall "SQL" 发给全部。
报错会正常显示（不像之前被 2>/dev/null 吞掉）。
流程
0 启动环境
1 异步复制
2 半同步
3 GTID
4 演练
0 · 启动环境
cd ~/DockerV/mysql8-cluster/replication
sudo docker compose up -d
sudo docker compose ps # 等三个都 (healthy)
验证（server_id 必须是 21/22/23，是 1 说明配置没生效）：
mall "SELECT @@server_id, @@log_bin, @@gtid_mode;"
1 · 异步复制（binlog 位点）
1.1 主库建复制账号
m mysql-source "
CREATE USER 'fox'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'fox'@'%';
FLUSH PRIVILEGES;"
1.2 查主库位点
m mysql-source "SHOW BINARY LOG STATUS\G"
8.4 没有
SHOW MASTER STATUS，用这个。记下File和Position。
1.3 抓位点到变量
LOGFILE=$(sudo docker exec mysql-source mysql -uroot -p123456 -N -B -e "SHOW BINARY LOG STATUS" 2>/dev/null | awk '{print $1}')
LOGPOS=$(sudo docker exec mysql-source mysql -uroot -p123456 -N -B -e "SHOW BINARY LOG STATUS" 2>/dev/null | awk '{print $2}')
echo "$LOGFILE / $LOGPOS"
1.4 两个从库指向主库
mrep "
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'mysql-source',
SOURCE_PORT = 3306,
SOURCE_USER = 'fox',
SOURCE_PASSWORD = '123456',
SOURCE_LOG_FILE = '$LOGFILE',
SOURCE_LOG_POS = $LOGPOS,
GET_SOURCE_PUBLIC_KEY = 1;"
⚠️ 最后一行是
GET_SOURCE_PUBLIC_KEY（不是SOURCE_GET_PUBLIC_KEY）。8.4 必需，漏了连不上。
1.5 启动复制
mrep "START REPLICA;"
1.6 验证 ← 要看到两个 Yes
mrep "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running:|Replica_SQL_Running:|Last_.*Error:|--- "
1.7 测试数据真的同步
# 主库建表插数据
m mysql-source "
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE test.user (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(50), address varchar(50),
last_updated bigint, is_deleted int);
INSERT INTO test.user VALUES (2,'张三','广州白云山',1691563465,0);"
# 从库查（应该都有张三）
mrep "SELECT * FROM test.user;"
2 · 半同步复制
2.1 装插件
m mysql-source "INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';"
mrep "INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';"
2.2 开开关
m mysql-source "SET GLOBAL rpl_semi_sync_source_enabled = 1;"
mrep "SET GLOBAL rpl_semi_sync_replica_enabled = 1;"
mrep "STOP REPLICA IO_THREAD; START REPLICA IO_THREAD;"
第三行必做：从库要重连一次才能注册成半同步副本。
2.3 验证 ← clients 应为 2，status 应为 ON
m mysql-source "SHOW STATUS LIKE 'Rpl_semi_sync_source_clients';
SHOW STATUS LIKE 'Rpl_semi_sync_source_status';"
2.4 退化实验（看半同步超时退化成异步）
# 改成必须等 2 个从库，超时 100 秒
m mysql-source "SET GLOBAL rpl_semi_sync_source_wait_for_replica_count = 2;
SET GLOBAL rpl_semi_sync_source_timeout = 100000;"
sudo docker stop mysql-replica2 # 停一个，凑不够 2 个
time m mysql-source "INSERT INTO test.user VALUES (4,'退化测试',NULL,NULL,NULL);"
# ↑ 会卡约 100 秒，这就是半同步等不到确认的代价
m mysql-source "SHOW STATUS LIKE 'Rpl_semi_sync_source_status';" # 变成 OFF = 已退化为异步
2.5 恢复
sudo docker start mysql-replica2
sleep 20
m mysql-replica2 "SET GLOBAL rpl_semi_sync_replica_enabled = 1;
STOP REPLICA IO_THREAD; START REPLICA IO_THREAD;"
sleep 5
m mysql-source "SHOW STATUS LIKE 'Rpl_semi_sync_source_status';" # 自动回到 ON
# 调回 1，否则后面演练会一直卡
m mysql-source "SET GLOBAL rpl_semi_sync_source_wait_for_replica_count = 1;
SET GLOBAL rpl_semi_sync_source_timeout = 10000;"
3 · GTID
3.1 全部设为只读
mall "SET @@GLOBAL.read_only = ON;"
3.2 停机 → 改配置 → 启动
cd ~/DockerV/mysql8-cluster/replication
sudo docker compose stop
sed -i 's/^# gtid_mode = ON/gtid_mode = ON/; s/^# enforce_gtid_consistency = ON/enforce_gtid_consistency = ON/' node*/conf/custom.cnf
grep -H "^gtid_mode" node*/conf/custom.cnf # 应输出 3 行
sudo docker compose start
sleep 30
3.3 验证 GTID 开了
mall "SELECT @@gtid_mode, @@enforce_gtid_consistency;"
3.4 从库改用自动定位 ← 对比 1.4，位点两行没了
mrep "
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'mysql-source',
SOURCE_PORT = 3306,
SOURCE_USER = 'fox',
SOURCE_PASSWORD = '123456',
SOURCE_AUTO_POSITION = 1,
GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;"
3.5 解除只读 ← 别忘，忘了主库写不进去
mall "SET @@GLOBAL.read_only = OFF;"
3.6 验证 ← Auto_Position: 1
mrep "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running:|Replica_SQL_Running:|Auto_Position|Executed_Gtid_Set|--- "
4 · 演练
演练 1 · 制造两个从库进度不一致
m mysql-replica2 "STOP REPLICA;" # 从库2 掉队
m mysql-source "INSERT INTO test.user VALUES (12,'fox',NULL,NULL,NULL);"
sleep 3
mall "SELECT COUNT(*) FROM test.user WHERE id=12;" # 主/从1 是 1，从2 是 0
演练 2 · 主库宕机 → 从库1 升主 → 从库2 改投
sudo docker stop mysql-source # 主库挂了
m mysql-replica1 "CREATE USER 'fox'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'fox'@'%';
FLUSH PRIVILEGES;" # 新主库建账号
m mysql-replica2 "
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'mysql-replica1',
SOURCE_PORT = 3306,
SOURCE_USER = 'fox',
SOURCE_PASSWORD = '123456',
SOURCE_AUTO_POSITION = 1,
GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;" # ★ 不用查任何位点
sleep 5
m mysql-replica2 "SELECT COUNT(*) FROM test.user WHERE id=12;" # ★ 从 0 变成 1，掉队的补上了
演练 3 · 复制中断 → 注入空事务修复
# 制造中断：从库删表，主库插数据
m mysql-replica2 "DROP TABLE test.user;"
m mysql-replica1 "INSERT INTO test.user VALUES (14,'AAA',NULL,NULL,NULL);"
sleep 3
# 看中断
m mysql-replica2 "SHOW REPLICA STATUS\G" | grep -E "Replica_SQL_Running:|Last_SQL_Error:|Retrieved_Gtid_Set|Executed_Gtid_Set"
# 修复第一步：把表拷回来
sudo docker exec mysql-replica1 mysqldump -uroot -p123456 --set-gtid-purged=OFF test user 2>/dev/null \
| sudo docker exec -i mysql-replica2 mysql -uroot -p123456 test 2>/dev/null
# 修复第二步：找出卡住的 GTID（对比 Retrieved 和 Executed，缺口第一个就是）
m mysql-replica2 "SHOW REPLICA STATUS\G" | grep -E "Retrieved_Gtid_Set|Executed_Gtid_Set"
# 修复第三步：注入空事务跳过它（把 <UUID>:<N> 换成上一步找到的）
m mysql-replica2 "
STOP REPLICA;
SET @@SESSION.GTID_NEXT = '<UUID>:<N>';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START REPLICA;"
sleep 5
m mysql-replica2 "SHOW REPLICA STATUS\G" | grep -E "Replica_SQL_Running:|Last_SQL_Error:"
原理：一个 GTID 只执行一次。空事务把它「占掉」，主库就不再推送，复制从下一个继续。
演练 4 · 旧主库降级为从库（可选）
sudo docker start mysql-source
sleep 30
m mysql-source "
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'mysql-replica1',
SOURCE_PORT = 3306,
SOURCE_USER = 'fox',
SOURCE_PASSWORD = '123456',
SOURCE_AUTO_POSITION = 1,
GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;"
日常操作
cd ~/DockerV/mysql8-cluster/replication
sudo docker compose stop # 停掉，释放 1.4G 内存，数据全保留
sudo docker compose start # 回到停之前的状态，复制配置还在
重启后只有半同步开关会丢（
SET GLOBAL不持久化），重跑 2.2 即可。插件是持久的。
stop/start/down都不会删数据。只有cleanup.sh会真删。
排查
万能第一招：命令没输出但感觉不对 → 结尾换成 2>&1 | grep -v "Using a password" 重跑看报错。
（用上面的 m 函数就已经是这个行为了）
| 症状 | 原因 | 处理 |
|---|---|---|
server_id 都是 1 |
cnf 权限 world-writable 被忽略 | chmod 644 node*/conf/custom.cnf 后重启 |
ERROR 1064 near 'SOURCE_GET_PUBLIC_KEY' |
参数名反了 | 用 GET_SOURCE_PUBLIC_KEY |
ERROR 1064 near 'master status' |
8.4 移除了 | 用 SHOW BINARY LOG STATUS |
IO_Running 卡在 Connecting，报 requires secure connection |
漏了 GET_SOURCE_PUBLIC_KEY = 1 |
重做 1.4 |
CREATE USER ... WITH mysql_native_password 报错 |
8.4 默认禁用该插件 | 去掉 WITH ... |
| 主库 INSERT 一直卡住 | 半同步 wait_count=2 但只有 1 个从库 |
见 2.5 调回 1 |
主库报 --read-only |
3.1 设的只读没解除 | 跑 3.5 |
SQL_Running: No |
重放出错 | 看 Last_SQL_Error，参考演练 3 |
看日志：sudo docker compose logs mysql-source --tail 50
8.4 vs 8.0.27
| 8.4 | |
|---|---|
SHOW MASTER STATUS |
SHOW BINARY LOG STATUS |
SHOW SLAVE STATUS |
SHOW REPLICA STATUS |
CHANGE MASTER TO |
CHANGE REPLICATION SOURCE TO |
START SLAVE / STOP SLAVE |
START REPLICA / STOP REPLICA |
MASTER_HOST / MASTER_LOG_POS … |
SOURCE_HOST / SOURCE_LOG_POS … |
IDENTIFIED WITH mysql_native_password BY 'x' |
IDENTIFIED BY 'x' + 从库加 GET_SOURCE_PUBLIC_KEY = 1 |
浙公网安备 33010602011771号