Mysql-Proxy使用

http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-configuration.html
https://wikis.oracle.com/display/mysql/MySQL+Proxy

新公司用到Mysql-Proxy做mysql读写分离，整理一下

简单总结目前了解
1、Proxy其实不是一台机器，只是一个连接方式，读取数据的时候自动从Salve上取，写数据则自动向Master中写。
所以Mysq-Proxy要基于Master-Slave
2、Mysql-proxy可以放在Master，Slave WEB应用上或者独立一台服务器都可以
(1)放在一台独立服务器时
好处：前端配置一样 方便配置，只需要连接同一个Proxy。坏处:有单点,浪费一台机器。
(2)放在Master或者Slave时
好处 前端配置一样，方便配置，只需要连接同一个Proxy。坏处:会增加真实数据库负载，也有单点
(3)放在每台WEB上
好处:没有单点。坏处：每台WEB都要配置proxy 管理麻烦
3、mysql-proxy 维护一个连接池。当连接数<4个时 读写都连接master。当连接数>4个时,只有写连接master，读会连接slave

一、安装
注意需要先安装lua
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。
# yum -y install lua

# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz
# mv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit /usr/local/mysql-proxy
下载来就可以用

# echo "/usr/local/mysql-proxy/lib/" > /etc/ld.so.conf.d/mysql-proxy.cnf
# ldconfig
# ldconfig -v |grep proxy
/usr/local/mysql-proxy/lib:
libmysql-proxy.so.0 -> libmysql-proxy.so.0.0.0

二、设置代理用户权限
主从都给予读写权限，防止将从替换为主的时候 没有写入权限
mysql -hmasterip -uroot -p password -e"grant all on *.* to 'dbproxy'@'192.168.0.%' identified by 'password';
mysql -hslaveip -uroot -p password -e"grant all on *.* to 'dbslave'@'192.168.0.%' identified by 'password';

三、配置
1、配置参数说明
PROXY_PATH=/usr/local/mysql-proxy/bin //定义mysql-proxy服务二进制文件路径
admin-address=127.0.0.1:4011 //管理端口
admin-username=root //定义内部管理服务器账号
admin-password=password //定义内部管理服务器密码

admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua //定义lua管理脚本路径
proxy-lua-script=/usr/local/mysql-proxy/scripts/rw-splitting.lua" //定义lua读写分离脚本路径

proxy-address=192.168.0.10:4011 //程序连接的服务器地址
proxy-read-only-backend-addresses=192.168.0.100:3311 //定义后端只读从服务器地址
proxy-backend-addresses=192.168.0.100:3311 //定义后端主服务器地址

daemon=true //定义以守护进程模式启动
keepalive=true //使进程在异常关闭后能够自动恢复
user=mysql //以mysql用户身份启动服务
log-level=warning //定义log日志级别，由高到低分别有(error|warning|info|message|debug)
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log //定义log日志文件路径
pid-file=/usr/local/mysql-proxy/run/mysql-proxy.pid //定义mysql-proxy PID文件路径

更多参数说明，参考
http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-configuration.html

2、源码配置范例
[mysql-proxy]
admin-address=127.0.0.1:4011
admin-user=dbslave
admin-pass=password
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting-utf8.lua

proxy-address=192.168.0.10:4011
proxy-read-only-backend-addresses=192.168.0.100:3311
proxy-backend-addresses=192.168.0.100:3311

daemon=true
keepalive=true
log-level=debug
max-open-files=1024
log-file=/var/log/mysql-proxy/proxy-4011.log
pid-file=/var/run/mysql-proxy/proxy_4011.pid

注意yum安装没有rw-splitting-utf8.lua脚本

4、建立相关目录和配置文件
mkdir -p /var/log/mysql-proxy/
mkdir -p /var/run/mysql-proxy
mkdir -p /etc/mysql-proxy/

#vim /etc/mysql-proxy/proxy_4011.cnf
[mysql-proxy]
admin-address=127.0.0.1:4011
admin-user=dbslave
admin-pass=password
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

proxy-address=192.168.78.250:4011
proxy-read-only-backend-addresses=192.168.78.251:3311
proxy-backend-addresses=192.168.78.252:3311

daemon=true
keepalive=true
log-level=debug
max-open-files=1024
log-file=/var/log/mysql-proxy/proxy_4011.log
pid-file=/var/run/mysql-proxy/proxy_4011.pid

5、启动
# /usr/local/mysql-proxy/libexec/mysql-proxy -V
mysql-proxy 0.8.3
chassis: mysql-proxy 0.8.3
glib2: 2.16.6
libevent: 1.4.13-stable
LUA: Lua 5.1.4
package.path: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua
package.cpath: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.so
-- modules
proxy: 0.8.3

启动
# /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy/proxy_4011.cnf
# ps xua|grep mysql-proxy
root 3840 0.0 0.0 3856 840 ? S 18:05 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy/proxy_4011.cnf
root 3841 0.0 0.1 3856 1132 ? S 18:05 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy/proxy_4011.cnf
# netstat -antlp |grep 4011
tcp 0 0 192.168.78.250:4011 0.0.0.0:* LISTEN 3841/mysql-proxy

 

四、读写分写启动设置

/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

 

if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,

is_debug = false
}
end