Enable MySQL’s slow query log without a restart

You’re debugging a MySQL server and want to enable the Slow Query, you can do so via the MySQL CLI. There’s no need to make changes to the my.cnf file and restart your MySQL service – even though that would also work.

Log in to the MySQL CLI via mysql.

$ mysql

Set the log-file location for the Slow Query log. Make sure the mysql user has write permissions to that file.

mysql> SET GLOBAL slow_query_log_file = '/path/to/slow_query.log';

Determine what makes a query “slow”, by setting the limit (in seconds) after which a query is logged to the slow query log. The example below logs every query that exceeds 10 seconds in duration.

mysql> SET GLOBAL long_query_time = 10;

Now enable the Slow Query log.

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> FLUSH LOGS;

As a bonus, you can also make all queries that do not use indexes be logged to the same Slow Query log.

mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';

If you want to make these changes persistent, modify the my.cnf and add these lines to the [mysqld] part of the config.

[mysqld]
...
slow_query_log = /path/to/slow_query.log
long_query_time = 10
log_queries_not_using_indexes = ON

This will also work for MariaDB or Percona’s version of MySQL.

To verify if your settings are working, request the running parameters from MySQL.

mysql> SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /path/to/slow_query.log       |
+---------------------+-------------------------------+

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

And don’t forget to either disable it again after your debug-session, or enable logrotate to prevent the files from growing too large. To disable the logging, but leave the rest of the requested configs intact, use the following at the CLI.

mysql> SET GLOBAL slow_query_log = 'OFF';
mysql> FLUSH LOGS;

To disable it permanently in the my.cnf file, set the slow_query_log to 0.

[mysqld]
...
slow_query_log = 0
long_query_time = 10
log_queries_not_using_indexes = ON