您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 5.0 主從復制 FAQ（五）：從庫搭建、延遲監控、雙主風險、高可用架構實戰

編輯：MySQL 資料庫相關知識

前言

MySQL 復制是實現讀寫分離、災備、高可用的基石。本文聚焦 無停機搭建從庫、同步延遲量化、雙向復制陷阱、性能擴展模型、故障切換腳本化 五大高頻實戰場景，提供可落地的操作指南。

一、Master 運行中零停機搭建 Slave

方案 A：二進制快照 + 位點同步（推薦大數據量）

Master 加全局讀鎖並記錄位點：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Position
# 另開終端備份數據目錄
tar zcf /tmp/mysql_snapshot.tar.gz /var/lib/mysql
UNLOCK TABLES;

將備份傳輸到 Slave，解壓至數據目錄，設置 server-id，啟動 MySQL
Slave 配置同步座標並啟動：

CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_pass',
  MASTER_LOG_FILE='mysql-bin.000123',
  MASTER_LOG_POS=456;
START SLAVE;

方案 B：mysqldump --master-data（中小數據量）

# Master
mysqldump --all-databases --master-data=2 --single-transaction > full.sql
# Slave
mysql < full.sql
# 導入後自動包含 CHANGE MASTER TO 信息，直接 START SLAVE

--master-data=2 將 CHANGE MASTER 註釋在文件中；=1 則為生效語句。

方案 C：LOAD DATA FROM MASTER（僅 MyISAM，舊版本）

自動獲取快照+位點，但會長時間持有全局讀鎖，大表慎用。未來版本將增強此功能。

二、Slave 斷開重連與延遲監控

斷開重連機制

Slave 可離線數小時/天，重連後自動追趕 Binlog
適用撥號/間歇性網絡場景
參數 --master-connect-retry=60 控制重試間隔（默認 60 秒）

延遲監控三種方法

MySQL 4.1.1+ 專用指標：SHOW SLAVE STATUS\G 中 Seconds_Behind_Master
進程列時間差：SHOW PROCESSLIST 中 SQL 線程 Time 列 = 當前時間 - 最後執行事件時間戳
自定義心跳表：Master 定期 UPDATE heartbeat SET ts=NOW()，Slave 查詢對比

若 Slave 斷開 1 小時重連，Time 會顯示 ~3600，正在重放 1 小時前的事件。

三、強制 Master 等待 Slave 追平（一致性切換）

# 1. Master 封寫並記錄位點
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- 記錄 File, Pos

# 2. Slave 等待追平
SELECT MASTER_POS_WAIT('mysql-bin.000123', 456);
-- 阻塞直到 Slave 執行到該位點

# 3. Master 解鎖恢復寫入
UNLOCK TABLES;

適用：主從切換、備份前凍結、跨庫一致性檢查。

四、雙向復制（雙主）風險與對策

⚠️ 核心風險

無分布式鎖：雙邊並發寫入同一行 → 數據發散（PK 沖突、數值覆蓋）
性能提升有限：寫入仍需雙邊執行，僅減少鎖爭用、網絡延遲補償
自增衝突：需配置 auto_increment_offset/increment 錯開 ID

✅ 安全使用前提

業務邏輯保證「任意順序執行結果一致」（冪等/可交換操作）
或嚴格按功能/數據分片路由寫入（如：A 寫奇數 ID，B 寫偶數 ID）
監控 Seconds_Behind_Master 雙向均接近 0

五、讀寫分離性能擴展模型

理論吞吐量計算

假設：讀佔 90%、寫佔 10%；單節點 max_reads = 1200 - 2×max_writes

N 個 Slave 時最大寫入量：
writes = 1200 / (2 + 9/(N+1))

N=0 (單主)   → 109 writes/s
N=1          → 184 writes/s  (+69%)
N=8          → 400 writes/s  (+267%)
N=17         → 480 writes/s  (+340%)
N→∞          → 600 writes/s  (+450% 理論上限)

實際受限於網絡帶寬、Binlog 傳輸延遲、Slave 重放並行度（MySQL 5.6+ 支持多線程 Slave）。

架構建議

Master 專寫，掛載 --log-bin
Slave 掛 --skip-innodb --skip-bdb --low-priority-updates --delay-key-write=ALL 以 MyISAM 高速重放
應用層抽象 safe_writer_connect() / safe_reader_connect() 封裝路由邏輯

六、高可用故障切換腳本化流程

MHA/Orchestrator 思想前身：手動提升 Slave 為 Master

# 1. 確認所有 Slave 追平 Relay Log
STOP SLAVE IO_THREAD;
SHOW PROCESSLIST;  -- 等待 "Has read all relay log"

# 2. 選定新 Master (如 S1)
STOP SLAVE;
RESET MASTER;  -- 清空舊 Binlog，重置位點

# 3. 其他 Slave (S2, S3) 重新指向新 Master
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='S1', MASTER_USER=..., MASTER_PASSWORD=...;
START SLAVE;

# 4. 應用層切換寫入地址至 S1
# 5. 故障 Master (M) 恢復後，作為 Slave 掛載回集群
CHANGE MASTER TO MASTER_HOST='S1' ...; START SLAVE;

關鍵配置

Slave 啟用 --log-bin 不啟用 --log-slave-updates → Binlog 為空，提升後無衝突
動態 DNS (nsupdate) 實現 VIP 漂移，應用無感知

七、復制故障排查標準流程

錯誤日志優先：tail -f /var/log/mysql/error.log
Master Binlog 狀態：SHOW MASTER STATUS → Position > 0 且啟用 log-bin, server-id
Slave 線程狀態：SHOW SLAVE STATUS → Slave_IO_Running=Yes, Slave_SQL_Running=Yes
IO 線程連接狀態：SHOW PROCESSLIST → Connecting to master 檢查權限/網絡/DNS
SQL 線程報錯：
a. 對比 Master/Slave 表結構差異
b. 手工修復數據後 START SLAVE
c. 跳過錯誤：SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1/2; START SLAVE;（AUTO_INCREMENT 需跳 2）
d. 仍失敗 → 疑似 Bug，收集證據報官方