MYSQL读写分离

MySQL Proxy最强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

　　Jan Kneschke在《MySQL Proxy learns R/W Splitting》中详细的介绍了这种技巧以及连接池问题：
　　为了实现读写分离我们需要连接池。我们仅在已打开了到一个后端的一条经过认证的连接的情况下，才切换到该后端。MySQL协议首先进行握手。当进入到查询/返回结果的阶段再认证新连接就太晚了。我们必须保证拥有足够的打开的连接才能保持运作正常。
　　实现读写分离的LUA脚本：
　　

复制代码 代码如下:

-- 读写分离
　　--
　　-- 发送所有的非事务性Select到一个从数据库
　　if is_in_transaction == 0 and
　　packet:byte() == proxy.COM_QUERY and
　　packet:sub(2, 7) == "SELECT" then
　　local max_conns = -1
　　local max_conns_ndx = 0
　　for i = 1, #proxy.servers do
　　local s = proxy.servers[i]
　　-- 需要选择一个拥有空闲连接的从数据库
　　if s.type == proxy.BACKEND_TYPE_RO and
　　s.idling_connections > 0 then
　　if max_conns == -1 or
　　s.connected_clients < max_conns then
　　max_conns = s.connected_clients
　　max_conns_ndx = i
　　end
　　end
　　end
　　-- 至此，我们找到了一个拥有空闲连接的从数据库
　　if max_conns_ndx > 0 then
　　proxy.connection.backend_ndx = max_conns_ndx
　　end
　　else
　　-- 发送到主数据库
　　end
　　return proxy.PROXY_SEND_QUERY

 

 

 

--------------------------------------------------------------------------------

 

 

 

一直想等到BETA版出来再试验的，可还是经不住诱惑阿,下午终于有时间测试一下了。
（本文参考地址：http://blog.chinaunix.net/u/8111/showart.php?id=451420）
一、必备软件：
1、LUA
   可以去LUA的官方下载：www.lua.org
2、MySQL Proxy
   这里有好多二进制版本。
    http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
   或者去MYSQL官方下载源代码。

 

我这里下载了：

http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.0.tar.gz

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/

四、使用MySQL Proxy
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@localhost sbin]# 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 | Has read 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 | Has read 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

这个读写分离应该看得很清楚。其他的功能等我测试了再总结吧。

如果要知道为什么连接多了才会进行分离的话，看看