Enable the Slow Query Log

Enable the Slow Query Log

Check if Slow Query Logging is Enabled: After login to mysql

SHOW VARIABLES LIKE ‘slow_query_log’;

If it returns OFF, you need to enable it.

Enable Slow Query Logging Temporarily

Run the following SQL commands:

SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL long_query_time = 2; — Log queries taking longer than 2 seconds

These settings will be lost after a MySQL restart.

Enable Slow Query Logging Permanently

Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or modify the following lines under the [mysqld] section:

slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1 # Optional: Log queries not using indexes

Save and exit the file.

Restart MySQL

Apply the changes by restarting MySQL:

sudo systemctl restart mysql

View Logged Slow Queries

To view the slow queries, use:

sudo cat /var/log/mysql-slow.log

Or use mysqldumpslow for a summarized output:

sudo mysqldumpslow -a /var/log/mysql-slow.log

Adjust Log File Permissions (If Needed)

If you get a permission error when accessing the log file:

sudo chmod 644 /var/log/mysql-slow.log