MySQL: how to enable Slow Query Log?

Solution 1:

Correct.

It's best to put this in the general/global (top) part of your my.cnf file.

There are two things you need to do : (1) enable it, and (2) define the logfile destination.

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log

Hope this helps.

Solution 2:

Here is something interesting.

MySQL 5.1/5.5 allows you to convert the slow query log to MyISAM.

Here is how I do it for my clients:

Step 1) Add these line to /etc/my.cnf

log_output=TABLE
slow_query_log
slow_query_log_file=slow-queries.log

Step 2) Restart mysql (service mysql restart)

At this point, the table mysql.slow_log exists but as storage engine CSV.

Step 3) Convert mysql.slow_log to MyISAM and Index the start_time field

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Now you can run SELECT queries against it.

Give it a Try.