MySQL-Proxy实现读写分离及负载均衡(共12页)

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 ncur

2、ses ncurses-devel glib2 glib2-devel1.2 安装Luawget -c /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 ins

3、tall1.3 安装MySQL Proxywget -c 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.a -prefix=/usr/local/mysql-proxy -with-luamake & make install2. MySQL Proxy配置与启动2.1 配置MySQL Pro

4、xyMySQL 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 /usr/local/mysql-proxy/confrootcentos190 conf# cat mysql-proxy.conf mysql-proxydaemon=truepid-f

5、ile=/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-splitting.luaproxy-address=90:3306proxy-read-only-backend-addresses=92:3306,19

6、89:3306proxy-backend-addresses=91:3306keepalive=trueproxy-skip-profiling=true#proxy-connect-timeout=2#proxy-read-timeout=60 #proxy-write-timeout=30admin-address=:4041admin-lua-script=/usr/local/mysql-proxy/lua-scripts/admin.luaadmin-username=adminadmin-password=adminplugins=proxy

7、,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 proxy.global.config.rwsplit = min_idle_connections = 1, max_idle_connections = 3, is_debug = f

8、alse end2.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 2013-02-24 08:25:41: (message) chassis-unix-daemon.c:136: angel we try to keep PID=31827 al

9、ive2013-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 started2013-02-24 08:25:41: (critical) plugin admin 0.8.3 started2013-02-24 08:25:41: (debug)

10、max open file-descriptors = 10242013-02-24 08:25:41: (message) proxy listening on port 90:40402013-02-24 08:25:41: (message) added read/write backend: 91:33062013-02-24 08:25:41: (message) added read-only backend: 92:33062013-02-24 08:25:41: (message) added read-only

11、 backend: 89:33062.4 使用MySQL Proxy命令行代理界面登陆：rootcentos190 conf# mysql -uroot -p -h 90 -P 4040管理界面登陆：rootcentos190 conf# mysql -uadmin -p -h 90 -P 40413. 负载均衡和读写分离测试3.1 分别在189(read),191(write),192(read)三台MySQL上建test表mysqlCREATE TABLE test ( id int(11) DEFAULT NULL ) E

12、NGINE=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 = $result-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 . -