DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Mysql Proxy To Log Failed Queries

10.24.2011
| 5023 views |
  • submit to reddit
        // install and start mysql proxy that will log failed queries



#!/bin/sh
# kill the current proxy if any
ps aux | grep "mysql-proxy" | awk '{print "kill -9 ", $2}' | sh

# download and install
cd /usr/share/mysql/ && wget http://mysql.oss.eznetsols.org/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz
tar -xvf mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz

# create an alias
alias myproxy='sh /usr/share/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/bin/mysql-proxy --plugins=proxy --proxy-lua-script=/usr/share/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/share/doc/mysql-proxy/failed-query.lua >> /home/failed_query.log &'

# failed query script
cat > /usr/share/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/share/doc/mysql-proxy/failed-query.lua << "my_here_doc"

 local log_file = '/home/mysql.log'
 local fh = io.open(log_file, "a+")

function read_query(packet)
if string.byte(packet) == proxy.COM_QUERY then
query = string.sub(packet, 2)
proxy.queries:append(1, packet, {resultset_is_needed = true} )
return proxy.PROXY_SEND_QUERY
end
end

function read_query_result (inj)
local res = assert(inj.resultset)

-- if res.query_status == proxy.MYSQLD_PACKET_ERR then

if (res.query_status == proxy.MYSQLD_PACKET_ERR) or (res.warning_count > 0) then

local query = string.sub(inj.query, 2)
local err_code     = res.raw:byte(2) + (res.raw:byte(3) * 256)
local err_sqlstate = res.raw:sub(5, 9)
local err_msg      = res.raw:sub(10)

fh:write(string.sub(inj.query, 2), "\n")
fh:write(res.raw:sub(10), "\n")
fh:flush()

print("Query Received -", query)
print("Query Error code -", err_code)
print("Query Error Sqlstate -", err_sqlstate)
print("Query Error message -", err_msg)
print("Query warnings -", res.warning_count)

end
end

my_here_doc