Java Bytecode Assembly, Interesting concepts in the art of Computer Science/Software Development, maybe from time to time other pesky corp. related development issues.

Tuesday, July 13, 2010

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
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.


1 comment:

  1. Oh my goodness! That is just SOO sweet!! Something like that would retail for a few hundred dollars and you did it Fabulously for oh so less!!! Thanks for the great pics of everything you did. :>


About Me

My photo
Tel Aviv, Israel
I work in one of Israel's Hi-Tech company's. I do 5 nines system development with some system administration. This blog will focus on the technical side of things, with some philosophical strings attached.