MySQL 主從復制原理與配置全解析：二進制日志、中繼日志、多層級聯架構與常見拓撲

MySQL 主從復制完整指南：原理、配置、拓撲與進階

1. 復制原理三步驟

1. Master：事務提交前將變更順序寫入 Binary Log (binlog)。
2. Slave I/O 線程：連接 Master，請求 binlog 事件，寫入本地 Relay Log。
3. Slave SQL 線程：讀取 Relay Log，重放事件更新本地數據。

關鍵限制：Slave 重放為串行，Master 並行寫入在 Slave 端順序執行。

2. 復制類型

• Statement-Based (SBR)：記錄 SQL 語句，日志小，但非確定性函數（NOW、RAND）可能導致不一致。
• Row-Based (RBR)：記錄行變更，精確、無歧義，日志大。
• Mixed：預設 SBR，遇不安全語句自動切 RBR（binlog_format=MIXED）。

3. 基礎配置步驟

Master

[mysqld]
server-id=1
log-bin=mysql-bin
# 可選：binlog_format=MIXED

創建復制帳號：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip' IDENTIFIED BY 'pass';
FLUSH PRIVILEGES;

Slave

[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave-updates=1   # 若作為下級 Master
read-only=1           # 防止誤寫

建立復制鏈接

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='pass',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=4;
START SLAVE;

驗證

SHOW SLAVE STATUS\G
-- 關鍵：Slave_IO_Running=Yes, Slave_SQL_Running=Yes, Seconds_Behind_Master=0

4. 相關文件

• mysql-bin.index：binlog 文件索引。
• mysql-relay-bin.index：relay log 索引。
• master.info：記錄 Master 連接信息、binlog 位點（勿刪）。
• relay-log.info：SQL 線程執行位點。

5. 常見拓撲架構

6. 進階：多源復制（MySQL 5.7+）

單 Slave 可從多 Master 復制不同庫/表，通道隔離：

CHANGE MASTER TO MASTER_HOST='m1', ... FOR CHANNEL 'c1';
CHANGE MASTER TO MASTER_HOST='m2', ... FOR CHANNEL 'c2';
START SLAVE FOR CHANNEL 'c1';

7. 監控與運維建議

• 監控 Seconds_Behind_Master、Slave_*_Running、Relay Log 積壓。
• 定期 SHOW SLAVE STATUS、SHOW MASTER STATUS 對比位點。
• 備份從庫（--single-transaction），主庫避免備份壓力。
• 使用 GTID (Global Transaction ID) 簡化故障轉移與拓撲變更。