MySQL 主從不同步常見場景與分級修復實戰手冊

MySQL 主從不同步常見場景與分級修復實戰手冊

主從復制中斷或數據不一致是 DBA 高頻痛點。本文將故障分為「IO 線程斷連」「SQL 線程報錯」「數據不一致」三級，提供標準化排查修復 SOP。

一、快速診斷入口

# 1. 查看錯誤日志 (首選)
tail -f /var/log/mysqld.log

# 2. 關鍵信號
# IO 線程連接成功:
# 140304 11:59:18 [Note] Slave I/O thread: connected to master 'repl@192.168.1.1:3306', replication started in log 'binlog.000011' at position 294

# SQL 線程初始化:
# 140304 11:59:08 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000011' at position 294, relay log 'mysql-relay-bin.000001' position: 4

二、分級故障處理 SOP

Level 1: IO 線程斷連 (從庫無法拉取 Binlog)

典型錯誤：連接超時、認證失敗、網絡不通、主庫 Binlog 被清理 (位點不存在)。

1. 檢查網絡連通性：telnet master_ip 3306、ping master_ip。
2. 驗證復制賬號密碼：對比 /etc/my.cnf、/var/lib/mysql/master.info 中的 host/user/password。
3. 確認主庫 Binlog 存在：SHOW BINARY LOGS 是否包含 master.info 記錄的文件。
4. 手動重置同步點 (最常用修復)：

   STOP SLAVE;
   CHANGE MASTER TO
     MASTER_HOST='192.168.1.1',
     MASTER_USER='repl',
     MASTER_PASSWORD='pass',
     MASTER_LOG_FILE='binlog.000011',   -- 主庫現有最早 Binlog
     MASTER_LOG_POS=4;                  -- 從頭開始
   START SLAVE;

Level 2: SQL 線程報錯 (Relay Log 重放失敗)

典型錯誤：主鍵衝突 (1062)、外鍵失敗、表不存在、列不匹配、死鎖超時。

1. SHOW SLAVE STATUS\G 記下 Last_SQL_Error、Exec_Master_Log_Pos。
2. 根據錯誤碼決策：
   • 1062 主鍵衝突：主庫已有數據，從庫重放衝突。SET GLOBAL sql_slave_skip_counter=1; START SLAVE; (跳過 1 事務) 或手動刪除衝突行後重試。
   • 表/列不存在：DDL 未同步或順序亂。補齊 DDL 或跳過。
   • 死鎖/鎖等待超時：通常瞬時，START SLAVE 重試即可。
3. 若錯誤大量、無法逐個跳過 → 進入 Level 3。

Level 3: 數據不一致 (無報錯但數據對不上)

檢測手段：pt-table-checksum (Percona Toolkit) 定期巡檢，或業務發現異常。

1. 停止業務寫入主庫 (或鎖表 FLUSH TABLES WITH READ LOCK) 確保數據靜止。
2. 主庫全量備份 + Binlog 位點：

   mysqldump --single-transaction --master-data=2 --routines --databases db1 > full.sql
   # 記錄輸出的 CHANGE MASTER TO 位點

3. 備份傳輸到從庫，全量恢復：

   mysql -uroot -p < full.sql

4. 重新配置復制位點並啟動：

   STOP SLAVE;
   CHANGE MASTER TO MASTER_LOG_FILE='binlog.xxx', MASTER_LOG_POS=yyy;
   START SLAVE;

5. 驗證一致性：pt-table-checksum 再次巡檢，確認無差異。

三、預防體系建設

1. GTID 模式 (MySQL 5.6+)：gtid_mode=ON, enforce_gtid_consistency=ON，自動定位位點，極大簡化修復。
2. Row 格式 Binlog：binlog_format=ROW，避免 SBR 非確定性導致邏輯不一致。
3. 半同步復制：rpl_semi_sync_master=ON，主庫至少一從庫收到事務才提交，減少數據丟失窗口。
4. 定期校驗：每日/每周 pt-table-checksum + pt-table-sync 修復。
5. 監控告警：Seconds_Behind_Master > 300、Slave_IO_Running != Yes、Slave_SQL_Running != Yes 立即告警。
6. 備份保留：全備保留 7 天，Binlog 保留 30 天，確保隨時可重搭從庫。

四、一鍵修復腳本框架 (參考)

#!/bin/bash
# fix_replication.sh
MASTER_HOST=$1
MASTER_USER=$2
MASTER_PASS=$3
MASTER_LOG_FILE=$4
MASTER_LOG_POS=$5

mysql -uroot -p$ROOT_PASS -e "STOP SLAVE;"
mysql -uroot -p$ROOT_PASS -e "CHANGE MASTER TO MASTER_HOST='$MASTER_HOST', MASTER_USER='$MASTER_USER', MASTER_PASSWORD='$MASTER_PASS', MASTER_LOG_FILE='$MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS;"
mysql -uroot -p$ROOT_PASS -e "START SLAVE;"
sleep 3
mysql -uroot -p$ROOT_PASS -e "SHOW SLAVE STATUS\G" | grep -E 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master'