MySQL主从复制延迟诊断与GTID故障切换：零数据丢失完整方案【转】

MySQL主从复制延迟诊断与GTID故障切换：零数据丢失完整方案

适用场景 & 前置条件

适用业务：读写分离架构、高可用数据库集群、跨地域容灾、数据仓库 ETL 同步前置条件：

  • • MySQL ≥ 5.7（推荐 8.0+，GTID 增强特性）
  • • 主从网络延迟 < 10ms（同机房）/ < 50ms（跨地域）
  • • 主库已启用二进制日志（log_bin=ON）
  • • 从库磁盘 I/O 性能 ≥ 主库（SSD 推荐）
  • • Root 或 SUPER 权限账号

环境与版本矩阵

组件版本要求关键特性复制延迟（典型值）
MySQL 5.7 5.7.17+ GTID, 多线程复制, 半同步 < 1s（局域网）
MySQL 8.0 8.0.23+ GTID 自动定位, Writeset 并行复制 < 500ms
MariaDB 10.5+ 10.5+ GTID 兼容, 多源复制 < 1s
MHA 0.58 自动故障切换（传统复制） -
Orchestrator 3.2+ GTID 故障切换与拓扑管理 -

快速清单（Checklist）

  1. 1. 验证主从复制状态（SHOW SLAVE STATUS）
  2. 2. 诊断复制延迟根因（网络/SQL 线程/大事务）
  3. 3. 启用 GTID 模式（在线迁移或新建）
  4. 4. 配置半同步复制（防数据丢失）
  5. 5. 优化多线程复制（并行回放）
  6. 6. 监控复制延迟与告警（Seconds_Behind_Master）
  7. 7. 测试故障切换（主库宕机场景）
  8. 8. 执行 GTID 故障切换（CHANGE MASTER TO 自动定位）
  9. 9. 验证数据一致性（pt-table-checksum）
  10. 10. 配置自动故障切换工具（Orchestrator/MHA）

实施步骤

Step 1：验证主从复制状态

在从库执行：

SHOW SLAVE STATUS\G

关键字段解读：

***************************1.row***************************
               Slave_IO_State: Waiting for master to send event  -- IO 线程状态
                  Master_Host: 192.168.1.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000023-- 主库当前 binlog
          Read_Master_Log_Pos: 45678901-- 已读取位置
               Relay_Log_File: relay-bin.000012
                Relay_Log_Pos: 45678654
        Relay_Master_Log_File: mysql-bin.000023
             Slave_IO_Running: Yes                               -- IO 线程正常
            Slave_SQL_Running: Yes                               -- SQL 线程正常
              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: 45678654-- 已执行位置
              Relay_Log_Space: 23456789
              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: 2-- 复制延迟（秒）
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: 1
                  Master_UUID: a1b2c3d4-e5f6-11ed-9876-000c29123456
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: a1b2c3d4-e5f6-11ed-9876-000c29123456:1-12345-- 已接收的 GTID
            Executed_Gtid_Set: a1b2c3d4-e5f6-11ed-9876-000c29123456:1-12340-- 已执行的 GTID
                Auto_Position: 1-- GTID 自动定位已启用
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0

健康检查要点：

  • • Slave_IO_Running 与 Slave_SQL_Running 必须为 Yes
  • • Seconds_Behind_Master 应 < 5 秒（理想值 < 1 秒）
  • • Last_Errno / Last_SQL_Errno 应为 0
  • • Retrieved_Gtid_Set 与 Executed_Gtid_Set 差距 < 10 个事务

快速诊断命令：

# 检查主从状态（简化版）
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Running|Behind|Error"

# 查看 GTID 差异
mysql -e "SHOW SLAVE STATUS\G" | grep -E "Retrieved_Gtid_Set|Executed_Gtid_Set"

Step 2：诊断复制延迟根因

场景 1：网络延迟

# 测试主从网络延迟
ping -c 10 192.168.1.10

# 测试 MySQL 端口连通性与延迟
time mysql -h 192.168.1.10 -u repl -p -e "SELECT 1"

场景 2：大事务阻塞

-- 在主库查看当前运行的大事务
SELECT*FROM information_schema.INNODB_TRX
WHERE trx_started < NOW() -INTERVAL30SECOND
ORDERBY trx_started;

-- 查看二进制日志中的大事务
SHOW BINLOG EVENTS IN'mysql-bin.000023' LIMIT 10;

场景 3：从库 SQL 线程单线程回放

-- 查看从库并行回放配置
SHOW VARIABLES LIKE'slave_parallel%';

-- 查看当前并行回放线程数
SELECTCOUNT(*) FROM performance_schema.replication_applier_status_by_worker;

场景 4：从库磁盘 I/O 瓶颈

# 监控从库磁盘 I/O
iostat -x 1 10 | grep -E "Device|sd"

# 查看 MySQL I/O 等待
mysql -e "SHOW PROCESSLIST" | grep "Waiting for"

诊断脚本（综合检查）：

#!/bin/bash
# /usr/local/bin/diagnose_replication_lag.sh

echo"=== 主从复制延迟诊断 ==="

# 1. 复制延迟值
DELAY=$(mysql -ss -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $2}')
echo"当前延迟：$DELAY 秒"

# 2. IO 线程状态
IO_STATE=$(mysql -ss -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{print $2}')
echo"IO 线程：$IO_STATE"

# 3. SQL 线程状态
SQL_STATE=$(mysql -ss -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running:" | awk '{print $2}')
echo"SQL 线程：$SQL_STATE"

# 4. GTID 差异
RETRIEVED=$(mysql -ss -e "SHOW SLAVE STATUS\G" | grep "Retrieved_Gtid_Set:" | awk -F: '{print $NF}' | awk -F- '{print $NF}')
EXECUTED=$(mysql -ss -e "SHOW SLAVE STATUS\G" | grep "Executed_Gtid_Set:" | awk -F: '{print $NF}' | awk -F- '{print $NF}')
GTID_GAP=$((RETRIEVED - EXECUTED))
echo"GTID 待执行：$GTID_GAP 个事务"

# 5. 网络延迟
MASTER_HOST=$(mysql -ss -e "SHOW SLAVE STATUS\G" | grep "Master_Host:" | awk '{print $2}')
PING_LATENCY=$(ping -c 5 $MASTER_HOST | tail -1 | awk -F'/''{print $5}')
echo"网络延迟：$PING_LATENCY ms"

# 6. 从库并行回放
PARALLEL_WORKERS=$(mysql -ss -e "SHOW VARIABLES LIKE 'slave_parallel_workers'" | awk '{print $2}')
echo"并行回放线程数：$PARALLEL_WORKERS"

Step 3：启用 GTID 模式（在线迁移）

前置检查（确保兼容性）：

-- 检查是否有非事务引擎表（MyISAM）
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE ENGINE !='InnoDB'AND TABLE_SCHEMA NOTIN ('mysql','information_schema','performance_schema','sys');

-- 检查是否有存储过程使用非确定性函数
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA NOTIN ('mysql','sys') AND SQL_MODE NOTLIKE'%STRICT%';

在线启用 GTID（MySQL 5.7.6+）：

Step 1：主库配置：

# /etc/my.cnf [mysqld] 段添加
gtid_mode=ON_PERMISSIVE               # 允许 GTID 与非 GTID 事务并存
enforce_gtid_consistency=ON# 强制 GTID 一致性检查

重启主库：

systemctl restart mysqld

Step 2：从库配置：

# /etc/my.cnf
gtid_mode=ON_PERMISSIVE
enforce_gtid_consistency=ON

重启从库：

systemctl restart mysqld

Step 3：主库切换到纯 GTID 模式：

-- 在主库执行
SETGLOBAL gtid_mode=ON;

Step 4：从库切换到纯 GTID 模式并启用自动定位：

-- 在从库执行
STOP SLAVE;
SETGLOBAL gtid_mode=ON;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
START SLAVE;

验证 GTID 启用：

-- 主库
SHOW MASTER STATUS\G

-- 从库
SHOW SLAVE STATUS\G | grep Auto_Position
# 预期输出：Auto_Position: 1

Step 4：配置半同步复制（防数据丢失）

安装半同步插件：

主库：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SETGLOBAL rpl_semi_sync_master_enabled=1;
SETGLOBAL rpl_semi_sync_master_timeout=1000;  -- 超时 1 秒降级为异步

从库：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SETGLOBAL rpl_semi_sync_slave_enabled=1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

持久化配置/etc/my.cnf：

# 主库
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

# 从库
[mysqld]
rpl_semi_sync_slave_enabled=1

验证半同步状态：

-- 主库
SHOW STATUS LIKE'Rpl_semi_sync_master_status';
-- 预期：ON

SHOW STATUS LIKE'Rpl_semi_sync_master_clients';
-- 预期：>= 1（半同步从库数量）

-- 从库
SHOW STATUS LIKE'Rpl_semi_sync_slave_status';
-- 预期：ON

监控半同步性能：

-- 主库半同步等待时间（微秒）
SHOW STATUS LIKE'Rpl_semi_sync_master_tx_avg_wait_time';

-- 半同步超时次数（频繁超时需优化网络）
SHOW STATUS LIKE'Rpl_semi_sync_master_no_tx';

Step 5：优化多线程复制（并行回放）

MySQL 5.7 多线程配置：

# /etc/my.cnf [mysqld]
slave_parallel_type=LOGICAL_CLOCK          # 基于组提交的并行复制
slave_parallel_workers=4# 并行线程数（建议 = CPU 核心数）
slave_preserve_commit_order=1# 保持事务提交顺序

MySQL 8.0 增强配置：

# /etc/my.cnf [mysqld]
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
binlog_transaction_dependency_tracking=WRITESET  -- 基于 Writeset 的并行复制
transaction_write_set_extraction=XXHASH64        -- 写集合哈希算法

重启从库应用配置：

systemctl restart mysqld

验证并行回放生效：

-- 查看并行线程
SELECT*FROM performance_schema.replication_applier_status_by_worker;

-- 查看并行回放统计
SHOW STATUS LIKE'Slave_parallel%';

预期输出：

+------------------------------+-------+
| Variable_name                |Value|
+------------------------------+-------+
| Slave_parallel_workers       |4|
| Slave_parallel_running       |4|
+------------------------------+-------+

Step 6：监控复制延迟与告警

通过 MySQL Exporter 暴露指标：

# 安装 mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
sudocp mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/

# 创建监控账号
mysql -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password' WITH MAX_USER_CONNECTIONS 3;"
mysql -e "GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"

# 创建配置文件
cat > /etc/.mysqld_exporter.cnf <<EOF
[client]
user=exporter
password=password
EOF

# 创建 systemd 服务
sudotee /etc/systemd/system/mysqld_exporter.service <<EOF
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
Type=simple
User=mysql
ExecStart=/usr/local/bin/mysqld_exporter --config.my-cnf=/etc/.mysqld_exporter.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

# 启动服务
sudo systemctl daemon-reload
sudo systemctl enable mysqld_exporter
sudo systemctl start mysqld_exporter

# 验证指标
curl http://localhost:9104/metrics | grep mysql_slave_status_seconds_behind_master

Prometheus 告警规则：

groups:
-name:mysql-replication
rules:
-alert:MySQLReplicationLag
expr:mysql_slave_status_seconds_behind_master>30
for:5m
labels:
severity:warning
annotations:
summary:"MySQL 复制延迟超过 30 秒"

-alert:MySQLReplicationStopped
expr:mysql_slave_status_slave_io_running==0ormysql_slave_status_slave_sql_running==0
for:1m
labels:
severity:critical
annotations:
summary:"MySQL 复制线程停止"

-alert:MySQLReplicationError
expr:mysql_slave_status_last_errno>0
for:1m
labels:
severity:critical
annotations:
summary:"MySQL 复制错误（Errno: {{ $value }}）"

-alert:MySQLSemiSyncDisabled
expr:mysql_global_status_rpl_semi_sync_master_status==0
for:5m
labels:
severity:warning
annotations:
summary:"MySQL 半同步复制已降级为异步"

Grafana 面板查询：

# 复制延迟（秒）
mysql_slave_status_seconds_behind_master

# IO/SQL 线程状态（1=Running, 0=Stopped）
mysql_slave_status_slave_io_running
mysql_slave_status_slave_sql_running

# GTID 执行进度
mysql_slave_status_executed_gtid_set

Step 7：测试故障切换（模拟主库宕机）

准备工作：

-- 在所有从库确认复制延迟 = 0
SHOW SLAVE STATUS\G | grep Seconds_Behind_Master

-- 记录当前主库的 GTID
-- 主库
SHOW MASTER STATUS\G

-- 从库
SHOW SLAVE STATUS\G | grep Executed_Gtid_Set

模拟主库宕机：

# 在主库执行
systemctl stop mysqld

选举新主库（选择 GTID 最新的从库）：

# 在所有从库执行
mysql -e "SHOW SLAVE STATUS\G" | grep Executed_Gtid_Set

# 比较输出，选择 GTID 序号最大的从库作为新主
# 示例输出：
# 从库 1：a1b2c3d4-e5f6-11ed-9876-000c29123456:1-12345
# 从库 2：a1b2c3d4-e5f6-11ed-9876-000c29123456:1-12340
# 选择从库 1 作为新主（12345 > 12340）

Step 8：执行 GTID 故障切换

Step 1：在新主库停止复制：

STOP SLAVE;
RESET SLAVE ALL;  -- 清除复制配置

Step 2：在新主库启用写入：

SETGLOBAL read_only=0;
SETGLOBAL super_read_only=0;

Step 3：在其他从库重新指向新主库：

-- 在从库 2/3/... 执行
STOP SLAVE;

CHANGE MASTER TO
  MASTER_HOST='new-master-ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;  -- GTID 自动定位

START SLAVE;

-- 验证状态
SHOW SLAVE STATUS\G

Step 4：验证数据一致性：

# 在新主库执行测试写入
mysql -e "CREATE DATABASE test_failover; USE test_failover; CREATE TABLE t1(id INT); INSERT INTO t1 VALUES(1);"

# 在从库验证
mysql -e "SELECT * FROM test_failover.t1;"
# 预期输出：id = 1

Step 5：更新应用连接配置（指向新主库）：

# 更新应用配置文件或通过 VIP 漂移
# 示例：通过 keepalived VIP 自动切换

Step 9：验证数据一致性

安装 Percona Toolkit：

# RHEL/CentOS
sudo yum install -y percona-toolkit

# Ubuntu/Debian
sudo apt install -y percona-toolkit

执行一致性校验：

# 在主库执行
pt-table-checksum \
  --host=localhost \
  --user=root \
  --password=password \
  --databases=mydb \
  --replicate=percona.checksums \
  --no-check-binlog-format

# 输出示例
TS            ERRORS  DIFFS  ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
10-31T10:30:00     0      0  1000          0       1       0   0.123 mydb.users
10-31T10:30:01     0      0  5000          0       2       0   0.456 mydb.orders

修复不一致数据：

# 如发现 DIFFS > 0，使用 pt-table-sync 修复
pt-table-sync \
  --execute \
  --sync-to-master \
  h=slave-host,u=root,p=password \
  --databases=mydb \
  --tables=users

Step 10：配置自动故障切换工具（Orchestrator）

安装 Orchestrator：

# 下载
wget https://github.com/openark/orchestrator/releases/download/v3.2.6/orchestrator-3.2.6-1.x86_64.rpm

# 安装
sudo rpm -ivh orchestrator-3.2.6-1.x86_64.rpm

# 或使用 Docker
docker run -d \
  --name orchestrator \
  -p 3000:3000 \
  -e ORC_TOPOLOGY_USER=orc_user \
  -e ORC_TOPOLOGY_PASSWORD=password \
  openark/orchestrator:latest

配置数据库连接/etc/orchestrator.conf.json：

{
"MySQLTopologyUser":"orc_user",
"MySQLTopologyPassword":"password",
"MySQLOrchestratorHost":"127.0.0.1",
"MySQLOrchestratorPort":3306,
"MySQLOrchestratorDatabase":"orchestrator",
"MySQLOrchestratorUser":"orc_server",
"MySQLOrchestratorPassword":"orc_password",
"RecoveryPeriodBlockSeconds":300,
"RecoverMasterClusterFilters":["*"],
"RecoverIntermediateMasterClusterFilters":["*"],
"ApplyMySQLPromotionAfterMasterFailover":true
}

创建 Orchestrator 数据库：

CREATE DATABASE orchestrator;
CREATEUSER'orc_server'@'%' IDENTIFIED BY'orc_password';
GRANTALLON orchestrator.*TO'orc_server'@'%';

-- 在所有 MySQL 节点创建拓扑用户
CREATEUSER'orc_user'@'%' IDENTIFIED BY'password';
GRANT SUPER, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, RELOAD ON*.*TO'orc_user'@'%';

启动 Orchestrator：

systemctl enable orchestrator
systemctl start orchestrator

# 访问 Web UI
http://your-server:3000

发现拓扑：

# 通过 API 发现主库
curl -X POST http://localhost:3000/api/discover/192.168.1.10/3306

# 查看拓扑
curl http://localhost:3000/api/clusters-info | jq

测试自动故障切换：

# 模拟主库宕机
ssh 192.168.1.10 "systemctl stop mysqld"

# 观察 Orchestrator 日志
tail -f /var/log/orchestrator/orchestrator.log

# 预期输出：检测到主库宕机 → 选举新主 → 重新配置从库

监控与告警

关键监控指标

-- 复制延迟
SHOW SLAVE STATUS\G | grep Seconds_Behind_Master

-- GTID 执行进度
SELECT @@GLOBAL.gtid_executed;

-- 半同步状态
SHOW STATUS LIKE'Rpl_semi_sync%';

-- 复制错误统计
SHOW SLAVE STATUS\G | grep -E "Last_Errno|Last_Error"

Prometheus 监控指标

# 复制延迟超过 10 秒
mysql_slave_status_seconds_behind_master > 10

# IO/SQL 线程停止
mysql_slave_status_slave_io_running == 0
mysql_slave_status_slave_sql_running == 0

# 半同步降级
mysql_global_status_rpl_semi_sync_master_status == 0

# GTID 待执行事务数
mysql_slave_status_retrieved_gtid_set - mysql_slave_status_executed_gtid_set > 100

性能与容量

复制延迟优化

瓶颈诊断方法优化方案
网络延迟 ping / mtr 专线/内网直连/半同步超时调优
单线程回放 SHOW PROCESSLIST 启用多线程复制（slave_parallel_workers）
大事务阻塞 SHOW INNODB STATUS 拆分大事务/增加 binlog_row_image=MINIMAL
磁盘 I/O 慢 iostat -x 升级 SSD/调整 innodb_flush_log_at_trx_commit

容量规划

从库规格建议：

  • • CPU：≥ 主库 × 0.8（多线程回放需要更多核心）
  • • 内存：= 主库（innodb_buffer_pool_size 相同）
  • • 磁盘：≥ 主库（SSD 推荐，IOPS ≥ 主库）
  • • 网络：≥ 10Gbps（高并发写入场景）

安全与合规

复制账号权限最小化

-- 创建复制专用账号
CREATEUSER'repl'@'%' IDENTIFIED BY'StrongPassword123!';
GRANT REPLICATION SLAVE ON*.*TO'repl'@'%';

-- 禁止使用 root 账号复制

SSL 加密复制

-- 主库生成 SSL 证书（使用 mysql_ssl_rsa_setup）
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

-- 从库配置 SSL 复制
STOP SLAVE;
CHANGE MASTER TO
  MASTER_SSL=1,
  MASTER_SSL_CA='/var/lib/mysql/ca.pem',
  MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem',
  MASTER_SSL_KEY='/var/lib/mysql/client-key.pem';
START SLAVE;

-- 验证 SSL 启用
SHOW SLAVE STATUS\G | grep Master_SSL_Allowed
# 预期：Master_SSL_Allowed: Yes

合规核对点

  • • [ ] 复制账号使用强密码（≥ 16 字符）
  • • [ ] 跨公网复制启用 SSL 加密
  • • [ ] 从库设置为只读（read_only=1）
  • • [ ] 半同步复制已启用（防数据丢失）
  • • [ ] 复制延迟告警已配置（< 30 秒）
  • • [ ] 定期执行数据一致性校验（pt-table-checksum）
  • • [ ] 故障切换剧本已演练并文档化

常见故障与排错

症状诊断命令可能根因快速修复永久修复
IO 线程停止 SHOW SLAVE STATUS\G 网络中断/主库宕机 START SLAVE IO_THREAD 检查网络/切换主库
SQL 线程停止 SHOW SLAVE STATUS\G 复制错误（如主键冲突） 跳过错误事务（SET GLOBAL sql_slave_skip_counter=1） 修复数据冲突
复制延迟持续增大 iostat / SHOW PROCESSLIST 磁盘 I/O 瓶颈/大事务 临时停止写入/扩容 优化 SQL/增加并行线程
GTID 不一致 SELECT @@gtid_executed 从库手动写入 使用 RESET MASTER 重建 确保从库 read_only=1
半同步降级 SHOW STATUS LIKE 'Rpl_semi_sync%' 从库延迟/网络抖动 增大 rpl_semi_sync_master_timeout 优化网络/增加半同步从库

变更与回滚剧本

维护窗口

推荐时间：凌晨 2:00 - 4:00（业务低峰期）变更前置条件：

  • • [ ] 备份主库数据（mysqldump 或物理备份）
  • • [ ] 确认复制延迟 < 1 秒
  • • [ ] 准备回滚 SQL 脚本
  • • [ ] 通知业务方（只读维护时间）

灰度策略

阶段 1：单从库验证

# 在单个从库测试配置变更
mysql -h slave-01 -e "STOP SLAVE; SET GLOBAL slave_parallel_workers=8; START SLAVE;"

# 观察 10 分钟
watch -n 10 'mysql -h slave-01 -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master'

阶段 2：批量应用

# Ansible 批量配置
ansible mysql-slaves -m shell -a "mysql -e 'STOP SLAVE; SET GLOBAL slave_parallel_workers=8; START SLAVE;'"

回退条件与命令

触发条件：

  • • 复制延迟 > 60 秒持续 10 分钟
  • • SQL 线程频繁停止
  • • 数据一致性校验失败

回退操作：

-- 恢复原配置
STOP SLAVE;
SETGLOBAL slave_parallel_workers=0;  -- 改回单线程
START SLAVE;

最佳实践

  1. 1. 始终启用 GTID：简化故障切换与拓扑变更
  2. 2. 启用半同步复制：关键业务场景防数据丢失（至少 1 个半同步从库）
  3. 3. 多线程复制必选：slave_parallel_workers ≥ 4（MySQL 8.0 用 WRITESET）
  4. 4. 从库只读保护：read_only=1 + super_read_only=1
  5. 5. 监控复制延迟：告警阈值 < 30 秒
  6. 6. 定期一致性校验：每周执行 pt-table-checksum
  7. 7. 自动化故障切换：生产环境部署 Orchestrator/MHA
  8. 8. 避免从库手动写入：破坏 GTID 一致性
  9. 9. 大事务拆分：单个事务不超过 1GB（避免复制阻塞）
  10. 10. 故障切换演练：每季度演练一次（包含数据验证）

附录

完整主从配置模板

主库/etc/my.cnf：

[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

# 半同步复制
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

# 二进制日志保留
expire_logs_days=7
max_binlog_size=1G

# 性能优化
sync_binlog=1
innodb_flush_log_at_trx_commit=1

从库/etc/my.cnf：

[mysqld]
server_id=2
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
read_only=1
super_read_only=1

# 半同步复制
rpl_semi_sync_slave_enabled=1

# 多线程复制
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
slave_preserve_commit_order=1

# MySQL 8.0 增强
binlog_transaction_dependency_tracking=WRITESET
transaction_write_set_extraction=XXHASH64

# Relay log
relay_log=relay-bin
relay_log_recovery=ON

Ansible 自动化部署 Playbook

---
-name:配置MySQL主从复制
hosts:mysql_slaves
become:yes
vars:
master_host:"192.168.1.10"
repl_user:"repl"
repl_password:"StrongPassword123!"

tasks:
-name:停止从库复制
mysql_replication:
mode:stopslave

-name:配置主库连接
mysql_replication:
mode:changemaster
master_host:"{{ master_host }}"
master_user:"{{ repl_user }}"
master_password:"{{ repl_password }}"
master_auto_position:yes

-name:启动从库复制
mysql_replication:
mode:startslave

-name:验证复制状态
mysql_replication:
mode:getslave
register:slave_status
failed_when:slave_status.Slave_IO_Running!='Yes'orslave_status.Slave_SQL_Running!='Yes'

-name:显示复制延迟
debug:
msg:"复制延迟：{{ slave_status.Seconds_Behind_Master }} 秒"

测试环境：MySQL 8.0.35, RHEL 8.8 / Ubuntu 22.04 LTS测试日期：2025-10-31维护周期：配置每季度审查，一致性校验每周执行，故障切换每季度演练

https://mp.weixin.qq.com/s/61EfGAx-VcaI2zRkcKISXw

posted @ 2025-11-04 10:23  paul_hch  阅读(10)  评论(0)    收藏  举报