MySQL 主從複制全解析：原理、搭建、故障排查與進階技巧

MySQL 複制較 NoSQL 更複雜，但功能強大。本文系統梳理複制原理、搭建步驟、故障處理、進階技巧與常用工具。

核心原理：三線程協作

1. 主庫 Binlog Dump 線程：將數據變更寫入二進制日志
2. 從庫 I/O 線程：讀取主庫 Binlog，寫入本地中繼日志
3. 從庫 SQL 線程：重放中繼日志，實現數據同步

關鍵日志坐標

SHOW SLAVE STATUS 輸出三組關鍵坐標：

理解這三組坐標是故障排查的基石。

搭建步驟：標準化流程

1. 主庫配置

[mysqld]
server_id = 100
log_bin = mysql-bin
log_bin_index = mysql-bin.index
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1

創建複制賬號：

GRANT REPLICATION SLAVE ON *.* 
TO 'slave_user'@'slave_host' 
IDENTIFIED BY 'password';

2. 從庫配置

[mysqld]
server_id = 200
log_bin = mysql-bin
relay_log = mysql-relay-bin
read_only = 1
skip_slave_start = 1
log_slave_updates = 1

3. 數據初始化（兩種方式）

mysqldump 熱備份（適合中小數據量）：

# MyISAM
mysqldump --all-databases --master-data=1 > data.sql

# InnoDB（推薦單事務一致性）
mysqldump --all-databases --single-transaction --master-data=1 > data.sql

物理文件拷貝（大數據量）：

# 主庫鎖表獲取位置
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
# 拷貝數據目錄（InnoDB 需停庫）
# 從庫恢復後執行 CHANGE MASTER TO 指定位置

4. 啟動複制

CHANGE MASTER TO 
MASTER_HOST='...', MASTER_USER='...', MASTER_PASSWORD='...',
MASTER_LOG_FILE='...', MASTER_LOG_POS=...;
START SLAVE;
SHOW SLAVE STATUS;

確認 Slave_IO_Running=Yes 且 Slave_SQL_Running=Yes。

故障排查決策樹

Binlog 損壞

mysqlbinlog master-bin.000001 > /dev/null  # 檢測主庫
mysqlbinlog relay-bin.000001 > /dev/null   # 檢測從庫

主庫錯誤：SET GLOBAL sql_slave_skip_counter = 1; START SLAVE;
中繼日志錯誤：重新 CHANGE MASTER TO 指向 Relay_Master_Log_File / Exec_Master_Log_Pos。

主庫宕機：從庫提升為主

選擇數據最新的從庫（比較 Master_Log_File / Read_Master_Log_Pos）。其他從庫重新指向新主庫，位置計算：新主庫 Exec_Master_Log_Pos - (舊主庫位置差值)。

進階技巧

• 異構存儲引擎：主庫 InnoDB（事務），從庫 MyISAM（只讀、易備份）
• 異構索引策略：主庫最小索引（寫優化），從庫豐富索引（讀優化），不同從庫針對不同查詢建索引
• 中繼節點：一主多從時，引入 BLACKHOLE 引擎中繼節點分擔主庫壓力

推薦工具生態

• MHA：主從自動故障切換
• Percona XtraBackup：物理熱備份
• Maatkit / Percona Toolkit：複制檢查、數據一致性驗證
• Tungsten Replicator：跨版本、異構複制