Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can you give me an example on how to enable MySQL's slow query log?

According to the doc:

As of MySQL 5.1.29, use --slow_query_log[={0|1}] to enable or disable the slow query log, and optionally --slow_query_log_file=file_name to specify a log file name. The --log-slow-queries option is deprecated.

So how do I use that option? Can I put it in my.cnf? An example would be greatly appreciated.

share|improve this question
up vote 5 down vote accepted


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.

share|improve this answer
Can you also give me an example of how to do it in command line? Thanks – Continuation Feb 28 '11 at 12:16
I'm unsure; It's best to define it outside of editing files / command line. If you have FreeBSD, you can edit rc.conf .. many linux distros (and Solaris, etc) have rc.d files that you can control the server operation with. What OS are you running? – jamie rishaw Feb 28 '11 at 16:44
To answer your Q in a basic form, you'd add the flags to the mysqld_safe script .. Note, also, the underscores (I believe) become dashes.. so, you'd add to mysqld / mysqld "--slow-query-log=1 --slow-query-log-file=/var/log/mysql/whereever.log". – jamie rishaw Feb 28 '11 at 16:51
You may also want to set the long_query_time value to something suitable to your databases. – Coops Mar 4 '11 at 9:22

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


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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.