架構拓撲

Master (192.168.1.100)  ──Binlog──►  Slave1 (192.168.1.101)
                                    Slave2 (192.168.1.102)
                                    ...

• Master：寫入、Binlog 記錄
• Slave：Relay log → SQL 執行緒重放、提供讀查詢

配置步驟（MySQL 5.7/8.0 通用）

1. Master 配置 /etc/my.cnf

[mysqld]
server-id = 1                    # 唯一，1-2^32-1
log-bin = mysql-bin              # 啟用二進制日誌
binlog-format = ROW              # 推薦 ROW 格式，數據一致性最好
binlog-do-db = business_db       # 只同步業務庫（可選）
binlog-ignore-db = mysql,information_schema,performance_schema,sys
expire_logs_days = 7             # 自動清理舊 binlog
max_binlog_size = 100M
sync_binlog = 1                  # 每次提交刷盤，最安全
innodb_flush_log_at_trx_commit = 1

重啟：systemctl restart mysqld

2. 建立複寫帳號

CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';
FLUSH PRIVILEGES;

3. 獲取 Master 同步座標

FLUSH TABLES WITH READ LOCK;  -- 短暫鎖表
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 154      | business_db  | ...              |
+------------------+----------+--------------+------------------+
UNLOCK TABLES;

記下 File 和 Position。

4. Slave 配置 /etc/my.cnf

[mysqld]
server-id = 2                    # 唯一，不同於 Master
relay-log = mysql-relay-bin
log-slave-updates = 1            # 從庫也寫 binlog，支援鏈式複寫
read-only = 1                    # 防止誤寫入
replicate-do-db = business_db    # 只同步業務庫
replicate-ignore-db = mysql,information_schema,performance_schema,sys

重啟 Slave。

5. 啟動複寫

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='ReplPass123!',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

關鍵指標：

• Slave_IO_Running: Yes — IO 執行緒連接 Master 正常
• Slave_SQL_Running: Yes — SQL 執行緒重放正常
• Seconds_Behind_Master: 0 — 無延遲

資料初始化同步（避免從頭重放 binlog）

# Master 熱備份（不鎖表）
mysqldump --single-transaction --master-data=2 --default-character-set=utf8mb4 \
  -uroot -p business_db > /backup/business_db.sql

# 複製到 Slave 導入
mysql -uroot -p business_db < /backup/business_db.sql
# --master-data=2 會在檔案註解中記錄 binlog 位置，導入後自動定位

常見容錯與避坑指南

1. 主從延遲監控與告警

# 秒數超過 30 秒告警
Seconds_Behind_Master > 30

原因：大事務、DDL、從庫硬體較弱、長查詢阻塞 SQL 執行緒。

2. 讀寫分離中「寫後讀」不一致

問題：Master 寫入 → 返回成功 → Slave 尚未同步 → 讀請求打到 Slave 讀不到新資料。

解決：

• 關鍵業務強制走 Master 讀
• 應用層快取新寫入資料（Redis），讀取時優先查快取
• 半同步複寫 rpl_semi_sync_master=ON 降低延遲風險

3. 自增 ID 衝突（雙主/多主）

# Master 1
auto_increment_offset = 1
auto_increment_increment = 2

# Master 2
auto_increment_offset = 2
auto_increment_increment = 2

4. 表結構差異導致複寫中斷

• DDL 變更需在低峰期、先在 Slave 執行（或用 pt-online-schema-change）
• 忽略特定錯誤：SET GLOBAL sql_slave_skip_counter = 1;（慎用）

5. GTID 模式（MySQL 5.6+ 推薦）

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = 1

# 啟動複寫簡化為
CHANGE MASTER TO MASTER_HOST='...', MASTER_USER='...', MASTER_PASSWORD='...', MASTER_AUTO_POSITION=1;

優點：自動定位、故障切換簡單、支援多源複寫。

生產環境檢查清單

• ✅ server-id 全局唯一
• ✅ binlog_format=ROW + sync_binlog=1
• ✅ 專用複寫帳號、最小權限、強密碼
• ✅ Slave read-only=1、監控 Seconds_Behind_Master
• ✅ 定期驗證資料一致性 pt-table-checksum
• ✅ 備份策略覆蓋 Master/Slave
• ✅ 故障切換演練（MHA/Orchestrator/手動）