To enable MySQL logging temporarily for debugging purposes
Have you ever faced a situation where you want to check which tables/views and queries are issued when you are using a given application with a MySQL database and you are using a version higher than 5.1.12? If that is the case, then this is the right recipe for you.
This recipe will enable the MySQL logging and it will direct its output to mysql.general_log table. Just write the following commands:
SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'ON';
Which will set the log output to a table (the general_log table in mysql schema) and will activate the general log (execute the corresponding statement with ‘OFF’ to disable the logging again, namely when you start digging into the queries results – notice that querying on the general_log table will also write those queries into that table).
In case you prefer output to a file instead (which is the default, by the way), simply replace the first statement shown before with the following:
SET GLOBAL log_output = "FILE"; SET GLOBAL general_log_file = "/path/to/your/logfile.log"
This recipe will make MySQL log all queries into the mysql.general_log or to the file specified, according to your needs. I personally prefer the table option because it will allow me to query for the results more easily. However, when doing so, I usually disable the MySQL general log so that none of the queries I will write to fetch results from general_log will also be stored in it.
For more informations, please check the MySQL 5.1 Reference Manual – Server System Variables – general_log.