How to enable the MySQL slow query log

You can use the slow query log to help determine which database queries take a long time to run. However, the slow queries can affect database performance and overall server performance.

Follow below steps to enable the mysql slow query log on your server :
# Login to SSH shell with root user
# vi /etc/my.cnf

# Add below lines under the [mysqld] section:

slow_query_log = 1
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 2

Note : where long_query_time – time taken by an SQL query to be executed in seconds. If a query takes longer than the value specified, this query will be recorded in the slow query log file.

Note: In MySQL 5.7, the variable log-slow-queries is deprecated so use slow_query_log_file instead of log-slow-queries .

# Save and close /etc/my.cnf file.

# touch /var/log/mysql-slow.log
# chown mysql:mysql /var/log/mysql-slow.log
# service mysqld restart

You can test your log file is working by typing
# tail -t /var/log/mysql-slow.log