Start a conversation

Enabling MySQL slow query log on cPanel/WHM servers

When we encountered performance issues on MySQL database, the first step in troubleshooting is to identify the long-running queries, and the next step is to determine why these queries are running slow and what is the root cause of such behavior. 

In this tutorial, we will see how to enable Slow Query Log on cPanel/WHM Servers.

1. Connect to a cPanel server via SSH

2. Enable the MySQL slow query log in the MySQL configuration file located at /etc/my.cnf

3. Open the file my.cnf in any text editor. In this example, we are using the "vi" editor. 

# vi /etc/my.cnf

4. Add the records below the [mysqld] section: 

slow_query_log_file=/var/lib/mysql/slow.log

5. Save the changes and close the file.

6. Create the slow query log file /var/log/mysql-slow.log and set correct ownership on it. 

touch /var/lib/mysql/slow.log

chmod 660 /var/lib/mysql/slow.log

chownmysql:mysql /var/lib/mysql/slow.log

7. Restart MySQL. The command depends on the operating system and installed MySQL version: for CentOS/RHEL-based distributions.

# service mysqld restart

8. Start monitoring the slow query log file (/var/lib/mysql/slow.log.)

9. To print all the recorded slow queries run the below command:

# mysqldumpslow -a /var/lib/mysql/slow.log 

Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. RelateHost Support

  2. Posted
  3. Updated

Comments