Flashback for MySQL 5.7

Implementation Principle

The concept of flashback first appeared in the Oracle database, which was used to quickly restore the user's misoperation.

Flashback for MySQL is used to recover erroneous operations caused by DML statements and currently does not support DDL statements. For example, the following statement:

DELETE FROM XXX;UPDATE XXX SET YYY=ZZZ;

If there is no flashback function, then the user can recover only through the full standby + binary log roll forward when the misoperation occurs. Typically, this requires a very long recovery time. In order to shorten the time of misoperation recovery, it is usually possible to build LVM on slave to shorten the recovery time of misoperation by periodic snapshots. However, the disadvantage of LVM snapshots is that it has a certain effect on the performance of slave.

The Official Mysqlbinlog command is a binary log for parsing mysql. When the binary log format is row format, you can output the preceding and latter of each record for each operation . Then the rollback operation can be done by reverse operation, for example:

原始操作：INSERT INTO ...flashback操作：DELETE ...原始操作：DELETE FROM ...flashback操作：INSERT INTO ...原始操作：UPDATE XXX SET OLD_VALUES ...flashback操作：UPDATE XXX SET NEW_VALUES ...

At present, flashback functions are integrated into the official Mysqlbinlog command, and the flashback function is opened by means of parameters.

Related Parameters-B--flashback

Flashback core parameters, rollback binary log

[email protected]test-1:~# ./mysqlbinlog -B --base64-output=decode-rows  -vv /mdata/mysql_data_old/bin.000008
......

-A--skip_database

The database is filtered out when parsing binlog.

-A--skip_table

The table is filtered out when parsing binlog, and is generally used with skip_datebase.

-O--split-size-interval

Splits the Binlog file into segments at the specified size, resolving the result to print the starting offset position of each segment.

Note that when flashback is performed, the contents of the flashback are saved in memory first. If your binlog size is 10G, then you need extra 10G of memory to save this information temporarily. In some cases, such as a cloud environment, or a small server memory, the flashback log cannot be output. This parameter can then be used to set the size of the memory save file, for example, set this value to 100M, then every 100M will be flushed to a file.

-D--datetime_to_pos

Based on the input time information, the first Binlog event offset location corresponding to the time is resolved, and the format reference start-datetime is

Flashback to find the starting offset first, the DBA can navigate to the specific location by this parameter before the flashback operation.

-T--table

This table is parsed only and is generally used with database.

-E--fb_event

Only the log event of this type is parsed and is generally used with the database and table options. The optional values are:

• DELETE

• INSERT

• UPDATE

About the flashback functionality of DDL

The flashback feature only supports fast recovery of DML statements, but if the error is DDL, then there is nothing you can do, such as:

TRUNCATE TABLE  xxx;
DROP TABLE xxxx;
DROP DATABASE xxx;

To support the quick flashback functionality described above, you need to modify the MySQL source code to save the deleted library or table to a garbage-collected library, such as the $recycle library. To support this feature, consider using the Innosql commercial version.

Video

Video is the best document

Operation Video

For technical support, you can contact: 82946772.

Original link

Flashback for MySQL 5.7