How do I enable or disable MySQL slow query logging?
This article will show you how to enable and disable MySQL Slow Query logging within your Virtual Private Server.
If you’re experiencing any issues with your MySQL databases, you may wish to enable slow query logging to check this. Simply click the relevant title from the list below and follow the provided instructions to find out more:
Please note: the following article was originally created for our current generation of Virtual Private Servers. As such, these instructions may not be relevant to our previous generation of Virtual Private Servers.
Enabling MySQL Slow Query logging
From there, make a backup of /etc/my.cnf with the following name: /etc/my.cnf.bak.$(date +%F_%R)
Edit the /etc/my.cnf file to include the following line:
vi /etc/my.cnf
- Next, you’ll need to add a lines within the [mysqld] section. This will depend on what version you have.
For MySQL versions less than 5.5:
# Slow Query Log
set-variable=long_query_time=4
log-slow-queries=/var/log/slow-queries.log
For MySQL versions 5.5 and higher:
# Slow Query Log
slow_query_log=1
long_query_time=4
slow_query_log_file=/var/log/slow-queries.log
For MariaDB:
# Slow Query Log
slow_query_log
long_query_time=4
slow_query_log_file=/var/log/slow-queries.log
Save your text and close the editor.
Create the log file and use the following command to set the appropriate permissions:
touch /var/log/slow-queries.log && chown mysql:mysql /var/log/slow-queries.log
Restart your database by entering one of the following commands, depending on your operating system:
For CentOS 6 cPanel MySQL:
service mysql restart
service mysqld restart
service mariadb restart
For CentOS 7
systemctl mysqld restart
systemctl mariadb restart
Check your Slow Query log to ensure it’s working correctly by entering the following command:
cat /var/log/slow-queries.log
You may also wish to use the command mysqldumpslow to parse the log and make it more readable:
mysqldumpslow -r -a /var/log/mysql/mysqld.slow.log
Disabling MySQL Slow Query logging
From there, edit the /etc/my.cnf file to include the following:
vi /etc/my.cnf
- In the same document, delete the following lines within the [mysqld] section:
For MySQL versions less than 5.5:
# Slow Query Log
set-variable=long_query_time=4
log-slow-queries=/var/log/slow-queries.log
For MySQL versions 5.5 and higher:
# Slow Query Log
slow_query_log=1
long_query_time=4
slow_query_log_file=/var/log/slow-queries.log
For MariaDB:
# Slow Query Log
slow_query_log
long_query_time=4
slow_query_log_file=/var/log/slow-queries.log
Save your text and close the editor.
Restart your database by entering one of the following commands, depending on your operating system:
For CentOS 6 cPanel MySQL:
service mysql restart
service mysqld restart
service mariadb restart
For CentOS 7
systemctl mysqld restart
systemctl mariadb restart
Check your Slow Query log to ensure it’s working correctly by entering the following command:
cat /var/log/slow-queries.log