架構選型對比

架構	特點	適用場景
主從	單向寫入，從庫只讀，簡單穩定	讀寫分離、報表查詢、備份
雙主	雙向寫入，需解決自增衝突	高可用雙活、異地雙中心
多主（Galera/Group Replication）	多寫入節點，強一致性	金融核心、高併發強一致

雙主同步部署步驟（基於 GTID/文件位置）

1. 環境準備

• Server A: 192.168.1.1，Server B: 192.168.1.2
• MySQL 版本一致（主版本號相同，如 5.7.x）
• 關閉防火牆或放行 3306

2. 創建同步賬號

-- On A
CREATE USER 'sync_user'@'192.168.1.2' IDENTIFIED BY 'StrongPass!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync_user'@'192.168.1.2';

-- On B
CREATE USER 'sync_user'@'192.168.1.1' IDENTIFIED BY 'StrongPass!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync_user'@'192.168.1.1';

FLUSH PRIVILEGES;

3. 配置 my.cnf（關鍵差異化參數）

# Server A
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
binlog-do-db = business_db
replicate-do-db = business_db
auto_increment_increment = 2
auto_increment_offset = 1

# Server B
[mysqld]
server-id = 2
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
binlog-do-db = business_db
replicate-do-db = business_db
auto_increment_increment = 2
auto_increment_offset = 2

關鍵點：auto_increment_increment/offset 交錯配置，避免雙主自增 ID 衝突。

4. 初始數據同步

# On A：鎖表導出
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
mysqldump -u root -p --single-transaction --master-data=2 --databases business_db > /tmp/biz.sql
mysql -u root -p -e "UNLOCK TABLES;"

# 複製到 B 導入
scp /tmp/biz.sql 192.168.1.2:/tmp/
mysql -u root -p business_db < /tmp/biz.sql

5. 建立雙向同步

# On A：查看 master 狀態
SHOW MASTER STATUS\G;
-- 記下 File: mysql-bin.000001, Position: 154

# On B：配置從 A 同步
CHANGE MASTER TO
  MASTER_HOST='192.168.1.1',
  MASTER_USER='sync_user',
  MASTER_PASSWORD='StrongPass!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_AUTO_POSITION=1;  -- GTID 模式
START SLAVE;

# On B：查看 master 狀態
SHOW MASTER STATUS\G;
-- 記下 File: mysql-bin.000002, Position: 200

# On A：配置從 B 同步
CHANGE MASTER TO
  MASTER_HOST='192.168.1.2',
  MASTER_USER='sync_user',
  MASTER_PASSWORD='StrongPass!',
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=200,
  MASTER_AUTO_POSITION=1;
START SLAVE;

6. 驗證同步狀態

SHOW SLAVE STATUS\G;
-- 關鍵指標：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Last_Error: (空)

常見故障與避坑

現象	原因	處理
Slave_IO_Running: Connecting	網絡不通/賬號密碼錯/防火牆	telnet 3306、檢查 grant host、防火牆
Slave_SQL_Running: No	SQL 執行報錯（主鍵衝突/字段缺失）	SHOW SLAVE STATUS\G 看 Last_Error，修數據或 SET GLOBAL sql_slave_skip_counter=1 跳過
Seconds_Behind_Master 偶發飆升	大事務/長SQL/從庫硬件弱	拆分大事務、優化慢查詢、升級從庫配置
自增 ID 衝突	未配置 auto_increment_increment/offset	補齊交錯配置，重新初始化從庫

異步 vs 半同步

• 異步（默認）：主庫不等從庫確認，性能最好，主庫掛掉可能丟數據
• 半同步：rpl_semi_sync_master=ON，主庫等至少一個從庫收到日志再提交，數據更安全，略增延遲
• 生產核心業務強烈建議開啟半同步