Enable MySQL’s slow query log without a restart

In this post, I will tell you how to enable MySQL’s slow query log to see which query takes longer to execute.

This is happen because of coding mistakes and using sloq query log you can easily find out those queries and make them easy to debug your application faster.

There are two way for getting slow query log.

Either you can directly edit your my.cnf file and restart your mysql service or you can go through MySQL CLI via mysql.

Create slow query log file.

mkdir /var/log/mysql/
touch /var/log/mysql/mysql-slow.log
chown -R mysql:mysql /var/log/mysql/

Log in to the MySQL CLI via mysql.

$ mysql

Now you have mysql-sloq.log file which is need to set for slow query log.

mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; 

Now set the limit so that query is logged to the slow query log after limit exceeds.

mysql> SET GLOBAL long_query_time = 10; 

Above example will logs each query that exceeds 10 seconds in duration.

Now you will have to enable the sloq query log.

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

If you wish, you can log only those query that do not use indexes.

mysql> SET GLOBAL log_queries_not_using_indexes = 'YES'; 

To enable slow query log you can also make changes in my.cnf file.,

[mysqld]
...
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 10
log_queries_not_using_indexes = YES

To check if these settings are working fine, run following query from mysql.

mysql> SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

You will get following output :

+-------------------------------+-------+
| 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 | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
	

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

If you need to disable the logging, use following query.

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

You can directly disable logging in my.cnf

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