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