Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful. Today we ran across a good use case, and so I wanted to pass this along. If you’ve not used Proxy yet, this is a great opportunity to get your feet wet with it and some Lua.

When queries are routed though MySQL Proxy from various servers, the MySQL Server only sees that the query came from the machine Proxy is running on.

So when you are viewing output of SHOW PROCESSLIST, you have no way of telling what server a particular query originated from.

However, this could be very useful information to have, especially to determine which server a particular long-running query is coming from at that particular moment in time.

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:61252 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | 10.1.10.1:61267 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | 10.1.10.2:62047 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | 10.1.10.3:62050 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | 10.1.10.4:62052 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

In the above, you can easily see the IP address of the server the query originated from.

However, now see the output when using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:62255 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | localhost:62271 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | localhost:63038 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | localhost:63042 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | localhost:63043 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

Here, every query shows as originating from ‘localhost’. This is accurate, but in this example, we’d like to know the originating server.

So, how do we accomplish this?

A good work-around to this problem is to append the originating host to the query in the form of a query comment. In this way, the comment will get passed through, and will appear in query itself, under the “Info” column of the output. This will also incur very little overhead, as we’re only appending a known value to the query, and then the MySQL parser handles it as it would any other query (which is to execute it while ignoring the comment).

Now, how do we accomplish this with Lua and Proxy?

I started using one of the included sample scripts – tutorial-rewrite.lua.

I chose this file because it already has the stub code included for both the following function:

function read_query( packet )

It is in this function where the query is read, and before it is sent to the MySQL Server. So this is where we can modify the query to append this information.

Here is the resulting function read_query():

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)
    print("we got a normal query: " .. query)
    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
    return proxy.PROXY_SEND_QUERY
  end
end

The first 3 lines are already in that file. However, I did modify the 3rd line to include the comment with the originating server IP.

Here is the new 3rd line:

local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)

This defines “query” as a local variable, and sets it equal to the comment concatenated with the actual query.

You can see it starts by appending the comment, which is of the form: /* comment */, where comment is the IP address of the server the query came from.

Note that “..” is used to concatenate two strings in Lua.

The IP address can be found in an existing variable, which is:

proxy.connection.client["address"]

And the query itself can be found in an existing variable, which is:

string.sub(packet, 2)

So as long as we’re within function read_query(), then we have access to these variables, and they’ll be properly assigned.

The next statement is the print() statement, which is originally from the sample script, and I left it in for debugging purposes:

print("we got a normal query: " .. query)

And then you just need the following two statements:

proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
return proxy.PROXY_SEND_QUERY

The former appends the new query to the query stack to be executed. This must be done so the changes you make to the variable “query” are actually reflected in the packet that gets sent to the server. And in the latter, PROXY_SEND_QUERY is called, which sends the packet (with the modified query) to the MySQL Server to be executed.

So, now we just need to save this function to a file, start up proxy with this script, and then issue a SHOW PROCESSLIST.

I’ll save the above function to a file named ‘retain-hosts.lua’.

In Window #1:

cd "../../Program Files/MySQL/mysql-proxy-0.6.0"
mysql-proxy --proxy-lua-script="C:\Program Files\MySQL\mysql-proxy-0.6.0\retain-hosts.lua"

In Window #2:

mysql -u root -pmysql -h 127.0.0.1 -P 4040 --comments

Note you must pass the –comments option to the MySQL Client, or the comments will get stripped out. This is at least necessary for testing, or if actually passing queries through the command line interface.

Now in Window #2, issue your SHOW PROCESSLIST (note I’ve thrown a few queries at it from some other connections):

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| Id | User | Host            | db   | Command | Time | State | Info                                   |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| 11 | root | localhost:62252 | test | Query   |    0 | NULL  | /* 127.0.0.1:11251 */ show processlist |
| 12 | root | localhost:62267 | NULL | Query   |   17 | init  | /* 10.1.10.1:15091 */ select sleep(30) |
| 13 | root | localhost:63047 | NULL | Query   |   18 | init  | /* 10.1.10.2:18166 */ select sleep(30) |
| 14 | root | localhost:63050 | NULL | Query   |   19 | init  | /* 10.1.10.3:18934 */ select sleep(30) |
| 15 | root | localhost:63052 | NULL | Query   |   20 | init  | /* 10.1.10.4:19446 */ select sleep(30) |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
5 rows in set (0.00 sec)

Voila! Now you can see in the IP address of the server the query originates from in the actual query itself, as a comment.

For more information on MySQL Proxy, please see the following links:

http://dev.mysql.com/downloads/mysql-proxy/index.html
http://forge.mysql.com/wiki/MySQL_Proxy
http://forge.mysql.com/wiki/MySQL_Proxy_Cookbook
http://jan.kneschke.de/projects/mysql/mysql-proxy/
http://datacharmer.blogspot.com/