MySql query log to table or file

For debugging it’s often useful to see all queries and since you usually don’t want to save all this data, it’s nice to be able to turn it on and off efficiently.

You can log to file but also to a table in the mysql database.

While you can turn this on and off by updating the my.cnf file, you can also do it via mysql commands:

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

One nice thing about logging to a table is that you can search for specific things, see examples below.

USE mysql;
SELECT * FROM general_log;
SELECT * FROM general_log WHERE argument LIKE "DELETE%";

You probably want to clear the log periodically and can do so with:

TRUNCATE general_log;

Then simply turn OFF when finished.

SET GLOBAL general_log = 'OFF';

If you would rather log to file (the default), use:

SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Leave a Reply