MySQL slow query log is helpful when you needed to debug slow down or failure of your MySQL server. Slow query log can house queries which took more than n number of seconds.
MySQL Slow query log could be enabled by editing MySQL configuration file named my.cnf and adding three lines of configuration code.
In this tutorial I will show you how to do it in simple steps.
- Connect to server via SSH.
- Enable the MySQL slow query log in the MySQL configuration file
my.cnf:2.1. Open the
my.cnffile in a text editor. In this example, we use the vi editor:
- on CentOS/RHEL-based distributions1vi /etc/my.cnf
- on Debian/Ubuntu-based distributions1vi /etc/mysql/my.cnf
2.2. Add the records below under the
Note: In MySQL 5.7 and MariaDB 10.X, the variable
log-slow-queriesis deprecated. Use
slow_query_log_fileinstead.123slow_query_log = 1log-slow-queries = /var/log/mysql/slow.loglong_query_time = 2
- where long_query_time – time taken by an SQL query to be executed in seconds. If a query takes longer than the value specified, this query will be recorded in the slow query log file.
2.3. Save the changes and close the file.
- on CentOS/RHEL-based distributions
- Create the slow query logfile
/var/log/mysql/slow.logand adjust ownership on it:1touch /var/log/mysql/slow.log && chown mysql:mysql /var/log/mysql/slow.log
- Restart MySQL. The command depends on operating system and installed MySQL version:1service mysql restart
OR1service mysqld restart
OR1service mariadb restart
- Start monitoring the slow query logfile. Use the command mysqldumpslow to analyze it and print summary of the slow query logfile.
For example, to print all slow queries that have already been recorded, run the command:1mysqldumpslow -a /var/log/mysql/slow.log
The full list of mysqldumpslow options is available here.