mysql-proxy以前也测试过，当时记录下来了，需要看的时候怎么也找不到存放在什么位置了，看来知识也是需要管理的，第一个想到的是去mysql官网下载安装包，找了N久也没找到，google了一把，终于找到地址下载地址了http://dev.mysql.com/downloads/mysql-proxy，现在的版本是0.8.0了。
mysql-proxy官方描述及工作原理图如下：
http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html
mysql-proxy is a lightweight binary application standing between one or more MySQL clients and a server. The clients connect to the proxy with the usual credentials, instead of connecting to the server. The proxy acts as man-in-the-middle between client and server.
In its basic form, the proxy is just a redirector. It gets an empty bucket from the client (a query), takes it to the server, fills the bucket with data, and passes it back to the client.
If that was all, the proxy would just be useless overhead. There is a little more I haven't told you yet. The proxy ships with an embedded Lua interpreter. Using Lua, you can define what to do with a query or a result set before the proxy passes them along.
测试过程如下：
一，服务器使用情况：
Node
|
IP Address
|
主服务器
|
192.168.0.176
|
从服务器
|
192.168.0.178
|
Proxy服务器
|
192.168.0.180
|
客户端服务器
|
192.168.0.205
|
二，从mysql官网下载二进制版本，下载地址：http://dev.mysql.com/downloads/mysql-proxy。
三，proxy服务器安装与配置(注意事项：0.7.2版的时候mysql-proxy是在sbin目录下，0.8.0在bin目录下)：
[root@localhost mysql-proxy]# tar -zxvf mysql-proxy-0.8.0-linux-rhel5-x86-64bit.tar.gz -C /usr/local/mysql-proxy/
[root@localhost mysql-proxy]# mv mysql-proxy-0.8.0-linux-rhel5-x86-64bit mysql-proxy
[root@localhost mysql-proxy]# vi /etc/profile
export PATH=$PATH:/usr/local/mysql-proxy/mysql-proxy/bin
[root@localhost mysql-proxy]# mysql-proxy --help-all
Usage:
mysql-proxy [OPTION...] - MySQL App Shell
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module
admin-module
--admin-address=<host:port> listening address:port of the admin-server (default: :4041)
--admin-username=<string> username to allow to log in (default: root)
--admin-password=<string> password to allow to log in (default: )
--admin-lua-script=<filename> script to execute by the admin plugin
proxy-module
-P, --proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
-r, --proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
-b, --proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
-s, --proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy don't start the proxy-module (default: enabled)
--proxy-pool-no-change-user don't use CHANGE_USER to reset the connection coming from the pool (default: enabled)
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higer
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
[root@localhost mysql-proxy]# mysql-proxy --proxy-read-only-backend-addresses=192.168.0.178:3306 --proxy-backend-addresses=192.168.0.176:3306 --proxy-lua-script=/usr/local/mysql-proxy/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --keepalive
2010-08-11 13:32:32: (critical) chassis-limits.c:81: could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 4096.
四，安装完成后的测试结果：
1，在客户端服务器上连接proxy：
mysql -h192.168.0.180 -P4040 -uourgame -pourgame
2，连接上服务器之后，在abc库建立chlotte表并插入数据：
mysql> use abc;
Database changed
mysql> create table chlotte (id int,name varchar(50),address varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into chlotte values (1,'xx','beijing');
Query OK, 1 row affected (0.00 sec)
mysql> insert into chlotte values (2,'yy','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> select * from chlotte;
+------+------+----------+
| id | name | address |
+------+------+----------+
| 1 | xx | beijing |
| 2 | yy | shanghai |
+------+------+----------+
2 rows in set (0.00 sec)
主服务器：
mysql> select * from chlotte;
+------+------+----------+
| id | name | address |
+------+------+----------+
| 1 | xx | beijing |
| 2 | yy | shanghai |
+------+------+----------+
2 rows in set (0.00 sec)
从服务器：
mysql> select * from chlotte;
+------+------+----------+
| id | name | address |
+------+------+----------+
| 1 | xx | beijing |
| 2 | yy | shanghai |
+------+------+----------+
2 rows in set (0.00 sec)
3，停止从服务器的复制后，再次插入数据：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into chlotte values (3,'cc','anhui');
Query OK, 1 row affected (0.00 sec)
mysql> insert into chlotte values (4,'ww','guangzhou');
Query OK, 1 row affected (0.00 sec)
mysql> select * from chlotte;
+------+------+-----------+
| id | name | address |
+------+------+-----------+
| 1 | xx | beijing |
| 2 | yy | shanghai |
| 3 | cc | anhui |
| 4 | ww | guangzhou |
+------+------+-----------+
4 rows in set (0.00 sec)
主服务器：
mysql> select * from chlotte;
+------+------+-----------+
| id | name | address |
+------+------+-----------+
| 1 | xx | beijing |
| 2 | yy | shanghai |
| 3 | cc | anhui |
| 4 | ww | guangzhou |
+------+------+-----------+
4 rows in set (0.00 sec)
从服务器：
mysql> select * from chlotte;
+------+------+----------+
| id | name | address |
+------+------+----------+
| 1 | xx | beijing |
| 2 | yy | shanghai |
+------+------+----------+
2 rows in set (0.00 sec)
五，压力测试：
1，安装sysbench工具：
注：make的时候报下面的错误，安装上devel包及share包之后正常。
drv_mysql.c:879: 错误：expected expression before ‘)’ token
make[3]: *** [libsbmysql_a-drv_mysql.o] 错误 1
make[3]: Leaving directory `/usr/local/sysbench-0.4.12/sysbench/drivers/mysql'
make[2]: *** [all-recursive] 错误 1
make[2]: Leaving directory `/usr/local/sysbench-0.4.12/sysbench/drivers'
make[1]: *** [all-recursive] 错误 1
make[1]: Leaving directory `/usr/local/sysbench-0.4.12/sysbench'
[root@youxia205 sysbench-0.4.12]# rpm -qa | grep MySQL
MySQL-client-community-5.1.48-1.rhel5
MySQL-shared-5.1.48-1.glibc23
MySQL-devel-community-5.1.48-1.rhel5
MySQL-server-community-5.1.48-1.rhel5
[root@youxia205 sysbench-0.4.12]#./configure --prefix=/usr/local/sysbench --with-mysql --with-mysql-includes=/usr/include/mysql/ --with-mysql-libs=/usr/lib64/mysql/ &&make &&make install
2，测试条件(生成测试数据，引擎为innodb表大小为1KW条记录)：
[root@youxia205 sysbench]# /usr/local/sysbench-0.4.12/sysbench/sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-db=test --mysql-host=192.168.0.180 --mysql-port=4040 --mysql-user=devuser --mysql-password=devuser --mysql-db=test prepare
sysbench 0.4.12: multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Creating table 'sbtest'...
Creating 10000000 records in table
3，最终测试结果如下，测试过程中proxy没有出错：
mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (8.15 sec)
总体感觉比0.7.2稳定多了，生产中不知是否有同学使用。
