All things sysadmin

mysqlsniff is a tool that I find very useful and utilise a lot, but it doesn’t seem to be so widely known as it deserves to be. I often see people ask “how can I see what queries are being ran on my server?” to which the answer almost always is “enable general logging or run show processlist”. That’s all fine and well in some cases general logging requires restarting the server (unless you’re on 5.1) and show processlist is just point-in-time. They can both help, but aren’t ideal in all situations! Sure, show processlist is good to see that long running query, so it’s obviously not altogether useless, but as a tool to get an overview of query frequency etc. it’s rather limited.

With querysniffer you get a real time overview of all queries which are running. It’s a simple perlscript and is easy enough to get going with. On RedHat/CentOS, you’d go about it like this:

1
2
3
4
5

wget http://iank.org/querysniffer/mysqlsniff-0.10.pl.txt -O mysqlsniff.pl
yum install libpcap-devel
cpan -i Net::PcapUtils
cpan -i NetPacket::Ethernet
perl mysqlsniff.pl eth0

And now you should see any query sent to the server over eth0 on your terminal.