MySQL 資料同步架構實戰：主從單向同步與雙主互備完整配置指南

MySQL 3.23.15+ 引入的複製功能，是高可用、讀寫分離、異地災備的基石。本文基於實戰環境 (Windows Server 2000 + MySQL 4.0.4)，詳細記錄 主從單向 與 雙主互備 兩種架構的配置步驟、關鍵參數、故障處理與效能驗證。

環境準備與前置條件

角色	IP	MySQL 版本	Server ID
Master (A)	10.10.10.22	4.0.4-beta-max-nt-log	1
Slave (B)	10.10.10.53	4.0.4-beta-max-nt-log	2

雙方共同準備

-- 1. 建立同步專用帳號 (雙向各建一次)
GRANT FILE ON *.* TO 'backup'@'10.10.10.53' IDENTIFIED BY '1234';  -- A 上建立 B 連線用
GRANT FILE ON *.* TO 'backup'@'10.10.10.22' IDENTIFIED BY '1234';  -- B 上建立 A 連線用

-- 2. 建立同步目標資料庫
CREATE DATABASE backup;

架構一：主從單向同步 (A → B)

Master (A) 配置 - my.ini [mysqld] 區段

server-id = 1
log-bin = mysql-bin                    # 啟用二進位日誌
# log-bin = C:/mysqlbak/mysqlbinlog    # 可指定日誌目錄與前綴
binlog-do-db = backup                  # 僅記錄 backup 資料庫變更 (可選，減少日誌體積)

重啟服務，驗證：SHOW MASTER STATUS; 記錄 File 與 Position。

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              # 重試間隔 60 秒
replicate-do-db = backup               # 僅同步 backup 資料庫

重啟服務，驗證：SHOW SLAVE STATUS\G 關注：

• Slave_IO_Running: Yes (I/O 執行緒連線 Master 讀取 Binlog)
• Slave_SQL_Running: Yes (SQL 執行緒重放 Relay Log)
• Seconds_Behind_Master: 0 (延遲秒數)

關鍵檔案：master.info

Slave 啟動後自動生成 master.info 記錄同步座標。**修改 Slave 配置前必須刪除此檔**，否則新配置不生效。

架構二：雙主互備 (A ↔ B) - 雙向熱備

在單向基礎上，雙方互為 Master/Slave。

A 追加 Slave 配置

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

B 追加 Master 配置

log-bin = C:/mysqlbak/mysqlbinlog
binlog-do-db = backup

雙主衝突預防 (關鍵！)

• 自增 ID 錯開：
  A: auto_increment_increment = 2; auto_increment_offset = 1 (產生 1,3,5...)
  B: auto_increment_increment = 2; auto_increment_offset = 2 (產生 2,4,6...)
• 業務層路由：寫入路由到固定 Master，避免雙寫衝突
• 唯一鍵約束：業務主鍵 (如訂單號) 必須全局唯一

故障處理與日常運維

同步錯誤處理流程

1. 檢查 SHOW SLAVE STATUS\G 中 Last_Error、Last_SQL_Error
2. 查看 .err 錯誤日誌檔案
3. 修正資料衝突 (手動修正/跳過/重做)
4. 執行 START SLAVE; 恢復同步

常見錯誤與對策

錯誤	原因	處理
Duplicate entry for key PRIMARY	雙主自增 ID 衝突	配置 auto_increment_offset/increment 錯開
Relay log read failure	網路中斷導致 Relay Log 損毀	STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE=..., MASTER_LOG_POS=...; START SLAVE;
Table doesn't exist	DDL 未同步 / 表結構不一致	手動同步結構，或使用 pt-table-sync

效能驗證數據 (文中實測)

• 向 B 批次插入 1,872,000 條資料
• A 每秒同步約 **2,500 條**
• 網路頻寬、磁碟 IO、Binlog 格式 (ROW/STATEMENT/MIXED) 影響最終速度

現代 MySQL (5.6+/8.0) 複製增強特性

• GTID (Global Transaction ID)：GTID_MODE=ON，自動定位同步位置，無需手動管理 File/Position
• 多源複製：單 Slave 可從多 Master 同步
• 延遲複製：MASTER_DELAY=3600 延遲 1 小時同步，防誤操作
• 半同步複製：rpl_semi_sync_master=ON，至少一個 Slave 收到才提交
• Binlog 格式：binlog_format=ROW (預設)，資料一致性最高

總結

MySQL 複製架構演進：
📋 主從單向：讀寫分離、報表分流、備份卸載
🔄 雙主互備：高可用雙活，**必須解決自增 ID 衝突與雙寫路由**
⚙️ 核心參數：server-id、log-bin、binlog-do-db、replicate-do-db、master-host/user/password
🔧 運維鐵律：修改 Slave 配置必刪 master.info、監控 Seconds_Behind_Master、定期 pt-table-sync 校驗一致性