Installation and basic commands for MySQL proxy tutorial _mysql

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:

Mysql-proxy--help-all

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

Mysql> show Processlist;

+-------+------+----------------+------+---------+------+-------+------------------+
| 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