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

MySQLProxy实现读写分离及负载均衡MySQLProxy实现读写分离及负载均衡Postedon2013年2月23日byJose1.MySQLProxy安装MySQLProxy安装之前需要的依赖包:-libevent1.x-lua5.1.x或更高版本-glib22.6.0或更高版本-pkg-config-libtool1.5或更高版本-MySQL5.0.x或更高版本1.1安装一些依赖包：yum-yinstallgcclibeventlibevent-develreadlinereadline-develncursesncurses-develglib2glib2-devel1.2安装Luawget-c将INSTALL_TOP=/usr/local修改为:INSTALL_TOP=/usr/local/luavi./src/Makefile#将CFLAGS=-O2-Wall$(MYCFLAGS)修改为:CFLAGS=-O2-Wall-fPIC$(MYCFLAGS)makelinuxmakeinstall1.3安装MySQLProxywget-c://cdn.mysql.com/tarzxvfmysql-proxy-0.8.3.tar.gzcdmysql-proxy-0.8.3./configureLDFLAGS=-lm-ldlLUA_CFLAGS=/usr/local/lua/bin/lua-I/usr/local/lua/includeLUA_LIBS=/usr/local/lua/lib/liblua.a--prefix=/usr/local/mysql-proxy--with-luamake&&makeinstall2.MySQLProxy配置与启动2.1配置MySQLProxyMySQLProxy的配置选项既可以作为MySQLProxy启动命令行的参数,也可以放到配置文件当中使用.mkdir/var/log/mysql-proxymkdir/usr/local/mysql-proxy/lua-scriptsmkdir/usr/local/mysql-proxy/conf启动使用的配置文件mysql-proxy.conf:[root@centos190conf]#cd/usr/local/mysql-proxy/conf[root@centos190conf]#catmysql-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/?.luaproxy-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@centos190conf]#chmod660mysql-proxy.conf2.2修改MySQLProxy启动时加载的lua脚本修改实现读写分离功能的rw-splitting.lua脚本连接池部分:---config----connectionpoolifnotproxy.global.config.rwsplitthenproxy.global.config.rwsplit={min_idle_connections=1,max_idle_connections=3,is_debug=false}end2.3启动MySQLProxy[root@centos190conf]#/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf查看启动日志：[root@centos190conf]#tail-f/var/log/mysql-proxy/mysql.log2013-02-2408:25:41:(message)chassis-unix-daemon.c:136:[angel]wetrytokeepPID=31827alive2013-02-2408:25:41:(debug)chassis-unix-daemon.c:157:waitingfor318272013-02-2408:25:41:(debug)chassis-unix-daemon.c:121:wearethechild:318272013-02-2408:25:41:(critical)pluginproxy0.8.3started2013-02-2408:25:41:(critical)pluginadmin0.8.3started2013-02-2408:25:41:(debug)maxopenfile-descriptors=10242013-02-2408:25:41:(message)proxylisteningonport192.168.1.190:40402013-02-2408:25:41:(message)addedread/writebackend:192.168.1.191:33062013-02-2408:25:41:(message)addedread-onlybackend:192.168.1.192:33062013-02-2408:25:41:(message)addedread-onlybackend:192.168.1.189:33062.4使用MySQLProxy命令行代理界面登陆：root@centos190conf]#mysql-uroot-p-h192.168.1.190-P4040管理界面登陆：root@centos190conf]#mysql-uadmin-p-h192.168.1.190-P40413.负载均衡和读写分离测试3.1分别在189(read),191(write),192(read)三台MySQL上建test表mysqlCREATETABLE`test`(`id`int(11)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8;QueryOK,0rowsaffected(0.62sec)3.2使用PHP脚本测试query(insertintotestvalues(10007),(10008););$result=$mysqli-query(select*fromtest);$rows=array();while($row=$result-fetch_assoc()){$rows[]=$row;}var_dump($rows);3.3测试发现多个问题-可以实现读写分离和负载均衡,但是不太稳定,刷新PHP脚本太快就会返回warning错误信息-Admin4041端口的登陆之后,使用MySQLProxy的管理命令,均返回错误提示[ERROR1105(07000):needaresultset+proxy.PROXY_SEND_RESULT...]-如果proxyserver和backendserver在同一台机器上,那么PHP脚本中的mysqli就返回(unknowncommand)错误-测试过程中,还发现189和191机器down之后,192还正常的情况,MySQLProxy返回无法连接错误[ERROR1105(HY000):(proxy)allbackendsaredown]MySQLProxyFAQ：