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.