server1: 192.168.2.32
server2: 192.168.2.114
1、先做主从S1为主,S2为从。
2、安装mysql-proxy到S1
下载安装包:
wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz
解压安装包:
tar zxvf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz
配置服务路径
mv mysql-proxy-0.8.4 /usr/local/mysql-proxy
3、编写配置文件
vim /etc/mysql-proxy.conf
[mysql-proxy]
user=root
admin-username=root
admin-password=Aa-123456
proxy-address=192.168.2.28:3308 代理库
proxy-read-only-backend-addresses=192.168.2.88:3306 从库
proxy-backend-addresses=192.168.2.18:3306主
#proxy-write-only-backend-addresses=192.168.2.18:3306 主库
#proxy-backend-addresses=192.168.2.18:3306
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
#admin-address=192.168.2.32:4001 ##proxy的管理用户adminiphe端口
#admin-username=admin
#admin-password=qijkkwcw
#admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
log-level=info
daemon=1
log-file=/var/log/mysql-proxy.log
keepalive=1
chmod 660 /etc/mysql-proxy.conf
4、更改分离规则
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
更改处
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, ##最小连接数
max_idle_connections = 2, ##最大连接数后实现读写分离
is_debug = false
}
当连接数为2时,开始读写分离
5、启动
./mysql-proxy --defaults-file=/etc/mysql-proxy.conf