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

1、MySQL中文网MySQLProxy快速实现读写分离以及负载均衡1.安装下载已经编译好的安装包，或者预编译安装包均可，在这里，使用预编译版本。#tarzxfmysql-proxy-0.6.0-linux-rhas4-x86.tar.gz#cdmysql-proxy-0.6.0-linux-rhas4-x86#可以看到有2个目录mysql-proxy-0.6.0-linux-rhas4-x86#lssbinsharemysql-proxy-0.6.0-linux-rhas4-x86#mvsbin/mysql-proxy/usr/local/sbin/mysql-proxy-0.6.0-linux

2、-rhas4-x86#lssharemysql-proxytutorial-constants.luatutorial-packets.luatutorial-rewrite.luatutorial-warnings.luatutorial-basic.luatutorial-inject.luatutorial-query-time.luatutorial-states.lua#将lua脚本放到/usr/local/share下，以备他用mysql-proxy-0.6.0-linux-rhas4-x86#mvshare/mysql-proxy/usr/local/share/#删除符号连接等

3、垃圾代码mysql-proxy-0.6.0-linux-rhas4-x86#strip/usr/local/sbin/mysql-proxy2.启动编译一下启动管理脚本：s1#vi/etc/initd/mysql-proxy#!/bin/shexportLUAPATH=/usr/local/share/mysql-proxy/?luamode=$1if-z$mode;thenmode=startficase$modeinstart)mysql-proxy-daemon-admin-address=:4401-proxy-address=:3307-proxy-backend-addresses

4、=:3306-proxy-read-only-backend-addresses=192.168133232:3306proxy-read-only-backend-addresses=101074.61:3306一一proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua；stop)killallmysql-proxyrestart)if$0stop;then$0startelseechoretartfailed!exit1esacexit0现在解释一下启动脚本：-daemon采用daemon方式启动-admin-addre

5、ss=:4401指定mysqlproxy的管理端口，在这里，表示本机的4401端口-proxy-address=:3307指定mysqlproxy的监听端口，也可以用:3307表示-proxy-backend-addresses=:3306指定mysql主机的端口-proxy-read-only-backend-addresses=:3306指定只读的mysql主机端口-proxy-read-only-backend-addresses=:3306指定另一个只读的mysql主机端口-proxy-lua-script=/usr/local/share/mysq卜proxy/rw-splittin

6、g.lua指定lua脚本，在这里，使用的是rw-splitting脚本，用于读写分离完整的参数可以运行以下命令查看：mysql-proxy-help-all运行以下命令启动/停止/重启mysqlproxy：#/etc/init.d/mysql-proxystart#/etc/init.d/mysql-proxystop#/etc/init.d/mysql-proxyrestart3.试用#mysql-h-uroot-P3307mysqlshowprocesslist;TOC o 1-5 h z+|Id|User|Host|db|Command|Time|State|Info|+|30052|r

7、oot|localhost:9656|NULL|Query|0|NULL|showprocesslist|+可以看到，产生了一个新连接。用sysbench测试一下，看会不会挂掉：#sysbench-test=oltp-mysql-table-engine=innodb-oltp-table-size=1000000-mysql-socket=/tmp/mysql.sock-mysql-user=root-mysql-db=testprepare#sysbench-test=oltp-mysql-table-engine=innodb-oltp-table-size=1000000-mysql-socket=/tmp/mysql.sock-mysql-user=root-mysql-db=testrunThreadsfairness:events(avg/stddev):10000.0000/0.00executiontime(avg/stddev):23.0387/0.00还好，没给大家丢脸，剩下的测试自己完成吧:)4.其他mysqlproxy还可以实现连接池的功能，这在很多LAMP开发中是