How to enable slow query log in MySQL/MariaDB

To enable MySQL slow query login, edit my.cnf file

vi /etc/my.cnf

For MariaDB, edit

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Add

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Or

log_slow_query_file    = /var/log/mysql/slow.log
log_slow_query_time    = 2
log_slow_verbosity     = query_plan,explain
log-queries-not-using-indexes
log_slow_min_examined_row_limit = 1000

Restart MySQL

systemctl restart mysql

On CentOS, the command is “systemctl retstart mysqld”. If you are using MariaDB, use “systemctl restart mariadb”.

You can use mysqldumpslow command to view slow queries.

mysqldumpslow /var/log/mysql/slow.log

To test, you can run the command

SELECT SLEEP(10);

This will simulate a query, that take 10 seconds.