You are currently viewing Mysql slow query log

Mysql slow query log

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


You May Also Enjoy Reading This …

Leave a Reply