How to log all or slow MySQL queries?

Better Stack Team
Updated on August 25, 2023

To log all or slow MySQL queries, you'll need to modify the MySQL configuration file and enable query logging. The exact steps may vary depending on your MySQL version and the operating system you're using, but I'll provide a general guide that should work for most scenarios.

1. Open MySQL Configuration File

The location of the MySQL configuration file varies, but it's commonly named my.cnf or my.ini. On Linux, it's often found in /etc/mysql/my.cnf, and on Windows, it's typically in the MySQL installation directory.

2. Enable General Query Log

To log all queries, find the [mysqld] section in the configuration file and add or uncomment the following line:

general_log = 1
general_log_file = /var/log/mysql/mysql.log

This will enable the general query log and specify the file where the queries will be logged. Adjust the file path as needed for your system.

3. Enable Slow Query Log

To log slow queries, you can also add or uncomment the following lines in the [mysqld] section:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

This will enable the slow query log, specify the file where slow queries will be logged, and set the threshold for slow queries (in this example, queries taking longer than 2 seconds will be considered slow).

4. Restart MySQL

After making these changes, you'll need to restart the MySQL server for the changes to take effect:

On Linux:

sudo service mysql restart

On Windows, you can restart the MySQL service from the Services panel.

5. Review the Logs

Now that logging is enabled, you can review the logs you specified in the configuration file to see the logged queries.

  • The general query log will contain all queries, which can be useful for debugging or auditing, but be cautious as it may fill up quickly in a busy environment.
  • The slow query log will contain queries that exceed the specified long_query_time, helping you identify performance bottlenecks.

Remember to manage the log files, as they can grow large over time, and consider adjusting the logging settings based on your specific needs and server resources.

To learn more about logging, visit Better Stack Community.

Got an article suggestion? Let us know
Explore more
Licensed under CC-BY-NC-SA

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Make your mark

Join the writer's program

Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.

Write for us
Writer of the month
Marin Bezhanov
Marin is a software engineer and architect with a broad range of experience working...
Build on top of Better Stack

Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.

or submit a pull request and help us build better products for everyone.

See the full list of amazing projects on github