MySQL 主從複寫與雙主熱備配置完整指南：線上資料同步實戰

MySQL 複寫架構概覽

MySQL 3.23.15+ 支援資料庫複寫，實現：

• 主從模式：單向同步 (Master → Slave)
• 互備模式：雙向同步 (Master ↔ Master)

環境準備

版本：MySQL 4.0.4-beta-max-nt-log，OS：Windows 2000

共同前置作業

建立複寫專用帳號

-- 節點 A 執行
GRANT FILE ON *.* TO 'backup'@'10.10.10.53' IDENTIFIED BY '1234';

-- 節點 B 執行
GRANT FILE ON *.* TO 'backup'@'10.10.10.22' IDENTIFIED BY '1234';

建立同步資料庫

CREATE DATABASE backup;

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

Master (A) 設定 my.ini

[mysqld]
server-id = 1
log-bin
# log-bin = c:\mysqlbak\mysqlbinlog  # 可指定日誌路徑與前綴
binlog-do-db = backup                # 僅記錄 backup 資料庫變更

重啟服務，執行 SHOW MASTER STATUS; 確認二進制日誌狀態。

Slave (B) 設定 my.ini

[mysqld]
server-id = 2
master-host = 10.10.10.22
master-user = backup
master-password = 1234
master-port = 3306
master-connect-retry = 60
replicate-do-db = backup

重啟服務，執行 SHOW SLAVE STATUS; 確認 Slave_IO_Running=Yes、Slave_SQL_Running=Yes。

關鍵提醒

• Slave 啟動後會在資料目錄產生 master.info，修改複寫設定必須先刪除此檔案再重啟，否則舊設定優先生效
• 發生錯誤時查看 *.err 日誌，修正後執行 SLAVE START 恢復同步

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

在模式一基礎上，雙向新增對方為 Master 的設定：

節點 A 新增 Slave 設定

[mysqld]
master-host = 10.10.10.53
master-user = backup
master-password = 1234
replicate-do-db = backup
master-connect-retry = 10

節點 B 新增 Master 設定

[mysqld]
log-bin = c:\mysqlbak\mysqlbinlog
binlog-do-db = backup

重啟雙節點

完成雙向熱備，任一節點寫入 backup 資料庫即同步至另一節點。

效能測試數據

• 批次插入 1,872,000 筆測試資料
• Slave 端每秒同步約 2,500 筆
• 網路延遲對同步延遲影響顯著，建議同機房部署

遷移至現代版本注意事項 (MySQL 5.6+/8.0)

• 使用 GTID (Global Transaction ID) 取代檔案定位點複寫
• CHANGE MASTER TO 動態配置取代 my.cnf 靜態配置
• 支援多源複寫、延遲複寫、半同步複寫
• 監控工具：SHOW SLAVE STATUS → performance_schema.replication_*