Bug #65178 Provide the flashback feature by binlog
Submitted: 2 May 2012 10:30 Modified: 9 May 2012 16:11
Reporter: Lixun Peng (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.* OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, Flashback
Triage: Needs Triage: D5 (Feature request)

[2 May 2012 10:30] Lixun Peng
I thought of a way to implement Flashback-like feature. If binlog is logging on ROW format(STATEMENT/MIXED formats are ok, too. But will more complex), we can flashback any storage engines' data.

Binlog is logging by binary format, we can add a new function, parse binlog event as opposite event. 
For example, 
an Update_Rows_Event contain "UPDATE @1=1,@2=2 WHERE @1=3 AND @2=4", 
we can parse it to 'UPDATE @1=3,@2=4 WHERE @1=1 AND @2=2', 
and send the new event to do_apply_event(). 

And an Insert_Rows_Event can reversed to Delete_Rows_Event, Delete_Rows_Event can reversed to Insert_Rows_Event. 

If we apply binlog event parsed by the new function, from new to the old, we can flashback to a timestamp snapshot. And if we add some filters on do_apply_event(), we can even implement the table-level flashback or database-level flashback.

How to repeat:
For example:

Add some command:

mysql > flashback instance to '2012-04-02 00:00:00';

mysql > flashback database TEST to '2012-04-02 00:00:00'

mysql > flashback table TEST.TEST_TABLE to '2012-04-02 00:00:00'
[9 May 2012 16:11] Valeriy Kravchuk
Thank you for the feature request.
[26 Jun 2012 8:37] Lixun Peng
this is an experimental patch, I add a option "-B/--flashback" for mysqlbinlog.

Attachment: 5.5.18_flashback.patch (application/octet-stream, text), 18.08 KiB.

[26 Jun 2012 8:44] Lixun Peng
this is an execution example of "mysqlbinlog -B"

Attachment: flashback.txt (text/plain), 61.29 KiB.

[11 Jul 2012 17:04] Shane Bester
also see bug #65861
[22 Aug 2012 10:22] Lixun Peng
I have fixed some bugs and verified in several Chinese companies' environment

Attachment: 5.5.18_flashback.diff (application/octet-stream, text), 18.40 KiB.

[29 Jan 2019 8:16] Shlomi Noach
This is 7 years old, but so very relevant. I'd like to voice my opinion that this would be a useful feature.