MySQL Bugs: #26424: Add ability to look at a database based on a point in time

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:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	All	OS:	Any (All)
Assigned to:		CPU Architecture:	Any
Triage:	D5 (Feature request)

Description:
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..."

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

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.