mysql代理服务器功能,实现mysql读写分离,把用户不同的请求发送给不同的服务器。
mysql-server1:192.168.70.129
mysql-server2:192.168.70.132
mysql-proxy:192.168.70.133
将mysql-proxy解压
[root@localhost 桌面]# tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz -C /usr/local/
[root@localhost 桌面]# cd /usr/local/
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-proxy-0.8.3-linux-rhel5-x86-64bit share
[root@localhost local]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit mysqlproxy
[root@localhost local]# cd mysqlproxy/
[root@localhost mysqlproxy]# ls
bin include lib libexec licenses share
[root@localhost mysqlproxy]# ls bin
mysql-binlog-dump mysql-myisam-dump mysql-proxy(启动脚本)
[root@localhost mysqlproxy]# ls share/doc/mysql-proxy/(区分用户读写请求的脚本)都是用lua语言编写
active-queries.lua ro-balance.lua tutorial-resultset.lua
active-transactions.lua ro-pooling.lua tutorial-rewrite.lua
admin-sql.lua rw-splitting.lua(读写分离)tutorial-routing.lua
analyze-query.lua tutorial-basic.lua tutorial-scramble.lua
auditing.lua tutorial-constants.lua tutorial-states.lua
commit-obfuscator.lua tutorial-inject.lua tutorial-tokenize.lua
commit-obfuscator.msc tutorial-keepalive.lua tutorial-union.lua
COPYING tutorial-monitor.lua tutorial-warnings.lua
histogram.lua tutorial-packets.lua xtab.lua
load-multi.lua tutorial-prep-stmts.lua
README tutorial-query-time.lua
给读写读写分离文件执行的权限
[root@localhost mysql-proxy]# ls -l rw-splitting.lua -rw-r--r-- 1 7157 wheel 11341 8月 6 2012 rw-splitting.lua[root@localhost mysql-proxy]# chmod +x rw-splitting.lua
启动脚本所在目录
[root@localhost bin]# lsmysql-binlog-dump mysql-myisam-dump mysql-proxy
./mysql-proxy 选项
-P:指定代理监听的IP地址、端口
-r :指定读服务器的IP地址、端口
-b:指定写服务器的IP地址、端口
-s:指定lua脚本文件的路径
--keepalive: 若进程崩溃,自动重启此进程
启动mysql-proxy服务,启动时检查本机3306端口是否被占用。
[root@localhost bin]# ./mysql-proxy -P 192.168.70.133:3306 -r 192.168.70.132:3306 -b 192.168.70.129:3306 -s /usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua --keepalive &[root@localhost bin]# 2014-03-21 11:00:50: (critical) plugin proxy 0.8.3 started
查看服务是否已启动
[root@localhost bin]# jobs
[1]+ Running ./mysql-proxy -P 192.168.70.133:3306 -r 192.168.70.132:3306 -b 192.168.70.129:3306 -s /usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua --keepalive &
[root@localhost bin]# netstat -anutp | grep :3306
tcp 0 0 192.168.70.133:3306 0.0.0.0:* LISTEN 5140/mysql-proxy
验证,在两台mysql服务器上建立一个表插入不同的数据,并在两个服务器上做相同的授权,在客户端执行mysql -uroot -h192.168.70.133 -p,通过得到的数据验证是否读写分离成功。