When using a database, developers often want to know what’s happening behind the scenes, whether for troubleshooting, performance tuning, or sheer curiosity.
General query logs remain one of the most popular auditing and diagnostic information sources 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 increase 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 -p2. 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';Code language: PHP (php)The general query log is written in the same data directory that holds the database subdirectories (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;Code language: PHP (php)Let’s check the MySQL/MariaDB general query log status again:
SHOW VARIABLES LIKE "general_log%";Code language: JavaScript (javascript)
That’s it. 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.
Related: Head and Tail Commands in Linux Explained with Examples
Once you have done your inspection, you can disable MySQL/MariaDB query logging as follows:
SET GLOBAL general_log = 0;Code language: PHP (php)It’s certainly undesirable to have turned on logging on a production server. So be careful with this; the log file can become big quickly.
Suppose you don’t want to run queries directly. In that case, 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.
Conclusion
When you suspect an error in a client and want to know what the client sent to the database exactly, the general MySQL/MariaDB query log can be helpful.
So, if you have a database issue, one way to troubleshoot it is to enable query logging and observe what is going on.
You can read more about enabling the general MySQL/MariaDB query log here and here.
