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.