I'm just wondering if it is easy to trace MySQL queries on my small linux server because they happen?

For instance I'd love to setup some kind of listener, then request an internet page and examine all the queries the engine performed, or simply view all the queries being operate on a production server.

Exist tools to get this done?


Take a look at mtop.

You are able to run the mysql command "show processlist" to determine what queries are now being processed at any time, but that most likely will not achieve what you are wishing for.

The best way to obtain a history without needing to modify every application while using server is most likely through triggers. You can setup triggers to ensure that every query run leads to the query being placed into some kind of history table, after which produce a separate page to gain access to these details.

Do remember that this can most likely substantially decelerate everything around the server though, with adding an additional Place on the top of each and every single query.

Edit: another alternative may be the General Query Log, but getting it written to some flat file would remove lots of options for versatility of exhibiting, particularly in real-time. Should you simply want an easy, easy-to-implement method to see what's happening though, enabling the GQL after which using running tail -f around the logfile would have the desired effect.

You are able to log every query to some lengthy file really easily:

mysql> SHOW VARIABLES LIKE "general_log%"


 Variable_title     Value                      


 general_log       OFF                        

 general_log_file  /var/run/mysqld/mysqld.log 


mysql> SET GLOBAL general_log = 'ON'

Do your queries (on any db). Grep or else examine /var/run/mysqld/mysqld.log

Then be sure to

mysql> SET GLOBAL general_log = 'OFF'

or even the performance will plummet as well as your disk will fill!

I am inside a particular situation where I don't have permissions to show logging on, and wouldn't have permissions to determine the logs when they were switched on. I possibly could not give a trigger, however i did have permissions to call show processlist. So, I gave it a best effort and emerged with this particular:

Produce a party script known as "showsqlprocesslist":


while [ 1 -le 1 ]


         mysql --port=**** --protocol=tcp --password=**** --user=**** --host=**** -e "show processlistG"  grep Info  grep -v processlist  grep -v "Info: NULL"


Execute the script:

./showsqlprocesslist > showsqlprocesslist.out &lifier

Tail the output:

tail -f showsqlprocesslist.out

Bingo bango. Despite the fact that it isn't throttled, it only required up 2-4% CPU around the boxes I went it on. I really hope maybe this can help someone.