123 Reg Support

How do I enable or disable MySQL slow query logging?

Michael Jordan

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

Step 1 of 8

Start by accessing your server as the root user via SSH.

Step 2 of 8

From there, make a backup of /etc/my.cnf with the following name: /etc/my.cnf.bak.$(date +%F_%R)

Step 3 of 8

Edit the /etc/my.cnf file to include the following line:

vi /etc/my.cnf

Step 4 of 8
  • 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

Step 5 of 8

Save your text and close the editor.

Step 6 of 8

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

Step 7 of 8

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

Step 8 of 8

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

Step 1 of 6

Start by accessing your server as the root user via SSH.

Step 2 of 6

From there, edit the /etc/my.cnf file to include the following:

vi /etc/my.cnf

Step 3 of 6
  • 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

Step 4 of 6

Save your text and close the editor.

Step 5 of 6

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

Step 6 of 6

Check your Slow Query log to ensure it’s working correctly by entering the following command:

cat /var/log/slow-queries.log