Bug #65178 Provide the flashback feature by binlog

• Submitted: 2 May 2012 10:30
• Modified: 9 May 2012 16:11
• Reporter: Lixun Peng (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: 5.*
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, Flashback

[2 May 2012 10:30] Lixun Peng

Description:
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] MySQL Verification Team

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.