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

MySQL 主從複寫配置速查手冊：從基礎部署到動態調整的完整指令集

編輯：MySQL 資料庫相關知識

MySQL 主從複寫是讀寫分離、異地備份、高可用架構的基石。本文整理最精簡的配置流程、核心參數、雙向同步要點、動態管理指令與故障排查清單，作為 DBA 桌面速查手冊。

單向主從配置最小集

主庫 my.cnf

[mysqld]
server-id = 1                    # 叢集內唯一
log-bin = mysql-bin              # 啟用二進制日誌
binlog-format = ROW              # 推薦 ROW 格式
# 可選：指定同步/忽略的庫（多行寫法）
binlog-do-db = biz_db
binlog-ignore-db = mysql,test,sys

從庫 my.cnf

[mysqld]
server-id = 2                    # 唯一，≠ 主庫
# 以下參數 5.6+ 建議用 CHANGE MASTER TO 動態設定，不寫死配置檔
# master-host = 192.168.1.100
# master-user = repl
# master-password = ReplPass@2024
# master-port = 3306
# replicate-do-db = biz_db
# replicate-ignore-db = mysql,test,sys

部署標準流程（5 步走）

主庫建立複寫帳號

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

主庫鎖表、導出基線、記錄位置

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G;  -- 記下 File、Position
mysqldump --single-transaction --master-data=2 --all-databases > /backup/full.sql
UNLOCK TABLES;

從庫導入基線資料
```
mysql -u root -p < /backup/full.sql
```

從庫配置主庫連線並啟動

CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'ReplPass@2024',
    MASTER_PORT = 3306,
    MASTER_LOG_FILE = 'mysql-bin.000001',  -- 步驟2記錄值
    MASTER_LOG_POS = 154;
START SLAVE;

驗證同步狀態

SHOW SLAVE STATUS\G;
-- 關鍵指標：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

核心動態管理指令速查

類別	指令	用途
主庫	SHOW MASTER STATUS\G	查看當前 binlog 檔案/位置
	SHOW BINLOG EVENTS [IN 'file'] [FROM pos] [LIMIT n]	檢視 binlog 事件細節
	PURGE BINARY LOGS TO 'mysql-bin.000010'	清理指定檔案前的舊 binlog
	PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00'	清理指定時間前的舊 binlog
	RESET MASTER	重置 binlog 索引（慎用，會中斷從庫）
	SET SQL_LOG_BIN = 0/1	當前會話停用/啟用 binlog 記錄
從庫	START SLAVE	啟動 IO + SQL 線程
	STOP SLAVE	停止複寫
	START SLAVE IO_THREAD	僅啟動 IO 線程（拉取 binlog）
	START SLAVE SQL_THREAD	僅啟動 SQL 線程（重放 relay log）
	STOP SLAVE IO_THREAD / SQL_THREAD	單獨停止某線程
	RESET SLAVE [ALL]	重置從庫狀態（清 relay log、master.info）
	SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N	跳過 N 個錯誤事件（緊急修復）
	CHANGE MASTER TO ...	動態修改主庫連線/位置/過濾規則
	SHOW SLAVE STATUS\G	核心監控指令（必會讀懂每個欄位）
	SHOW RELAYLOG EVENTS ...	檢視 relay log 事件

雙向主主同步關鍵點

雙方皆為 Master 且互為 Slave

-- Server A (server-id=1) 額外配置
log-slave-updates = 1          # 關鍵：從庫重放也寫入自己的 binlog
auto_increment_increment = 2   # 自增步長
auto_increment_offset = 1      # 起始偏移 (A:1, B:2)

-- Server B (server-id=2) 額外配置
log-slave-updates = 1
auto_increment_increment = 2
auto_increment_offset = 2

自增 ID 錯開原理

A 產生：1, 3, 5, 7... (offset=1, increment=2)
B 產生：2, 4, 6, 8... (offset=2, increment=2)
**永不衝突**，但**不連續**，應用層需適應

雙寫衝突風險與對策

衝突類型	風險	對策
主鍵衝突	高	auto_increment 錯開 + 業務層寫入路由
唯一鍵衝突	高	業務層唯一性保證 / 悲觀鎖
並發 UPDATE 同一行	中	樂觀鎖 / 佇列化串行化
DDL 衝突	低	統一由單一方執行 DDL，另一方同步

⚠️ **生產環境非必要不建議雙主**，推薦「單主多從 + 自動故障轉移 (MHA/Orchestrator)」。

GTID 複寫簡化配置 (MySQL 5.6+)

主庫額外配置

gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates = 1

從庫啟動同步（無需指定檔案/位置）

CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'ReplPass@2024',
    MASTER_AUTO_POSITION = 1;
START SLAVE;

故障排查快速定位表

現象	首檢指令	常見原因	處理方向
IO_Running: Connecting	telnet master 3306	網路/防火牆/帳號密碼/bind-address	網路通聯 → 帳號驗證 → 主庫 bind-address=0.0.0.0
SQL_Running: No	SHOW SLAVE STATUS\G → Last_SQL_Error	主鍵衝突/表結構差異/外鍵/觸發器錯誤	補數據/修結構/跳過錯誤/重做基線
Seconds_Behind_Master 大	SHOW PROCESSLIST / 慢查詢日誌	從庫硬體弱/大事務/長查詢/單線程重放	升級規格/拆大事務/優化慢查詢/啟用並行複寫
Relay log 佔滿磁碟	df -h / ls -lh relay-log*	SQL 線程延遲/主庫寫入過快	relay_log_space_limit / 限制主庫寫入/擴容
主庫 binlog 異常增長	SHOW BINLOG EVENTS / mysqlbinlog	大批次 UPDATE/DELETE/無 WHERE	業務優化/分批提交/檢查異常腳本

監控指標體系（最小可用集）

# 1. 同步狀態
Slave_IO_Running = Yes AND Slave_SQL_Running = Yes

# 2. 延遲秒數
Seconds_Behind_Master < 10 (視業務 SLA)

# 3. Relay log 空間
Relay_Log_Space < 磁碟剩餘 20%

# 4. 錯誤計數
Last_IO_Error = '' AND Last_SQL_Error = ''

# 5. 主庫 binlog 位置推進
SHOW MASTER STATUS 定期比對