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.

Installation

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.

• Basically in your my.cnf. Change your servers port to 3307(server only not the client). Then restart mysql.
• Now install mysql-proxy using your regular package manager(it should even work on homebrew).

mysql-proxy proxy-backend-addresses=127.0.0.1:3307 log-level=debug proxy-address=127.0.0.1:3306 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 = proxy.connection.client.src.name 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() == '127.0.0.1' ) 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 inj.id == 1 then errflag = false if res.querystatus == proxy.MYSQLDPACKETERR then errflag = true return proxy.PROXYIGNORERESULT end elseif inj.id == 2 then return proxy.PROXYIGNORERESULT elseif inj.id == 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 inj.id == 4 then return proxy.PROXYIGNORERESULT end end ```

Log all queries with stats/warnings

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