MySQL General Query Log Filtering using grep and regular expressions

MYSQL has a very useful feature called Query Log which allows you among other things to debug your application code by viewing DB DML's submitted live from the application straight into DB back-end (Low level, the best!).

To configure this little joy you will need to play a bit with your mysql server settings.

Edit your my.cnf file (Under debian that is /etc/mysql/my.cnf) in the section of

[mysqld]

add

log=/var/log/mysql/query.log

This is explained in greater details at this post.


Now the thing is query log as the name suggests logs everything that has SQL smell on it and I do mean EVERYTHING (besides CRUD operations you will see: commit/rollbacks, show tables, use DB-XYZ and co.) - Which is mostly junk when the only thing you really need is to trace your hibernate based Console save to get the back-end integration going... To summarize in a catch phrase: You wan't to filter out the log spam when debugging your application (vs. your replication ;).

To be more efficient and be able to look at the logs in real time I've created a fast and dirty regex. The following chain of piped grep's will leave you only with INSERT, UPDATE, DELETE statements which is exactly what I was after (Your mileage might vary).

One would use something like this:
tailf /var/log/mysql/query.log | grep -E '[[:space:]]+[[:digit:]]+[[:space:]]Query' | grep -ivE 'Query([[:space:]])+(/\*.*\*/)?(select|set|show|commit|rollback|use)'

The command works by constantly (tailf) pushing events from mysql query.log throw the 2 greps which do some smarty ass filtering to leave you with the interesting part. If you need other command (SELECT for example) you can remove them from the second grep section. If you want to see just a single table add another 3'rd grep to the chain and filter just by table name.

Comments are welcome.

Enjoy,
Maxim.