MySQL 主從複製配置完整實戰指南

MySQL 主從複製配置完整實戰指南

核心原理

MySQL 採用非同步單向複製：

1. Master 將資料變更寫入 Binary Log (Binlog)
2. Slave 的 I/O Thread 連線 Master，請求 Binlog 內容
3. Master 的 Binlog Dump Thread 傳送 Binlog 事件
4. Slave I/O Thread 寫入本地 Relay Log (中繼日誌)
5. Slave 的 SQL Thread 讀取 Relay Log 並重放執行

三線程架構圖解

Master: [Client] → Binlog → [Binlog Dump Thread] → Network
                                        ↑
Slave:  [I/O Thread] ← Relay Log ← [SQL Thread] → Execute SQL

為什麼需要主從複製？

配置前置條件

• Master/Slave 版本一致（建議同主版本）
• Master 啟用 log-bin 並設定唯一 server-id
• Slave 設定唯一 server-id（不同於 Master）
• 網路互通，防火牆開放 3306
• Master 建立專用複製帳號：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

逐步配置流程（基於資料目錄快照法）

步驟 1：Master 鎖表並記錄 Binlog 位置

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

步驟 2：建立資料快照（保持鎖定狀態）

shell> cd /var/lib/mysql
shell> tar -czf /tmp/mysql-snapshot.tar.gz ./data

shell> tar -czf /tmp/mysql-snapshot.tar.gz --exclude=./data/mysql ./data

步驟 3：Master 解鎖

mysql> UNLOCK TABLES;

步驟 4：傳輸快照至 Slave 並還原

shell> scp /tmp/mysql-snapshot.tar.gz slave_host:/tmp/
shell> cd /var/lib/mysql
shell> tar -xzf /tmp/mysql-snapshot.tar.gz
shell> chown -R mysql:mysql /var/lib/mysql/data

步驟 5：配置 Slave 連線資訊並啟動

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

步驟 6：驗證複製狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master_ip
Master_User: repl
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 73
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Error: 
Seconds_Behind_Master: 0

關鍵指標：Slave_IO_Running: Yes、Slave_SQL_Running: Yes、Seconds_Behind_Master: 0

重要配置檔參數

Master (my.cnf)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW          # 推薦 ROW 格式，避免非確定性函數問題
sync_binlog = 1              # 每次提交同步到磁碟，最安全
innodb_flush_log_at_trx_commit = 1

Slave (my.cnf)

[mysqld]
server-id = 2
relay-log = slave-relay-bin
read-only = 1                # 防止誤在 Slave 寫入
log-slave-updates = 1        # 若 Slave 需作為下一級 Master

常見問題排查

進階主題

• GTID 複製：MySQL 5.6+，自動定位位置，故障切換更簡單
• 多源複製：MySQL 5.7+，單 Slave 複製多 Master
• 半同步複製：rpl_semi_sync_master=1，提升資料安全性
• 並行複製：slave_parallel_workers=4，提升 SQL 執行效能