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:
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.
chmod 660 /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