In this short and easy to follow guide, we cover how to enable MySQL/MariaDB query logging and save queries to a file.
When using a database, it often happens that developers want to know what’s going on behind the scene. Whether it is for troubleshooting, performance tuning, or out of sheer curiosity.
General query logs remain one of the most popular sources of auditing and diagnostic information in MySQL/MariaDB databases. After logging is enabled, the database server will write information to the log file when clients connect or disconnect, and it will log each SQL statement.
However, keep in mind that enabling the general query log affects the MySQL/MariaDB performance. This will decrease the throughput by about 13% and increased the response time from the MySQL/MariaDB server by about 17%.
So without further ado, let’s begin as the process is pretty straightforward.
Enable MySQL/MariaDB General Query Logging
In MySQL/MariaDB the general query log is disabled by default.
1. Enter to MySQL/MariaDB server command-line tool as root:
mysql -u root -p
2. Set the general log file path to
/var/logs/mysql/general-query.log by executing the below query.
SET GLOBAL general_log_file='/var/log/mysql/general-query.log';
By default, the general query log is written in the same data directory that holds the database subdirectories themselves (typically
/var/lib/mysql) and the log file name default to the hostname. However, as you can see, this can be changed.
3. Enable the server general log:
SET GLOBAL general_log = 1;
Let’s check again the MySQL/MariaDB general query log status:
SHOW VARIABLES LIKE "general_log%";
That’s it. From here on you can do something similar to
sudo tail -f on the
general-query.log file from the command line and keep an eye on things.
Once you have done with your inspection, you can disable MySQL/MariaDB query logging as follows:
SET GLOBAL general_log = 0;
It’s certainly undesirable to have turned on logging on a production server. So just be careful with this, log file can become big pretty fast.
If you don’t want to run queries directly, you can still enable MySQL/MariaDB query logging by directly modifying the MySQL/MariaDB config file, but keep in mind that this approach requires a restart of the database server.
The general MySQL/MariaDB query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to the database. So if you have some bug or problem with database, one of the way to trace the problem in turning on this log, and check what is happening.