add every update, delete, insert query to a new record in mysql

Question

Is there a way that if there's a change in records, that a query that changed the data (update, delete, insert) can be added to a "history" table transparently?

For example, if mySQL detects a change in a record or set of records, is there a way for mySQL to add that query statement into a separate table so that way, we can track the changes? That would make "rollback" possible since every query (other than SELECT) would be able to reconstruct database from its first row. Right?

I use PHP to interact with mySQL.

Answer 1

Read about transaction logging in MySQL. This is built in to MySQL.

Answer 2

Hi. MySQL has logging functionality that can be used to log all queries. I usually leave this turned off since these logs can grow very rapidly, but it is useful to turn on when debugging.

If you are looking to track changes to records so that you can "roll back" a sequence of queries if some error condition presents itself, then you may want to look into MySQL's native support of transactions.

Answer 3

You need to enable the MySQL BinLog. This automatically logs all the alteration statements to a binary log which can be replied as needed.

The alternative is to use an auditing function through Triggers