Track MySQL Queries with mysqlsniffer on Ubuntu

How-To Geek

By Lowell Heddings

Published May 25, 2007

Why doesn't MySQL let you track queries on the fly? At least you can whip out the geek gloves and track them with a modified network sniffer.

You've got a production database server, and you can't enable query logging... so how do you see the queries being executed against the database? The answer: use a modified network sniffer to parse out the MySQL packets and decode them. You'll have to do a little compiling, but it'll be worth it. Note that this will not usually work for local connections, although you are welcome to try. First, you need to install libpcap-dev, which is the development library that allows an application to sniff network packets.

sudo apt-get install libpcap-dev

Now let's make a directory, download the source code and compile it

mkdir mysqlsniffer cd mysqlsniffer wget hackmysql.com/code/mysqlsniffer.tgz tar xvfz mysqlsniffer.tgz gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (somewhere in the path would be useful) To run mysqlsniffer, you need to specify the network interface that MySQL is listening on. For me, it's eth0.

sudo /path/to/mysqlsniffer eth0

Loads of stuff starts flying by... let's filter it out a little more so we can just get the queries and not all the excess data.

$ sudo /path/to/mysqlsniffer --no-mysql-hdrs eth0 | grep COM_QUERY 192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=''
192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

Ah, now there we are... all sorts of query information, without having to restart MySQL. Here are the full options for the command: Usage: mysqlsniffer [OPTIONS] INTERFACE OPTIONS:
--port N Listen for MySQL on port number N (default 3306)
--verbose Show extra packet information
--tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
--net-hdrs Show major IP and TCP header values
--no-mysql-hdrs Do not show MySQL header (packet ID and length)
--state Show state
--v40 MySQL server is version 4.0
--dump Dump all packets in hex
--help Print this Original source code and more information at:
http://hackmysql.com/mysqlsniffer

If you are running on a development server, it would be easier to just turn on query logging.