数据读写分离

 

测试环境:

用了三台服务器OS全为RHEL4,客户端用的Windows具体如下：

192.168.3.5安装Mysql-Proxy

192.168.3.6安装Mysql-Server（已安装）并且设置可写

192.168.3.4安装Mysql-server （已安装）并且设置只读

客户端为

192.168.3.66 （Windows）安装Mysql 客户端

1.下载MySQL-Proxy二进制版本

[root@jsjzhang src]#

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy- 0.6.1 -linux-rhel4-x86-32bit.tar.gz

[root@jsjzhang src]# tar -zxvf mysql-proxy- 0.6.1 -linux-rhel4-x86-32bit.tar.gz

 

[root@jsjzhang src]# mv mysql-proxy- 0.6.1 -linux-rhel4-x86-32bit /usr/local/mysql-proxy

2.登陆192.168.3.6

进入MySQL并建立test数据库

[root@jsjzhang src]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or /g.

Your MySQL connection id is 146

Server version: 5.0.45 -log Source distribution

 

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

 

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

 

mysql> use test;

Database changed

mysql> create table proxy(id int(5),name char(10));

Query OK, 0 rows affected (0.02 sec)

mysql> desc proxy;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id    | int(5)   | YES  |     | NULL    |       |

| name  | char(10) | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

2 rows in set (0.01 sec)

 

mysql>

 

 

mysql> grant all privileges on test.* to proxy@'%' identified by 'proxy';

Query OK, 0 rows affected (0.10 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

 

 

 

3.同样的方法登陆192.168.3.4建立test数据库，现在两台服务器上的test库都为空.

 

4.启动Mysql-Proxy

[root@jsjzhang share]# LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua" /usr/local/mysql-proxy/sbin/mysql-proxy --proxy-read-only-backend-addresses=192.168.3.4:3306 --proxy-backend-addresses=192.168.3.6:3306 --proxy-lua-script=/us

r/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua &

[1] 6403

 

 

5.下面用Windows 来进行测试

C:/ >mysql -uproxy -pproxy -P4040 -h192.168.3.5

Welcome to the MySQL monitor.  Commands end with ; or /g.

Your MySQL connection id is 155Server version: 5.0.45 -log

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> use test;

Database changed

mysql> insert into proxy(name) values("10"); 成功写入，接下来我们来查看一下

Query OK, 1 row affected (0.02 sec)

mysql>

 

mysql> select * from proxy;  没有结果？这就对了..

Empty set (0.00 sec)

 

mysql>

6.接下来我们再到可写的这台（192.168.3.6）上看看刚刚的数据是否已经被写入.

C:/ >mysql -uproxy -pproxy  -h192.168.3.6

 

Welcome to the MySQL monitor.  Commands end with ; or /g.

Your MySQL connection id is 164

Server version: 5.0.45 -log Source distribution

 

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

 

mysql> use test;

Database changed

mysql> select * from proxy;

+------+------+

| id   | name |

+------+------+|

| NULL | 10  |

+------+------+

2 rows in set (0.00 sec)

 

mysql>

 

看到了吧？数据已经成功写入，证明这个读写分离已经成功，这只是一个测试其它方面的考虑还需要要进一步研究.