MySQL 主從雙機熱備配置全流程：從版本選型到故障切換的生產級實戰手冊

MySQL 主從雙機熱備配置全流程

MySQL 原生複製 (Replication) 基於 Binlog 異步傳輸，是構建高可用、讀寫分離、異地災備的基石。本文覆蓋 **版本相容性原則、主庫配置、從庫建立、同步驗證、日志清理、故障切換** 全生命週期，給出生產級配置模板與運維 SOP。

版本相容性鐵律

• **主庫版本 ≤ 從庫版本** (如 5.7 主 → 8.0 從 ✅；8.0 主 → 5.7 從 ❌)。
• **最低版本**：主從均 ≥ 5.6 (GTID、並行複製、崩潰安全複製)。生產建議 **5.7/8.0 同版本**。
• 跨大版本升級掛載需官方文檔確認兼容性矩陣。

主庫配置 (my.cnf)

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

主庫備份與坐標記錄 (停寫窗口)

[mysqld]
server-id = 2                              # 唯一，≠ 主庫
relay_log = relay-bin                      # Relay Log 前綴
read_only = ON                             # 從庫預設唯讀
super_read_only = ON                       # 限制 SUPER 用戶寫入 (5.7.8+)
relay_log_recovery = ON                    # 啟動自動修復 Relay Log (5.6+)
skip_slave_start = ON                      # 啟動不自動啟動複製，手動控制

# 並行複製 (5.6+ 效能關鍵)
slave_parallel_workers = 8                 # SQL 線程並行數
slave_parallel_type = LOGICAL_CLOCK        # 基於組提交並行 (5.7+)
master_info_repository = TABLE             # 主庫信息存表 (抗崩潰)
relay_log_info_repository = TABLE

從庫初始化與啟動複製

-- 主庫: 鎖表獲取最新坐標
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Position
UNLOCK TABLES;

-- 從庫: 停止 SQL 線程
STOP SLAVE SQL_THREAD;
-- 等待 IO 線程追上 (Relay_Log_Space 穩定)

-- 更新同步坐標 (GTID 模式無需此步)
CHANGE MASTER TO MASTER_LOG_FILE='新文件', MASTER_LOG_POS=新位置;

-- 重新啟動
START SLAVE SQL_THREAD;

故障切換與高可用架構演進

監控核心指標 (Zabbix/Prometheus/Grafana)

• Slave_IO_Running / Slave_SQL_Running = Yes (雙 Yes 才健康)。
• Seconds_Behind_Master < 300 (業務容忍度)。
• Relay_Log_Space 增長趨勢 (IO 線程阻塞預警)。
• Last_IO_Error / Last_SQL_Error 非空即告警。
• GTID 集合差集：GTID_SUBTRACT(Executed, Retrieved) 非空表示從庫落後事務。

避坑清單

1. server-id 衝突：主從/多從必須全局唯一，否則複製中斷。
2. 字符集不一致：主從 character_set_server、collation_server 必須一致，否則亂碼/截斷。
3. 大事務阻塞複製：主庫大批量 DELETE/UPDATE 拆分小事務，避免從庫 SQL 線程長時間鎖表。
4. 臨時表複製失敗：binlog_format=ROW 可複製臨時表，STATEMENT 模式會報錯。
5. 跳過錯誤陷阱：SET GLOBAL sql_slave_skip_counter=1 僅作臨時應急，**必須事後補數據、根因修復**，否則數據永久不一致。

總結

MySQL 主從雙機熱備核心在於 **「GTID + ROW + 並行複製 + 監控自動化」** 四大支柱。配置階段嚴守版本相容、server-id 唯一、雙 1 刷盤、備份坐標精準記錄；運維階段建立「雙 Yes 監控、延遲告警、日志清理 SOP、切換演練」常態化機制。演進至 MGR/InnoDB Cluster 實現原生強一致高可用，是生產架構終局。