MySQL 主從複寫原理與實戰配置：基於多實例環境的完整演示

MySQL 主從複寫原理與實戰配置：基於多實例環境的完整演示

主從複寫是 MySQL 高可用、讀寫分離、備份卸載的基石。本文以同機多實例（3406=Master、3506=Slave）為例，詳述複寫四階段原理、配置檔差異、帳號建立、同步啟動、驗證全流程。

一、複寫四階段原理

1. Master Binlog Dump：Master 將資料變更寫入 Binary Log，Binlog Dump 線程推送給 Slave。
2. Slave I/O Thread：連接 Master，請求 Binlog，寫入本地 Relay Log（中繼日誌）。
3. Slave SQL Thread：讀取 Relay Log，重放 SQL，回放資料變更。
4. 同步完成：Seconds_Behind_Master=0 表示追上。

二、配置檔差異化

Master (3406) my3406.cnf

[mysqld]
server-id=3406
log_bin=/data/inst3406/log/bin/inst3406bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# 確保事務不丟失、Binlog 即時落盤

Slave (3506) my3506.cnf

[mysqld]
server-id=3506
relay_log=/data/inst3506/log/relay/relay-bin
read_only=1
# 防止誤寫入，強制只讀

三、建立複寫帳號

# Master 上
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;
RESET MASTER;  -- 重置 Binlog（僅測試環境用）

四、配置 Slave 指向 Master

# Slave 上
CHANGE MASTER TO
  MASTER_HOST='192.168.1.177',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl',
  MASTER_PORT=3406,
  MASTER_LOG_FILE='inst3406bin.000001',
  MASTER_LOG_POS=0;
START SLAVE;

五、驗證同步狀態

SHOW SLAVE STATUS\G
-- 關鍵指標
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Log_File: inst3406bin.000001
Read_Master_Log_Pos: 120
Exec_Master_Log_Pos: 120

六、實測同步效果

# Master 建庫建表寫入
CREATE DATABASE tempdb;
USE tempdb;
CREATE TABLE tb_engines AS SELECT * FROM information_schema.engines;

# Slave 查詢
SELECT COUNT(*) FROM tempdb.tb_engines;  -- 返回 9，同步成功

七、常見故障與對策

八、進階延伸

• 半同步複寫：rpl_semi_sync_master=ON，Master 等待至少 1 Slave 收到 Binlog 再提交，強化數據安全。
• GTID 複寫：MySQL 5.6+ 支援，自動定位位點，故障切換更簡單。
• 多源複寫：MySQL 5.7+，單 Slave 同步多 Master。
• 延遲從庫：MASTER_DELAY=3600，防誤刪誤更新的「後悔藥」。

掌握此標準化流程，可快速在生產環境部署穩定的 MySQL 主從架構。