您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 讀寫分離架構實戰：MySQL Proxy 部署、驗證與效能原理深度解析

編輯：MySQL 綜合教學

什麼是讀寫分離？

主庫處理寫入 (INSERT/UPDATE/DELETE/DDL)，從庫處理讀取 (SELECT)。主從同步透過 Binlog 異步複制。應用端或中間件自動路由 SQL。

讀寫分離八大效能提升原理

#	原理	說明
1	物理服務器增加	單機 → 多機，總 CPU/記憶體/IOPS 線性擴展
2	鎖爭用分離	主庫無 SELECT 共享鎖 (S-lock) 干擾寫入排他鎖 (X-lock)；從庫無寫入鎖
3	從庫引擎優化	從庫可用 MyISAM/ColumnStore 等純讀優化引擎，或關閉 InnoDB 降低開銷
4	異步複制天然緩衝	主庫 Binlog 發送、從庫 SQL 執行均異步，寫入延遲不阻塞主庫回應
5	適用讀多寫少場景	讀壓力分散至 N 個從庫，寫壓力集中主庫但量小
6	從庫啟動參數調優	`--skip-innodb --low-priority-updates --delay-key-write=ALL` 等純讀優化
7	讀壓力分攤數學模型	1主3從：10寫/分 → 總40寫 (各10)；150讀/分 → 各50讀。總吞吐大幅提升
8	高可用性副產品	主庫故障可提升從庫為新主，分鐘級恢復 (需配合 MHA/Orchestrator)

實驗環境拓撲

Proxy: serv01 (192.168.1.11) — MySQL Proxy 0.8.2
Master: serv08 (192.168.1.18) — MySQL 5.5.29, 讀寫
Slave: serv09 (192.168.1.19) — MySQL 5.5.29, 唯讀

部署步驟完整紀錄

1. 主從同步建立


# Master 建立同步用戶
GRANT REPLICATION SLAVE ON *.* TO 'larry'@'192.168.1.%' IDENTIFIED BY 'larry';

# Slave 配置
CHANGE MASTER TO
    MASTER_HOST='192.168.1.18',
    MASTER_USER='larry',
    MASTER_PASSWORD='larry',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS\G  -- 確認 Slave_IO_Running=Yes, Slave_SQL_Running=Yes

2. MySQL Proxy 安裝與配置


# 解壓
tar -xvf mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/
mv mysql-proxy-0.8.2-linux-glibc2.3-x86-64bit mysql-proxy

# 修改 rw-splitting.lua 關鍵參數
min_idle_connections = 1
max_idle_connections = 1
is_debug = true

# 啟動腳本 /etc/init.d/mysql-proxy 關鍵參數
PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.1.19:3306 \
               --proxy-backend-addresses=192.168.1.18:3306 \
               --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
--proxy-address=192.168.1.11:3306
--user=mysql --log-level=debug --log-file=/var/log/mysql-proxy.log

⚠️ 關鍵：--proxy-read-only-backend-addresses 指定從庫，--proxy-backend-addresses 指定主庫。反了會導致寫入走從庫報錯。

3. 驗證讀寫路由


# 連接 Proxy
mysql -ularry -plarry -h 192.168.1.11

# 執行寫入
INSERT INTO larrydb.user VALUES (5, 'test');
-- Proxy 日誌顯示: sending to backend : 192.168.1.18:3306 (Master)

# 執行查詢
SELECT * FROM larrydb.user;
-- Proxy 日誌顯示: sending to backend : 192.168.1.18:3306 (Master, 因 Slave 已 STOP SLAVE)

# 啟動 Slave 同步後
START SLAVE;
SELECT * FROM larrydb.user;
-- Proxy 日誌顯示: sending to backend : 192.168.1.19:3306 (Slave) ✅

MySQL Proxy 關鍵參數對照

參數	值	含義
`--proxy-backend-addresses`	主庫 IP:Port	寫入流量目標
`--proxy-read-only-backend-addresses`	從庫 IP:Port (可多個,逗號分隔)	讀取流量目標
`--proxy-lua-script`	rw-splitting.lua	讀寫分離路由邏輯
`--proxy-address`	Proxy 監聽 IP:Port	應用連線入口
`min_idle_connections`	1	每後端最小空閒連線
`max_idle_connections`	1	每後端最大空閒連線

生產環境遷移建議

棄用 MySQL Proxy：官方已停止維護 (EOL)，單執行緒、高延遲、無連線池、不支援準備語句。
推薦中間件：
- ProxySQL：高效能、連線池、查詢重寫、快取、熔斷、Galera/Group Replication 原生支援。
- MaxScale：MariaDB 官方、支援讀寫分離、Binlog 伺服器、防火牆。
- ShardingSphere-Proxy：分庫分表+讀寫分離、多語言 SDK。
- Vitess：YouTube 級規模、Kubernetes 原生、連線池、查詢快取。
應用層直連 + 程式碼路由：簡單場景可行，但運維成本高 (配置變更需重啟應用)。

主從延遲監控與緩解


-- 監控延遲
SHOW SLAVE STATUS\G  -- Seconds_Behind_Master
-- 或
SELECT TIMESTAMPDIFF(SECOND, 
    (SELECT MAX(event_timestamp) FROM performance_schema.events_statements_history_long),
    NOW()) AS lag_sec;

-- 緩解：半同步複制 (rpl_semi_sync_master)
-- 從庫規格對齊主庫 (CPU/記憶體/磁碟)
-- 關閉從庫不必要服務 (binlog, slow log 等)
-- 並行複制 (slave_parallel_workers > 0, MySQL 5.6+)