How to Enable MySQL/MariaDB Query Logging

This short and easy-to-follow guide covers how to enable MySQL/MariaDB query logging and save queries to a file.

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.

Check MySQL General Query Logging Status

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';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)
MySQL/MariaDB Enable General Query Logging

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

MySQL/MariaDB Enable General Query Logging

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.

Bobby Borisov

Bobby Borisov

Bobby, an editor-in-chief at Linuxiac, is a Linux professional with over 20 years of experience. With a strong focus on Linux and open-source software, he has worked as a Senior Linux System Administrator, Software Developer, and DevOps Engineer for small and large multinational companies.

Think You're an Ubuntu Expert? Let's Find Out!

Put your knowledge to the test in our lightning-fast Ubuntu quiz!
Ten questions to challenge yourself to see if you're a Linux legend or just a penguin in the making.

1 / 10

Ubuntu is an ancient African word that means:

2 / 10

Who is the Ubuntu's founder?

3 / 10

What year was the first official Ubuntu release?

4 / 10

What does the Ubuntu logo symbolize?

5 / 10

What package format does Ubuntu use for installing software?

6 / 10

When are Ubuntu's LTS versions released?

7 / 10

What is Unity?

8 / 10

What are Ubuntu versions named after?

9 / 10

What's Ubuntu Core?

10 / 10

Which Ubuntu version is Snap introduced?

The average score is 68%