MySQL 高階架構：主從複制原理與配置實戰

MySQL 高階架構：主從複制原理與配置實戰

MySQL 主從複制是實現資料庫高可用、讀寫分離、災備備份的核心技術。本文深入解析其運作原理、Binlog 格式選擇，並提供完整的配置步驟與驗證方法。

核心運作原理

主從複制的本質是「主庫寫入，從庫重放」：

1. 主庫 開啟 Binary Log（Binlog），記錄所有修改數據的 SQL 語句或行變更。
2. 從庫 配置 Relay Log，通過 I/O 線程連接主庫，請求 Binlog 內容並寫入本地 Relay Log。
3. 從庫 的 SQL 線程讀取 Relay Log，重放其中的事件，實現數據同步。

整過程需主庫建立具備 REPLICATION SLAVE 權限的專用帳號，從庫配置主庫連接資訊（IP、Port、User、Password、Binlog 文件名及 Position）。

關鍵配置：Binlog 格式選擇

binlog_format 參數決定 Binlog 記錄內容，直接影響複制的一致性與效能：

建議：生產環境優先使用 MIXED 或 ROW，確保數據一致性。

完整配置步驟（以 MySQL 5.6/5.7 為例）

1. 主庫配置

編輯 /etc/my.cnf（或 my.ini），在 [mysqld] 區塊添加：

[mysqld]
log-bin=mysql-bin          # 啟用 Binlog，指定檔案前綴
server-id=1                # 唯一識別 ID，主從必須不同
binlog_format=ROW          # 推薦 ROW 或 MIXED
# 可選：只同步特定庫
# binlog-do-db=your_db
# binlog-ignore-db=mysql

重啟 MySQL 服務使配置生效。

2. 建立複制專用帳號

CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3. 獲取主庫 Binlog 位置

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      120 |              |                  |
+------------------+----------+--------------+------------------+

記下 File 與 Position，從庫配置時需用到。

4. 從庫配置

編輯 /etc/my.cnf：

[mysqld]
server-id=2                # 唯一 ID，不可與主庫重複
relay-log=relay-bin        # 指定 Relay Log 前綴
read-only=1                # 建議開啟，防止從庫誤寫入

重啟從庫 MySQL 服務。

5. 配置從庫連接主庫

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl',
  MASTER_PASSWORD='StrongPassword123!',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=120,
  MASTER_CONNECT_RETRY=10;

6. 啟動複制並驗證

START SLAVE;
SHOW SLAVE STATUS\G

重點檢查兩個關鍵指標必須為 Yes：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

若均為 Yes，代表複制已正常運行。Seconds_Behind_Master: 0 表示無延遲。

進階應用：讀寫分離

主從搭建完成後，可在應用層或中間件（如 MyCat、ProxySQL、MaxScale）配置：寫操作導向主庫，讀操作分發至從庫，顯著提升系統吞吐量。

常見問題排查

• IO 線程不啟動： 檢查網路連通性、防火牆、帳號密碼、MASTER_LOG_FILE/POS 是否正確。
• SQL 線程報錯： 查看 Last_SQL_Error，常見於主從表結構不一致、主鍵衝突、非確定性函數導致數據差異。
• 延遲過大： 優化從庫硬體、調整 slave_parallel_workers（多線程複制）、檢查長事務、考慮升級網路頻寬。

總結

MySQL 主從複制是架構演進的基石。掌握其原理、合理選擇 Binlog 格式、規範配置流程、建立監控告警，才能構建穩定可靠的資料庫叢集。