One of the most powerful features of MySQL Proxy is the end of "Read and write parting (Read/write splitting)". It works by having the primary database process a transactional query and processing a select query from a database. Database replication is used to synchronize changes caused by transactional queries to the database in the cluster.
0. Prerequisite Software:
1, LUA
Be able to go to Lua's official downloads: dpa.nsysu.edu.tw/downloads/mysql-proxy/.
May go to MySQL official download source code.
I have downloaded here:
Mysql.cdpa.nsysu.edu.tw/downloads/mysql-proxy/mysql-proxy-0.6.0.tar.gz
3, the test process abolished the B and C replication. So the SQL statement suddenly see where it came from.
In case of M-s (can first hold stop SLAVE on SLAVE)
1. Installation
download a compiled installation package, or precompile the installation package, where you can use the precompiled version.
[@s1. yejr.com ~]# tar zxf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
[@s1. yejr.com ~]# CD Mysql-proxy-0.6.0-linux-rhas4-x86
#可以看到有2个目录
[@s1. yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# ls
Sbin share
[@s1. yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# mv sbin/mysql-proxy/usr/local/sbin/
[@ s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# ls share
mysql-proxy Tutorial-constants.lua Tutorial-packets.lua Tutorial-rewrite.lua Tutorial-warnings.lua
Tutorial-basic.lua Tutorial-inject.lua Tutorial-query-time.lua Tutorial-states.lua
#将lua脚本放到/usr/local/share for his use
[@s1. yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# mv share/mysql-proxy/usr/local/share/
#删除符号连接等垃圾代码
[@s1. yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# Strip/usr/local/sbin/mysql-proxy
2. Start
Compile the Startup management script:
[@s1. yejr.com ~]# vi/etc/init.d/mysql-proxy #!/bin/sh export Lua_path=/usr/local/shar E/mysql-proxy/? Lua mode=$1 if [-Z "$mode"]; Then mode= "Start" fi case $mode in ' start ') mysql-proxy--daemon \--admin-address=:4401 \--proxy-address=:3307 \ proxy-backend-addresses=:3306 \--proxy-read-only-backend-addresses=192.168.133.232:3306 \-- proxy-read-only-backend-addresses=10.10.74.61:3306 \--proxy-lua-script=/usr/local/share/mysql-proxy/
Rw-splitting.lua;;
' Stop ') killall mysql-proxy; ' Restart ') if $ stop;
Then $ start else echo "Retart failed!!!"
Exit 1 fi;; ESAC Exit 0
Now explain the startup script:
--daemon starts with daemon mode
--ADMIN-ADDRESS=:4401 Specifies the management port of the MySQL proxy, where it represents the 4401 port on this machine
--PROXY-ADDRESS=:3307 Specifies the listening port for the MySQL proxy, or it can be represented by 127.0.0.1:3307
--proxy-backend-addresses=:3306 Specify the port of the MySQL host
--PROXY-READ-ONLY-BACKEND-ADDRESSES=192.168.1.1:3306 specifies a read-only MySQL host port
--PROXY-READ-ONLY-BACKEND-ADDRESSES=192.168.1.2:3306 specifies another read-only MySQL host port
--PROXY-LUA-SCRIPT=/USR/LOCAL/SHARE/MYSQL-PROXY/RW-SPLITTING.LUA Specifies the Lua script, where the rw-splitting script is used for read-write separation
The full parameters can be viewed by running the following command:
Run the following command to start/stop/restart MySQL proxy:
[@s1. yejr.com ~]#/etc/init.d/mysql-proxy start
[@s1. yejr.com ~]#/etc/init.d/mysql-proxy Stop
[@s1. yejr.com ~]#/etc/init.d/mysql-proxy Restart
3. Trial
[@s1. yejr.com ~]# mysql-h127.0.0.1-uroot-p3307
+-------+------+----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+----------------+------+---------+------+-------+------------------+
| 30052 | root | localhost : 9656 | NULL | Query | 0 | NULL | Show Processlist |
+-------+------+----------------+------+---------+------+-------+------------------+
As you can see, a new connection has been created.
Test it with Sysbench to see if it will hang out:
[@s1. yejr.com ~]# sysbench--test=oltp--mysql-table-engine=innodb--oltp-table-size=1000000 \--mysql-socket=/
Tmp/mysql.sock--mysql-user=root--mysql-db=test Prepare
[@s1. yejr.com ~]# sysbench--TEST=OLTP-- Mysql-table-engine=innodb--oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock--mysql-user=root-- Mysql-db=test Run
.........
.........
Threads Fairness:
Events (Avg/stddev): 10000.0000/0.00
Execution Time (avg/stddev): 23.0387/0.00
Fortunately, no disgrace to everyone, the rest of the test to finish it yourself:)
4. Other
MySQL Proxy can also achieve the function of the connection pool, which in many lamp development is a weakness, so, with the MySQL proxy, you can no longer worry about the number of connections overrun problem.
If you use the Rw-splitting.lua script, it is a good idea to modify the default values for the following 2 parameters:
min_idle_connections = 1
max_idle_connections = 3