萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 主從復制故障診斷實戰:Slave_IO/SQL_Running=No 根因分析與修復

雙線程架構與健康標準

從庫兩大核心線程:

  • IO 線程:連接主庫、拉取 binlog 寫入 relay log → Slave_IO_Running: Yes
  • SQL 線程:讀取 relay log 重放 SQL → Slave_SQL_Running: Yes
雙 Yes 才是健康狀態,任一 No 均視為復制中斷。

場景一:Slave_IO_Running = No

常見原因排查表

原因類別典型錯誤日誌排查動作
網絡不通Can't connect to MySQL server on 'master_ip'ping/telnet 3306、檢查防火牆/安全組
認證失敗Access denied for user 'repl_user'核對 CHANGE MASTER TO 密碼、主庫賬號權限
Binlog 位點無效Client requested master to start replication from impossible position (errno=1236)主庫 binlog 已過期/被清理,需重新全量同步
包體過大Packet too large / max_allowed_packet主從同步調大 max_allowed_packet(如 1G)
SSL/TLS 不匹配SSL connection error統一 SSL 模式或均關閉

經典案例:Binlog 位點丟失導致 IO 線程停止

# 錯誤日誌
[ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)
[ERROR] Got fatal error 1236 from master when reading data from binary log

根因:主庫 binlog 已按 expire_logs_days 清理,從庫請求的位點不存在。

修復:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000080', MASTER_LOG_POS=0;
START SLAVE;
若無有效 binlog 覆蓋增量窗口,**必須重新全量備份建立從庫**。

場景二:Slave_SQL_Running = No

常見原因

  1. 從庫誤寫入:應用直連從庫執行 DML,導致數據衝突
  2. DDL 不兼容:主庫執行 DDL(如 ALTER TABLE),從庫重放失敗
  3. 事務回滾/死鎖:SQL 線程遇到錯誤自動停止
  4. 主庫寫入異常:主庫自身報錯(如 1053),binlog 記錄不完整語句

通用修復:SQL_SLAVE_SKIP_COUNTER 跳過錯誤事件

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 跳過 1 個事件
START SLAVE;
⚠ 僅適用於確認可安全跳過的錯誤(如已知的重複鍵衝突、臨時表操作)。跳過前務必分析 Last_Error 內容,必要時在從庫手工執行修正 SQL 再跳過。

深度修復:基於 GTID 的精確跳過(MySQL 5.6+)

-- 查看具體失敗的 GTID
SHOW SLAVE STATUS\G
-- Last_SQL_Error 顯示具體 GTID

-- 跳過特定 GTID 事務
STOP SLAVE;
SET GTID_NEXT = 'uuid:seq_no';  -- 失敗的 GTID
BEGIN; COMMIT;                  -- 空事務佔位
SET GTID_NEXT = AUTOMATIC;
START SLAVE;

場景三:主庫故障導致從庫數據落後

主庫宕機重啟後,從庫 Slave_SQL_Running: No,錯誤:

Last_Error: Query partially completed on the master (error on master: 1053) and was aborted.

修復步驟:

  1. STOP SLAVE;
  2. 在從庫手工執行報錯的 SQL(Last_Error 中提取)
  3. SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  4. START SLAVE;
  5. 觀察 Seconds_Behind_Master 追平至 0

預防與監控體系

  1. 雙 Yes 監控:Nagios/Prometheus 監控 Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master
  2. 只讀強制:從庫 read_only=1、super_read_only=1(5.7+)防誤寫
  3. Binlog 保留策略:expire_logs_days=7 + 歸檔備份,確保故障恢復窗口
  4. GTID 模式:簡化故障切換與位點管理
  5. 半同步復制:關鍵業務啟用,保證至少一從庫收到 binlog

總結

主從故障核心在於 **IO 線程「連不上/找不到位點」** 與 **SQL 線程「重放報錯/數據衝突」**。建立 錯誤日誌分析 → 根因分類 → 精準修復 → 驗證追平 → 監控固化 的標準化處理流程,配合 GTID 與半同步,可將復制故障影響降至最低。

copyright © 萬盛學電腦網 all rights reserved