MySQL 主從複製原理與配置深度詳解（完整版）

一、複製原理架構

MySQL 複製基於二進制日誌，採用三線程模型：

1. Binlog Dump Thread（主庫）：讀取 Binlog 發送給從庫。
2. I/O Thread（從庫）：連接主庫，請求 Binlog，寫入本地中繼日誌。
3. SQL Thread（從庫）：讀取 Relay Log，重放 SQL，更新從庫資料。

二、線程狀態詳解

主庫 Binlog Dump 線程

• Sending binlog event to slave：正常發送事件
• Finished reading one binlog; switching to next binlog：切換 Binlog 檔案
• Has sent all binlog to slave; waiting for binlog to be updated：空閒等待新事件

從庫 I/O 線程

• Connecting to master、Checking master version、Registering slave on master：連線階段
• Requesting binlog dump：請求 Binlog
• Waiting for master to send event：正常等待（主庫無寫入時長時間處於此狀態）
• Queueing master event to the relay log：寫入 Relay Log
• Waiting for slave mutex on exit：停止中

從庫 SQL 線程

• Reading event from the relay log：讀取事件準備執行
• Has read all relay log; waiting for the slave I/O thread to update it：追上主庫，等待新事件

三、關鍵檔案

備份建議：備份從庫時務必包含 master.info、relay-log.info、Relay Log，以便災難恢復後無縫續傳。

四、配置步驟完整流程

1. 主庫建立複製帳號

GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY 'logzgh';
-- 若需 LOAD DATA FROM MASTER，額外授予 SUPER、RELOAD、SELECT

2. 主庫資料快照（三種方式）

方式一：MyISAM 表 — FLUSH TABLES WITH READ LOCK + tar

鎖表 → 打包 /var/lib/mysql → SHOW MASTER STATUS 記錄位置 → UNLOCK TABLES → 傳輸解壓

方式二：InnoDB 表 — InnoDB Hot Backup (xtrabackup) / FLUSH TABLES WITH READ LOCK + 關機拷貝

熱備份無需鎖表；冷備份需記錄 Binlog 位置後關機拷貝資料/日誌/.frm

方式三：通用 — mysqldump --master-data

mysqldump --master-data=2 -u root -p --all-databases > full.sql（較慢，含 Binlog 位置註釋）

3. 配置 my.cnf

# Master
[mysqld]
log_bin = mysql-bin
server_id = 1
# 建議
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
expire_logs_days = 7

# Slave
[mysqld]
server_id = 2
read_only = 1
relay_log = relay-bin
relay_log_index = relay-bin.index
# 可選：只同步特定庫
replicate_do_db = backup
replicate_ignore_db = mysql,test

4. 啟動從庫並指定同步起點

# 啟動 MySQL 但不啟動 Slave
mysqld_safe --skip-slave-start &

# 登入設定
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='rep',
  MASTER_PASSWORD='logzgh',
  MASTER_LOG_FILE='mysql-bin.000007',
  MASTER_LOG_POS=471632;

START SLAVE;

5. 驗證

SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

五、常用啟動參數

六、不停機配置複製

1. 主庫 FLUSH TABLES WITH READ LOCK → 打包 → SHOW MASTER STATUS → UNLOCK TABLES（鎖表時間極短）
2. 或使用 xtrabackup 熱備份（無鎖）
3. 從庫解壓 → 設定 CHANGE MASTER TO → START SLAVE

七、進階主題

• GTID 複製 (5.6+)：全域事務識別碼，簡化故障切換、多源複製。
• 半同步複製：rpl_semi_sync_master=1，主庫等待至少一從庫收到 Binlog 才提交，提升資料安全。
• 多源複製 (5.7+)：一從庫同時從多主庫同步不同庫。
• 延遲從庫：MASTER_DELAY=3600，防誤操作（Drop Table）回滾。
• 並行複製：slave_parallel_workers=N（5.6+），slave_parallel_type=LOGICAL_CLOCK（5.7+），大幅降低延遲。

八、常見問題 FAQ

Q: 從庫需始終連線？

A: 否。斷線後重連自動追上，前提是主庫 Binlog 未被清理（expire_logs_days）。

Q: 如何知道從庫延遲？

A: SHOW SLAVE STATUS 中 Seconds_Behind_Master。

Q: 強制主庫阻塞直到從庫同步？

A: 主庫 FLUSH TABLES WITH READ LOCK → 記錄位置 → 從庫 SELECT MASTER_POS_WAIT('file', pos) → 主庫 UNLOCK TABLES。

Q: 字符集不一致導致複製錯誤？

A: 主從必須使用相同 character_set_server、collation_server。

結語

MySQL 複製是高可用架構基石。深入理解三線程模型、Binlog/Relay Log/Info 檔案、GTID、半同步、並行複製，並建立標準化部署、監控、演練流程，才能駕馭大規模生產環境的複製集群。