您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 主從同步狀態排查與 Slave 故障修復實戰手冊

編輯：MySQL 綜合教學

MySQL 主從同步狀態排查與 Slave 故障修復實戰手冊

主從同步中斷是 DBA 最常面對的線上事故之一。本文提供標準化排查流程：看 Master 狀態 → 看 Slave 狀態 → 看錯誤日誌 → 精準修復，涵蓋 IO/SQL 線程異常、同步位點偏移、重複鍵衝突、特殊字元導致的 SQL 報錯等高頻場景。

一、標準排查三步曲

1. 檢查 Master 狀態

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 98       |              |                  |
+------------------+----------+--------------+------------------+

Position 不應為 0（0 表示無寫入）。
SHOW PROCESSLIST; 確認有 Binlog Dump 線程，State 為 Has sent all binlog to slave; waiting for binlog to be updated。

2. 檢查 Slave 狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 98
Relay_Log_File: mysql-relay-bin.000012
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

核心指標：Slave_IO_Running=Yes、Slave_SQL_Running=Yes、Seconds_Behind_Master=0（或極小）。
SHOW PROCESSLIST; 應有兩條 system user 線程：
- IO 線程：Waiting for master to send event
- SQL 線程：Has read all relay log; waiting for the slave I/O thread to update it

3. 查看錯誤日誌

# 典型路徑
/var/log/mysqld.log
/var/lib/mysql/hostname.err
# 關鍵字搜尋
grep -i "error\|fail\|stop" /var/log/mysqld.log

二、高頻故障修復場景

場景 1：Slave_IO_Running = No（IO 線程停止）

常見原因：Master binlog 位點變更（如 Master 重啟、binlog 清理、GTID 清除），Slave 仍持有舊位點。

修復步驟：

# 1. 在 Master 獲取最新位點
mysql> SHOW MASTER STATUS;
# 記錄 File、Position，如 mysql-bin.000004, 98

# 2. 在 Slave 重設同步位點
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=98;
mysql> START SLAVE;

# 3. GTID 模式更簡單
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION=1;
mysql> START SLAVE;

場景 2：Slave_SQL_Running = No（SQL 線程停止）

常見原因：重複鍵衝突、外鍵約束失敗、特殊字元導致 SQL 語法錯誤、DDL 執行失敗。

錯誤日誌典型特徵：

Error "Duplicate entry '1' for key 'PRIMARY'" on query...

修復策略 A：跳過錯誤事務（謹慎使用）

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 跳過 1 個事務
mysql> START SLAVE;

適用場景：確認該事務在 Master 已執行、Slave 已有相同資料、跳過不影響一致性（如重複 INSERT IGNORE 類）。

修復策略 B：手動修正資料後繼續

SHOW SLAVE STATUS\G 取得 Last_SQL_Error 具體報錯 SQL。
在 Slave 手動執行修正（如刪除重複行、修正字元）。
START SLAVE; 繼續同步。

修復策略 C：重新同步（資料差異大、無法手動修正）

Master 執行 FLUSH TABLES WITH READ LOCK，記錄位點。
mysqldump --master-data=2 --single-transaction 導出。
Slave 停止同步、導入備份、CHANGE MASTER TO 設定位點、START SLAVE。

場景 3：同步延遲大

排查：

# 檢查 Slave 處理能力
SHOW PROCESSLIST;  -- SQL 線程是否長時間 "Sending data"、"Creating sort index"
# 檢查 Master 壓力
SHOW MASTER STATUS;  -- binlog 增長速度
# 檢查網絡
ping/traceroute Master IP

優化：

Slave 端：slave_parallel_workers > 0（5.6+）、slave_parallel_type=LOGICAL_CLOCK 開啟並行複制。
Master 端：binlog_format=ROW、sync_binlog=1、innodb_flush_log_at_trx_commit=1。
硬體：SSD、關閉 Swap、RAID BBWC WriteBack。

場景 4：特殊字元/編碼導致 SQL 報錯

現象：Last_SQL_Error 顯示 Incorrect string value 或語法錯誤。

根因：Master/Slave 字元集不一致（如 Master utf8mb4、Slave utf8）。

修復：

統一字元集：ALTER DATABASE ... CHARACTER SET utf8mb4、表/欄位同步修改。
連線字元集：應用/複制用戶均用 SET NAMES utf8mb4。
跳過錯誤事務（臨時）+ 事後補齊資料。

三、線程狀態速查表

線程	正常 State	異常 State 範例
IO 線程	Waiting for master to send event	Connecting to master、Reconnecting after a failed binlog dump request
SQL 線程	Has read all relay log; waiting for the slave I/O thread to update it	Executing、Reading event from the relay log、System lock

四、緊急處理 SOP（標準作業程序）

告警觸發：監控發現 Slave_IO_Running!=Yes 或 Slave_SQL_Running!=Yes 或 Seconds_Behind_Master > 300。
初步定性：SHOW SLAVE STATUS\G 記錄 Last_IO_Error、Last_SQL_Error、Master_Log_File、Read_Master_Log_Pos。
查錯誤日誌：確認具體報錯 SQL 與錯誤碼。
決策修復：
- IO 異常 → CHANGE MASTER TO 重設位點 / MASTER_AUTO_POSITION=1。
- SQL 異常單一事務 → SQL_SLAVE_SKIP_COUNTER=1（確認安全後）。
- SQL 異常批量/資料不一致 → 重新同步。
驗證恢復：START SLAVE → 連續 3 分鐘 Seconds_Behind_Master=0 → 解除告警。
事後複盤：記錄根因、影響範圍、修復耗時、預防措施，納入知識庫。