Understanding MySQL Proxy

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/\
svnpublic/mysql-proxy/ mysql-proxy
$ cd mysql-proxy/trunk
$ ./autogen.sh
$ ./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 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 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 \
--proxy-backend-addresses= \
--proxy-backend-addresses= &

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.

Comments on "Understanding MySQL Proxy"


Jeremy, if we all had a little imagination, such all too brief articles would be unnecessary!

Would it not be possible to expand a little more on load balancing, for example? Specifically, how to direct writes to the current master, and deal with failover in more concrete terms?



I am as curious as ahaveland.

I have a master/slave setup with a pretty big rails app.. and it would be pretty cool to setup mysql proxy to read off of both the master and slave.. but write only to the master.

I’ll have to read-up elsewhere..


Just how “young” is this product. And would you say this is ready for production use

Shelon Padmore



Thanks for the feedback (and I apologize for the delayed response). Keep in mind that the articles posted to this site are copied from the print magazine and my column has a specific maximum word length. Often times that means I will introduce you to a subject, but you’ll need to do additional reading or research.

This is really interesting, You are a very skilled blogger. I’ve joined your rss feed and look forward to seeking more of your wonderful post. Also, I’ve shared your website in my social networks!

zeIhSQ Very good information. Lucky me I found your website by accident (stumbleupon). I ave book-marked it for later!

“Wow, amazing blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your website is great, as well as the content!”

“My brother suggested I might like this website. He was totally right. This post actually made my day. You can not imagine simply how much time I had spent for this info! Thanks!”

Amazing! Its actually awesome article, I have got much clear
idea regarding from this paragraph.

Here is my site – MelvinaDView

Nice post. I learn something totally new and challenging on websites I stumbleupon everyday.
It will always be useful to read through content from other writers
and use a little something from other sites.

Also visit my webpage CheriSRivkin

Leave a Reply