MySQL 雙主同步複寫實戰教學：跨機房高可用架構部署

本文實作兩台異地 Linux 伺服器互為主從的 MySQL 雙主同步複寫架構，實現資料即時一致與故障自動切換。涵蓋帳號建立、參數配置、手動同步、狀態監控、網路中斷恢復與同步失效修復完整流程。

架構規劃

同步資料庫：umsdb，忽略特定日誌表減少同步負擔。

1. 建立同步帳號

-- ServA 執行，允許 ServB 連線
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'10.240.136.149' IDENTIFIED BY '123456';

-- ServB 執行，允許 ServA 連線
GRANT ALL PRIVILEGES ON *.* TO 'tongbu'@'10.240.136.9' IDENTIFIED BY '123456';

2. 配置 my.cnf 關鍵參數

ServA (server-id=1)

[mysqld]
default-character-set=utf8
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=1
master-host=10.240.136.149
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

ServB (server-id=2)

[mysqld]
default-character-set=utf8
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
server-id=2
master-host=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. 手動啟動初始同步

# 以 ServA 為主，ServB 為從
# ServB 重啟後登入 MySQL
STOP SLAVE;
LOAD DATA FROM MASTER;  -- 僅支援 MyISAM 表
START SLAVE;

# ServA 重啟完成雙向同步

4. 監控同步狀態

SHOW SLAVE STATUS\G

關鍵指標：

• Slave_IO_Running: Yes — I/O 執行緒正常
• Slave_SQL_Running: Yes — SQL 執行緒正常
• Seconds_Behind_Master: 0 — 無延遲
• Last_Error 為空 — 無錯誤

5. 網路中斷自動恢復優化

問題：網路斷線後恢復，Slave_IO_Running 長時間為 No，同步嚴重延遲。

原因：預設 slave-net-timeout 過長（預設 3600 秒），Slave 等待超時才重連。

解決：在 my.cnf [mysqld] 區段加入：

slave-net-timeout=60

重啟 MySQL 後，斷線 60 秒即自動重連同步。

6. 同步失效修復流程

情況一：Master Binlog 仍存在

# Slave 執行
STOP SLAVE;
START SLAVE;  -- 自動追上

情況二：Master Binlog 已清理（需全量重同步）

# Slave
STOP SLAVE;
LOAD DATA FROM MASTER;
RESET MASTER;
START SLAVE;

# Master
STOP SLAVE;
RESET SLAVE;
START SLAVE;

注意： LOAD DATA FROM MASTER 僅支援 MyISAM 表。InnoDB 請用 mysqldump --master-data=2 方案。

生產環境建議

• 啟用 sync_binlog=1、innodb_flush_log_at_trx_commit=1 確保資料不丟失
• 配合 auto_increment_offset/auto_increment_increment 避免主鍵衝突
• 部署 MHA/Orchestrator 實現自動故障切換
• 定期 pt-table-checksum 驗證資料一致性