All Products
Search
Document Center

SQL Flashback

Last Updated: Aug 13, 2020

Background

Distributed Relational Database Service (DRDS) provides SQL flashback on SQL statements for incorrect operations, which supports row-level data restoration.

After you execute the SQL statement for an incorrect operation (such as INSERT, UPDATE, or DELETE) on a DRDS instance, you can use SQL flashback based on the SQL statement of the incorrect operation to find the matched event in BINLOG files. Then, you can generate and download the corresponding restoration file, and restore data as required.

SQL flashback supports fuzzy match and exact match to locate lost data and automatically selects a matching policy for SQL statements for incorrect operations. For more information, see Exact match and fuzzy match.

SQL flashback provides two types of SQL statements for data restoration in different scenarios: rollback SQL statements and original SQL statements. You can choose an appropriate SQL statement type for data restoration based on your business scenario.

Advantages

  • Easy operation: It is easy to configure SQL flashback. To find lost data, you only need to enter a little information about the SQL statements for incorrect operations.
  • Fast speed and light weight: SQL flashback does not depend on the backup policies of ApsaraDB for RDS (RDS) instances. You can quickly restore data by enabling RDS log backup before executing SQL statements for incorrect operations.
  • Flexible restoration methods: SQL flashback provides rollback SQL statements and original SQL statements for different scenarios.
  • Exact match at the SQL statement level: SQL flashback supports exact match of incorrect operation data at the SQL statement level, which can increase the accuracy of restored data.

Limits

  • SQL flashback depends on the retention time of RDS BINLOG files. Therefore, you must enable RDS log backup. RDS BINLOG files are only retained for a period of time. We recommend that you use SQL flashback to generate a restoration file as soon as possible after an incorrect operation is executed.
  • A restoration file generated by SQL flashback is stored for seven days by default. We recommend that you download the file as soon as possible after it is generated.
  • Exact match of SQL flashback requires the following conditions:
    • The DRDS instance version is 5.3.4-15378085 or later.
    • The RDS instance version used by DRDS is 5.6 or later.
    • Exact match of SQL flashback is enabled before SQL statements for incorrect operations are executed.
    • The TRACE_ID of the SQL statement for an incorrect operation is provided.
  • To ensure the accuracy of restored data, DRDS enables exact match for databases created on instances of 5.3.4-15378085 and later versions. After exact match is enabled, the information about the SQL statements will be contained in RDS BINLOG files by default. This requires additional RDS storage space. If you need exact match, we recommend that you upgrade your DRDS instance to enable exact match. For more information, see Enable exact match.

Procedure

Generate a restoration file

  1. Log on to DRDS console.

  2. On the left-upper corner, choose the region where the target instance locating.

  3. On the Instances page, find the target instance and click on its ID.

  4. On the left navigation pane, cick on Data Recovery>SQL flashback.

  5. On the SQL flashback page, enter the following settings.

    Parameters Descriptions
    Database
    Specify the DRDS database name.
    Table name Specify the table name on which the mistaken SQL statement is executed. This field is optional.
    Time Range Specify the approximate time range for the execution of the mistaken SQL statement. The beginning of the time range is earlier than the time when the SQL statement execution starts. The end of the time range is later than the time when the SQL execution ends. To ensure efficient restoration, we recommend that you set the time range as precisely as possible, it would be better if you can limit the time to 5 minutes.
    TRACE_ID DRDS allocates a unique TRACE_ID for each SQL statement. This field is optional.
    SQL Statement Type Choose one or several types from INSERT, UPDATE, DELETEE.
  6. Click Precheck.

    • If no RDS BINLOG file exists within the specified time range, the precheck would fail and the data cannot be restored.
    • If RDS BINLOG files exist within the specified time range, the precheck would pass.
  7. In the dialog box that pops up, you can choose the SQL Statement Type for Recovery as Rollback SQL or Original SQL Statement. More details, see Rollback SQL statements and original SQL statements.

  8. Click Generate SQL.
    2

  9. When the SQL falshback task finished, you can check if this task is exact match or not and view the numbers of recovered rows.

    On the Action column, you can click Download to download the corresponding restoration file for data restoration. The restoration file is retained for 7 days by default. If you need, please download it as soon as possible.

Enable exact match

Prerequisite: Your DRDS instance version must be 5.3.4-15378085 or later. If your DRDS instance version is earlier than the required version, we recommend that you upgrade it first.

  1. Log on to the DRDS console and go to the Parameter Settings page of the specified instance. For more information, see Set DRDS parameters.
  2. Set Exact Match for SQL flashback of the corresponding database to ON.3

Terms

Exact match and fuzzy match

SQL flashback searches RDS BINLOG files for a matched event based on the information of the SQL statement for an incorrect operation, which you need to provide, and then generates a corresponding restoration SQL statement. Affected by the instance version and parameter settings, SQL flashback supports two matching policies for BINLOG events: exact match and fuzzy match. You do not need to select a specific matching policy. The SQL flashback feature of DRDS automatically detects and selects the optimal matching policy, and notifies you of the policy after the flashback task is completed.

Exact match

The SQL flashback feature searches the event that exactly matches the SQL statement for an incorrect operation in RDS BINLOG files to generate a restoration file.
Advantage: The restoration file only contains data incorrectly deleted or modified due to the execution of a SQL statement, which you can directly use to ensure the accuracy and efficiency of data restoration.
Disadvantage: Exact match requires the following conditions:

  • The DRDS instance version must be 5.3.4-15378085 or later.
  • The RDS instance version used by DRDS is 5.6 or later.
  • Exact match of SQL flashback is enabled before SQL statements for incorrect operations are executed. After exact match of SQL flashback is enabled, the information about the SQL statements will be contained in RDS BINLOG files by default, which requires additional RDS storage space.
  • The TRACE_ID of the SQL statement for an incorrect operation is provided.

Note: Exact match of SQL flashback is enabled by default for new databases on DRDS instances of 5.3.4-15378085 and later versions. We recommend that you upgrade instances of earlier versions and then manually enable exact match. For more information, see Enable exact match.

Fuzzy match

The SQL flashback feature searches RDS BINLOG files for matching events based on the information of SQL statements for incorrect operations, such as the time range, table name, and SQL statement type, and then generates a restoration file.
Advantage: Fuzzy match does not depend on the instance version or parameter settings. Therefore, it is supported on all instances.
Disadvantage: Fuzzy match cannot match accurate data that is deleted or modified by mistake. The restoration file contains data modified by other business SQL statements. You need to filter the required data from the restoration file.

Rollback SQL statements and original SQL statements

To support different business scenarios, DRDS SQL flashback allows you to generate rollback SQL statements and original SQL statements for data restoration. Before you generate a restoration SQL file, you must select a restoration method based on your business scenario.

The following figure shows how the SQL flashback feature generates a rollback SQL statement and an original SQL statement for an incorrect operation.
1

Rollback SQL statements

A rollback SQL statement is generated after the BINLOG files are traversed for matching events in a reverse order and reverse operations are executed for the INSERT, UPDATE, and DELETE events.

  • The reverse operation of INSERT is equivalent to DELETE.
  • The reverse operation of DELETE is equivalent to REPLACE.
  • The reverse operation of UPDATE is equivalent to UPDATE SET value= the value before the update.

How to use: Execute the SQL statement in the original table to restore data by overwriting existing data.
Scenario: If the data to be restored has no conflict or some conflicted data can be overwritten, we recommend that you generate a rollback SQL statement.

Original SQL statements

An original SQL statement is generated after the BINLOG files are traversed for matching events in a sequential order and images are constructed for all records of the INSERT, UPDATE, and DELETE events.

  • The image of INSERT is equivalent to INSERT.
  • The image of DELETE is equivalent to INSERT.
  • The image of UPDATE is equivalent to the value before INSERT.

How to use: Execute the SQL statement in a temporary table to write the original data, compare it with the existing data, analyze the conflict, and determine the final data.
Scenario: If the data to be restored conflicts with the existing data, we recommend that you generate an original SQL statement.