MySQL 主從複寫與雙主熱備配置全攻略 (my.cnf 實戰範本)

架構模式對比

環境規劃

• OS: Windows 2000 / Linux 通用
• MySQL: 4.0.4+ (現代建議 5.7+/8.0)
• Node A: 10.10.10.22 (Server ID=1)
• Node B: 10.10.10.53 (Server ID=2)
• 同步資料庫: backup
• 複寫帳號: repl_user / StrongPass!

共同前置作業

-- 兩節點皆執行
CREATE DATABASE backup;
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.10.10.%' IDENTIFIED BY 'StrongPass!';
FLUSH PRIVILEGES;

模式一：單向主從 (A → B)

Master (A) my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=backup
binlog-format=ROW          -- 建議 ROW 格式
sync_binlog=1              -- 最大持久性
innodb_flush_log_at_trx_commit=1

Slave (B) my.cnf

[mysqld]
server-id=2
relay-log=relay-bin
read-only=1
replicate-do-db=backup
master-host=10.10.10.22
master-user=repl_user
master-password=StrongPass!
master-port=3306
master-connect-retry=60

啟動同步

# Master 熱備份並記錄座標
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Position
UNLOCK TABLES;

# Slave 還原備份後
CHANGE MASTER TO
  MASTER_HOST='10.10.10.22',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='StrongPass!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G

模式二：雙主熱備 (A ⇄ B)

在單向基礎上，雙方皆加入對方為 Master：

A 追加配置

master-host=10.10.10.53
master-user=repl_user
master-password=StrongPass!
master-connect-retry=10
replicate-do-db=backup

B 追加配置

log-bin=mysql-bin
binlog-do-db=backup

關鍵防衝突設定 (雙主必須)

[mysqld]
auto_increment_increment=2
auto_increment_offset=1   -- A 設 1, B 設 2
# 或啟用 GTID (5.6+)
gtid_mode=ON
enforce_gtid_consistency=ON

故障處理 SOP

1. SHOW SLAVE STATUS\G 檢查 Last_IO_Error/Last_SQL_Error。
2. 錯誤修復後：STOP SLAVE; START SLAVE;。
3. 若 relay-log 損壞：RESET SLAVE ALL; 重新 CHANGE MASTER TO。
4. 監控指標：Seconds_Behind_Master < 10s 正常，> 300s 告警。

現代化建議 (5.7+/8.0)

• 棄用 master-host 等舊參數，全面改用 CHANGE MASTER TO 動態配置。
• 啟用 GTID + MASTER_AUTO_POSITION=1，免記錄 File/Position。
• 使用 Replication Applier 多執行緒 (slave_parallel_workers=4) 加速回放。
• 部署 Orchestrator / MHA 實現自動故障轉移。

複寫是 MySQL 高可用基石，配置雖繁但邏輯清晰：Binlog → Relay Log → SQL Thread，掌握三線程模型即能舉一反三。