Tuesday, February 19, 2013

Using MySQL General Query Log

MySQL comes with the feature to log all SQL queries which is sent to the server.  This feature can be enabled dynamically without having to restart the server.  However, only MySQL 5.1 and above supports this.

To enable this feature, login to MySQL as root.  At the MySQL prompt, type this:

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

Double check if it's really been turned on:

mysql> select @@global.general_log;
| @@global.general_log |
|                    1 |
1 row in set (0.00 sec)

The value should read '1'.  You can now see all SQL queries logged in the general_log table in the mysql database.  

To turn off the logging, set the general_log to OFF using the same syntax as above.

No comments: