How to enable slow query log in MySQL/MariaDB
To enable MySQL slow query login, edit my.cnf file
1 |
vi /etc/my.cnf |
For MariaDB, edit
1 |
vi /etc/mysql/mariadb.conf.d/50-server.cnf |
Add
1 2 3 |
long_query_time = 2 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql_slow.log |
Restart MySQL
1 |
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.
1 |
mysqldumpslow /var/log/mysql/slow.log |
To test, you can run the command
1 |
SELECT SLEEP(10); |
This will simulate a query, that take 10 seconds.