How can I view live MySQL queries?

Question

Hey all,

I am just wondering if its possible to trace MySQL queries on my linux server as they happen?

For example I'd love to set up some sort of listener, then request a web page and view all of the queries the engine executed, or just view all of the queries being run on a production server.

Are there tools to do this?

Thank you,

Answer 1

Check out mtop.

Answer 2

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for.

The best method to get a history without having to modify every application using the server is probably through triggers. You could set up triggers so that every query run results in the query being inserted into some sort of history table, and then create a separate page to access this information.

Do be aware that this will probably considerably slow down everything on the server though, with adding an extra INSERT on top of every single query.

---

Edit: another alternative is the General Query Log, but having it written to a flat file would remove a lot of possibilities for flexibility of displaying, especially in real-time. If you just want a simple, easy-to-implement way to see what's going on though, enabling the GQL and then using running tail -f on the logfile would do the trick.

Answer 3

I'm in a particular situation where I do not have permissions to turn logging on, and wouldn't have permissions to see the logs if they were turned on. I could not add a trigger, but I did have permissions to call show processlist. So, I gave it a best effort and came up with this:

Create a bash script called "showsqlprocesslist":

#!/bin/bash

while [ 1 -le 1 ]
do
         mysql --port=**** --protocol=tcp --password=**** --user=**** --host=**** -e "show processlist\G" | grep Info | grep -v processlist | grep -v "Info: NULL";
done

Execute the script:

./showsqlprocesslist > showsqlprocesslist.out &

Tail the output:

tail -f showsqlprocesslist.out

Bingo bango. Even though it's not throttled, it only took up 2-4% CPU on the boxes I ran it on. I hope maybe this helps someone.