架構定位

**雙主雙向複寫**：兩台 MySQL 互為主從，雙向同步寫入。適用於雙活、跨機房高可用、讀寫分離負載均衡。關鍵挑戰：自增 ID 衝突，必須透過 auto_increment_increment / auto_increment_offset 錯開。

雙節點規劃

→ Node A 產生奇數 ID (1,3,5...)，Node B 產生偶數 ID (2,4,6...)，**永不衝突**。

Node A（Master 1）配置

# /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
log-slave-updates=1
relay_log=/var/lib/mysql/mysql-relay-bin
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
slave-skip-errors=all
validate_password=OFF

Node B（Master 2）配置

# /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2
log-bin=mysql-bin
log-slave-updates=1
relay_log=/var/lib/mysql/mysql-relay-bin
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
slave-skip-errors=all
validate_password=OFF

關鍵參數解析

建立複寫帳號（雙向各執行一次）

-- Node A 執行，授權 Node B 連線
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.3.115' IDENTIFIED BY 'zcxc123';
FLUSH PRIVILEGES;

-- Node B 執行，授權 Node A 連線
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.3.116' IDENTIFIED BY 'zcxc123';
FLUSH PRIVILEGES;

初始化同步流程（冷備份法）

1. Node A 鎖表備份：

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;  -- 記錄 File/Position
   mysqldump -uroot -p --all-databases --master-data=2 --single-transaction > /tmp/all_db.sql
   UNLOCK TABLES;

2. 傳輸並導入 Node B：

   scp /tmp/all_db.sql root@192.168.3.115:/tmp/
   mysql -uroot -p < /tmp/all_db.sql

3. Node B 配置複寫指向 Node A：

   RESET SLAVE;
   CHANGE MASTER TO
     MASTER_HOST='192.168.3.116', MASTER_USER='repl_user', MASTER_PASSWORD='zcxc123',
     MASTER_LOG_FILE='mysql-bin.000080', MASTER_LOG_POS=154;
   START SLAVE;

4. Node A 配置複寫指向 Node B（同理，用 Node B 的 Binlog 位置）

驗證雙向同步

-- Node A 執行
CREATE DATABASE test_db;
CREATE TABLE test_db.t1 (id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(20));
INSERT INTO test_db.t1 (v) VALUES ('from A');

-- Node B 查詢
SHOW DATABASES LIKE 'test_db';
SELECT * FROM test_db.t1;  -- 應看到 id=1 (奇數)

-- Node B 執行
INSERT INTO test_db.t1 (v) VALUES ('from B');

-- Node A 查詢
SELECT * FROM test_db.t1;  -- 應看到 id=2 (偶數)

常見坑點與對策

監控指標

• Seconds_Behind_Master 兩向皆 < 1 秒
• SHOW SLAVE STATUS\G 無 Last_SQL_Error
• Binlog 磁碟增長速率正常（無暴增）
• 定期 pt-table-checksum 驗證數據一致性