- Nikhil Bhaskar
- May 18, 2021
The slow query log feature is turned off by default in MySQL, so in order to turn this feature on, we need to set the slow_query_log parameter to ON
Enter the MySQL shell and run the following command:
set global slow_query_log = 'ON';
Enable any other desired options. Here are some common examples:
Log details for queries expected to retrieve all rows instead of using an index:
set global log_queries_not_using_indexes = 'ON'
Set the path to the slow query log:
set global slow_query_log_file ='/var/log/mysql/slow-query.log';
Confirm the changes are active by entering the MySQL shell and running the following command:
show variables like '%slow%';
Here is the output.
Set the amount of time a query needs to run before being logged:
set global long_query_time = 2; (default is 10 seconds)
To check if the time is inserted.
mysql> SHOW GLOBAL VARIABLES LIKE ‘long_query_time’;
After doing the above task, we need to flush logs.
mysql> FLUSH LOGS;
Note − We can permanently disable or enable it with the help of my.cnf or mysqld.cnf (File path & name is depend on linux distribution) file.
Set slow_query_log = 0; to disable.