Shane Dowling : Log Queries with MySQL Proxy

What is it?

Have you ever found yourself wanting live statistics of you mysql database, or a log of all the erroring queries. Well MySQL Proxy might

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication.

Basically MySQL Proxy collects database queries and acts on them using scripts built in lua that can do some useful things. I find this handy when debugging SugarCRM as sometimes failed database queries get suppressed(especially when making API requests) and I needed a transparent way to check nothing has failed on the DB side.


This example I'm going to show you allows you to sit the proxy on 3306(instead of mysql), so you don't need to alter any of the code point to your existing MySQL installation. If this isn't what you want change your steps accordingly.

mysql-proxy proxy-backend-addresses= log-level=debug proxy-address= proxy-lua-script=/usr/local/Cellar/mysql-proxy/0.8.3/lib/mysql-proxy/lua/proxy/mysql-proxy-log-error-queries.lua plugins=proxy

If that default example lua script doesn't exist, simply copy one of the files below and point proxy-lua-script to that.

Log any bad queries the database has to handle.

``` local errflag = false function readquery( packet ) if packet:byte() == proxy.COMQUERY then local user = proxy.connection.client.username local host = print(user .. '@' .. host) if [[true or]] user:lower() == 'root' then [[ host will always be localhost because of proxy and not ( host:lower() == 'localhost' or host:lower() == '' ) then ]] proxy.queries:append(1, packet, {resultsetisneeded = true}) proxy.queries:append(2, string.char(proxy.COMQUERY) .. "SET @lastquery = '" .. string.sub(packet, 2) .. "'", {resultsetisneeded = true} ) proxy.queries:append(3, string.char(proxy.COMQUERY) .. "SHOW WARNINGS", {resultsetisneeded = true} ) end return proxy.PROXYSENDQUERY end end

function insertquery(errt, errn, errm) local query = "INSERT INTO `somedb`.`mysqlerror` " .. "(`date`, `errnum`,`errtype`, `errmessage`, `problemquery`, `connid`)" .. " VALUES ( NOW(), " .. errn .. "," .. """ .. errt ..""" .. "," .. """ .. errm .. """ .. "," .. "@lastquery" .. "," .. proxy.connection.server.threadid .. ")" print(query) proxy.queries:append(4, string.char(proxy.COMQUERY) .. query, {resultsetisneeded = true}) return proxy.PROXYSENDQUERY end

function readqueryresult(inj) local res = assert(inj.resultset) if == 1 then errflag = false if res.querystatus == proxy.MYSQLDPACKETERR then errflag = true return proxy.PROXYIGNORERESULT end elseif == 2 then return proxy.PROXYIGNORERESULT elseif == 3 then if errflag == true then for row in res.rows do proxy.response.type = proxy.MYSQLDPACKETERR proxy.response.errmsg = row[3] insertquery(row[1], row[2], row[3]) end return proxy.PROXYSENDRESULT end return proxy.PROXYIGNORERESULT elseif == 4 then return proxy.PROXYIGNORERESULT end end ```

Log all queries with stats/warnings

Found here. Useful for spotting those slow/inefficient queries.