How to dynamically enable general log in MySQL

Ever wonder what the exact queries are that cause MySQL to spike your CPU constantly? Here is an quick and easy way to enable the general log within MySQL without restarting MySQL.

First, confirm your initial variables before changing anything:

mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF                      |
| general_log_file | /var/lib/mysql/web01.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

So in this case, the logs will be recorded to /var/lib/mysql/web01.log. If you have nothing set for ‘general_log_file’, you can set it as shown below, but just make sure its in a directory that MySQL can write to:

mysql> set global general_log_file='/var/lib/mysql/web01.log';

With the log file ready, enable the general log by:

SET global general_log = 1;

Verify it is enabled by running:

mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | On                       |
| general_log_file | /var/lib/mysql/web01.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

You can now view the queries being ran by looking at your general_log_file. Please note, your log will be in a different location then the one I have below:

tail -f /var/lib/mysql/web01.log

On busy MySQL servers, leaving the general_log setting enabled will quickly cause your hard drive to fill up. So after a few seconds or minutes, disable it by running:

SET global general_log = 0;

Then verify the setting is now off by:

mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF                      |
| general_log_file | /var/lib/mysql/web01.log |
+------------------+--------------------------+
2 rows in set (0.00 sec)

You can now go through your log file to see exactly what queries have been running.

Fixing invalid system activity with sar

Sometimes sar can return errors complaining about an invalid system activity file such as:

sar
Invalid system activity file: /var/log/sysstat/sa03

It can be resolved by:

rm /var/log/sysstat/sa03
/etc/init.d/sysstat start
* Starting the system activity data collector sadc [ OK ]

Now if you rerun sar, you should see the stats output starting to populate:

sar
Linux 3.2.0-83-virtual (web01) 04/03/2014 _x86_64_ (8 CPU)