MySQL雙主同步配置全流程：GTID前身架構、自增偏移量、衝突檢測與故障切換實戰

架構拓撲

• ODD (Master A)：192.168.1.116，server-id=1，auto_increment_offset=1
• EVEN (Master B)：192.168.1.115，server-id=2，auto_increment_offset=2
• 同步庫：db_rocky
• 雙向復制：A→B、B→A 互為主從

核心配置參數解析

[mysqld]
# === 雙主專用關鍵參數 ===
server-id=1                    -- 全局唯一，極其重要
log-bin=mysql-bin              -- 啟用二進制日誌
log-slave-updates=ON           -- 從庫收到的變更也寫入binlog，傳遞給下游
sync_binlog=1                  -- 每次提交刷盤，最高持久性

# 自增ID防衝突（核心）
auto_increment_offset=1        -- 起始值：A=1, B=2
auto_increment_increment=2     -- 步長：均為2 → A產生奇數，B產生偶數

# 同步過濾
binlog-do-db=db_rocky          -- 只記錄指定庫
binlog-ignore-db=mysql         -- 忽略系統庫
replicate-do-db=db_rocky       -- 只同步指定庫
replicate-ignore-db=mysql,information_schema

# 容錯
slave-skip-errors=all          -- 跳過非致命錯誤（生產慎用，建議具體錯誤碼）

配置部署六步走

1. 創建同步賬號：雙向授權GRANT REPLICATION SLAVE ON *.* TO 'water'@'對端IP' IDENTIFIED BY 'pwd'
2. 修改my.cnf：雙方配置上述參數，重啟MySQL生效
3. 鎖表獲取坐標：`FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS\G` 記錄File、Position
4. 配置同步起點：

   -- ODD 指向 EVEN
   CHANGE MASTER TO MASTER_HOST='192.168.1.115', MASTER_USER='water', MASTER_PASSWORD='pwd',
       MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=107;
   
   -- EVEN 指向 ODD
   CHANGE MASTER TO MASTER_HOST='192.168.1.116', MASTER_USER='water', MASTER_PASSWORD='pwd',
       MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=438;

5. 解鎖啟動：`UNLOCK TABLES; START SLAVE;`
6. 驗證狀態：`SHOW SLAVE STATUS\G` 確認Slave_IO_Running: Yes、Slave_SQL_Running: Yes

測試驗證

-- EVEN 建表插入
CREATE TABLE water (id INT);
INSERT INTO water VALUES (1);

-- ODD 查詢同步結果
SELECT * FROM water;  -- 顯示 id=1，同步成功

避坑指南與生產建議

• 寫入分離：應用層路由寫入單一主庫，避免雙寫衝突（自增偏移量只解決PK衝突，不解決業務唯一鍵衝突）
• GTID升級：MySQL 5.6+強烈建議啟用GTID_MODE=ON，簡化故障切換、自動定位坐標
• 監控告警：`Seconds_Behind_Master`、Slave_IO_Running/SQL_Running、Last_Error納入監控
• 備份策略：從庫停庫備份或XTRABACKUP熱備，主庫不做備份
• 只讀開關：從庫設置read_only=ON（超級用戶除外），防止誤寫入

雙主同步是高可用架構基石，但僅解決主庫單點故障，不解決寫入擴展。寫入擴展需分庫分表或NewSQL（TiDB/OceanBase）。