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)
测试通过,读写分离功能正常。
posted on 2012-01-29 10:17 wait4friend 阅读(350) 评论(0) 编辑 收藏 举报