MySQL Proxy - Implementation & Usage

A post from dbpandit

1) Download MySQL proxy (tar) from MySQL download link. Remember that mysql-proxy 0.8.1 is somehow incomptabile with mysql 5.6 (got handshake err during use)

2) Unzip and place add its bin folder path to /etc/environment file -> source /etc/environment -> echo $PATH. If it's coming in print command then everything is fine till now.

3) Place a conf file in /etc/mysql/ as mysql-proxy.cnf and place following content into it.

MySQL Proxy's configuration file (mysql-proxy.cnf)  
[mysql-proxy]
Below will run mysql-proxy as a deamon  
daemon = true  
Below paths will store pid and log file for mysql-proxy  
pid-file = /home/harshkumar/mysql-proxy/mysql-proxy.pid  
log-file = /home/harshkumar/mysql-proxy/mysql-proxy.log  
log level  
log-level = debug  
Below option will disable profiling of queries.  
proxy-skip-profiling = true  
Proxy's admin address  
admin-address = 127.0.0.1:3309  
Below address will forward all connections to any of proxy backend address in roundrobin fashion.  
proxy-address = 127.0.0.1:3308  
Backend mysql server instances  
proxy-backend-addresses = 127.0.0.3:3307, 127.0.0.1:3306  
The number of event threads reserved to handle incoming requests.  
event-threads = 8  
Creates a process surrounding the main mysql-proxy which will attempt to restart the true mysql-proxy process in thr event a crash or other failure.  
keepalive = 1  
Credentials to use while connecting through --> admin-address = 127.0.0.1:3309  
admin-username=root  
admin-password=secretpassword  
proxy-lua-script=/home/harshkumar/Downloads/mysql-proxy/mysql-proxy/lib/mysql-proxy/lua/reporter.lua  
Script to execute by the admin module  
admin-lua-script=/home/harshkumar/Downloads/mysql-proxy/mysql-proxy/lib/mysql-proxy/lua/admin.lua  
Path of mysql-proxy plugin directory  
plugin_dir=/home/harshkumar/Downloads/mysql-proxy/mysql-proxy/lib/mysql-proxy/plugins/  
Plugins to use  
plugins=proxy,admin  

4) After saving the above file. Start mysql-proxy by using the below command:
mysql-proxy –defaults-file=/etc/mysql/mysql-proxy.cnf
5) Try to connect mysql using proxy address: mysql -uroot -pabc@123 -h127.0.0.1 –port=3308

6) Try via different consoles to see connection to different mysql instances.

Ref Links:
ftp://ftp.icm.edu.pl/vol/rzm1/mysql/doc/mysql-ha-scalability/en/mysql-proxy-configuration.html http://anothermysqldba.blogspot.in/2013/05/setup-mysql-proxy.html

Also, Please don't forget to look into admin.lua to get exact idea of what is happening inside or which commands to use.
Note: show proxy processlist; will not provide any result until there is any active connection.