How to enable slow query log in MySQL

Slow queries can affect the perfomance of database and also the server as well. MySQL providing an option to log the queries that exceeds a time limit for its execution. This will help to identify the problematic queries and sort it accordingly.

This article explains the steps to enable slow query log in MySQL.

Step1: Login to MySQL as root.

Step2: Run the following query to enable slow query log.

SET GLOBAL slow_query_log = 'ON';

Step3: Run the following query to update the log file name.

SET GLOBAL slow_query_log_file = 'path to log file';

Here we need to specify the full path to the log file in query.

Step4: This step is optional. By default the above settings will log the queries that taking more than 10 seconds for execution. If we need to change this, run the following query.

SET GLOBAL long_query_time = time;

Here we need to specify the time in seconds.

Please note that the above settings are temporary and it will get reset on restarting the MySQL service. So if we need this for temporary, follow the below steps.

Step5: Update the following lines in MySQL config '/etc/my.cnf'

slow_query_log = 'ON';
slow_query_log_file = 'path to log file';

Here we need to specify the full path to the log file in query.

Step6: Restart MySQL service.

service mysqld restart

That's al…