For version 5.1.6 and above you can enable slow query logging without restarting the MySQL service. For versions before 5.1.6 a service restart will be needed. Note that slow query logging should not remain enabled for a long period of time, especially on production environments. This could result in poor performance and the log files can grow rather quickly.

Version 5.1.6 and above:


1. Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:

     Log details for queries expected to retrieve all rows instead of using an index:
     set global log_queries_not_using_indexes = 'ON'

     Set the path to the slow query log:
     set global slow_query_log_file ='/var/log/mysql/slow-query.log';

     Set the amount of time a query needs to run before being logged:
     set global long_query_time = '20';
     (default is 10 seconds)

3. Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';

Versions below 5.1.6:


1. Edit the /etc/my.cnf file with your favorite text editor
vi /etc/my.cnf

2. Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:
log-slow-queries=/var/log/mysql/slow-query.log

3. Enable additional options as needed. Here are the same commonly used examples from above:

     Set the amount of time a query needs to run before being logged:
     long_query_time=20
     (default is 10 seconds)

     Log details for queries expected to retrieve all rows instead of using an index:
     log-queries-not-using-indexes

4. Restart the MySQL service:
service mysqld restart

5. Confirm the change is active by entering the MySQL shell and running the following:
show variables like '%slow%';

2 Responses to “Enable slow query logging in MySQL”

Leave a Reply