您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 主從同步原理與實戰部署步驟詳解

編輯：MySQL 綜合教學

MySQL 主從同步原理與實戰部署步驟詳解

一、同步原理

主庫將資料變更寫入 Binlog（二進位日誌）。
從庫 I/O 線程 連接主庫，請求 Binlog 事件，寫入本地 Relay Log（中繼日誌）。
從庫 SQL 線程 讀取 Relay Log，重放 SQL 語句，實現資料重放。

核心優勢：非阻塞、異步（預設）、支援多級從庫、可做讀寫分離與備份。

二、實驗拓撲

Master: 192.168.2.1 (RHEL 6.5)
Slave: 192.168.2.2 (RHEL 6.5)

三、部署步驟

1. 公共環境準備

# 兩台均執行
yum -y install mysql-server
service mysqld start
setenforce 0
service iptables stop

2. Master 配置

# /etc/my.cnf
[mysqld]
log-bin = mysql-bin
server-id = 1

service mysqld restart
mysql -uroot -p -e "SHOW VARIABLES LIKE 'log_bin';"  # 確認 ON

3. Master 建立同步帳號與鎖定

mysql -uroot -p
GRANT REPLICATION SLAVE ON *.* TO 'wsyht'@'192.168.2.%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;  -- 全域讀鎖，保持此會話不斷開
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000028 | 107      |              |                  |
+------------------+----------+--------------+------------------+
# 記錄 File 與 Position

4. Master 全庫備份（另開終端）

mysqldump -uroot -p -A -B --events > /opt/all.$(date +%F).sql
rsync -avzP -e "ssh -p 22" /opt/all.$(date +%F).sql root@192.168.2.2:/opt/

5. Slave 配置

# /etc/my.cnf
[mysqld]
server-id = 2
# 可選：log-bin, relay-log, read-only=1

service mysqld restart

6. Slave 導入備份資料

mysql -uroot -p < /opt/all.$(date +%F).sql
mysql -uroot -p -e "SHOW DATABASES;"  # 確認 d1 等庫已導入

7. Slave 驗證連線並建立同步

mysql -h192.168.2.1 -uwsyht -p123456  # 測試連線
mysql -uroot -p
CHANGE MASTER TO
  MASTER_HOST='192.168.2.1',
  MASTER_USER='wsyht',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='mysql-bin.000028',
  MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G

8. 關鍵狀態確認

# Master 端
UNLOCK TABLES;
CREATE DATABASE test;  -- 新建庫驗證同步

# Slave 端
SHOW DATABASES;  -- 應出現 test

四、常見問題排查

現象	可能原因	處理
Slave_IO_Running: No	網路不通、帳號密碼錯、MASTER_LOG_FILE/POS 錯誤、防火牆	檢查連線、Last_IO_Error、重新 CHANGE MASTER
Slave_SQL_Running: No	SQL 執行錯誤（主鍵衝突、外鍵、欄位不存在）	查看 Last_SQL_Error，手動修正或跳過 `SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;`
Seconds_Behind_Master 偏大	從庫硬體弱、大事務、長 SQL、單線程瓶頸	升級硬體、拆分大事務、優化 SQL、升級 5.7+ 使用 MTS (Multi-Threaded Slave)
主從資料不一致	直接在從庫寫入、非決定性函數、Statement 格式缺陷	啟用 `binlog_format=ROW`、`read_only=1`、定期 `pt-table-checksum` 校驗

五、進階配置建議

GTID 模式 (5.6+)：gtid_mode=ON, enforce_gtid_consistency=ON，簡化故障轉移與拓撲變更。
半同步複制：rpl_semi_sync_master_enabled=1，主庫至少一從庫收到 Binlog 才提交，降低資料丟失風險。
多線程從庫 (5.7+)：slave_parallel_workers=4，基於邏輯庫或組提交並行重放。
監控指標：Seconds_Behind_Master、Slave_IO_Running、Slave_SQL_Running、Relay_Log_Space、Binlog 增長速率。