Mysql - How can I query about the past (go back in time)?

I have a database that it is always changing... updates, inserts and deletes. For statistics reason, we need sometime to "go back in time" (-: and query the database like we are in the past.

For example: I have a table named user, I have updated yesterday one of the users like the following:

update user set status = 1 where user_id = 2656

The old 开发者_开发百科status was 0, so if i can "go back in time" to yesterday and query it ill get status 0, and if ill query it now, ill get status 1.

I know that one way to do it is to trigger update,insert and delete and log it to different tables, but it is not so clean, and will make the development complicated.

I hope you got me right, not so simple to explain for none English speaker...

Edit-1

We have few algorithms in the application and we fix them and upgrade them always, we have to know how the database was in the past so we can work on the algorithms, we have queries/interface that can be changed to support that (I am aware that this is not a one week project, we have the resources for that)

Thanks

---

Option 1 Enable the binary log.
This will safe all updates, inserts and deletes.
There are query tools for the binlog.

See:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
http://www.mydigitallife.info/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/

Option 2 Create a trigger that saves the changes
The trigger save a data into a parallell database that has the same layout as the original database, except all tables have two extra fields. A unqiue id called log_id and a timestamp. Whenever a field changes you have the trigger log in in the log.
You need at least an after_update and after_delete trigger and I would recommend a after_insert trigger as well. If you feel like it, you can add a third field to every tables called operation, which is an ENUM('insert','delete','update').

DELIMITER $$

CREATE TRIGGER au_table1_each AFTER UPDATE ON table1 FOR EACH ROW
BEGIN
  insert into back_to_the_past.table1_log (`timestamp`,operation,f1,f2,f3.f4) 
    values (now(), 'update', OLD.f1, OLD.f2, OLD.f3, OLD.f4);
END $$

DELIMITER ;

Of course if you create an extra timestamp type field in table1_log you don't need to get it explicitly to now() the DB will do that.

See: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

---

Actually, a "time machine" for an OLTP-database is often implemented using a Data Warehouse.

Your OLTP (Online transaction processing) database need only contain the latest data, so your applications can work. Stuffing it with historical data will indeed pollute the schema, slow it down, make it more complex, etcetera.

Your Datawarehouse will contain LOTS of data, but does not need to accept frequent writes. Instead, you use it to generate reports, analyse trends, make predictions. It is read-optimized, an OLAP (On-Line Analytical Processing) system.

---

If you have resources as you say, I suggest you buy yourself a SAN and put your MySQL data files on a virtual disk. Set up incremental snapshots of that disk at whatever inteval you want.

Whenever you need to look up some historical data, you could create a new virtual disk based on one of those snapshots. Mount the new disk with another MySQL server, and ... play with it.

This method will not give you a continous timeline, so if that is needed you should ignore this suggestion...