Logging MySQL Queries (without stopping the service)

A quicky, but something I don’t think many people know about. If you’ve ever had a MySQL server that seems slammed, but a SHOW PROCESSLIST shows nothing of note, it’s likely because your server is keeping up with processing the queries and not hanging up on them – which is good because that means your server can keep up with the load, but bad because the query isn’t “hanging out” in a non-completed status and showing in your list.

If you want to “dip a ladle” into the queries for a few moments and see what’s being processed, log into the server with root (or equivalent privileges) and issue the following commands. Please note that these entered use around MySQL 5.1 (check here for more information):

SET GLOBAL general_log_file="C:/QueryLog.txt"; (or /var/log/mysql/query.log, etc)
SET GLOBAL general_log=ON;
[Wait a few seconds, minutes, etc to collect your data]
SET GLOBAL general_log=OFF;

MySQL will happily log the queries entering the server for you as long as you want it to, to give you a chance to see what’s got the thing so busy. If you’re not the only admin on the server, you might want to issue a SHOW VARIABLES command to see if someone else is already doing some logging to avoid stepping on their toes.