Temporarily logging MySQL queries

Goal

To enable MySQL logging temporarily for debugging purposes

Description

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.

How to

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"

Explanations

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s