Getting Started with MySQL Proxy

The launch of MySQL Proxy has caused quite a commotion in the community. And with reason. For feature-hungry people, this is undeniably the most exciting addition to MySQL set of tools.
If the last statement has left you baffled, because you don't see the added value, don't worry. This article aims to give you the feeling of what the Proxy can do.

Get ready for a wonderful trip to Proxyland.

MySQL Proxy Overview

MySQL Proxy is a lightweight binary application standing between one or more MySQL clients and a server. The clients connect to the Proxy with the usual credentials, instead of connecting to the server. The Proxy acts as man-in-the-middle between client and server.

In its basic form, the Proxy is just a redirector. It gets an empty bucket from the client (a query), takes it to the server, fills the bucket with data, and passes it back to the client.

If that were all, the Proxy would just be useless overhead. There is a little more I haven't told you yet. The Proxy ships with an embedded Lua interpreter. Using Lua, you can define what to do with a query or a result set before the Proxy passes them along.

Figure 1. MySQL Proxy can modify queries and results

The power of the Proxy is all in its flexibility, as allowed by the Lua engine. You can intercept the query before it goes to the server, and do everything conceivable with it:

Pass it along unchanged (default)

Fix spelling mistakes (ever written CRATE DATAABSE?)

Filter it out, i.e., remove it altogether

Rewrite the query according to some policy (enforcing strong passwords, forbidding empty ones)

Add forgotten statements (autocommit is enabled and the user sent a BEGIN WORK? You can inject a SET AUTOCOMMIT = 0 before that)

Much more: if you can think of it, it's probably already possible; if it isn't, blog about it: chances are that someone will make it happen

In the same way, you can intercept the result set. Thus you can:

Remove, modify, or add records to the result. Want to mask passwords, or hide information from unauthorized prying eyes?

Make your own result sets, including column names. For example, if you allow the user to enter a new SQL command, you can build the result set to show what was requested.

Ignore result sets, i.e., don't send them back to the client.

Want to do more? It could be possible. Look at the examples and start experimenting!

Key Concepts

MySQL Proxy is built with an object-oriented infrastructure. The main class exposes three member functions to the public. You can override them in a Lua script to modify the Proxy's behavior.

connect_server(): Called at connection time, you can work inside this function to change connection parameters. It can be used to provide load balancing.

read_query(packet): This function is called before sending the query to the server. You can intervene here to change the original query or to inject more to the queue. You can also decide to skip the backend server altogether and send back to the client the result you want (e.g., given a SELECT * FROM big_table you may answer back "big_table has 20 million records. Did you forget the WHERE clause?")

read_query_result(injection_packet): This function is called before sending back the result in answer for an injected query. You can do something here to decide what to do with the result set (e.g., ignore, modify, or send it unchanged).

By combining these three back doors to the server, you can achieve a high degree of maneuverability over the server.

Installation

Installing the Proxy is quite easy. The distribution package contains just one binary (and as of 0.5.1, also some sample Lua scripts). You can unpack that and copy it where you like. For some operating system it's even easier, because there are RPM packages that will take care of everything.

If your operating system is not included in the distribution, or if you want to try the bleeding-edge features as soon as they leave the factory, you may get the source from the public Subversion tree and then build the proxy yourself. It should need just a few basic actions.

./autogen.sh
./configure && make
sudo make install
# will copy the executable to /usr/local/sbin

Simple Query Interception

As our first example, let's do a "I was there" kind of action, just to give you the feeling that you are standing where you want to be.

If you come back to the previous terminal window, you will see that the Proxy has intercepted something for you.

Hello world! Seen the query: select @@version_comment limit 1
Hello world! Seen the query: SHOW TABLES FROM test

The first query was sent on connection by the MySQL client. The second one is the one you sent. As you can see, you are able to get in the middle, and make the Proxy do something for you. For now, this something is very minimal, but we're going to see more interesting stuff in the next paragraphs.

Note on Usage

Until version 0.5.0, to use a Lua script you also need to use the option --proxy-profiling, or else the read_query and read_query_result functions don't kick in. Starting from version 0.5.1, this option is no longer necessary. The above mentioned functions are activated by default. Instead, a new option was introduced to skip their usage. If you are using the proxy only for load balancing, you should now specify --proxy-skip-profiling.

Query Rewriting

The more interesting stuff starts with query rewriting. To demonstrate this feature, let's choose a practical task. We want to catch queries with a common typing error and replace it with the correct keyword. We will look for my most frequent finger twists SLECT and CRATE.

The first two queries are stuff the client needs for its purpose. Then came my first mistake, CRATE, which was graciously changed to CREATE, and in the end it received SLECT, and turned it into SELECT.

This script is quite crude, but it gives you an idea of the possibilities.