Log all queries to a MySQL server
|Debian (Etch, Lenny, Squeeze)|
|Ubuntu (Hardy, Intrepid, Jaunty, Karmic, Lucid, Maverick, Natty, Trusty)|
To configure a MySQL server to log the content of all queries.
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.
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
On Red Hat-based systems replace
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.
Connect to the database using
mysql and issue the command:
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.)
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.