How to enable slow query log in MySQL/MariaDB

Written by

Yujin Boby

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
log_slow_query_time = 2

For older versions use long_query_time instead of log_slow_query_time

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.

Comments