Wednesday, June 27, 2007

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.

4 comments:

Scott said...

Could you use Proxy to transform queries like
... WHERE DATE(someField) = '2007-07-27' ...
into the index-friendly
... WHERE someField BETWEEN '2007-07-27' AND '2007-07-27 23:59:59' ...

I assume you can, so the real question is, would there be any reason not to?

gmax said...

Sure you can.
The reason not to do it could be only in the number of queries to change. If you have a data warehouse where a query like the one you describe could run in 10 seconds instead of 2 minutes, you should use such filter.
If you had 1,000 queries per second, probably the overhead necessary to filter and rewrite them would be too much.
It depends on who is sending the queries. If it is a bad application producing inefficient queries, you'd better change the application. If you are dealing with occasional bad queries from careless users, then this filter could be beneficial.
Benchmark first, then act.

אראל סגל said...

The download link is broken, the correct link is probably:
http://forge.mysql.com/tools/tool.php?id=76

אראל סגל said...

The link to download is broken. The proper link is probably:

http://forge.mysql.com/tools/tool.php?id=76

Vote on Planet MySQL