1、LUA
可以去LUA的官方下载：www.lua.org
2、MySQL Proxy
这里有好多二进制版本。
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
或者去MYSQL官方下载源代码。
3、测试过程中取消了B和C的REPLICATION。这样SQL语句一下子就看出来从哪里来的。
如果是M-S（可以先在SLAVE上进行STOP SLAVE）

1、MySQL Proxy 安装地址：192.168.0.234(简称A)
2、MySQL 服务器地址：192.168.0.235(简称B)/236(简称C)

如果是按照二进制包安装的，跳过这一步。
1、 LUA的安装
[root@localhost ~]#tar zxvf lua-5.1.2.tar.gz -C /usr/local
[root@localhost ~]# cd /usr/local/
[root@localhost local]# mv lua-5.1 lua
[root@localhost lua]# cd lua
[root@localhost lua]#make local;make install;
导出环境变量：
[root@localhost lua]#export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm"
2、MySQL Proxy 安装
[root@localhost ~]#tar -zxvf mysql-proxy-0.6.1-linux-rhel4-x86-32bit.tar.gz -C /usr/local/mysql/
[root@localhost ~]#cd /usr/local/mysql
[root@localhost mysql]#mv mysql-proxy-0.6.1-linux-rhel4-x86-32bit/ mysql-proxy
[root@localhost sbin]# export PATH=$PATH:/usr/local/mysql/mysql-proxy/sbin/

1、查看帮助选项
[root@localhost ~]# mysql-proxy --help-all
2、对MySQL 操作
MySQL服务器假设已经安装。(安装步骤这里就不写了)
两台机器上的表初始结构和数据都是一样的，而且都有t_girl_user这个用户。
mysql> desc t;+-------+----------+------+-----+---------+----------------+|
Field | Type | Null | Key | Default |
Extra
|+-------+----------+------+-----+---------+----------------+|
id | int(11) | NO | PRI |
NULL | auto_increment | | c_str | char(64) |
NO |
|
|
| +-------+----------+------+-----+---------+----------------+2
rows in set (0.00 sec)2 rows in set (0.00 sec)我在B上插入一条记录mysql> insert into t(c_str) values('B');Query OK, 1 row affected
(0.00 sec)在C上同样插入一条记录mysql> insert into t(c_str) value('C');Query OK, 1 row affected
(0.00
sec)
mysql>
3、启动MySQL-Proxy（测试读写分离）
[root@localhostsbin]# mysql-proxy--proxy-read-only-backend-addresses=192.168.0.236:3306--proxy-backend-addresses=192.168.0.235:3306 --proxy-lua-script=/usr/local/mysql/mysql-proxy/share/mysql-proxy/rw-splitting.lua&
[1] 32554
让MYSQL PROXY自动启动的简单脚本
#!/bin/sh
# export PATH=$PATH:/usr/local/mysql-proxy
cd /usr/local/mysql-proxy
./mysql-proxy--proxy-read-only-backend-addresses=192.168.0.236:3306--proxy-backend-addresses=192.168.0.235:3306--proxy-lua-script=rw-splitting.lua >> /tmp/log
~
这个例子中限制192.168.0.236为只读,192.168.0.235为可写。
4、下来我们来看试验结果。
我们用几台客户端开启4个连接。
[root@localhost ~]# /usr/local/mysql/bin/mysql -ut_girl_user -p123456 -P4040 -h192.168.0.234 -Dt_girl
我这边已经启动了好几个客户端，这里就不贴了，命令和上面的一样。
写数据。
mysql> insert into t(c_str) values ('wangwang');Query
OK, 1 row affected (0.01 sec)mysql>
show processlist;+----+-------------+---------------------+--------+---------+------+-------+------------------+| Id |
User |
Host
| db | Command | Time | State |
Info
|+----+-------------+---------------------+--------+---------+------+-------+------------------+| 12 | t_girl_user |
192.168.0.234:44975 | t_girl | Sleep | 28
| |
NULL
| | 13 |
t_girl_user | 192.168.0.234:44976 | t_girl | Sleep | 15
| |
NULL
| | 14 |
t_girl_user | 192.168.0.234:44977 | t_girl | Sleep | 19
| |
NULL
| | 15 |
t_girl_user | 192.168.0.234:44978 | t_girl | Query
| 0 | NULL | show processlist | +----+-------------+---------------------+--------+---------+------+-------+------------------+4 rows in set (0.00
sec)4 rows in set (0.00 sec)读数据（现在还是写和读都在B上）mysql> select * from
t;+----+----------+| id | c_str
|+----+----------+| 1 |
B | | 2 | wangwang
| +----+----------+2 rows in set (0.00 sec)再增加一个客户端连接。1 row in set (0.00 sec)mysql> show
processlist;+----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+|Id
| User |
Host
| db | Command | Time
|State
|Info
|+----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+| 2
| system user
|
| NULL | Connect | 1842 | Hasread all relay log; waiting for the
slave I/O thread to update it
|NULL
| | 5 | root |
localhost |t_girl |
Query | 0
|NULL
|show processlist | | 12 | t_girl_user | 192.168.0.234:44975 |t_girl |
Sleep |
446| |
NULL
| | 13 | t_girl_user | 192.168.0.234:44976 |t_girl | Sleep
|
188| |
NULL
| | 14 | t_girl_user | 192.168.0.234:44977 |t_girl | Sleep
|
206| |
NULL
| | 15 | t_girl_user | 192.168.0.234:44978 |t_girl | Sleep
|
203| |
NULL
| | 16 | t_girl_user | 192.168.0.234:44979 |t_girl | Sleep
|
164| |
NULL
| | 17 | t_girl_user | 192.168.0.234:44980 |t_girl | Sleep
|
210| |
NULL
| +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+8 rows in set (0.00 sec)现在我们来读数据。mysql> select * from
t;+----+-------+| id | c_str |+----+-------+| 1 |
C | +----+-------+1 row in set (0.00
sec)这个数据很明显是来自C的。再插入一条记录mysql> insert into t(c_str) values ('wangwei');Query
OK, 1 row affected (0.00 sec)mysql> select * from
t;+----+-------+| id | c_str |+----+-------+| 1 |
C | +----+-------+1 row in set (0.00
sec)C上的数据没有变。还是没有数据。现在跑到B上看看。mysql> show processlist;+----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+|Id |
User |
Host
| db | Command | Time
|State
|Info
|+----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+| 2 | system user
|
| NULL | Connect | 1842 | Hasread all relay log; waiting for the
slave I/O thread to update it
|NULL
| | 5 |
root |
localhost | t_girl |
Query | 0
|NULL
|show processlist | |12 | t_girl_user | 192.168.0.234:44975 | t_girl |
Sleep |
446| |
NULL
| |13 |
t_girl_user | 192.168.0.234:44976 | t_girl | Sleep |
188| |
NULL
| |14 |
t_girl_user | 192.168.0.234:44977 | t_girl | Sleep |
206| |
NULL
| |15 |
t_girl_user | 192.168.0.234:44978 | t_girl | Sleep |
203| |
NULL
| |16 |
t_girl_user | 192.168.0.234:44979 | t_girl | Sleep |
164| |
NULL
| |17 |
t_girl_user | 192.168.0.234:44980 | t_girl | Sleep |
210| |
NULL
| +----+-------------+---------------------+--------+---------+------+-----------------------------------------------------------------------+------------------+8 rows in set (0.00 sec)mysql> select * from t;+----+----------+| id |
c_str |+----+----------+| 1 | B
| | 2
| wangwang | | 3 | wangwei | +----+----------+ 3 rows in set (0.00 sec)数据被成功插入到B