MySQL 主從同步完整實戰教學：架構原理、配置步驟、驗證與故障排查

MySQL 主從同步完整實戰教學：架構原理、配置步驟、驗證與故障排查

MySQL Replication (主從同步) 是實現讀寫分離、災備、多源分析的核心基礎設施。本文基於 MySQL 5.0/5.7 通用原理，詳述雙機熱備 (Master-Master) 與標準單向 (Master-Slave) 完整配置流程。

一、核心架構原理 (三線程模型)

主從同步涉及三個關鍵線程：

1. Master Binlog Dump Thread：主庫為每個 Slave 開啟一線程，讀取 Binlog 事件發送給 Slave。
2. Slave I/O Thread：從庫連接主庫，請求 Binlog，寫入本地 **Relay Log (中繼日誌)**。
3. Slave SQL Thread：從庫讀取 Relay Log，解析並重放 SQL，**在從庫重放執行**，實現數據同步。

關鍵檔案：

• Master：mysql-bin.000001 (Binlog)、master.info (記錄同步位點)
• Slave：relay-bin.000001 (Relay Log)、master.info (主庫連線資訊)、relay-log.info (SQL 執行位點)

二、環境規劃 (範例)

前置條件：

• 雙向網路通暢 (ping、telnet 3306)
• 關閉防火牆或開放 3306：service iptables stop / ufw allow 3306
• 時間同步 (NTP)，避免時間戳混亂
• 同版本、同字元集、同 sql_mode

三、Master 配置 (A 主機)

1. 編輯 /etc/my.cnf ([mysqld] 區段)

[mysqld]
server-id = 1                    # 全局唯一，1-2^32-1
log-bin = mysql-bin              # 啟用 Binlog，指定檔案前綴
binlog-do-db = test              # 僅記錄 test 庫變更 (可選，建議全記錄再用 replicate-do-db 過濾)
# binlog-ignore-db = mysql       # 忽略系統庫
sync_binlog = 1                  # 每次提交刷盤，最高數據安全 (效能折衷可設 100-1000)
binlog_format = ROW              # 強烈推薦 ROW 格式，避免非確定性函數導致主從不一致
expire_logs_days = 7             # 自動清理 7 天前 Binlog，防磁碟滿
max_binlog_size = 100M           # 單檔大小限制

2. 重啟並建立同步專用帳號

service mysqld restart

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.10.8.112' IDENTIFIED BY 'ReplPass123!';
-- 僅授予 REPLICATION SLAVE 權限，最小權限原則
-- 指定 Slave IP，禁止 '%'
FLUSH PRIVILEGES;

3. 建立同步測試庫表

CREATE DATABASE test;
USE test;
CREATE TABLE mytest (username VARCHAR(20), password VARCHAR(20));

4. 查看 Master 狀態 (記錄 File 與 Position，Slave 配置需用)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 840
Binlog_Do_DB: test
Binlog_Ignore_DB:
Executed_Gtid_Set: (GTID 模式 5.6+ 才有)
1 row in set (0.00 sec)

四、Slave 配置 (B 主機)

1. 編輯 /etc/my.cnf

[mysqld]
server-id = 2                    # 唯一，不同於 Master
# 以下參數 5.6+ 建議用 CHANGE MASTER TO 動態設定，寫死配置檔不靈活
# master-host=10.10.0.119        # 已棄用
# master-user=repl_user
# master-password=ReplPass123!
# master-port=3306
# master-connect-retry=60
# replicate-do-db=test           # 僅同步 test 庫

relay-log = relay-bin            # 指定 Relay Log 前綴
relay-log-index = relay-bin.index
log-slave-updates = 1            # 關鍵：Slave 重放的變更也寫入自身 Binlog，支援鏈式/級聯同步
read-only = 1                    # 建議：防止誤在 Slave 寫入導致不一致

2. 重啟並動態配置同步參數 (5.6+ 標準方式)

service mysqld restart

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL; -- 清除舊 master.info、relay-log.info (關鍵！避免舊位點干擾)

mysql> CHANGE MASTER TO
    MASTER_HOST='10.10.0.119',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000003',  -- 來自 Master SHOW MASTER STATUS
    MASTER_LOG_POS=840,
    MASTER_CONNECT_RETRY=60;

mysql> START SLAVE;

3. 驗證 Slave 狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.0.119
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 236
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes      <-- 關鍵：I/O 線程正常
Slave_SQL_Running: Yes     <-- 關鍵：SQL 線程正常
Replicate_Do_DB: test
Seconds_Behind_Master: 0   <-- 延遲秒數，0 為同步
Last_Error:                <-- 空為無錯誤
1 row in set (0.00 sec)

五、同步驗證測試

-- Master 寫入
mysql> USE test; INSERT INTO mytest VALUES ('user1', 'pass1');

-- Slave 立即查詢
mysql> USE test; SELECT * FROM mytest;
+----------+----------+
| username | password |
+----------+----------+
| user1    | pass1    |
+----------+----------+

六、雙主熱備 (Master-Master) 配置

在單向同步基礎上，互為 Master/Slave：

-- 在原 Master (A) 上新增 Slave 配置，指向 B
CHANGE MASTER TO
    MASTER_HOST='10.10.8.112',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='ReplPass123!',
    MASTER_LOG_FILE='mysql-bin.000001', -- B 的 Binlog 位置
    MASTER_LOG_POS=120;
START SLAVE;

-- 在原 Slave (B) 上新增 Master 配置 (已有 log-bin)
-- 確保 B 的 my.cnf 有 log-bin, server-id=2
-- 雙方 auto_increment_offset/auto_increment_increment 設定避免主鍵衝突
-- A: auto_increment_offset=1, auto_increment_increment=2
-- B: auto_increment_offset=2, auto_increment_increment=2

警告：雙主寫入極易衝突，**生產極少純雙主寫入**，常見架構為「一主一備 + 讀寫分離」或「MHA/Orchestrator 自動故障轉移」。

七、常見故障與排查清單

八、關鍵最佳實踐總結

1. Server ID 全局唯一，規劃號段 (如 1000+ 為生產主庫)。
2. Binlog 格式強制 ROW，binlog_format=ROW，避免 90% 不一致根因。
3. 同步專用帳號 + 最小權限 + 指定 Host。
4. Slave 設定 read-only=1, log-slave-updates=1。
5. 監控三大核心指標：Slave_IO_Running=Yes、Slave_SQL_Running=Yes、Seconds_Behind_Master < 阈值。
6. 定期校驗：pt-table-checksum + pt-table-sync (Percona Toolkit)。
7. 備份策略：從庫停庫備份 (STOP SLAVE) 或 xtrabackup 熱備，不影響主庫。

結語

MySQL 主從同步看似配置簡單，實則涉及網路、存儲、鎖、日誌格式、一致性協議等深層機制。掌握三線程模型、Binlog/Relay Log 流轉、關鍵狀態解讀、故障排查體系，才能構建可靠的資料庫高可用架構。