MySQL 主從復制完整實戰：從部署到常見錯誤排查全記錄

MySQL 主從復制完整實戰：從部署到常見錯誤排查全記錄

主從復制是 MySQL 高可用與讀寫分離的基石。本文基於 Debian 5 + MySQL 5.0 + InnoDB 環境，詳細記錄搭建步驟、關鍵參數解析，並深度剖析 Slave_IO_Running: No、Error 1236 等經典故障的根因與修復方案。

一、架構規劃

• Master：10.1.1.45
• Slave：10.1.1.43
• 引擎：InnoDB，啟用 --single-transaction 熱備

二、主庫配置

1. 修改 my.cnf

[mysqld]
log-bin = /opt/log.bin/45
server-id = 45

2. 建立同步帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';

注意：使用 GRANT 語句無需手動 FLUSH PRIVILEGES，權限即時生效。

3. 導出一致性快照並記錄 Binlog 位點

mysqldump -uroot -p --single-transaction --flush-logs --master-data --all-databases > all.sql

關鍵參數解析：

• --single-transaction：InnoDB 熱備核心，利用 MVCC 獲得一致性視圖，不阻塞寫入。
• --flush-logs：備份前滾動 binlog，新日誌檔對應備份時間點。
• --master-data：在 dump 檔頭部寫入 CHANGE MASTER TO 語句，自動記錄 MASTER_LOG_FILE 與 MASTER_LOG_POS。

三、從庫配置

1. 修改 my.cnf

[mysqld]
server-id = 43

2. 導入主庫快照

cat all.sql | mysql -uroot -p

3. 設定同步位點並啟動

從 dump 檔頭部提取位點資訊（或手動指定）：

CHANGE MASTER TO
    MASTER_HOST='10.1.1.45',
    MASTER_USER='repl',
    MASTER_PASSWORD='replpass',
    MASTER_LOG_FILE='45.000064',
    MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS\G;

確認 Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0。

四、進階過濾配置

replicate-do-db=test
replicate-wild-do-table=test.dave
replicate-wild-do-table=test.davehe

五、常見錯誤與修復實戰

1. 跳過臨時同步錯誤

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;  -- 事務類型可能需執行多次
START SLAVE;

或單獨控制 IO/SQL 線程：

STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD; START SLAVE SQL_THREAD;

2. 經典故障：Slave_IO_Running: No + Error 1236

現象：主機宕機重啟後，從庫報錯：

Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236)

根因分析：

• 預設 sync_binlog=0，事務提交不強制 fsync，binlog 留在 OS cache。
• Slave IO 線程讀取的是 Master 的 log buffer 位點（較大），而非已刷盤的實際檔案位點（較小）。
• 主機掉電導致 buffer 未刷盤數據丟失，重啟後實際 binlog 位點小於從庫記錄的位點，故報「impossible position」。

驗證步驟：

# 主庫檢視實際 binlog 末尾位點
mysqlbinlog 99.000302 > /tmp/test
tail -n 10 /tmp/test
# 發現實際位點 165111351 < 從庫記錄的 165112917

修復方案：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.1',
    MASTER_USER='repl',
    MASTER_PASSWORD='replpass',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='99.000303',  -- 指向下一個 binlog 檔
    MASTER_LOG_POS=98;            -- 新檔案起始位點

3. 根治建議：調整 sync_binlog

[mysqld]
sync_binlog=1  -- 每次事務提交強制 fsync，犧牲約 5 倍寫入效能，換取零數據丟失

若效能敏感，可妥協設 sync_binlog=100 或配合電池備援寫入快取（BBWC）控制器。

六、監控與維護清單

• 定期檢查 Seconds_Behind_Master，設定告警閾值（如 > 300 秒）
• 監控 Slave_IO_Running、Slave_SQL_Running 狀態
• 定期對比主從資料一致性（可用 pt-table-checksum）
• 備份主庫 master.info、relay-log.info 以便災難恢復

主從復制看似簡單，細節決定成敗。掌握 binlog 機制、事務提交與 fsync 的關係，才能在故障發生時精準定位、快速恢復，守住資料的最後一道防線。