How can I log MySQL queries?

Question

Is it possible to log all queries that are executed? I am looking at a database that is accessed by many different apps. One of them is modifying a table's value in a way it should not. I am trying figure out which app is the culprit. It would help me out a lot if I can capture all the queries that are executed on that table and at what time.

Many thanks in advance for your help.

Answer 1

You can use the general log in MySQL to achieve this. I only recommend you do that on a test/development database without many concurrent users, because the amount of output generated is huge. I'm not sure if it logs the timestamp, though.

If it doesn't, on a unix/linux setup, I'd say write a simple script that read lines from the stdin and print the lines with the current timestamp when they were read, and pipe tail -f on the log file to it, so you can add your own timestamps.

Answer 2

Take a look here: http://dev.mysql.com/doc/refman/5.1/en/server-logs.html You're looking for general query log: http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Answer 3

Either use the --log[=file_name] command line switch on mysqld or edit/create a my.cnf containing:

[mysqld]
log=/tmp/mysql.log

Explained fully in this article.

Answer 4

A possible solution to your problem is to utilize an update trigger on the table in question. The trigger will be fired on any update to the table, and it possible to write the trigger such that when it meets certain criteria (the value in question is changed), an action is performed (perhaps writing to a temporary table, the SQL statement that makes the change). For more information, I suggest looking at Trigger Syntax.

Answer 5

in your .ini configuration add this line

log=allqueries.log

you will need to restart mysql

Answer 6

As far as I am aware, there are currently no auditing capabilities built in to MySQL. Log queries from within the applications that generate them, or sniff connections to the server.