mysql proxy负载均衡_MySQL-Proxy实现读写分离及负载均衡

《MySQL-Proxy实现读写分离及负载均衡》由会员分享，可在线阅读，更多相关《MySQL-Proxy实现读写分离及负载均衡(8页珍藏版)》请在人人文库网上搜索。

1、MySQL Proxy实现读写分离及负载均衡MySQL Proxy实现读写分离及负载均衡 Posted on 2013 年 2 月 23 日 by Jose1. MySQL Proxy安装MySQL Proxy安装之前需要的依赖包:- libevent 1.x- lua 5.1.x 或更高版本- glib2 2.6.0 或更高版本- pkg-config- libtool 1.5 或更高版本- MySQL 5.0.x 或更高版本1.1 安装一些依赖包：yum -y install gcc libevent libevent-devel readline readline-devel ncurs。

2、es ncurses-devel glib2 glib2-devel1.2 安装Luawget -c http:/www.lua.org/ftp/lua-5.1.5.tar.gztar zxvf lua-5.1.5.tar.gzcd lua-5.1.5vi Makefile #将INSTALL_TOP=/usr/local修改为:INSTALL_TOP=/usr/local/luavi ./src/Makefile #将CFLAGS= -O2 -Wall $(MYCFLAGS)修改为:CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS)make linux make inst。

3、all1.3 安装MySQL Proxywget -c http:/dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.3.tar.gz/from/http:/cdn.mysql.com/tar zxvf mysql-proxy-0.8.3.tar.gzcd mysql-proxy-0.8.3./configure LDFLAGS=-lm -ldl LUA_CFLAGS=/usr/local/lua/bin/lua -I/usr/local/lua/include LUA_LIBS=/usr/local/lua/lib/liblua.。

4、a -prefix=/usr/local/mysql-proxy -with-luamake & make install2. MySQL Proxy配置与启动2.1 配置MySQL ProxyMySQL Proxy的配置选项既可以作为MySQL Proxy启动命令行的参数,也可以放到配置文件当中使用.mkdir /var/log/mysql-proxymkdir /usr/local/mysql-proxy/lua-scriptsmkdir /usr/local/mysql-proxy/conf启动使用的配置文件mysql-proxy.conf:rootcentos190 conf# cd 。

5、/usr/local/mysql-proxy/confrootcentos190 conf# cat mysql-proxy.conf mysql-proxydaemon=truepid-file=/usr/local/mysql-proxy/mysql-proxy.pidlog-file=/var/log/mysql-proxy/mysql.loglog-level=debugLUA_PATH=/usr/local/mysql-proxy/lua-scripts/?.luaproxy-lua-script=/usr/local/mysql-proxy/lua-scripts/rw-split。

6、ting.luaproxy-address=192.168.1.190:3306proxy-read-only-backend-addresses=192.168.1.192:3306,192.168.1.189:3306proxy-backend-addresses=192.168.1.191:3306keepalive=trueproxy-skip-profiling=true#proxy-connect-timeout=2#proxy-read-timeout=60 #proxy-write-timeout=30admin-address=:4041admin-lua-script=/u。

7、sr/local/mysql-proxy/lua-scripts/admin.luaadmin-username=adminadmin-password=adminplugins=proxy,admin修改mysql-proxy.conf的权限为0600rootcentos190 conf# chmod 660 mysql-proxy.conf2.2 修改MySQL Proxy启动时加载的lua脚本修改实现读写分离功能的rw-splitting.lua脚本连接池部分:- config- connection poolif not proxy.global.config.rwsplit then。

8、proxy.global.config.rwsplit = min_idle_connections = 1,max_idle_connections = 3,is_debug = falseend2.3 启动MySQL Proxyrootcentos190 conf# /usr/local/mysql-proxy/bin/mysql-proxy -defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf查看启动日志：rootcentos190 conf# tail -f /var/log/mysql-proxy/mysql.log 。

9、2013-02-24 08:25:41: (message) chassis-unix-daemon.c:136: angel we try to keep PID=31827 alive2013-02-24 08:25:41: (debug) chassis-unix-daemon.c:157: waiting for 318272013-02-24 08:25:41: (debug) chassis-unix-daemon.c:121: we are the child: 318272013-02-24 08:25:41: (critical) plugin proxy 0.8.3 sta。

10、rted2013-02-24 08:25:41: (critical) plugin admin 0.8.3 started2013-02-24 08:25:41: (debug) max open file-descriptors = 10242013-02-24 08:25:41: (message) proxy listening on port 192.168.1.190:40402013-02-24 08:25:41: (message) added read/write backend: 192.168.1.191:33062013-02-24 08:25:41: (message。

11、) added read-only backend: 192.168.1.192:33062013-02-24 08:25:41: (message) added read-only backend: 192.168.1.189:33062.4 使用MySQL Proxy命令行代理界面登陆：rootcentos190 conf# mysql -uroot -p -h 192.168.1.190 -P 4040管理界面登陆：rootcentos190 conf# mysql -uadmin -p -h 192.168.1.190 -P 40413. 负载均衡和读写分离测试3.1 分别在189(r。

12、ead),191(write),192(read)三台MySQL上建test表mysqlCREATE TABLE test ( id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.62 sec)3.2 使用PHP脚本测试query(insert into test values (10007),(10008););$result = $mysqli-query(select * from test);$rows = array();while ($row = $res。

13、ult-fetch_assoc() $rows = $row;var_dump($rows);3.3 测试发现多个问题- 可以实现读写分离和负载均衡,但是不太稳定,刷新PHP脚本太快就会返回warning错误信息- Admin 4041端口的登陆之后,使用MySQL Proxy的管理命令,均返回错误提示ERROR 1105 (07000): need a resultset + proxy.PROXY_SEND_RESULT . - 如果proxy server和backend server在同一台机器上,那么PHP脚本中的mysqli就返回(unknown command)错误- 测试过程中,还发现189和191机器down之后,192还正常的情况, MySQL Proxy返回无法连接错误ERROR 1105 (HY000): (proxy) all backends are downMySQL Proxy FAQ：https:/wikis.oracle.com/display/mysql/MySQL+Proxy+FAQ。