Mysql-proxy Read/write separation

First, installation

Required Packages:
Mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
Tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-c/usr/local

Second, the configuration file modification

Vim/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

cd/usr/local/mysql-proxy/
mkdir etc/
Vim mysql-proxy.conf

Create a corresponding logs directory in the/usr/local/mysql-proxy/path, or an error will occur when the service is opened
You also have to modify the permissions of the Mysql-proxy profile to 660, otherwise there is an issue with too much permissions when you turn on the server.
Open service:
/usr/local/mysql-proxy/bin/mysql-proxy--plugins=admin--plugins=proxy--defaults-file=/usr/local/mysql-proxy/etc /mysql-proxy.conf
Try to use absolute paths as much as possible
Open ports:

4041 is the administrator port, 3306 is the port that Mysql-proxy direct communication with the two MySQL hosts that are read/write separated, and if you use the default port of Mysql-proxy, you must add-P 4040 at the back of the login

For administrator Login:

Mysql-uadmin-predhat-h 172.25.99.5-p 4041

You can see the master has RW permission, slave has the RO permissions

MySQL in master authorizes the managed user,

Grant Select,update,insert [ email protected] ' 172.25.99.% ' identified by ' zhaoyan+1998 '; #读写分离使用的用户
Flush privileges;
In the realization of read and write separation
Make sure you're on the basis of a read-write separation.
To connect:
Mysql-h 172.25.99.5-uproxy-pzhaoyan+1998
Connect using two clients so that both are up when viewed on the administrator side and can be

And when the client is using which database, the administrator side is also in real-time monitoring.
The client has been operating several times Zy this database

Use tcpdump or lsof to grab the packet, check the proxy side to throw the user's request to which MySQL server
Write on the 172.25.99.250 client, read on Server2, check the packet capture situation
This is a write operation, 250 to the proxy to make a request, proxy switch to master

This is a read operation, Server2 to proxy to make a request, proxy steering slave

A read/write separation is realized.

Mysql-proxy Read/write separation