MySQL 主從複製架構搭建與運維全攻略

為什麼需要主從複製？

• 讀寫分離：主庫寫、從庫讀，線性擴展查詢吞吐
• 備份卸載：從庫停止 SQL 線程冷備份，主庫零影響
• 災難恢復：主庫故障時提升從庫為新主
• 異構報表：從庫建不同索引支撐 OLAP 查詢

架構原理：三線程協作模型

1. Binlog Dump Thread (主)：讀取 Binlog 發送給從庫
2. I/O Thread (從)：接收 Binlog 寫入 Relay Log
3. SQL Thread (從)：重放 Relay Log 執行事務

支持多從庫、級聯複製、雙主互備等拓撲。

搭建步驟（基於配置文件方式，MySQL 5.1 風格）

1. 主庫配置 /etc/my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = db1
binlog-do-db = db2          # 多庫重複配置，勿用逗號
binlog-ignore-db = test

2. 創建複製賬戶

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'StrongPass#2024';

3. 從庫配置 /etc/my.cnf

[mysqld]
server-id = 2
relay-log = relay-bin
log-bin = mysql-bin          # 建議開啟，便於從庫升主
master-host = 192.168.1.100
master-user = repl_user
master-password = StrongPass#2024
master-port = 3306
replicate-do-db = db1
replicate-do-db = db2

4. 初始化數據同步

# 主庫
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File + Position
mysqldump --single-transaction --master-data=2 db1 db2 > init.sql
UNLOCK TABLES;

# 從庫
mysql < init.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=370558;
START SLAVE;

驗證與監控

核心命令

SHOW SLAVE STATUS\G

關鍵指標：

• Slave_IO_Running: Yes — I/O 線程正常
• Slave_SQL_Running: Yes — SQL 線程正常
• Seconds_Behind_Master: 0 — 無延遲
• Master_Log_File / Read_Master_Log_Pos — 已讀取 Binlog 位點
• Relay_Log_File / Relay_Log_Pos — 已執行 Relay Log 位點

常見故障排查

進階運維技巧

1. 從庫冷備份不停主庫

STOP SLAVE;
# 拷貝數據目錄或 LVM 快照
START SLAVE;

2. 動態調整複製過濾

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1,db3);

3. 跳過錯誤事務（慎用）

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

4. GTID 模式（5.6+ 推薦）

啟用 gtid_mode=ON，CHANGE MASTER TO MASTER_AUTO_POSITION=1 自動定位，無需手動管理位點。

雙主互備注意事項

• 雙向配置 auto_increment_offset/auto_increment_increment 避免主鍵衝突
• 嚴禁雙向寫入同一表，邏輯分庫或應用層路由
• 監控雙向 Seconds_Behind_Master，防止環狀複製風暴

版本兼容性建議

• 主從大版本一致（如 5.7→5.7）
• 從庫版本 ≥ 主庫版本（支持向上兼容）
• 跨大版本升級採用「從庫先升、切主、再升舊主」滾動方式