How to Enable MySQL Slow Query Log

Identifying slow-running database queries is essential for optimizing application performance. This guide provides a straightforward, step-by-step walkthrough on how to enable and configure the slow query log in MySQL using the configuration file for permanent changes, as well as dynamic runtime commands for immediate activation.

Step 1: Edit the MySQL Configuration File

To make the slow query log persistent across server reboots, you must edit the MySQL configuration file.

1. Open your terminal and locate the MySQL configuration file. On most Linux distributions (such as Ubuntu or CentOS), this file is located at /etc/mysql/my.cnf or /etc/my.cnf. Open it using a text editor with root privileges:

   sudo nano /etc/mysql/my.cnf

2. Scroll down to the [mysqld] section of the file and add or uncomment the following lines:

   # Enable the slow query log
   slow_query_log = 1
   
   # Specify the path to the log file
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   
   # Set the threshold in seconds (e.g., log queries taking longer than 2 seconds)
   long_query_time = 2
   
   # Optional: Log queries that do not use indexes
   log_queries_not_using_indexes = 1

3. Save the file and exit the text editor (in nano, press Ctrl+O, Enter, then Ctrl+X).

Step 2: Set File Permissions (If Necessary)

If you specified a custom path for the log file, ensure that the mysql user has the correct permissions to write to that directory:

sudo mkdir -p /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql

Step 3: Restart the MySQL Service

Apply the configuration changes by restarting the MySQL service:

sudo systemctl restart mysql

(On older systems or Red Hat-based systems, use sudo systemctl restart mysqld or sudo service mysql restart)

Alternative: Enable the Log Dynamically (Without Restarting)

If you cannot restart the database production server, you can enable the slow query log temporarily using the MySQL command line interface. These settings will remain active until the next MySQL service restart.

1. Log into your MySQL server as root:

   mysql -u root -p

2. Run the following SQL queries to enable the log and set the parameters:

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

Step 4: Verify the Changes

To confirm that the slow query log is active and properly configured, run the following command in the MySQL shell:

SHOW VARIABLES LIKE '%slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

The output should show slow_query_log as ON and display the correct path to your log file. You can now monitor /var/log/mysql/mysql-slow.log to analyze queries that exceed your defined time threshold.