MySQL 主從同步配置完整指南：從基礎設置到數據同步實戰

MySQL 主從同步配置完整指南：從基礎設置到數據同步實戰

MySQL 主從同步是資料庫高可用架構的核心組件，透過將主資料庫的變更實時複製到從資料庫，實現讀寫分離、備份容災等關鍵功能。本文將詳細介紹從基礎配置到進階場景的完整實施流程。

一、主資料庫配置

首先需要修改主庫的配置文件 my.cnf（Windows 為 my.ini），在 [mysqld] 區塊添加以下核心參數：

log-bin = mysql-bin
server-id = 1
binlog-do-db = powerdns  # 指定需要同步的資料庫

參數說明：

• log-bin：啟用二進制日誌，記錄所有數據變更操作
• server-id：唯一標識符，集群中每個節點必須不同
• binlog-do-db：僅同步指定資料庫，減少不必要的網絡傳輸

二、建立同步賬號

在主庫創建專用於同步的用戶，並授予必要權限：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.2' IDENTIFIED BY 'passwd';
mysql> FLUSH PRIVILEGES;

安全建議： 生產環境請使用強密碼，並限制來源 IP 範圍，避免使用 % 通配符。

三、獲取主庫同步坐標

重啟主庫後，查看當前二進制日誌位置：

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 309      | powerdns     | mysql            |
+------------------+----------+--------------+------------------+

關鍵信息： 記錄 File（日誌文件名）和 Position（位置偏移量），從庫配置時必須指定這兩個值。

四、從資料庫配置

修改從庫配置文件，設置唯一的 server-id：

server-id = 2

重啟從庫 MySQL 服務使配置生效。

五、啟動同步進程

在從庫執行以下命令建立同步連接：

mysql> CHANGE MASTER TO 
    MASTER_HOST='192.168.1.1',
    MASTER_USER='slave',
    MASTER_PASSWORD='passwd',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=309;

mysql> START SLAVE;

六、驗證同步狀態

檢查同步是否正常運行：

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

兩個線程均為 Yes 表示同步正常。若出現 No 或錯誤信息，需查看 Last_Error 字段排查。

七、進階場景：同步現有數據

若主庫已有數據，需先建立基線快照：

1. 主庫鎖表防寫入：FLUSH TABLES WITH READ LOCK;
2. 記錄當前坐標：SHOW MASTER STATUS;
3. 導出數據：mysqldump -u root -p --opt -R dbname > backup.sql
4. 解鎖：UNLOCK TABLES;
5. 從庫導入數據並配置同步起始位置

八、異構同步：主從資料庫名稱不同

使用 replicate-rewrite-db 參數實現資料庫名稱映射：

replicate-rewrite-db = master_db->slave_db

注意：MySQL 5.0 確認支持，高版本建議測試驗證兼容性。

九、常見問題排查

• 同步延遲：檢查網絡延遲、從庫硬件性能、長事務阻塞
• 數據不一致：使用 pt-table-checksum 定期校驗
• 主鍵衝突：確保 auto_increment_offset 和 auto_increment_increment 正確配置

十、最佳實踐建議

1. 生產環境採用 GTID 模式（MySQL 5.6+），簡化故障切換
2. 配置半同步複製 rpl_semi_sync_master_enabled=1 保證數據安全
3. 監控 Seconds_Behind_Master 指標，設置告警閾值
4. 定期演練主從切換流程，驗證災備方案有效性

掌握 MySQL 主從同步不僅是配置參數，更需要理解其底層原理與故障處理機制。建議在測試環境完整演練後再應用到生產系統。