Read/write Splitting Operation example using MySQL Proxy (1)

MySQL ProxyOne of the most powerful features is the end of"Read/write splitting(Read/Write Splitting )". The principle is to allow the primary database to process transactional queries, but to process SELECT queries from the database. Database Replication is used to synchronize changes caused by transactional queries to the slave database in the cluster.

 
 

  
  
max_conns = s.connected_clients   
  
  
 
  
  
max_conns_ndx = i   
  
  
 
  
  
end   
  
  
 
  
  
end   
  
  
 
  
  
end  
 
 

So far, we have found a database with easy connection

 
 

  
  
if max_conns_ndx > 0 then   
  
  
 
  
  
proxy.connection.backend_ndx = max_conns_ndx   
  
  
 
  
  
end   
  
  
 
  
  
else  
 
 

Send to primary database

 
 

  
  
end   
  
  
 
  
  
return proxy.PROXY_SEND_QUERY  
 
 

I always want to wait until the BETA version comes out and try again, but I still can't help but seduce myself. In the afternoon, I finally had time to test it.

See blog.chinaunix.net/u/8111/showart.php? Id = 451420)

I. necessary software:

1. LUA

You can download dpa.nsysu.edu.tw/downloads/mysql-proxy/from LUA /.

You may download the source code from the MYSQL official website.

I downloaded it here:

Mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.0.tar.gz

3. REPLICATION of B and C was abolished during the test. In this way, the SQL statement shows where it comes from.

In case the M-S is able to hold stop slave on SLAVE first)

Ii. Test host address:

1. MySQL Proxy installation address: 192.168.0.234 ()

2. MySQL server address: 192.168.0.235 (B)/236 (C)

Iii. installation experience:

Skip this step if the installation is based on the binary package.

1. LUA Installation

 
 

  
  
[root@localhost ~]#tar zxvf lua-5.1.2.tar.gz -C /usr/local  
  
  
 
  
  
[root@localhost ~]# cd /usr/local/  
  
  
 
  
  
[root@localhost local]# mv lua-5.1 lua  
  
  
 
  
  
[root@localhost lua]# cd lua  
  
  
 
  
  
[root@localhost lua]#make local;make install; 
 
 

Export environment variables:

 
 

  
  
[root@localhost lua]#export LUA_CFLAGS="-I/usr/local/include" LUA_LIBS="-L/usr/local/lib -llua -ldl" LDFLAGS="-lm" 
 
 

2. Install MySQL Proxy

 
 

  
  
[root@localhost ~]#tar -zxvf mysql-proxy-0.6.1-linux-rhel4-x86-32bit.tar.gz -C /usr/local/mysql/  
  
  
 
  
  
[root@localhost ~]#cd /usr/local/mysql  
  
  
 
  
  
[root@localhost mysql]#mv mysql-proxy-0.6.1-linux-rhel4-x86-32bit/ mysql-proxy  
  
  
 
  
  
[root@localhost sbin]# export PATH=$PATH:/usr/local/mysql/mysql-proxy/sbin/ 
 
 

4. Use MySQL Proxy

1. View help options

 
 

  
  
[root@localhost ~]# mysql-proxy --help-all 
 
 

2. monopoly on MySQL

The MySQL server was installed. (This is not part of the installation process)

The initial structure and data of the tables on the two machines are the same, and both have the user t_girl_user.

 
 

  
  
mysql> desc t;  
  
  
 
  
  
+-------+----------+------+-----+---------+----------------+  
  
  
 
  
  
| Field | Type | Null | Key | Default | Extra |  
  
  
 
  
  
+-------+----------+------+-----+---------+----------------+  
  
  
 
  
  
| id | int(11) | NO | PRI | NULL | auto_increment |   
  
  
 
  
  
| c_str | char(64) | NO | | | |   
  
  
 
  
  
+-------+----------+------+-----+---------+----------------+  
  
  
 
  
  
2 rows in set (0.00 sec)  
  
  
 
  
  
2 rows in set (0.00 sec) 
 
 

I inserted a registration entry on B.

 
 

  
  
mysql> insert into t(c_str) values('B');  
  
  
 
  
  
Query OK, 1 row affected (0.00 sec) 
 
 

Insert a record on C.

 
 

  
  
mysql> insert into t(c_str) value('C');  
  
  
 
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
 
  
  
mysql>  
 
 

3. Enable MySQL-Proxy test for read/write splitting)

 
 

  
  
[root@localhost sbin]# mysql-proxy --proxy-read-only-backend-addresses=192.168.0.236:3306 --proxy-backend-addresses=192.168.0.235:3306   
  
  
 
  
  
--proxy-lua-script=/usr/local/mysql/mysql-proxy/share/mysql-proxy/rw-splitting.lua &  
  
  
 
  
  
[1] 32554 
 
 

Easily enable MYSQL PROXY

 
 

  
  
#!/bin/sh  
  
  
 
  
  
# export PATH=$PATH:/usr/local/mysql-proxy  
  
  
 
  
  
cd /usr/local/mysql-proxy  
  
  
 
  
  
./mysql-proxy --proxy-read-only-backend-addresses=192.168.0.236:3306 --proxy-backend-addresses=192.168.0.235:3306 --proxy-lua-script=rw-splitting.lua >> /tmp/log 
 
 

In this example, only 192.168.0.236 is read-only and 192.168.0.235 is writable.