MySQL 主從同步與讀寫分離完整配置指南

環境準備與前置條件

本教學示範在兩台已安裝 MySQL 5.1.48 (RPM 套件) 的 Linux 伺服器上建立主從同步，並透過 MySQL-Proxy 實現讀寫分離。為避免版本相容性問題，主從伺服器的 MySQL 版本應保持一致。

• Master: 192.168.0.1
• Slave: 192.168.0.2

一、主從同步配置步驟

1. 配置 Master 伺服器 (my.cnf)

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = data
binlog-ignore-db = mysql
log-bin = /var/log/mysql/updatelog
log-slave-updates
slave-skip-errors

關鍵參數說明：

• server-id：唯一識別碼，Master 必須為 1～2³²⁻¹ 間的正整數
• log-bin：啟用二進制日誌，記錄所有數據變更
• binlog-do-db / binlog-ignore-db：指定同步/忽略的資料庫
• log-slave-updates：將從庫接收到的更新也寫入自身二進制日誌（鏈式同步需開啟）

2. 建立同步專用帳號

GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.0.2' IDENTIFIED BY 'your_password';

僅授予 REPLICATION SLAVE 權限，遵循最小權限原則。

3. 重啟 Master 服務

/usr/bin/mysqladmin -uroot shutdown
/usr/bin/mysqld_safe &

4. 備份 Master 數據並傳輸至 Slave

# Master 上執行
FLUSH TABLES WITH READ LOCK;
cd /var/lib/mysql
tar -czf data.tar.gz data
scp ./data.tar.gz root@192.168.0.2:/var/lib/mysql

注意： 鎖表期間不可寫入，操作需迅速完成。

5. 配置 Slave 伺服器 (my.cnf)

[mysqld]
server-id = 3
log-bin = mysql-bin
master-host = 192.168.0.1
master-user = test
master-password = your_password
master-port = 3306
master-connect-retry = 60
replicate-ignore-db = mysql
replicate-do-db = data
log-slave-updates
slave-skip-errors

server-id 唯一且不衝突；master-connect-retry 設定斷線重連間隔（秒）。

6. 解壓數據並啟動同步

# Slave 上執行
cd /var/lib/mysql
tar -xzf data.tar.gz
# 無需修改權限，預設保持原狀

# 登入 MySQL 啟動同步
SLAVE START;
SHOW SLAVE STATUS\G

確認 Slave_IO_Running: Yes 且 Slave_SQL_Running: Yes 表示同步正常。

7. 同步位點校準（關鍵步驟）

因備份過程中 Master 仍在寫入，需以實際 SHOW MASTER STATUS 為準：

# Master
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| updatelog.000012 | 15016    | data         | mysql            |
+------------------+----------+--------------+------------------+

# Slave 修正位點
SLAVE STOP;
CHANGE MASTER TO 
  MASTER_HOST='192.168.0.1',
  MASTER_USER='test',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='updatelog.000012',
  MASTER_LOG_POS=15016;
SLAVE START;

8. 解鎖 Master 表

UNLOCK TABLES;

至此主從同步配置完成，可透過 Seconds_Behind_Master: 0 驗證無延遲。

二、MySQL-Proxy 實現讀寫分離

1. 依賴套件安裝

需編譯安裝以下套件（版本為示範時期版本，實際部署請取最新穩定版）：

• check-0.9.8
• glib-2.18.4
• libevent-2.0.6-rc
• lua-5.1.4
• pkg-config-0.23
• mysql-5.0.56 (僅客戶端庫)
• mysql-proxy-0.8.0

編譯安裝通用流程：./configure && make && make install

2. 關鍵編譯問題與解決方案

Lua 編譯缺少 readline

yum install libtermcap-devel ncurses-devel libevent-devel readline-devel

pkg-config 找不到 lua5.1

cp etc/lua.pc /usr/local/lib/pkgconfig/lua5.1.pc

glib 版本過低

系統自帶 glib2 版本較舊，需手動編譯安裝 glib-2.18.4。

mysql-proxy 找不到 mysql_config

需從源碼編譯 MySQL 客戶端：

./configure --prefix=/usr/local/mysql --without-server
make && make install

隨後編譯 mysql-proxy 指定路徑：

./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-lua

3. 啟動腳本配置

建立 /var/lib/bin/mysql-proxy.sh：

#!/bin/bash
LUA_PATH="/usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua" \
/usr/local/mysql-proxy/bin/mysql-proxy \
  --proxy-backend-addresses=192.168.0.1:3306 \
  --proxy-read-only-backend-addresses=192.168.0.2:3306 \
  --proxy-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/rw-splitting.lua \
  >> /var/log/mysql-proxy.log &

賦予執行權限並啟動：chmod a+x /var/lib/bin/mysql-proxy.sh && /var/lib/bin/mysql-proxy.sh

4. 驗證服務

netstat -an | grep 404
# 應顯示 4040 (代理端口) 和 4041 (管理端口) 皆處於 LISTEN 狀態

5. 架構說明

• 4040 端口： 應用程式連接此端口，寫入請求導向 Master (192.168.0.1)，讀取請求導向 Slave (192.168.0.2)
• rw-splitting.lua： 內建 Lua 腳本實現讀寫分離邏輯

總結與最佳實踐

1. 主從同步是高可用架構基石，務必驗證 Seconds_Behind_Master 持續為 0
2. 生產環境建議使用 GTID (MySQL 5.6+) 替代傳統位點同步
3. MySQL-Proxy 0.8.x 屬早期版本，現代架構推薦使用 ProxySQL 或 MySQL Router
4. 監控同步延遲、錯誤日誌，設置告警機制