Log Queries with MySQL Proxy

Sat 19 July 2014

image0

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 be just what you're looking for. Taken from the site:

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).
  • Then run this command:
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.

Category: MySQL Tagged: lua sugarcrm

comments


Minimalist MySQL Reporting

Thu 10 July 2014

image0

Sometimes business folk can have crazy demands on the types of reports your system should produce and sometimes these can be really time consuming and painful to get right. Often I find the most flexible solution is to give these power-users access to tidier version of the raw data within …

Category: MySQL Tagged: reporting

comments

Read More
Page 1 of 1