MySQL 雙主同步複寫實戰教學：異地雙機熱備完整配置

架構概覽

MySQL 原生支援單向、非同步複寫。本文實作「雙主互備」架構：兩台異地伺服器互為 Master/Slave，任一節點故障時另一節點無縫接管，確保資料即時一致與服務高可用。

環境資訊

• 作業系統：RedHat Enterprise Linux 5
• MySQL 版本：5.0.22
• 節點 A (ServA)：10.240.136.9
• 節點 B (ServB)：10.240.136.149
• 同步資料庫：umsdb
• 忽略同步資料表：boco_tb_menu、boco_tb_connect_log、boco_tb_data_stat、boco_tb_log_record、boco_tb_workorder_record

1. 建立跨節點複寫帳號

於 ServA 建立供 ServB 連線的帳號，反之亦然：

-- On ServA
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'10.240.136.149' IDENTIFIED BY '123456';

-- On ServB
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'10.240.136.9' IDENTIFIED BY '123456';

2. 修改 my.cnf 參數（雙邊對稱配置）

關鍵差異： server-id 必須唯一（ServA=1, ServB=2），master-host 指向對方 IP。

[mysqld]
default-character-set=utf8
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=1                  # ServB 改為 2
master-host=10.240.136.149   # ServB 改為 10.240.136.9
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
replicate-ignore-table=umsdb.boco_tb_data_stat
replicate-ignore-table=umsdb.boco_tb_log_record
replicate-ignore-table=umsdb.boco_tb_workorder_record

3. 手動啟動初始同步

1. 以 ServA 為主，重啟 ServB MySQL 服務
2. 於 ServB 執行：

STOP SLAVE;
LOAD DATA FROM MASTER;
START SLAVE;

3. 重啟 ServA MySQL 服務，完成雙向同步建立

4. 監控同步狀態

執行 SHOW SLAVE STATUS\G，重點檢查兩行關鍵指標：

• Slave_IO_Running: Yes — I/O 執行緒正常接收 Binlog
• Slave_SQL_Running: Yes — SQL 執行緒正常重放事件

若任一為 No 或 Last_Error 非空，即表示同步異常需介入處理。

5. 實測遇到的坑與解決

問題：網路中斷後同步長時間不恢復

拔掉 ServB 網線，ServA 寫入資料，恢復網路後 ServB Slave_IO_Running 長時間為 No。

根因： 預設 slave-net-timeout 過長（預設 3600 秒），Slave 判定連線失敗太慢。

修正： 於 my.cnf 新增 slave-net-timeout=60，重啟後問題解決。

6. 同步失效後的災難恢復流程

1. 於 Slave 執行 SHOW SLAVE STATUS\G 記下 Master_Log_File 與 Read_Master_Log_Pos
2. 於 Master 執行 SHOW MASTER LOGS 確認該 Binlog 檔案是否仍存在
3. 存在：Slave 執行 START SLAVE 即可自動追趕
4. 不存在（Master 已輪轉刪除）：需重新全量同步

全量重同步步驟：

-- On Slave
STOP SLAVE;

-- On Master
STOP SLAVE;

-- On Slave
LOAD DATA FROM MASTER;
RESET MASTER;
START SLAVE;

-- On Master
RESET SLAVE;
START SLAVE;

注意： LOAD DATA FROM MASTER 僅支援 MyISAM 引擎資料表。

總結

雙主同步雖能提升可用性，但需謹慎處理 server-id、auto_increment_offset/increment 避免主鍵衝突，並建議搭配 MHA、Orchestrator 等自動化故障轉移工具落地生產環境。