How to enable the slow query log in MySQL

Step by step process for enabling mysql slow query log

Step 1: First we need to open mysql config file in my case mysql config file location is /etc/my.cnf

sudo vi /etc/my.cnf

Note: mysql config file location may be differnt in yor case

Step 2: Add below line in my.cnf file under the “[mysqld]” section

slow_query_log=1 long_query_time=5 slow_query_log_file=/var/log/mysql/slow-query.log log_queries_not_using_indexes=0

Then Save and Exit mysql config file (Esc :wq!) Now we used long_query_time=5 it is if any query taking more then 5 seconds to execute then the query will be saved in slow query log file.In your case you may used different path for log file.

Step 3: creating mysql folder and slow-query.log in /var/log/ and setting ownership

sudo mkdir /var/log/mysql sudo chmod -R 777 /var/log/mysql sudo touch /var/log/mysql/slow-query.log sudo chown mysql:mysql /var/log/mysql/slow-query.log

Step 4: Then we need to restart the MySQL service:

sudo service mysqld restart

Step 5: Confirm the changes for that enter in mysql shell mysql -uroot -ppassword databasename

mysql> show variables like '%slow%'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/slow-query.log | +---------------------+-------------------------------+

Here we can check log_slow_queries is ON

Step 6: Start monitoring file for this you may open the file in vi editor or use taill command to check

sudo tail -500 /var/log/mysql/slow-query.log

this line will display last 500 line of /var/log/mysql/slow-query.log Here we completed slow query log in MySQL