Logging MySQL queries with MySQLProxy

In need of viewing what went on the wire

I have been struggling with figuring out why something was wrong in my SQL queries.

I needed to see what was happening in real time and also keep a little log of things.

My MySQL server has the logging enabled, slow queries and all. It even logs to TABLE.

But this wasn’t doing the trick.

MySQLProxy

Enters MySQLProxy.

MySQLProxy mediates between your client app and the MySQL server (or servers, consolidating a bunch of servers into one, along with the ability to set a bunch of them as read sources, and another as a write source — which for scaling appears pretty smart).

More on the scalability ability for another time.

What I wanted is to log the traffic.

Getting MySQLProxy

Easily enough, get it.

I installed 0.8.3-Win32 as my dev box is a Windows 7 system (64bit).

Configuring MySQLProxy

First of all, MySQLProxy will listen by default to port 4040 and forward to 3306, the ususal MySQL port.

What needs to be done is to provide the host details as a place for the thing to log its technical output (to figure out what goes wrong).

I wrote a script for handling the startup: runmysqlproxy.cmd

set MYSQLPROXY_HOME=%programfiles(x86)%\MySQL\mysql-proxy-0.8.3-win32-x86
set MYSQLPROXY=%MYSQLPROXY_HOME%\bin\mysql-proxy.exe

"%MYSQLPROXY%" -b 127.0.0.1:3306 --log-file=E:\var\logproxy.log 
    --log-level=debug --proxy-lua-script=D:\Dev\tools\sqlproxy\logquery.lua

Logging queries

It happens that MySQLProxy can do pretty much everything you can dream with queries that do pass through. Okay, I just wanted to log queries to a file.

How to? Lua scripting to the rescue! Lua.org

That’s why logquery.lua is for.

Let’s check it out:

function read_query(packet)
   if string.byte(packet) == proxy.COM_QUERY then
     query = string.sub(packet, 2)
     log_query(query)
   end
 end

 function log_query(query)
 output = os.date() .. ";QUERY;" .. query
     print(output)
 local file = io.open("E:\\var\\mysqlproxy\\queries.log", "a")
 file:write(output .. "\n")
 file:flush()
 file:close()
end

I really never coded anything with Lua but it for sure will happen due to the power of that proxy.

Connecting to the proxy

Just a matter of using the mysql command line client (with my very secure credentials…):

mysql -hlocalhost -P4040 -uroot -proot

And then issue some queries.

These are captured:

02/21/13 23:45:11;QUERY;SELECT DATABASE()
02/21/13 23:45:23;QUERY;select * from tiki_todo

And the file filled in.

Hope it will help you get started with MySQLProxy.

Going further

There is quite a bunch of possibilities as you can see from the scripting reference.

Happy MySQLProxying!