To enable the Slow Query Log for MySQL or MariaDB:

Enable the Slow Query Log

To enable the Slow Query Log for MySQL or MariaDB:

  1. Log in to your server as the root user via SSH.
  2. Open the my.cnf
  3. vim /etc/my.cnf
  4. Add the code under the mysqld section:slow_query_log = 1 slow-query_log_file = /var/log/mysql-slow.log long_query_time = 2Note:In MySQL 5.6 and older, use the log-slow-queries variable instead of the slow-query_log_file variable.
  5. Create the /var/log/mysql-slow.log file and set its user as the mysql user. To do this, run the following commands:touch /var/log/mysql-slow.log chown mysql:mysql /var/log/mysql-slow.log
  6. Restart MySQL or MariaDB. To do this, run the following command: systemctl restart mariadb
  7. Start monitoring the slow query logfile. To analyze and print the file’s summary, run the mysqldumpslow command. For example, to print all slow queries that the system previously recorded, run the following command:mysqldumpslow -a /var/log/mysql-slow.log

For a complete list of options to use with the mysqldumpslow command, read MySQL’s mysqldumpslow article.



You may also like...