MySQL Proxy实现读写分离及负载均衡

Posted on 2013 年 2 月 23 日 by Jose

1. 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 ncurses ncurses-devel glib2 glib2-devel

1.2 安装Lua

wget -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 install

1.3 安装MySQL Proxy

wget -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.a" --prefix=/usr/local/mysql-proxy --with-luamake && make install

2. MySQL Proxy配置与启动

2.1 配置MySQL Proxy

MySQL Proxy的配置选项既可以作为MySQL Proxy启动命令行的参数,也可以放到配置文件当中使用.

mkdir /var/log/mysql-proxymkdir /usr/local/mysql-proxy/lua-scriptsmkdir /usr/local/mysql-proxy/conf

启动使用的配置文件mysql-proxy.conf:

[root@centos190 conf]# cd /usr/local/mysql-proxy/conf[root@centos190 conf]# cat mysql-proxy.conf [mysql-proxy]daemon=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/?.lua"proxy-lua-script=/usr/local/mysql-proxy/lua-scripts/rw-splitting.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=/usr/local/mysql-proxy/lua-scripts/admin.luaadmin-username=adminadmin-password=adminplugins=proxy,admin

修改mysql-proxy.conf的权限为0600

[root@centos190 conf]# chmod 660 mysql-proxy.conf

2.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 = false        }end

2.3 启动MySQL Proxy

[root@centos190 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

查看启动日志：

[root@centos190 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 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 started2013-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) added read-only backend: 192.168.1.192:33062013-02-24 08:25:41: (message) added read-only backend: 192.168.1.189:3306

2.4 使用MySQL Proxy命令行

代理界面登陆：

root@centos190 conf]# mysql -uroot -p -h 192.168.1.190 -P 4040

管理界面登陆：

root@centos190 conf]# mysql -uadmin -p -h 192.168.1.190 -P 4041

3. 负载均衡和读写分离测试

3.1 分别在189(read),191(write),192(read)三台MySQL上建test表

mysql>CREATE TABLE `test` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.62 sec)

3.2 使用PHP脚本测试

<?php// mysql-proxy.php$mysqli = new MySQLi('192.168.1.190', 'root', '123456', 'test', 4040); if ($mysqli->connect_errno) {  exit($mysqli->connect_error);}//$mysqli->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 ... ]
- 如果proxy server和backend server在同一台机器上,那么PHP脚本中的mysqli就返回(unknown command)错误
- 测试过程中,还发现189和191机器down之后,192还正常的情况, MySQL Proxy返回无法连接错误[ERROR 1105 (HY000): (proxy) all backends are down]

MySQL Proxy FAQ：https://wikis.oracle.com/display/mysql/MySQL+Proxy+FAQ