Enable MySQL Slow Query Log Without Restarting MySQL

We can enable the MySQL slow query log with the help of SET statement.

The following is the syntax.

SET GLOBAL slow_query_log = 'Value';

In the above syntax, value can be filled with ON/OFF. To enable slow query log, let us see the query.

mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

To check if the slow query is ON, implement the following query −

mysql> SHOW GLOBAL VARIABLES LIKE 'slow\_%';

Here is the output.

+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_launch_time    | 2                        |
| slow_query_log      | ON                       |                     
| slow_query_log_file | DESKTOP-QN2RB3H-slow.log |
+---------------------+--------------------------+
3 rows in set (0.00 sec)

We have set the slow query time in seconds because if any query is beyond the given seconds, it will enter into the slow query log file.

We can also set the seconds. Here is the query to set the seconds.

mysql> SET GLOBAL long_query_time = 20;
Query OK, 0 rows affected (0.00 sec)

To check if the time is inserted.

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

Here is the output of the above query.

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 20.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

After doing the above task, we need to flush logs.

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.25 sec)

Note − We can permanently disable it with the help of my.cnf file. Set slow_query_log = 0; to disable.