MySQL 主從複寫配置完整操作指南

複寫架構原理

MySQL 採用非同步單向複寫：主庫寫入 Binlog → 從庫 I/O 執行緒拉取 → 寫入 Relay Log → SQL 執行緒重放。三執行緒協作實現資料同步。

複寫優勢

• 高可用：主庫故障可提升從庫為新主
• 讀寫分離：查詢分流至從庫，主庫專注寫入
• 零影響備份：從庫執行備份不阻塞主庫
• 災難恢復：異地從庫作為冷備

配置前置條件

1. 主從版本一致（建議同主版本號）
2. 主庫啟用 log_bin 並設定唯一 server_id
3. 從庫設定不同的 server_id
4. 網路互通，防火牆開放 3306
5. 建立專用複寫帳號：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

逐步配置流程

階段一：主庫設定

# /etc/my.cnf
[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW          # 推薦 ROW 格式，最安全
sync_binlog = 1              # 最高持久性
expire_logs_days = 7         # 自動清理舊 binlog
# 可選：只複寫特定庫
# binlog_do_db = business_db
# binlog_ignore_db = mysql,test

重啟主庫：systemctl restart mysqld

階段二：取得同步基準點

# 主庫鎖表（僅針對 MyISAM/需一致性快照）
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
# 記錄 File 與 Position，例如：mysql-bin.000003, 73

# 熱備份資料目錄（InnoDB 可用 xtrabackup 免鎖表）
tar czf /tmp/mysql_snapshot.tar.gz /var/lib/mysql

# 解鎖
UNLOCK TABLES;

階段三：從庫設定

# /etc/my.cnf
[mysqld]
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1        # 從庫也寫 binlog，支援鏈式複寫
read_only = 1                # 防止誤寫入
# 可選：只複寫特定庫
# replicate_do_db = business_db

重啟從庫，解壓主庫快照至資料目錄，確保權限正確：chown -R mysql:mysql /var/lib/mysql

階段四：建立複寫連線

CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE = 'mysql-bin.000003',
    MASTER_LOG_POS = 73;

START SLAVE;

階段五：驗證狀態

SHOW SLAVE STATUS\G
# 關鍵指標：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0     # 延遲秒數，0 表示同步
Last_IO_Error:               # 空白表示無錯誤
Last_SQL_Error:

常見問題與排查

進階優化配置

• 平行複寫（5.6+）：slave_parallel_workers = 4、slave_parallel_type = LOGICAL_CLOCK
• GTID 複寫（5.6+）：gtid_mode = ON、enforce_gtid_consistency = ON，自動定位位置，故障切換更簡單
• 半同步複寫：rpl_semi_sync_master_enabled = 1，主庫等待至少一從庫確認才提交，減少資料遺失風險
• 延遲從庫：MASTER_DELAY = 3600，延遲 1 小時重放，防誤操作（如誤 DELETE）

監控與維運建議

1. 監控 Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running
2. 定期 pt-table-checksum 驗證資料一致性
3. Binlog 保留策略配合備份週期，避免從庫重新同步時缺失 binlog
4. 建立主從切換演練 SOP，包含 DNS/Proxy 切換、應用重連驗證

主從複寫是 MySQL 高可用架構基石，標準化配置、持續監控、定期演練，才能在關鍵時刻發揮作用。