MySQL 主從複制快速配置指南（含狀態監控與故障處理）

架構圖示

Master (寫入)  --binlog-->  Slave (讀取/備份)

Master 端配置

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = db_name          # 要同步的庫，多行重複
binlog-ignore-db = mysql        # 不同步的庫

建立複制帳號

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_pass';
FLUSH PRIVILEGES;

Slave 端配置

[mysqld]
server-id = 2
master-host = 10.0.0.1
master-user = repl_user
master-password = repl_pass
master-port = 3306
replicate-do-db = db_name
replicate-ignore-db = mysql
log-bin = mysql-bin                 # Slave 也開啟 binlog，支援鏈式複制
master-connect-retry = 30
skip-start-slave                    # 啟動不自動複制，手動控制

初始化同步步驟

1. Master 重啟 MySQL，執行 SHOW MASTER STATUS; 記錄 File、Position。
2. Slave 重啟 MySQL，進入客戶端：

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_pass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=12345;
START SLAVE;

3. 驗證：SHOW SLAVE STATUS\G 確認 Slave_IO_Running: Yes、Slave_SQL_Running: Yes。

常用監控指令

關鍵狀態欄位解讀

故障恢復標準流程

情況 1：Slave 遲滯但無錯誤

STOP SLAVE;
START SLAVE;

情況 2：SQL 執行緒報錯（主鍵衝突等）

SHOW SLAVE STATUS\G;  -- 記錄 Last_Error
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  -- 跳過 1 條錯誤事件
START SLAVE;

情況 3：Master binlog 已被清理，Slave 找不到對應位置

-- Slave 端
STOP SLAVE;
-- Master 端完整備份（mysqldump --master-data=2）導入 Slave
-- 或使用 LOAD DATA FROM MASTER（僅 MyISAM、需 SUPER/RELOAD 權限）
START SLAVE;

情況 4：雙主互備

兩台皆設為 Master 與 Slave，server-id 不同，互為對方 Master。需設定 auto_increment_offset、auto_increment_increment 避免主鍵衝突。

進階參數

• slave-net-timeout=60：網路中斷後等待秒數再重連，防止長時間假死。
• relay-log、relay-log-index：自訂中繼日誌路徑。
• sync_binlog=1、innodb_flush_log_at_trx_commit=1：雙 1 配置保證不丟資料。

驗證清單

1. Master/Slave 版本一致（或 Slave 版本 ≥ Master）。
2. server-id 全域唯一。
3. 防火牆開放 3306 互通。
4. 複制帳號僅授 REPLICATION SLAVE 權限。
5. 啟用 log_slow_queries 監控複制延遲大交易。

依此流程配置，即可建立穩定可監控的 MySQL 主從複制環境。