MySQL 主從與雙主熱備完整實戰：原理圖解、配置步驟、故障排查與避坑指南

核心原理：基於 Binlog 的非同步複制

MySQL 複制本質是主庫將寫操作記錄到 Binlog → 從庫 I/O 線程拉取存入 Relay Log → 從庫 SQL 線程重放。雙主 (Master-Master) 只是雙向各做一遍主從配置。

關鍵線程與流程

關鍵狀態指標

-- 主庫
SHOW MASTER STATUS\G
-- File: mysql-bin.000014, Position: 107  ← 同步座標

-- 從庫
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes   ← I/O 線程正常
-- Slave_SQL_Running: Yes  ← SQL 線程正常
-- Seconds_Behind_Master: 0  ← 延遲秒數

實戰：A→B 單向主從配置 (MySQL 5.5/5.6/5.7 通用)

前置條件

• 兩台服務器網路互通、防火牆開放 3306
• MySQL 版本一致 (或從庫版本 ≥ 主庫版本)
• 主庫開啟 Binlog、配置 server-id 唯一

步驟 1：主庫 A 建立複制賬號

GRANT REPLICATION SLAVE ON *.* 
TO 'repl_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass#2024';
FLUSH PRIVILEGES;

安全原則： 僅允許從庫 IP 段訪問、專用賬號、強密碼。

步驟 2：主庫 A 配置 /etc/my.cnf

[mysqld]
server-id = 1                    # 唯一，1~2^32-1
log-bin = mysql-bin              # 啟用 Binlog
binlog-format = ROW              # 推薦 ROW 格式
binlog-do-db = business_db       # 僅同步業務庫 (可選)
binlog-ignore-db = mysql,information_schema,performance_schema,sys
# 雙主避免自增衝突 (見下文)
auto-increment-increment = 10
auto-increment-offset = 1

重啟 MySQL：systemctl restart mysqld

步驟 3：獲取主庫同步座標 + 基線數據備份

-- 主庫鎖表 (僅備份階段)
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File + Position
-- 導出數據
mysqldump -u root -p --master-data=2 --single-transaction -B business_db > base.sql
-- 解鎖
UNLOCK TABLES;

--master-data=2 會在備份文件中寫入 CHANGE MASTER TO 註釋，含座標資訊。

步驟 4：從庫 B 導入基線數據 + 配置 /etc/my.cnf

mysql -u root -p < base.sql

[mysqld]
server-id = 2
relay-log = mysqld-relay-bin
log-slave-updates = 1            # 雙主必開：將重放的寫入自己的 Binlog
read-only = 1                    # 防止誤寫 (視業務決定)
replicate-ignore-db = mysql,information_schema,performance_schema,sys

重啟 MySQL。

步驟 5：從庫 B 啟動複制

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='StrongPass#2024',
    MASTER_LOG_FILE='mysql-bin.000014',
    MASTER_LOG_POS=107;

START SLAVE;
SHOW SLAVE STATUS\G;  -- 確認雙 Yes

進階：雙主 (Master-Master) 雙向同步配置

在 B→A 方向重複上述步驟，關鍵差異：

• B 的 server-id = 2，A 的 server-id = 1 (已設)
• 雙方都設 log-slave-updates = 1
• 自增衝突解決：

  -- 主庫 A
  auto-increment-increment = 10
  auto-increment-offset = 1   -- 生成 1, 11, 21...
  
  -- 主庫 B
  auto-increment-increment = 10
  auto-increment-offset = 2   -- 生成 2, 12, 22...
  

  預留 10 個槽位，支援未來擴展至 10 節點循環複制。

故障排查實戰手冊

場景 1：Slave_IO_Running = No (連不上主庫)

• 檢查網路：telnet master_ip 3306
• 檢查賬號/密碼/IP 白名單
• 查看錯誤日誌：tail -f /var/log/mysqld.log
• 常見錯誤：Access denied、Can't connect、Server ID conflict

場景 2：Slave_SQL_Running = No (重放失敗)

• SHOW SLAVE STATUS\G 看 Last_Error
• 主鍵衝突 (Error 1062)：SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; 跳過 (謹慎)
• 字符集不一致：確保主從 character_set_server 一致
• 表結構差異：補齊缺失欄位/索引

場景 3：Relay Log 損壞/丟失 (文中實戰案例)

STOP SLAVE;
RESET SLAVE ALL;  -- 5.5.16+ / 5.6+ 清理 Relay Log
-- 重新 CHANGE MASTER TO ... (使用主庫當前座標)
START SLAVE;

文中遇到的「找不到 Relay Log 文件」即因修改 relay-log 參數後未 RESET SLAVE 導致舊文件引用失效。

場景 4：Seconds_Behind_Master 持續增大

• 從庫硬體較弱：升級配置、調整 innodb_flush_log_at_trx_commit=2、sync_binlog=0 (犧牲安全換速度)
• 大事務阻塞：主庫拆分大事務、或用 pt-online-schema-change 類工具
• 並行複制 (5.6+：slave_parallel_workers、5.7+：slave_parallel_type=LOGICAL_CLOCK)

監控與運維檢查清單

結語

MySQL 主從/雙主複制雖屬成熟技術，但生產環境穩定運行需落實：基線備份座標精確、自增偏移預留、log-slave-updates 雙開、監控告警全覆蓋、故障預案演練。文中實戰記錄的 Relay Log 遺失、自增衝突、SQL 執行錯誤等坑點，皆為血淚經驗總結。建議團隊建立「複制拓撲圖、配置模板、故障手冊」三件套，新節點上線走清單，變更走演練。