How to enable MySQL query logging

To inspect MySQL query execution, one can enable query logging and pipe the output to a file or table to gain insights about running queries. In this article, we cover how to enable MySQL querying logging and save queries to a file.

When using an ORM framework, developers often want to know what’s happening behind the scene. Whether it is for troubleshooting, performance tuning, or out of sheer curiosity.

Of course, one way is to manipulate the log level to see what queries the ORM framework (like Hibernate) sends to the database. But that often is a bit obscure and unclear. Additionally, many transactions are not fully logged even with the trace log enabled.

It’s always better, especially when dealing with a problem, to go directly to the main source and start any investigation or research from there. In the case of capturing query logs, native MySQL logs are much better than any framework logs.

So without further ado, let’s begin as the process is pretty straightforward.

In order to enable MySQL query logging just run the following queries with the root user,

SET global general_log_file='/tmp/mysql_queries.log'; 
SET global log_output = 'file';
SET global general_log = on;

If you want to log the queries to a MySQL reserved table you can modify line two of the above config,

SET global log_output = 'table';

Then you can run this query to get log lists,

SELECT * FROM mysql.general_log;

To truncate logs,

TRUNCATE mysql.general_log

Once you have done with your inspection, you can disable the logging as follows:

SET global general_log = off;

A word of caution
Query logging significantly impacts database performance. Use it with caution and avoid enabling it in any production environment as much as possible. It causes significant performance issues on high-traffic databases.

If you can’t or don’t want to run queries directly, you can still enable MySQL query logging by modifying the MySQL config file. For that, refer to this article.

Conclusion

In this article, we discussed how to enable MySQL query logging to either a file or a reserved table. That should provide sufficient insights into what queries are executed by MySQL especially when troubleshooting issues like spotting slow queries. It is important to note that enabling query logging has a significant performance penalty on the database server. One should refrain from doing that in a production environment.