Experimental Environment] platform centos6.4IP allocation 192.168.30.101 node1 mysql-proxy192.168.30.102 node2 mysql master node 192.168.30.103 node3 mysql slave Node
192.168.30.104 node4 client
Note:1. mysql5.52 has been compiled and installed on all machines. Make sure that the data of node2 and node3 is consistent during the experiment.
3. Disable master-slave replication for node2 and node3 during the experiment to observe the effect, as soon as the new data is written to the master server, it is immediately copied to the slave server through master-slave replication, which makes it difficult to tell whether mysql-proxy actually implements read/write splitting)
Lab Topology]
650) this. width = 650; "src ="/e/u/themes/default/images/spacer.gif "style =" border: 1px solid # ddd; "alt =" spacer.gif "/> 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/21293540K-0.png "title =" clipboard.png "alt =" 184919541.png"/>
Lab procedure]
Perform the following steps on the node1 (mysql-proxy) machine:
Install required software packages
[Root @ node1 ~] # Yum install-y readline-devel gcc * gcc-c ++ * autoconf * automake * zlib * libxml * ncurses-devel * libmcrypt * libtool * flex * pkgconfig * libevent * glib *
Compile and install lua scripts
Because mysql-proxy implements read/write separation through lua scripts, you need to install lua
Source package download: http://www.lua.org/download.html?root@node1 src] # wget http://www.lua.org/ftp/lua-5.1.4.tar.gz?root@node1 src] # tar xf lua-5.1.4.tar.gz
[Root @ node1 src] # cd lua-5.1.4
[Root @ node1 lua-5.1.4] # vi src/Makefile find the CFLAGS =-O2-Wall $ (MYCFLAGS) line with-fPIC, as shown below
CFLAGS =-O2-Wall-fPIC $ (MYCFLAGS)
Modify INSTALL_TOP, as shown below: Specify the installation directory)
INSTALL_TOP =/usr/local/lua
[Root @ node1 lua-5.1.4] # make linux
[Root @ node1 lua-5.1.4] # make install
Install mysql-proxy: http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
[Root @ node1 src] # wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3.tar.gz
[Root @ node1 src] # tar xf mysql-proxy-0.8.3.tar.gz [root @ node1 src] # cd mysql-proxy-0.8.3 [root @ node1 mysql-proxy-0.8.3] #. /configure LDFLAGS = "-lm-ldl" LUA_CFLAGS = "/usr/local/lua/bin/lua-I/usr/local/lua/include" LUA_LIBS = "/usr/ local/lua/lib-llua "-- prefix =/usr/local/mysql-proxy -- with-lua
[Root @ node1 mysql-proxy-0.8.3] # make & make install
Make sure there is a script for rw-spitting.lua
[Root @ node1 ~] # Cp/usr/local/src/mysql-proxy-0.8.3/lib/rw-splitting.lua/usr/local/mysql-proxy/share/doc/mysql-proxy/
Start mysql-proxy [root @ node1 ~] #/Usr/local/mysql-proxy/bin/mysql-proxy -- proxy-backend-addresses = 192.168.30.102: 3306 -- proxy-read-only-backend-addresses = 192.168.30.103: 3306 -- proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua & [3] 30840 [2] Done/usr/local/ mysql-proxy/bin/mysql-proxy -- proxy-backend-addresses = 192.168.30.102: 3306 -- proxy-read-only-backend-addresses = 192.168.30.103: 3306 -- proxy-lua-script =/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua
[Root @ node1 ~] #2013-09-17 00: 14: 46: (critical) plugin proxy 0.8.3 started
Note that the above script path should not be written incorrectly during startup; otherwise, an error indicating that the file does not exist will be reported.
Connect to mysql for authorization
Mysql master server (node2)[Root @ node2 src] # mysql-uroot-pmysql> grant all on *. * to 'proxy' @ '192. 168.30.101 'identified by 'Password ';
Query OK, 0 rows affected (0.09 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql slave server (node3)[Root @ node3 src] # mysql-uroot-pmysql> grant all on *. * to 'proxy' @ '192. 168.30.101 'identified by 'Password ';
Query OK, 0 rows affected (0.09 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Test the connection to mysql-proxy on Mysql1 and Mysql2 respectively to ensure that mysql-proxy can be connected.) [root @ node2 src] # mysql-uproxy-ppassword-P4040-h192.168.30.101
[Root @ node3 src] # mysql-uproxy-ppassword-P4040-h192.168.30.101
Test the read/write splitting effect
Make sure that the mysql DATA of node node2 and node node3 is consistent.Mysql> insert into t1 values (1,100 );
Query OK, 1 row affected (0.02 sec)
Mysql> select * from t1; + ------ + | id | ip | + ------ + | 1 | 100 | + ------ +
1 row in set (0.08 sec)
Client Machine[Root @ node4 src] # mysql-uroot-pmysql> grant all on *. * to 'proxy' @ '192. 168.30.101 'identified by 'Password ';
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[Root @ node4 src] # mysql-uproxy-ppassword-h192.168.30.101-P4040mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | mysql | performance_schema | proxy | test | + ------------------ +
5 rows in set (0.01 sec)
Mysql> use proxy;
Database changed
Mysql> select * from t1; + ------ + | id | ip | + ------ + | 1 | 100 | + ------ +
1 row in set (0.01 sec)
Add the record mysql> insert into t1 values (2,101 );
Query OK, 1 row affected (0.03 sec)
Mysql> insert into t1 values (3,102 );
Query OK, 1 row affected (0.04 sec)
Query records
Mysql> select * from t1; + ------ + | id | ip | + ------ + | 1 | 100 | + ------ +
1 row in set (0.01 sec)
We can see that the newly updated record is not read through the read operation.
Return to the node2mysql master node), and find the updated write record mysql> select * from t1; + ------ + | id | ip | + ------ + | 1 | 100 | 2 | 101 | 3 | 102 | + ------ +
3 rows in set (0.00 sec)
In node3mysql slave node), the newly updated record mysql> select * from t1 is not found; + ------ + | id | ip | + ------ + | 1 | 100 | + ------ +
1 row in set (0.00 sec)
Therefore, we have implemented MySQL read/write splitting. Currently, all write operations are performed on the Master server to avoid data synchronization. In addition, all read operations are distributed to other Slave servers to share the database pressure.
A
