MySQL Proxy 读写分离

MySQL Proxy
二进制方式安装，版本0.8.1，安装地址192.168.40.129

# 为了方便测试，暂时停止MySQL的主从复制 ，然后分别插入一条数据
mysql> stop slave;

# Master库上的数据
mysql> select * from test.t;
+------+------+
| x    | y    |
+------+------+
|    1 | 130  |
+------+------+
1 row in set (0.00 sec)

# Slave库上的数据
mysql> select * from test.t;
+------+------+
| x    | y    |
+------+------+
|    1 | 131  |
+------+------+
1 row in set (0.00 sec)


# 新建或编辑mysql-proxy.cnf文件，添加以下内容(具体参数根据实际情况修改)：
shell> vi mysql-proxy.cnf
[mysql-proxy]
admin-username=root
admin-password=root
daemon=true
keepalive=true
admin-lua-script=/home/hadoop/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses=192.168.40.130:3306
proxy-read-only-backend-addresses=192.168.40.131:3306
proxy-lua-script=/home/hadoop/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/tmp/proxy-mysql.log
log-level=debug

# 编辑.bash_profile脚本
export PATH=$PATH:/home/hadoop/mysql-proxy/bin

# 启动和停止
shell> mysql-proxy --defaults-file=mysql-proxy.cnf > /tmp/proxy-mysql.log &
shell> killall -9 mysql-proxy


# 为达到测试效果，修改脚本,最小连接为1, 最大连接为2
shell> vi /home/hadoop/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,
                max_idle_connections = 2,
                is_debug = false
        }
end


# 同时启动多个客户端连接连接到Proxy
shell> mysql -utest -ptest -h192.168.40.129 -P4040 test
mysql> show processlist;
+----+------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host                 | db   | Command | Time | State | Info             |
+----+------+----------------------+------+---------+------+-------+------------------+
| 13 | test | 192.168.40.129:32802 | test | Sleep   |   15 |       | NULL             |
| 14 | test | 192.168.40.129:32803 | test | Query   |    0 | NULL  | show processlist |
+----+------+----------------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

# 所有通过Proxy的读，都是读取的Slave(IP 131)的数据
mysql> select * from test.t;
+------+------+
| x    | y    |
+------+------+
|    1 | 131  |
+------+------+
1 row in set (0.01 sec)


# 写入一条数据，但是读取不到。因为停止了复制
mysql> insert into t(x,y) values(3,'NEW');
Query OK, 1 row affected (0.38 sec)

mysql> select * from test.t;
+------+------+
| x    | y    |
+------+------+
|    1 | 131  |
+------+------+
1 row in set (0.01 sec)

# 直接登录到Master，可以读到新加的数据
shell>  mysql -utest -ptest -h192.168.40.130
mysql> select * from test.t;
+------+------+
| x    | y    |
+------+------+
|    1 | 130  |
|    3 | NEW|
+------+------+
2 rows in set (0.00 sec)

# 启动复制
mysql> start slave;

# 通过Proxy读取
mysql> select * from test.t;
+------+------+
| x    | y    |
+------+------+
|    1 | 131  |
|    1 | 130  |
|    3 | NEW |
+------+------+
3 rows in set (0.00 sec)

测试通过，读写分离功能正常。