How to Enable Slow Query Log in MySQL Quickly

To enable the slow query log in MySQL, set slow_query_log = ON and specify a log file with slow_query_log_file. You can do this dynamically using SET GLOBAL slow_query_log = 'ON'; and SET GLOBAL slow_query_log_file = '/path/to/slow-query.log'; or by adding these settings to your my.cnf configuration file.

Syntax

The slow query log is controlled by three main settings:

• slow_query_log: Enables or disables the slow query log. Set to ON or OFF.
• slow_query_log_file: Specifies the file path where slow queries are logged.
• long_query_time: Defines the threshold in seconds; queries running longer than this are logged.

You can set these either dynamically or in the MySQL configuration file.

sql

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;

Example

This example shows how to enable the slow query log dynamically and check its status.

sql

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

mysql> SET GLOBAL slow_query_log_file = '/tmp/mysql-slow.log';
Query OK, 0 rows affected (0.00 sec)

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

mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

Output

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

Common Pitfalls

Common mistakes when enabling the slow query log include:

• Not having write permission to the specified log file path, causing logging to fail.
• Forgetting to set slow_query_log to ON after specifying the log file.
• Setting long_query_time too high, so no queries get logged.
• Changing settings dynamically without updating the my.cnf file, so settings reset after restart.

sql

/* Wrong: log file set but slow_query_log is OFF */
SET GLOBAL slow_query_log_file = '/tmp/mysql-slow.log';
SET GLOBAL slow_query_log = 'OFF';

/* Right: enable logging after setting file */
SET GLOBAL slow_query_log_file = '/tmp/mysql-slow.log';
SET GLOBAL slow_query_log = 'ON';

Quick Reference

Setting	Description	Example Value
slow_query_log	Enable or disable slow query logging	ON
slow_query_log_file	File path for slow query log	/var/log/mysql/mysql-slow.log
long_query_time	Minimum query time in seconds to log	2
log_output	Where to log slow queries (FILE or TABLE)	FILE

Key Takeaways

Enable slow query log by setting slow_query_log = ON and specifying slow_query_log_file.

Use long_query_time to control which queries are logged based on execution time.

Set these variables dynamically or in my.cnf for persistence after restart.

Ensure MySQL has write permission to the log file location.

Check slow query log status with SHOW VARIABLES LIKE 'slow_query_log';