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

1. 安装
下载已经编译好的安装包,或者预编译安装包均可,在这里,使用预编译版本。

[@s1.yejr.com ~]# tar zxf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz

[@s1.yejr.com ~]# cd mysql-proxy-0.6.0-linux-rhas4-x86

#可以看到有2个目录
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# ls
sbin  share

[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# mv sbin/mysql-proxy /usr/local/sbin/

[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# ls share
mysql-proxy         tutorial-constants.lua  tutorial-packets.lua     tutorial-rewrite.lua  tutorial-warnings.lua
tutorial-basic.lua  tutorial-inject.lua     tutorial-query-time.lua  tutorial-states.lua

#将lua脚本放到/usr/local/share下,以备他用
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# mv share/mysql-proxy /usr/local/share/

#删除符号连接等垃圾代码
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# strip /usr/local/sbin/mysql-proxy

2. 启动
编译一下启动管理脚本:

[@s1.yejr.com ~]# vi /etc/init.d/mysql-proxy

#!/bin/sh
export LUA_PATH=/usr/local/share/mysql-proxy/?.lua

mode=$1
if [ -z "$mode" ] ; then
  mode="start"
fi

case $mode in
  'start')
    mysql-proxy --daemon \
--admin-address=:4401 \
--proxy-address=:3307 \
--proxy-backend-addresses=:3306 \
--proxy-read-only-backend-addresses=192.168.133.232:3306 \
--proxy-read-only-backend-addresses=10.10.74.61:3306 \
--proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua
    ;;

  'stop')
    killall mysql-proxy
    ;;

  'restart')
    if $0 stop ; then
      $0 start
    else
      echo  "retart failed!!!"
      exit 1
    fi
    ;;
esac
exit 0

现在解释一下启动脚本:
--daemon 采用daemon方式启动
--admin-address=:4401 指定mysql proxy的管理端口,在这里,表示本机的4401端口
--proxy-address=:3307 指定mysql proxy的监听端口,也可以用 127.0.0.1:3307 表示
--proxy-backend-addresses=:3306 指定mysql主机的端口
--proxy-read-only-backend-addresses=192.168.1.1:3306 指定只读的mysql主机端口
--proxy-read-only-backend-addresses=192.168.1.2:3306 指定另一个只读的mysql主机端口
--proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua 指定lua脚本,在这里,使用的是rw-splitting脚本,用于读写分离

完整的参数可以运行以下命令查看:

mysql-proxy --help-all

运行以下命令启动/停止/重启mysql proxy:

[@s1.yejr.com ~]# /etc/init.d/mysql-proxy start

[@s1.yejr.com ~]# /etc/init.d/mysql-proxy stop

[@s1.yejr.com ~]# /etc/init.d/mysql-proxy restart

3. 试用

[@s1.yejr.com ~]# mysql -h127.0.0.1 -uroot -P3307

mysql> show processlist;
+-------+------+----------------+------+---------+------+-------+------------------+
| Id    | User | Host           | db   | Command | Time | State | Info             |
+-------+------+----------------+------+---------+------+-------+------------------+
| 30052 | root | localhost:9656 | NULL | Query   |    0 | NULL  | show processlist |
+-------+------+----------------+------+---------+------+-------+------------------+

可以看到,产生了一个新连接。

用sysbench测试一下,看会不会挂掉:

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test prepare

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test run
.........
.........
Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   23.0387/0.00

还好,没给大家丢脸,剩下的测试自己完成吧 :)

4. 其他
mysql proxy还可以实现连接池的功能,这在很多LAMP开发中是软肋,因此,有了mysql proxy,就可以不用再担心连接数超限的问题了。
如果使用rw-splitting.lua脚本的话,最好修改以下2个参数的默认值:

min_idle_connections = 1
max_idle_connections = 3

技术相关:

评论

好文支持~
此文真是解了广大mysql用户的各种饥渴需求啊~

/usr/local/share/mysql-proxy/rw-splitting.lua
似乎用这个默认脚本测试并不能达到读写分离,分报错!

ERROR 1105 (07000): can't change DB to on slave 192.168.0.118:3306
如果用rw-splitting.lua脚本
打开多个客户端连接mysql-proxy操作数据库时就会出现上述错误

ERROR 1105 (07000): can't change DB to on slave c5n4:3306
这是rw-splitting.lua 文件的BUG
解决方法,参考http://bugs.mysql.com/bug.php?id=36962
Adding 'c.default_db ~= ""' for default DB check seems to solve the problem:
-- if client and server db don't match, adjust the server-side
--
-- skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and c.default_db and c.default_db ~= "" and c.default_db ~= s.default_db then
print(" server default db: '" .. s.default_db .. "'")
print(" client default db: '" .. c.default_db .. "'")
print(" syncronizing")
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db)
end
照上面这段修改rw-splitting.lua 文件。

syncronizing
ERROR 1105 (07000): can't change DB to on slave 192.168.0.108:3306
看来叶老大没有做过详细的测试啊! 当打开多个客户端连接操作数据库的时候就会出现这种问题!

这是rw-spliting.lua脚本的问题,我测试时碰到过了,不过没管他,等有时间了再深入去看看能不能解决

MySQL方案、培训、支持
MySQL 用户组

测试最新发现:

mysql> show databases;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> select count(*) from cjhjd.t_plan;
ERROR 1105 (07000): can't change DB to on slave 192.168.0.9:3306
mysql> use cjhjd;
Database changed
mysql> select count(*) from cjhjd.t_plan;
+----------+
| count(*) |
+----------+
| 1440 |
+----------+
1 row in set (1.10 sec)

mysql>

请问叶老大,有没有办法解决呢?谢谢.

其实很简单,判断一下是否已经选定数据库,否则就use一下。自己改改lua代码吧 :D

MySQL方案、培训、支持
MySQL 用户组

rw-splitting 是不是只能读写分离,而不能负载均衡?

可以自己修改lua脚本,DIY一下,应该能实现

您好,我现在在本地使用mysql-proxy来进行测试的时候出现以下问题,不是很明白报错的地方,请帮忙指导一下。
本地环境:windowsxp
Lua5.1
mysql-proxy版本信息:
C:\>mysql-proxy -V
mysql-proxy 0.8.1
chassis: mysql-proxy 0.8.1
glib2: 2.20.3
libevent: 1.4.12-stable
LUA: Lua 5.1.2
package.path: E:\mysql-proxy-0.8.1-win32-x86\lib
package.cpath: E:\mysql-proxy-0.8.1-win32-x86\bin\lua-?.dll
-- modules
admin: 0.8.1
proxy: 0.8.1

C:\>mysql-proxy --admin-address=192.168.40.75:4401 --proxy-address=192.168.40.75
:3307 --proxy-backend-addresses=192.168.40.75:3306 --proxy-read-only-backend-add
resses=192.168.40.75:3303 --proxy-read-only-backend-addresses=192.168.40.75:3309
--proxy-lua-script=E:\mysql-proxy-0.8.1-win32-x86\share\doc\mysql-proxy\rw-spli
tting.lua
2010-12-01 11:07:55: (critical) admin-plugin.c:569: --admin-username needs to be
set
2010-12-01 11:07:55: (critical) mainloop.c:267: applying config of plugin admin
failed
2010-12-01 11:07:55: (critical) mysql-proxy-cli.c:596: Failure from chassis_main
loop. Shutting down.

注:192.168.40.75是我本机IP地址,目前在该机器上已安装三个mysql数据库,master、slave工作正常,现在就是配置mysql-proxy的时候启不来.谢谢..

我也遇到上面的错误,但是我加上一些参数启动就正常,你也可以看下我的参数在做修改,主要是在0.8以上的版本
mysql-proxy --admin-username=111 --admin-password=111 --proxy-address=:3307 --admin-lua-script=/usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua

--proxy-backend-addresses=192.168.1.201:3306 &