See all the queries mysql does with the query log.

When debugging an older application, just reading the code might not be enough to find all the queries it does. Sometimes queries are done in a worker as well, so finding out exactly what happens can be difficult.

Thankfully we can enable mysql query log, to see the results. And its easy to enable as well. All you need are the following SQL statements:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

SELECT event_time, argument
FROM mysql.general_log 
WHERE command_type='Query' 
  AND argument NOT LIKE 'SELECT%'
  AND argument NOT LIKE 'SHOW%'
  AND argument NOT LIKE 'USE%'
  AND argument NOT LIKE 'SET%'
ORDER BY event_time DESC;

TRUNCATE mysql.general_log;

This part enables the query logs, and lets it log to a table. This is enabled for all connections to the database, so you can do this through the CLI, or favorite sql client.

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

The next part gets you all the querys that have happened. Personally I filter out SELECT, SHOW, USE, and SET statements. You can add (or remove) which ones you like.

SELECT event_time, argument
FROM mysql.general_log 
WHERE command_type='Query' 
  AND argument NOT LIKE 'SELECT%'
  AND argument NOT LIKE 'SHOW%'
  AND argument NOT LIKE 'USE%'
  AND argument NOT LIKE 'SET%'
ORDER BY event_time DESC;

And lastly I like to occasionally truncate this log table, so it doesn’t explode in size:

TRUNCATE mysql.general_log;

Do you use MySQLs query log? Let me know in the comments how it has helped you!

If you want to get notified of the next blog post, join the newsletter.