Enable MySQL slow Query Logs in Linux

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.

Leave a Reply

Your email address will not be published.