Joe Kottke from FeedBurner in his MySQL Conference presentation today FeedBurner: Scalable Web Applications Using MySQL and Java mentioned a novel way of extracting SQL Select Statements from a MySQL Server.

The obvious MySQL ways include SHOW [FULL] PROCESSLIST, the Slow Query Log, and the General Query Log. In 5.1 you also use the INFORMATION_SCHEMA.PROCESSLIST.

Anyway, Joe mentioned he does a strings on tcpdump to extract the queries, and there was no need to restart the server (this point interested me). So, not letting a challenge go, having never used tcpdump.

Well, here’s how.


tcpdump -w mysqldump.txt -i lo


NOTE: You must log output to file with -w to get the full packets and not just headers.

In a seperate session.


$ mysql -uroot -p mysql -h `hostname`
mysql> select * from user;
mysql> exit
# I did a ctrl-C in the other terminal (not sure if necessary)
$ strings tcpdump.txt
select * from user
mysql
user
user


NOTE: localhost wasn’t valid, it had to be the hostname of the server. Of course you could access the server from a different client.

Of course there may be other or better ways, but it worked for me and was rather cool.

Perhaps this is a good reason to use SSL?