Getting Started with MySQL Proxy

[sorry4uboth]

The launch of [مهمان/کاربر گرامی برای دیدن لینک ها ابتدا باید عضو سایت شوید و لاگین کنید برای ثبت نام اینجا کلیک کنید] 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.

[img width=476 height=480]http://www.oreillynet.com/databases/2007/07/12/graphics/Proxy-overview.png[/img]
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?&quot
* 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.

1. Create a Lua file, named first_example.lua, containing the lines listed below.
2. Assuming that your database server is on the same box, launch the proxy server.
3. From a separate console, connect to the proxy server, which is like connecting to the normal server, with the difference that you will use port 4040 instead of 3306.

 -- first_example.lua 
 function read_query(packet)
  if string.byte(packet) == proxy.COM_QUERY then
   print("Hello world! Seen the query: " .. string.sub(packet, 2))
  end
 end
# starting the proxy
$ mysql-proxy --proxy-lua-script=first_example.lua -D             
# from another console, accessing the proxy
$ mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040 -e 'SHOW TABLES FROM test'

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.

Here is second_example.lua:

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
   local query = string.sub(packet, 2)
   print ("received " .. query)
   local replacing = false
   -- matches "CRATE" as first word of the query
   if string.match(string.upper(query), '^%s*CRATE') then
     query = string.gsub(query,'^%s*%w+', 'CREATE')
     replacing = true
   -- matches "SLECT" as first word of the query
   elseif string.match(string.upper(query), '^%s*SLECT') then
     query = string.gsub(query,'^%s*%w+', 'SELECT')
     replacing = true
   end
   if (replacing) then
     print("replaced with " .. query )
     proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
     return proxy.PROXY_SEND_QUERY
   end
  end
 end

As before, start the server with the option --proxy-lua-script=second_example.lua and connect to it from a MySQL client

 $ mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040 
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 48
 Server version: 5.0.37-log MySQL Community Server (GPL)

 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 mysql> use test
 Database changed
 mysql> CRATE TABLE t1 (id int);     # Notice: TYPO!
 Query OK, 0 rows affected (0.04 sec)

 mysql> INSERT INTO t1 VALUES (1), (2);
 Query OK, 2 rows affected (0.01 sec)
 Records: 2 Duplicates: 0 Warnings: 0

 mysql> SLECT * FROM t1;         # Notice: TYPO!
 +------+
 | id  |
 +------+
 |  1 | 
 |  2 | 
 +------+
 2 rows in set (0.00 sec)

Isn't it sweet? I made my usual mistakes, but the Proxy was kind enough to fix them for me. Let's look at what was reported.

 received select @@version_comment limit 1
 received SELECT DATABASE()
 received CRATE TABLE t1 (id int)
 replaced with CREATE TABLE t1 (id int)
 received INSERT INTO t1 VALUES (1), (2)
 received SLECT * FROM t1
 replaced with SELECT * FROM t1

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.