Bug #26424 Add ability to look at a database based on a point in time
Submitted: 15 Feb 2007 20:52 Modified: 13 Mar 2007 20:25
Reporter: Christopher Fulton Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version:All OS:Any (All)
Assigned to: CPU Architecture:Any

[15 Feb 2007 20:52] Christopher Fulton
I searched and could not find another feature similar to this one.  I have always thought it would be nice if the tables in a DBMS could internally record all changes to them (INSERT's/UPDATES/DELETES/ALTER's/etc) and the date that they happened, and then to be able to query the database system based on a point in time.  So for example to be able to SELECT * FROM FOO AS OF '2006-01-01', and to have that be like a "snapshot" of the database on that date.  I realize this would be a major change, and would increase the storage size of a database n-fold, as well as slow down things considerably, so I'd never have it be something that was turned on by default, but it would be a cool thing to consider doing.  I don't know of any DBMS that does this.  

How to repeat:
This is just a feature request.  It's really not that high of a priority to me, but it was something that I was thinking "it would be nice if..."
[13 Mar 2007 11:54] Sveta Smirnova
Thank you for the report.

Seems this implemented already.

Please read about MySQL Server Logs at http://dev.mysql.com/doc/refman/5.1/en/log-files.html
[13 Mar 2007 20:25] Christopher Fulton
I don't really see how the logs implement this.  I know that the binary logs will record all the updates/alters/changes and such, but what I was more looking for was to be able to then via a query, query the database based on a point in time (i know that you can effectively do this by simply restoring a backup from that day, and then using the binary log to do a point in time recovery up to the exact time you want, but I was hoping to be able to have a way to do this internally.)

In other words, if I were to say SELECT * FROM FooTable WHERE FooId=1234 AS OF '2006-01-01', the database would run the query based on the data that was in the database on 2006-01-01.