MySQL 主從同步與讀寫分離完整部署指南（含常見錯誤排查）

架構規劃

主從版本保持一致可減少相容性問題。

一、Master 設定

1. 修改 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 唯一識別，log-bin 啟用二進制日誌，binlog-do-db 指定同步資料庫。

2. 建立複寫帳號

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

3. 重啟 MySQL 並備份資料

# 重啟
/usr/bin/mysqladmin -uroot shutdown
/usr/bin/mysql_safe &

# 鎖表備份
mysql> 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

二、Slave 設定

1. 修改 my.cnf

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

2. 還原資料並啟動複寫

cd /var/lib/mysql
tar xzf data.tar.gz
mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G

確認 Slave_IO_Running: Yes 且 Slave_SQL_Running: Yes。

3. 同步 Binlog 位置

若 SHOW MASTER STATUS 與 Slave 顯示的 Master_Log_File/Read_Master_Log_Pos 不一致，需手動校正：

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.0.1',
    MASTER_USER='test',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='updatelog.000012',
    MASTER_LOG_POS=15016;
mysql> SLAVE START;

4. 解鎖 Master

UNLOCK TABLES;

三、讀寫分離：MySQL-Proxy 部署

依賴套件安裝順序

1. check-0.9.8
2. glib-2.18.4（系統版本過舊需自行編譯）
3. libevent-2.0.6-rc
4. lua-5.1.4
5. pkg-config-0.23
6. mysql-5.0.56（僅安裝 client/devel，./configure --without-server）
7. mysql-proxy-0.8.0

常見編譯錯誤與解決

啟動腳本範例

#!/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 &

驗證埠 4040（讀寫）與 4041（管理）是否監聽。

總結

主從同步核心在於 server-id 唯一、Binlog 正確、複寫帳號權限、資料一致性基準點對齊。讀寫分離雖可用 MySQL-Proxy 實現，但該專案已停止維護，生產環境建議改用 ProxySQL、MaxScale 或中間件層處理。