您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 主從複制原理與配置完整指南

編輯：MySQL 綜合教學

MySQL 主從複制原理與配置完整指南

MySQL 複制將主庫的資料異步複製到一或多個從庫。核心用途：讀寫分離擴展讀效能、從庫備份不影響主庫、異地災備、報表查詢卸載。MySQL 5.5+ 支援半同步複制，提升資料一致性保障。

一、複制架構與執行緒模型

標準一主一從涉及三個執行緒：

主庫 Binlog Dump Thread：讀取主庫二進位日誌，推送事件給從庫
從庫 I/O Thread：接收事件，寫入本地中繼日誌
從庫 SQL Thread：讀取中繼日誌，在從庫重放執行

多從庫時，主庫為每個從庫建立一個 Dump Thread；從庫可獨立控制複制進度，互不干擾。

二、二進位日誌格式

格式	說明	優點	缺點
SBR (Statement-Based)	記錄修改 SQL 語句	日誌量小、可審計、相容舊版	非確定性函數/語句不安全、鎖競爭大、複雜語句從庫重放耗時
RBR (Row-Based)	記錄每行實際變更	最安全、鎖較少、從庫重放快	日誌量大、無法直接審計 SQL
MBR (Mixed)	MySQL 5.5+ 預設，自動切換	兼顧安全與日誌體積	極少數邊界情況仍需人工干預

配置：binlog_format = MIXED（動態可變）。

三、啟用複制步驟

1. 主庫建立複制專用帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'strong_password';
FLUSH PRIVILEGES;

建議單獨帳號，避免 master.info 明文密碼洩露風險。

2. 主庫配置

[mysqld]
log-bin = master-bin
server-id = 1
binlog_format = MIXED
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
skip-networking = 0

重啟後驗證：SHOW MASTER STATUS; 記錄 File、Position。

3. 從庫配置

[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
# log-bin 可選，開啟則從庫可作為下一級主庫

4. 獲取主庫同步點（若主庫已有資料）

-- 主庫鎖表防寫入
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 記錄 File、Position，然後導出資料
mysqldump --all-databases --master-data=2 --single-transaction > full.sql
-- 解鎖
UNLOCK TABLES;

5. 從庫指定主庫資訊並啟動

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='strong_password',
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;

6. 驗證狀態

SHOW SLAVE STATUS\G
-- 關鍵欄位：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

四、常用監控指令

SHOW SLAVE HOSTS：主庫查看所有已連線從庫
SHOW MASTER STATUS：主庫當前 Binlog 位置
SHOW SLAVE STATUS：從庫詳細狀態、延遲、錯誤
SHOW BINLOG EVENTS [IN 'log_file']：查看 Binlog 事件細節

五、進階拓撲與特性

鏈式複制：A→B→C，中間節點同時是從庫與主庫
雙主/環狀複制：需設定 auto_increment_offset/increment 避免主鍵衝突
半同步複制：主庫提交前等待至少一個從庫確認收到（rpl_semi_sync_master_enabled=1）
GTID 複制：MySQL 5.6+，基於全域事務 ID，簡化故障轉移與拓撲變更
多源複制：MySQL 5.7+，單從庫可同步多個主庫

六、常見故障與排查

現象	可能原因	處理
Slave_IO_Running: No	網路不通、認證失敗、主庫 Binlog 已清理	檢查防火牆、帳號密碼、`MASTER_LOG_FILE/POS` 是否存在
Slave_SQL_Running: No	重放錯誤（主鍵衝突、外鍵失敗、語法不相容）	`SHOW SLAVE STATUS` 看 `Last_SQL_Error`，修正資料後 `STOP SLAVE; START SLAVE;`
Seconds_Behind_Master 持續增大	從庫硬體較弱、大事務、長鎖表、單執行緒瓶頸	升級硬體、啟用多執行緒複制、優化大事務拆分
主從資料不一致	直接在從庫寫入、非確定性函數、SBR 邊界案例	使用 `pt-table-checksum` 校驗、`pt-table-sync` 修復