Linux Magazine's Jeremy Garcia shows you how to take command of MySQL using MySQL Proxy, a lightweight application that sits between MySQL server and client applications. Using MySQL Proxy, you can set up load balancing, dynamic fail over, query analysis, query filtering, query modification, and more.
The new MySQL Proxy is a lightweight program that sits between your client and your MySQL database and monitors, analyzes, and manipulates the communication. Your client connects to the proxy via your usual credentials. From there, an embedded Lua interpreter allows you to perform arbitrary actions on both the incoming query and the result set. Such interception and manipulations provides for a variety of uses, including load balancing, dynamic fail over, query analysis, query filtering, query modification, and more. The power of MySQL Proxy is the flexibility offered by the Lua engine.
MySQL Proxy is licensed under the GNU Public License and is available for download from http://forge.mysql.com/wiki/MySQL_Proxy. Keep in mind that the product is still young, so you may encounter a few rough edges. Additionally, versions MySQL 5.x and up are the only releases officially supported.
While official binaries are available for some platforms, you may prefer to use the Subversion tree, since it contains the bleeding-edge code. To check MySQL Proxy out, compile, and install, perform the following:
$ svn co http://svn.mysql.com/\
$ cd mysql-proxy/trunk
$ ./configure && make distcheck
# make install
Once installed, run
/usr/local/sbin/mysql-proxy --help-all to get a list of all command-line options. By default, the software’s proxy module listens on port 4040 and its administration module listens on port 4041, and all connections are passed to localhost, or 127.0.0.1. The Subversion tree contains some sample Lua scripts to get you started.
The following examples show a mysql-proxy command followed by a brief explanation. The connection string for the mysql client is
mysql-h 127.0.0.1-P4040 in all cases unless otherwise noted. For initial testing, include the
-D flag, which keeps MySQL Proxy in the foreground.
$ /usr/local/sbin/mysql-proxy -D \
This starts the program with a sample script (look in the examples subdirectory) that simply identifies SQL statements of type
COM_QUERY. Any query command you issue in the MySQL client, mysql, results in a line such as the following:
we got a normal query: show databases
This simple example proves that MySQL Proxy is working. Let’s try a slightly more advanced example.
/usr/local/sbin/mysql-proxy -D \
The tutorial-rewrite.lua script transforms a few Unix- like commands in the MySQL client to the proper MySQL syntax. For instance,
cd mysql is the functional equivalent to
use mysql, and
ls issues a
SHOW TABLES. You’re now starting to get a brief glimpse into the power of MySQL Proxy.
If you take a look at the remaining example scripts, you’ll notice three main member functions accessible via the Lua engine.
connect_server() is called at connection time and can be used to change connection parameters.
read_query(packet) is called before sending the query to the server and can be used to either modify the query or add additional information to it.
read_query_result(injection_packet) is called before sending the result to the client and can be used to modify the result set. With this information and a little practice with Lua, the sky is really the limit. In addition to the included sample scripts, there are additional examples and tutorials on the MySQL Proxy site.
Keep in mind that MySQL Proxy is useful even without a Lua script. You can specify one or more backend IP addresses and ports, which makes load balancing and fail over as easy as:
$ mysql-proxy \
With MySQL Proxy, a couple Lua scripts, and a little imagination, you can solve a variety of problems that were very difficult if not impossible to accomplish previously. From fixing common spelling mistakes, to masking passwords, to completely rewriting queries based on your specific requirements, the possibilities are nearly endless.