MySQL 主從同步完整配置指南：從架構原理到實戰部署

MySQL 主從同步架構原理

MySQL Replication 是企業級數據庫架構中最常見的高可用與讀寫分離方案。其核心機制是：主庫啟用二進制日誌，記錄所有數據變更操作；從庫通過 I/O 線程拉取主庫的 Binlog 到本地 Relay Log，再由 SQL 線程重放執行，最終實現主從數據一致性。

需注意：標準異步複制僅保證日誌同步與執行，不提供自動故障轉移。主庫故障時需人工介入，通常將從庫提升為新主庫。

環境規劃

一、主庫配置

[root@master ~]# vim /etc/my.cnf
# 在 [mysqld] 區塊添加
log-bin = mysql-bin          # 啟用二進制日誌
server-id = 1                # 全局唯一 ID，範圍 1~2^32-1

[root@master ~]# service mysqld start
[root@master ~]# netstat -anpt | grep 3306          # 驗證服務啟動
[root@master ~]# chkconfig --add mysqld
[root@master ~]# chkconfig --level 35 mysqld on    # 設定開機自啟

# 防火牆放行從庫 IP
[root@master ~]# iptables -I INPUT -s 192.168.1.80 -p tcp --dport 3306 -j ACCEPT
[root@master ~]# service iptables save

二、從庫配置

[root@slave ~]# vim /etc/my.cnf
# 在 [mysqld] 區塊添加
server-id = 2                              # 全局唯一 ID
relay-log = mysql-relay-bin                # 指定中繼日誌命名格式
replicate-wild-ignore-table = mysql.%      # 過濾系統庫，避免同步元數據
replicate-wild-ignore-table = test.%
replicate-wild-ignore-table = information_schema.%
replicate-wild-ignore-table = performance_schema.%

[root@slave ~]# service mysqld start
[root@slave ~]# chkconfig --add mysqld
[root@slave ~]# chkconfig --level 35 mysqld on
[root@slave ~]# iptables -I INPUT -s 192.168.1.88 -p tcp --dport 3306 -j ACCEPT
[root@slave ~]# service iptables save

三、主庫建立複制帳號並獲取 Binlog 位點

[root@master ~]# mysql -u root -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep1_user'@'192.168.1.80' IDENTIFIED BY 'rep1_passwd';
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 324      |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

關鍵信息：記錄 File (mysql-bin.000008) 與 Position (324)，從庫配置時需使用。

四、從庫指向主庫並啟動複制

[root@slave ~]# mysql -u root -p123456
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.88',
    -> MASTER_USER='rep1_user',
    -> MASTER_PASSWORD='rep1_passwd',
    -gt; MASTER_LOG_FILE='mysql-bin.000008',
    -> MASTER_LOG_POS=324;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

五、核心監控指標解讀

執行 SHOW SLAVE STATUS\G 後，重點關注以下字段：

• Slave_IO_Running / Slave_SQL_Running：必須均為 Yes，表示 I/O 線程與 SQL 線程正常運行。
• Slave_IO_State：顯示 Waiting for master to send event 為正常等待狀態。
• Seconds_Behind_Master：延遲秒數，0 表示無延遲；數值過大需排查網絡或 SQL 執行性能。
• Master_Log_File / Read_Master_Log_Pos：I/O 線程已讀取的主庫 Binlog 位置。
• Relay_Log_File / Relay_Log_Pos：SQL 線程已執行的中繼日誌位置。
• Replicate_Wild_Ignore_Table：確認過濾規則生效。

六、進階場景：主庫已有數據時的初始化同步

若主庫在搭建主從前已存在數據，需先進行基線同步：

# 1. 主庫鎖表，禁止寫入
mysql> FLUSH TABLES WITH READ LOCK;
# ⚠️ 必須保持此會話不斷開，關閉則鎖失效

# 2. 新開終端，打包主庫數據目錄
[root@master ~]# cd /usr/local/
[root@master local]# tar zcf mysql.tar.gz mysql

# 3. 傳輸至從庫
[root@master local]# rsync -a mysql.tar.gz 192.168.1.80:/usr/local/

# 4. 從庫停服務、替換數據目錄
[root@slave local]# service mysqld stop
[root@slave local]# mv mysql old.mysql
[root@slave local]# tar zxf mysql.tar.gz

# 5. 重啟雙側服務
[root@master local]# service mysqld restart   # 主庫解鎖並重啟
[root@slave local]# service mysqld start      # 從庫啟動，自動從記錄位點開始複制

七、常見問題排查

• IO 線程 Not Running：檢查網絡連通性、防火牆、MASTER_HOST/PORT/USER/PASSWORD 是否正確。
• SQL 線程 Not Running：查看 Last_SQL_Error，常見於主鍵衝突、表結構不一致、觸發器/存儲過程報錯。
• 延遲過高：優化從庫 SQL 執行效率（索引、大事務拆分）、升級網絡帶寬、考慮並行複制。