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.
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.
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.
in your .ini configuration add this line
log=allqueries.log
you will need to restart mysql
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.
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
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.
