How to enable the slow query log in MySQL/ MariaDB Server

When you are troubleshooting, and you want to determine which database queries take a long time to run, slow queries can affect database performance and overall server performance. The slow query log is a record of SQL queries that took a long time to perform. In addtion, slow query log is disabled by default. If you want to enable the slow query log in your MariaDB / MySQL Server, you can do so via the MySQL CLI with no needs to restart the MariaDB / MySQL Server or by making some changes to the my.cnf file.

To enable the slow query log in MySQL without a restart, follow these steps:

01- Log in to mysql using the MySQL CLI as follow:

# mysql -u root -p

02- By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log. To change the log path or filename. type the following command, replacing /path/filename by yours:

MariaDB [(none)]> SET GLOBAL slow_query_log_file = '/path/filename';

03- This step is optional, by default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run. To change this interval, type the following command, replacing X with the time in seconds:

MariaDB [(none)]> SET GLOBAL long_query_time = X;

04- Finaly, enable the slow query by running the following command:

MariaDB [(none)]> SET GLOBAL slow_query_log = 'ON';

05- Verify the configuration by executing the following commands:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'slow\_%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /var/lib/mysql/slow-queries.log |
+---------------------+---------------------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 rows in set (0.001 sec)

– When you are done troubleshooting, disable the slow query log. To do this, run the MySQL CLI again, and then type the following command:

MariaDB [(none)]>  SET GLOBAL slow_query_log = 'OFF';

Conclusion

You have successfully enabled the slow query log in MySQL/MariaDB Server. For more information, you can now visit the official MariaDB Documentation page.

See Also:

• How to Setup MariaDB Master-Slave Replication
• How To Reset MySQL or MariaDB Root Password
• Monitoring MYSQL/MariaDB Using Zabbix Server

If you like our content, please consider buying us a coffee. Thank you for your support!