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

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

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.