MySQL 雙主同步複制實戰：架構設計、配置細節與故障處理全紀錄

前言：雙主架構的高可用價值

異地雙主互備是 MySQL 高可用的經典模式：兩節點互為主從，寫入任一節點自動同步至另一節點，單點故障秒級切換。本文以生產實戰案例，完整記錄配置步驟、關鍵參數、同步驗證、網絡中斷恢復、同步失效修復全流程。

一、架構拓撲與前提條件

• OS：RedHat Enterprise Linux 5
• MySQL：5.0.22 (社區版)
• 節點 A (ServA)：10.240.136.9，server-id=1
• 節點 B (ServB)：10.240.136.149，server-id=2
• 同步庫：umsdb
• 忽略表：日誌、統計類大表（減少同步延遲與衝突）

二、核心配置：my.cnf 關鍵參數詳解

[mysqld]
# 基礎複制
log-bin=mysql-bin              # 開啟二進位日誌
relay-log=relay-bin            # 中繼日誌
relay-log-index=relay-bin-index
server-id=1                    # 唯一標識，雙主必須不同

# 雙主互指
master-host=10.240.136.149     # 對端 IP
master-user=tongbu             # 同步專用帳號
master-password=123456
master-port=3306
master-connect-retry=30        # 重試間隔(秒)

# 過濾規則
binlog-do-db=umsdb             # 僅記錄此庫
replicate-do-db=umsdb          # 僅同步此庫
replicate-ignore-table=umsdb.boco_tb_menu
replicate-ignore-table=umsdb.boco_tb_connect_log
... (其它忽略表)

# 關鍵網絡容錯參數(後文補充)
slave-net-timeout=60           # 從庫讀取超時重連秒數，預設 3600 秒太長

注意：MySQL 5.1+ 建議用 CHANGE MASTER TO 動態配置，而非寫死 my.cnf。

三、同步帳號建立（雙向授權）

-- ServA 執行
GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'10.240.136.149' IDENTIFIED BY '123456';

-- ServB 執行
GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'10.240.136.9' IDENTIFIED BY '123456';

最小權限原則：僅授 REPLICATION SLAVE，不可用 ALL PRIVILEGES。

四、初始化同步流程（冷備份 + 啟動）

1. ServB 停止從庫：STOP SLAVE;
2. ServB 從主載入基線資料：LOAD DATA FROM MASTER;（僅 MyISAM 有效，InnoDB 需 xtrabackup/mysqldump）
3. ServB 啟動從庫：START SLAVE;
4. 重啟 ServA MySQL 服務，雙向同步建立。

五、同步狀態監控與關鍵指標

SHOW SLAVE STATUS\G

核心健康指標：

• Slave_IO_Running: Yes — I/O 線程連接主庫正常
• Slave_SQL_Running: Yes — SQL 線程重放日誌正常
• Seconds_Behind_Master: 0 — 延遲秒數，非 0 表追趕中
• Last_Errno: 0 / Last_Error: — 無錯誤

六、實戰坑點：網絡中斷導致同步卡住

現象

拔網線模擬故障，恢復後 Slave_IO_Running: No 長時間不自動恢復。

根因

預設 slave-net-timeout=3600（1 小時），從庫 1 小時才發現連接斷開並重試。

解決

# my.cnf [mysqld] 區段新增
slave-net-timeout=60
# 重啟生效
service mysqld restart

配合 master-connect-retry=30，實現分鐘級自動恢復。

七、同步失效修復標準作業程序 (SOP)

場景 1：從庫 Relay Log 仍在主庫 Binlog 範圍內

-- 從庫
SHOW SLAVE STATUS\G  -- 記下 Master_Log_File, Read_Master_Log_Pos
-- 主庫
SHOW MASTER LOGS;    -- 確認該檔案存在
-- 從庫
START SLAVE;         -- 直接續傳

場景 2：主庫 Binlog 已輪轉刪除（Gap 不可追）

-- 1. 雙雙停止複制
STOP SLAVE;  -- 從庫
STOP SLAVE;  -- 主庫(若也是從)

-- 2. 從庫重新全量同步
LOAD DATA FROM MASTER;  -- 僅 MyISAM
-- 或 xtrabackup/mysqldump + 恢復 (InnoDB)

-- 3. 重置雙端複制座標
RESET MASTER;   -- 從庫(清空本地 binlog)
RESET SLAVE;    -- 從庫(清空 relay log)
START SLAVE;    -- 從庫

-- 4. 主庫同步重置
RESET SLAVE;    -- 主庫
START SLAVE;    -- 主庫

警告：LOAD DATA FROM MASTER 會鎖表、僅支援 MyISAM，生產環境嚴禁使用；請改用 Percona XtraBackup 熱備份 + CHANGE MASTER TO 指定新座標。

八、生產環境避坑清單

1. 避免雙主雙寫衝突：業務層路由寫入單一主庫，或用自增步長/偏移量（auto_increment_increment=2, auto_increment_offset=1/2）。
2. GTID 模式 (5.6+)：啟用 gtid_mode=ON，簡化故障轉移與座標管理。
3. 監控告警：Seconds_Behind_Master > 300、Slave_*_Running != Yes、Last_Errno != 0 立即告警。
4. 定期校驗：pt-table-checksum / pt-table-sync 檢測數據一致性。
5. 備份策略：從庫每日全備 + Binlog 增備，保證 RPO/RTO。