萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 主主雙向復制 (雙主同步) 完整配置步驟與避坑指南

雙主復制讓兩台 MySQL 互為主從,寫入自動雙向同步。本文提供生產級配置模板:server-id、auto_increment_offset/increment 錯開自增、雙向 CHANGE MASTER TO、同步驗證、防火牆/網路互通檢查清單。

架構圖

Server A (192.168.1.254, server-id=1) ←→ Server B (192.168.1.252, server-id=2)
   ↑ 寫入/讀取                                    ↑ 寫入/讀取
   Binlog → Relay Log → SQL Thread              Binlog → Relay Log → SQL Thread

核心配置 (my.cnf)

參數Server AServer B說明
server-id12全域唯一
log-binmysql-binmysql-bin啟用 Binlog
replicate-do-dbtesttest只同步 test 庫
binlog-ignore-dbmysql,information_schema同不同步系統庫
auto-increment-increment22節點總數
auto-increment-offset12錯開起始值 (奇/偶)
expire-logs-days100100Binlog 保留天數

部署步驟

  1. 雙邊修改 my.cnf 並 service mysqld restart
  2. 建立同步帳號互授:
    -- On A
    GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.252' IDENTIFIED BY '123456';
    -- On B
    GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.1.254' IDENTIFIED BY '123456';
  3. 同步初始資料:A 鎖表 → mysqldump → SCP 到 B → B 導入 → 解鎖
  4. 互通 Binlog 位置:
    -- On A: SHOW MASTER STATUS; 記錄 File/Position
    -- On B: SHOW MASTER STATUS; 記錄 File/Position
    
    -- On A 執行 (指向 B 的 Binlog)
    CHANGE MASTER TO MASTER_HOST='192.168.1.252', MASTER_USER='mysync', MASTER_PASSWORD='123456',
      MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=192;
    
    -- On B 執行 (指向 A 的 Binlog)
    CHANGE MASTER TO MASTER_HOST='192.168.1.254', MASTER_USER='mysync', MASTER_PASSWORD='123456',
      MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;
  5. 雙邊 START SLAVE;
  6. 驗證:SHOW SLAVE STATUS \G 確認 Slave_IO_Running: Yes、Slave_SQL_Running: Yes

關鍵避坑點

  • 🔥 防火牆/安全組必須雙向放行 3306,否則 IO Thread 永遠 Connecting
  • 🔥 auto_increment_offset 必須錯開,否則主鍵衝突導致 SQL Thread 停止
  • 🔥 僅同步指定庫 (replicate-do-db),避免系統表同步衝突
  • 監控 Seconds_Behind_Master,延遲超閾值告警
  • 5.6+ 強烈建議啟用 GTID (MASTER_AUTO_POSITION=1),故障切換零配置

測試驗證:A 插入一筆 → B 立即可查;B 更新 → A 同步生效。雙向寫入業務層需自行解決衝突 (如最後寫入勝、版本號樂觀鎖)。

copyright © 萬盛學電腦網 all rights reserved