Your first macros with MySQL Proxy

You may have noticed the excitement about MySQL Proxy. But perhaps you failed to realize what you can do with it.
Here's a simple tutorial that will guide you through your first Proxy experience. You will be able to use macros with the standard mysql client.

Assuming that you are working on the box where your MySQL server is, do the following:

1. Download and unpack MySQL Proxy
2. download the query rewriting tutorial and save it as rewrite.lua
3. start the proxy
   /path/to/sbin/mysql-proxy --proxy-lua-script=rewrite.lua --proxy-profiling
4. from another terminal, fire the mysql client
   mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040

Now you can use your client as usual, with a few additions. The rewrite.lua script implements three macros (cd, ls, who, corresponding respectively to USE, SHOW FULL TABLES, and SHOW PROCESSLIST).

mysql> cd test;
Query OK, 0 rows affected (0.00 sec)

mysql> select schema();
+----------+
| schema() |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> cd mysql;
Query OK, 0 rows affected (0.00 sec)

mysql> select schema();
+----------+
| schema() |
+----------+
| mysql    |
+----------+
1 row in set (0.00 sec)

Look at that! We send a macro (cd test) to the server. The proxy catches it, and turns it into "use test". The server executes it, unaware of the manipulation in between. Let's try the others:

mysql> ls mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql> who;
+----+-------------+-----------------+-------+---------+------+-------+------------------+
| Id | User        | Host            | db    | Command | Time | State | Info             |
+----+-------------+-----------------+-------+---------+------+-------+------------------+
|  4 | datacharmer | localhost       | bugs  | Sleep   | 1522 |       | NULL             |
| 11 | gmax        | localhost:50630 | mysql | Query   |    0 | NULL  | SHOW PROCESSLIST |
+----+-------------+-----------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

And now we add our own macro to the list. Stop the proxy. Edit rewrite.lua, and just before the line saying

elseif command == "who" then

add these three lines, implementing a info macro, which corresponds to "SELECT USER(), NOW()":

                elseif command == "info" then
                       proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SELECT USER(), NOW()" )
                       return proxy.PROXY_SEND_QUERY

Restart the proxy, and try your new macro:

mysql> info;
+----------------+---------------------+
| USER()         | NOW()               |
+----------------+---------------------+
| gmax@localhost | 2007-06-27 10:24:00 |
+----------------+---------------------+
1 row in set (0.00 sec)

Isn't it cool? And that's just the beginning.