單機多實例：MySQL 多 Server 部署與 mysql-proxy 讀寫分離實戰

場景：一台伺服器跑兩個 MySQL 實例

# 資料目錄規劃
/data1/mysql1/data/mysql
/data1/mysql2/data/mysql

# 複製初始化資料
cp -R /usr/local/mysql/var/mysql/* /data1/mysql1/data/mysql/
# 或使用 mysql_install_db --datadir=...

my.cnf 關鍵配置

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
password = 123456

[mysqld1]
port = 3306
socket = /tmp/mysql.sock1
pid-file = /data1/mysql1/db-app1.pid
log = /data1/mysql1/db-app.log
datadir = /data1/mysql1/data
user = root

[mysqld2]
port = 3307
socket = /tmp/mysql.sock2
pid-file = /data1/mysql2/db-app1.pid
log = /data1/mysql2/db-app.log
datadir = /data1/mysql2/data
user = root

啟動/停止/測試

/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 1,2
/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf stop 1,2

# 連接測試
mysql -uroot -S /tmp/mysql.sock1
mysql -uroot -S /tmp/mysql.sock2

MySQL 安裝標準流程（源碼編譯）

groupadd mysql
useradd -g mysql mysql
gunzip < mysql-VERSION.tar.gz | tar -xvf -
cd mysql-VERSION
./configure --prefix=/usr/local/mysql
make && make install
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql var
chgrp -R mysql .
bin/mysqld_safe --user=mysql &

初始化安全設置

UPDATE mysql.user SET Password = PASSWORD('123456');
FLUSH PRIVILEGES;
DELETE FROM mysql.user WHERE User = '';
FLUSH PRIVILEGES;

mysql-proxy 實現讀寫分離與連接池

安裝與啟動腳本

tar zxf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
mv sbin/mysql-proxy /usr/local/sbin/
mv share/mysql-proxy /usr/local/share/

# /etc/init.d/mysql-proxy
#!/bin/sh
export LUA_PATH=/usr/local/share/mysql-proxy/?.lua
mode=$1
case $mode in
  start)
    mysql-proxy --daemon \
      --admin-address=:4401 \
      --proxy-address=:3307 \
      --proxy-backend-addresses=:3306 \
      --proxy-read-only-backend-addresses=192.168.133.232:3306 \
      --proxy-read-only-backend-addresses=10.10.74.61:3306 \
      --proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua
    ;;
  stop) killall mysql-proxy ;;
  restart) $0 stop && $0 start ;;
esac

關鍵參數說明

• --proxy-address：應用連接的代理端口（3307）
• --proxy-backend-addresses：主庫地址
• --proxy-read-only-backend-addresses：從庫地址（可多個）
• --proxy-lua-script：rw-splitting.lua 實現讀寫分離

壓測驗證

sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
  --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test prepare
sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
  --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test run

測試通過，未出現崩潰。

連接池優化

修改 rw-splitting.lua 兩個參數：

min_idle_connections = 1
max_idle_connections = 3

有效緩解 LAMP 架構下連接數超限問題。