A more useful MySQL Query log that's suitable for tail -f'ing

Question

In order to debug a webapp and mysql queries, I've enabled the mysql query log. However it's not a very good format to tail and grep through. Ideally I'd like to be able to only show queries from a certain user, or queries to a certain database. If the mysql query log printed the database or user on each line, then I could grep it. Sometimes I want to see what happened at a certain date, but since mysql query log doesn't print the datetime on each line, then this is harder.

Is there some way to make the mysql query log be nicer to tail/grep/etc through?

Answer 1

You should probably use mk-query-digest - it's insanely powerful.

http://www.maatkit.org/doc/mk-query-digest.html

Answer 2

What you are trying to do, log queries for a single user, might be easier to accomplish through the mysql proxy. Basically it is a service that can sit between the mysql server and client(s). There are many code samples about how to log or manipulate data based on criteria you set.

Anyway I am not aware of any tools to do this. What you might need to do is find a mysql log parser library and write a script that uses the library and extracts the data you want.