How to Enable MySQL Slow Query Log (and Actually Use It)

Overview

The MySQL slow query log records any SQL query that takes longer than a defined execution time. It’s one of the most reliable tools for MySQL performance debugging — especially when a site is intermittently slow and you can’t reproduce the issue on demand.

You’ll typically need this when a WordPress site starts timing out, an application slows down under load, or a developer reports that database queries are taking seconds instead of milliseconds. The log doesn’t lie: it shows you the exact query, how long it ran, and how many rows it examined.

This guide covers enabling the slow query log on MySQL 8.0+ and MariaDB 10.6+ via config file and live SQL commands. It applies whether you’re on a cPanel shared server, a VPS SSD Hosting plan, or a dedicated server.

Prerequisites

• SSH access to the server, or access to MySQL as a user with SUPER or SYSTEM_VARIABLES_ADMIN privileges
• MySQL 8.0+ or MariaDB 10.6+ (commands vary slightly for older versions — noted where relevant)
• Write permissions to the directory where the slow query log will be stored
• Root or sudo access if you’re editing my.cnf / my.ini
• On cPanel servers: WHM root access if you want changes to persist across MySQL upgrades

Step-by-Step Instructions

Method 1: Enable via my.cnf (Persistent — Survives Restarts)

This is the approach I’d recommend for production servers. Changes in the config file survive MySQL restarts and server reboots. The live SQL method is faster but resets on restart.

1. Find your MySQL config file. On most Linux servers it’s /etc/my.cnf or /etc/mysql/my.cnf. On cPanel servers it’s almost always /etc/my.cnf.
   Copied to clipboard

   mysql --help | grep -A1 "Default options"

   This prints the config file search order so you know exactly which file MySQL is actually reading.

2. Open the config file in a text editor.
   Copied to clipboard

   sudo nano /etc/my.cnf

3. Add the following lines under the [mysqld] section. If the section doesn’t exist, add it.
   Copied to clipboard

   [mysqld]
   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 2
   log_queries_not_using_indexes = 1

   A few things worth understanding here:

   • long_query_time = 2 logs any query taking over 2 seconds. On a busy e-commerce site, you might drop this to 0.5 or even 0.1 temporarily to catch borderline slow queries.
   • log_queries_not_using_indexes is optional but genuinely useful — it catches queries that do full table scans even if they finish quickly. This is a common cause of slowdowns that only appear at scale.

   📝 Note: On MariaDB, log_queries_not_using_indexes can flood the log on tables with no indexes at all (like temp tables). Disable it if the log grows too fast.

4. Create the log directory and set correct permissions. MySQL won’t create the directory for you, and it’ll silently fail to log if the path doesn’t exist or isn’t writable.
   Copied to clipboard

   sudo mkdir -p /var/log/mysql
   sudo chown mysql:mysql /var/log/mysql
   sudo touch /var/log/mysql/mysql-slow.log
   sudo chown mysql:mysql /var/log/mysql/mysql-slow.log

   ⚠ Warning: If you skip the chown step, MySQL will start fine but the log file will stay empty. This trips up a lot of people.

5. Restart MySQL to apply the changes.
   Copied to clipboard

   # systemd (most modern distros)
   sudo systemctl restart mysql
   
   # or on older CentOS/cPanel systems
   sudo service mysql restart

6. Confirm the log is enabled.
   Copied to clipboard

   SHOW VARIABLES LIKE 'slow_query%';
   SHOW VARIABLES LIKE 'long_query_time';

   You should see slow_query_log set to ON and the file path you configured.

Method 2: Enable Live via SQL (No Restart Required)

Use this when you need to start logging immediately without a restart — for example, on a production server where restarting MySQL would drop active connections.

1. Log in to MySQL as root or a privileged user.
   Copied to clipboard

   mysql -u root -p

2. Run the following SQL commands.
   Copied to clipboard

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
   SET GLOBAL long_query_time = 2;
   SET GLOBAL log_queries_not_using_indexes = 1;

   📝 Note: These settings are active immediately but reset to whatever’s in my.cnf after the next MySQL restart. Always update the config file too if you want this to stick.

Reading the Slow Query Log

Raw log entries look messy. The mysqldumpslow tool groups similar queries together and sorts by total time, which is what you actually want.

# Top 10 slowest queries by total execution time
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# Top 10 queries by average execution time
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

# Live tail of the log file
tail -f /var/log/mysql/mysql-slow.log

If you want a full breakdown with execution plans, copy a slow query from the log and run EXPLAIN against it in MySQL:

EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post';

The output tells you whether MySQL is using an index or doing a full table scan. A type value of ALL in the EXPLAIN output means a full table scan — almost always something worth fixing.

Disabling the Log When You’re Done

Don’t leave the slow query log running indefinitely on high-traffic servers. On a busy database server, the log file can grow to several gigabytes per day. Once you’ve identified the problem queries, turn it off.

-- Disable without restarting
SET GLOBAL slow_query_log = 'OFF';

Or edit my.cnf and set slow_query_log = 0, then restart MySQL.

Common Issues & Troubleshooting

Log file exists but stays empty

Almost always a permissions problem. MySQL is running as the mysql system user and can’t write to the log path. Verify ownership:

ls -la /var/log/mysql/mysql-slow.log

The file should be owned by mysql:mysql. Fix it with sudo chown mysql:mysql /var/log/mysql/mysql-slow.log and check the MySQL error log (/var/log/mysql/error.log) for related messages.

MySQL fails to start after editing my.cnf

A typo in my.cnf will prevent MySQL from starting. Check the exact error:

sudo journalctl -u mysql -n 50 --no-pager

The most common mistake is adding slow query log directives outside the [mysqld] section, or using incorrect variable names. MySQL 8.0 renamed some variables from MySQL 5.7 — for example, log_slow_queries no longer exists; it’s slow_query_log.

SET GLOBAL returns “Access denied”

Your MySQL user doesn’t have SYSTEM_VARIABLES_ADMIN (MySQL 8.0) or SUPER (MySQL 5.7 / MariaDB) privilege. Log in as root or grant the required privilege. On cPanel servers, you can find the root MySQL password in WHM under SQL Services > MySQL Root Password.

long_query_time set to 2 but nothing is being logged

Check whether any queries are actually exceeding the threshold. Run a deliberate slow query to test:

SELECT SLEEP(3);

If that appears in the log, your setup is correct and your queries are simply running under 2 seconds. Try lowering long_query_time to 0.5 temporarily to cast a wider net. In my experience, WordPress sites with poorly written plugins often have dozens of queries in the 0.5-1.5 second range that never get caught at the default threshold.

Log file is growing too fast

Either log_queries_not_using_indexes is catching a lot of internal queries, or your long_query_time is set too low. Disable log_queries_not_using_indexes first and see if that helps. You can also rotate the log without restarting MySQL:

FLUSH SLOW LOGS;

This closes and reopens the log file — useful if you’re managing log rotation with logrotate.

FAQ