MySQL Bugs: #116806: Support flashback

Bug #116806	Support flashback
Submitted:	27 Nov 2024 12:49	Modified:	4 Dec 2024 3:44
Reporter:	ksql- team	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
When master-slave switching in asynchronous replication with binlog，old master may have extra transactions, and data may  be inconsistent between new master and old master,  if we want to add the old master as slave to new master, data should be consistent. We support flashback to the gtid that new master and old master has same data, extra transactions will be flashback, extra binlog will be truncated, and extra gtids will be rollbacked.

On behalf of our team(KuaiShou MySQL Team), I will contribute an implementation of flashback to mysql community.

Limitations:
1.gtid-mode=ON
2.binlog_format=ROW
3.binlog_row_image=FULL
4.read_only=ON, write was not allowed.
5.start gtid should be in last binlog file, otherwise flashback nothing. (can be extended in the future)
6.support flashback dml such as insert、update、delete, DDL  is not supported. 
7.mysql server exit abnormally during  flashback may lead to data inconsistent, not crash safe  (can be extended in the future)
8.gtid in last binlog file should be increasing.

Examples:
1.SELECT @@GLOBAL.gtid_executed;
+------------------------------------------+
| @@GLOBAL.gtid_executed                   |
+------------------------------------------+
| 0199deae-f082-11ee-b4ad-ee9a035daf9e:1-5 |
+------------------------------------------+
2.flashback to "0199deae-f082-11ee-b4ad-ee9a035daf9e:3";
3.SELECT @@GLOBAL.gtid_executed;
+------------------------------------------+
| @@GLOBAL.gtid_executed                   |
+------------------------------------------+
| 0199deae-f082-11ee-b4ad-ee9a035daf9e:1-3 |
+------------------------------------------+
4.show flashback status \G
*************************** 1. row ***************************
                 start_gtid: 0199deae-f082-11ee-b4ad-ee9a035daf9e:3
                 transaction_rollback_counts: 2
                binlog_name: xxxxxx/yxmysql-bin.000001
                 start_time: 2024-11-11 09:09:20.00
                   end_time: 2024-11-11 09:09:20.00
1 row in set (0.00 sec)

How does it work?
1.scan all transactions in last binlog file, find the start gtid we want to  flashback to.
2.from start gtid to end of binlog file, we  collect all binlog events and change it to flashback event.
3.apply all  flashback events in one transaction.
4.rollback gtid to start gtid of flashaback.
5.truncate binlog file to pos of  start gtid.
6.flashback is not crash safe, if flashback_temp_log was found when mysql server start, data may be inconsistent, we should rebuild it with new master.

How to repeat:
N/A

Suggested fix:
I'll attach a patch later

Thank you very much for your patch contribution, we appreciate it!

In order for us to continue the process of reviewing your contribution to MySQL, please send us a signed copy of the Oracle Contributor Agreement (OCA) as outlined in https://oca.opensource.oracle.com

Signing an OCA needs to be done only once and it's valid for all other Oracle governed Open Source projects as well.

Getting a signed/approved OCA on file will help us facilitate your contribution - this one, and others in the future.  

Please let me know, if you have any questions.

Thank you for your interest in MySQL.

Support flashback (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: flashback.patch (application/octet-stream, text), 98.20 KiB.

Thank you.