Log all queries to a MySQL server

Objective

To configure a MySQL server to log the content of all queries.

Scenario

Suppose that you have written a program that makes queries to a MySQL database. Some of the queries are constructed programmatically, however they are not returning the expected results. You wish to check that the queries reaching the database server have the intended content.

Method

Edit the main MySQL configuration file, my.cnf. On Debian-based systems this is located in /etc/mysql/, and on Red Hat-based systems in /etc/. For versions of MySQL prior to 5.1.29 insert the line:

log = /var/log/mysql.log

or from version 5.1.29 onwards:

general_log = 1
general_log_file = /var/log/mysql.log

You can use a different path for the log file if you prefer. Any existing log settings that would conflict with what you have inserted should be commented out or removed.

Restart the MySQL server process, using the service command if this is available on your system:

service mysql restart

or if not, using the corresponding init.d script:

/etc/init.d/mysql restart

On Red Hat-based systems replace mysql with mysqld.

The content of all queries to the server should now appear in the log, which will be located at the pathname specified in the configuration file.

Testing

Connect to the database using mysql and issue the command:

SELECT 2+2;

This should result in a log entry similar to:

101216 22:56:25      28 Query       SELECT 2+2

(The first two fields are the date and time. The third field identifies the connection that resulted in the log entry.)

Performance considerations

Logging every query will reduce the performance of the database server, especially if its workload consists of many simple queries. For this reason you will probably want to disable it once you have obtained the information you need.

See also

• Log all queries to a PostgreSQL server

• Conventions
• Privacy
• Legal