About MySQL Proxy

zhanhailiang 日期：2014-12-31

MySQL Proxy is a middleware between the MySQL client side and the MySQL server side that can monitor, analyze, or change their communications. Because its MySQL proxy to implement the MySQL C/S communication protocol, so it is transparent to the application, that is, the application of MySQL proxy as MySQL Server, only need to change the original directly connected MySQL host:port to MySQL Proxy host: Port can be;

Its main application scenarios:

1. Serves as a MySQL connection pool.
2. Monitor, analyze, or change SQL DML through LUA, such as connection control, filtering, and read-write separation and load balancing. The basic principle of "read and write separation" for MySQL Proxy is to let the primary database process transactional queries and let the select queries be processed from the library. Database replication is used to synchronize changes caused by transactional queries to the slave libraries in the cluster.

Next you'll learn how to install and use MySQL Proxy.

1. Installation

Download the source package and get the LUA test script

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.5.tar.gztar zxvf mysql-proxy-0.8.5.tar.gz

Download binary package, install

wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gztar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gzmv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/cd /usr/local/ln -s mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit mysql-proxy

2. Configuration

Adding environment variables to ~/.BASHRC

PATH=/usr/local/mysql-proxy/bin:$PATH

Create the MySQL proxy profile/usr/local/mysql-proxy/etc/master.conf, and note that the Master.conf file permissions are modified to 0660

[mysql-proxy]log-file = /var/log/mysql-proxy.loglog-level = messageproxy-backend-addresses = 127.0.0.1:3306

3. Testing

Start MySQL proxy and add--daemon will run as daemon, default MySQL proxy listens on port 4040:

mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/master.conf --proxy-lua-script=/root/software/mysql-proxy-0.8.5/examples/tutorial-query-time.lua

The MySQL client directly connects to MySQL proxy:

[[email protected]~/software/mysql-proxy-0.8.5/examples]#/usr/local/mysql/bin/ MySQL--host=127.0.0.1--port=4040-u root-penter password:welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 65Server version:5.5.39-log mysql Community Server (GPL) Copyright (c) #, Oracl e and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> 

To test the Lua script, test the script test.php as follows:

$db = mysql_connect(‘127.0.0.1:4040‘, ‘root‘, ‘******‘);if (!$db) {    exit(-1);}mysql_select_db(‘test‘, $db);$result = mysql_query(‘SELECT * FROM test.test‘, $db);while ($row = mysql_fetch_assoc($result)) {    var_dump($row);}

Execute the test.php:php test.php with the following output:

array(1) {  ["id"]=>  string(1) "1"}array(1) {  ["id"]=>  string(1) "2"}

View the MySQL proxy log as follows:

[root@/usr/local]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/etc/master.conf --proxy-lua-script=/root/software/mysql-proxy-0.8.5/examples/tutorial-query-time.luawe got a normal query: SELECT * FROM test.testquery-time: 0.3msresponse-time: 0.31ms

4. Read more

1. MySQL Proxy
2. Appendix A MySQL Proxy FAQ
3. Several articles of the MySQL proxy

About MySQL Proxy