How to log all or slow MySQL queries?

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

MySQL Logging

Where are MySQL logs stored?

Questions

How to enable MySQL Query Log?

Questions

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