All Products
Search
Document Center

PolarDB:Use SQL flashback

Last Updated:Sep 11, 2023

Background information

PolarDB-X 1.0 provides the SQL flashback feature to restore data rows after improper operations.

If you accidentally execute an SQL statement such as INSERT, UPDATE, or DELETE on a PolarDB-X 1.0 instance, you can use the SQL flashback feature to restore the data. After you provide the information about the SQL statement, PolarDB-X searches for the matching event in the binary log to generate a restoration file. You can download the file to restore the data.

To locate data that is lost due to an improper operation, SQL flashback automatically chooses one of the fuzzy match and exact match policies. For more information, see Terms.

You can use rollback SQL statements or original SQL statements to restore data in different scenarios. For more information, see Roll back an SQL statement or use the original data.

Benefits

  • Easy operation: To use SQL flashback to restore lost data, you need to submit only basic information about the improper operation.

  • Fast and lightweight: Regardless of the backup policy of the custom ApsaraDB RDS for MySQL instance, you can use SQL flashback to quickly restore data as long as you enable the log backup feature for the custom ApsaraDB RDS for MySQL instance before an improper operation.

  • Flexible restoration: Depending on the scenario, you can restore data by rolling back the SQL statement or using the original ata.

  • Exact match: SQL flashback can search for exact matches of SQL statements, which improves the accuracy of data restoration.

Limits

  • SQL flashback is implemented based on the binary logs of the ApsaraDB RDS for MySQL instance. To use SQL flashback for a PolarDB-X 1.0 instance, enable the log backup feature for the custom ApsaraDB RDS for MySQL instance in the PolarDB-X 1.0 instance. The binary log of an ApsaraDB RDS for MySQL instance is retained only for a limited time. We recommend that you use SQL flashback to generate a restoration file as soon as possible after an improper operation.

  • By default, a restoration file generated by SQL flashback is retained for seven days. After a restoration file is generated, you need to download the file as soon as possible.

  • SQL flashback requires the following conditions to search for exact matches:

    • The PolarDB-X 1.0 instance version is 5.3.4-15378085 or later.

    • The custom ApsaraDB RDS for MySQL instance used by PolarDB-X 1.0 is V5.6 or later.

    • Exact match is enabled for SQL flashback before an improper operation.

    • The TRACE_ID of the SQL statement is provided.

  • To ensure the accuracy of data restoration, exact match is enabled by default for SQL flashback in databases created in PolarDB-X 1.0 instances whose versions are 5.3.4-15378085 or later. After exact match is enabled, the information about SQL statements is contained in the binary log of the custom ApsaraDB RDS for MySQL instance. This requires additional storage space in the custom ApsaraDB RDS for MySQL instance. If you want to use exact match, we recommend that you upgrade PolarDB-X 1.0 before you enable exact match. For more information, see Enable exact match.

Generate a restoration file

If this is the first time you use SQL flashback, select an SQL statement and perform the following steps to try out the feature. The generated restoration file can help you learn more about SQL flashback.

  1. Log on to the PolarDB-X console.

  2. Select the region where the instance involved in an improper operation resides.

  3. On the Instance List page, click the instance name.

  4. In the left-side navigation pane, click SQL Flashback.

  5. Enter the basic information about the SQL statement, such as the database, time range when the SQL statement was executed, table name, TRACE_ID, and SQL statement type. For more information about how to obtain the information about the SQL statement, see Locate an SQL execution record.

    The following section describes the parameters.

    • Time Range: Select an approximate time range when the SQL statement was executed. The start time must be earlier than the time when the SQL statement was executed and the end time must be later than the time when the execution of the SQL statement was complete. To ensure the efficiency of data restoration, we recommend that you specify an accurate time range and limit it to five minutes.

    • TRACE_ID: PolarDB-X 1.0 assigns a unique TRACE_ID to each executed SQL statement. You can obtain the TRACE_ID of an SQL statement by using the SQL audit feature of PolarDB-X 1.0.

  6. Click Precheck to check whether a binary log exists in the custom ApsaraDB RDS for MySQL instance for the selected time range.

    1. If no binary logs exist in the custom ApsaraDB RDS for MySQL instance for the selected time range, the precheck fails and the data cannot be restored.

    2. If binary logs exist in the custom ApsaraDB RDS for MySQL instance for the selected time range, the precheck is passed.

  7. In the dialog box that appears, specify the SQL Statement Type for Recovery parameter. For more information, see Roll back an SQL statement or use the original data.

  8. Click Generate SQL to generate an SQL flashback task. The SQL Flashback page displays the status of SQL flashback tasks that are run on the current instance.

  9. After the SQL flashback task is complete, the task information is displayed, such as whether exact match was used for the task and the number of rows that are restored. Click Download to download the restoration file for the data.

    By default, the restoration file is retained for seven days. Download the file as soon as possible.

Locate an SQL execution record

  1. Select the database on which the SQL statement is executed.

  2. On the SQL Flashback page, click SQL Audit and Analysis to go to the SQL Audit and Analysis page. If SQL audit is not enabled, use the SQL audit feature to import historical data. You can import up to seven days of historical SQL records.

  3. Use SQL audit and analysis to locate the audit records of the SQL statement.

  4. You can view the sql_time, table_name, sql_type, and trace_id parameters of the SQL execution record.

Enable exact match

Prerequisites: The PolarDB-X 1.0 instance version is 5.3.4-15378085 or later. If the instance is of an earlier version, we recommend that you update the instance before you enable exact match.

  1. Log on to the PolarDB-X console and click the instance for which you want to enable exact match. In the left-side navigation pane, click Parameter Settings. For more information, see Set parameters.

  2. On the Database tab, change the value of ENABLE_SQL_FLASHBACK_EXACT_MATCH to ON.

Terms

SQL flashback searches for the matching event in the binary log of the custom ApsaraDB RDS for MySQL instance based on the information about the SQL statement that you provide and generates the SQL statement to restore the data. Depending on the instance version and parameter settings, SQL flashback supports two matching policies for events in the binary log. The policies are exact match and fuzzy match. You do not need to select a matching policy. In PolarDB-X 1.0, SQL flashback automatically selects the optimal matching policy, and notifies you when the flashback task is complete.

Exact match

SQL flashback searches for events that exactly match an SQL statement in the binary log of the custom ApsaraDB RDS for MySQL instance to generate a restoration file.

Advantage: The restoration file contains only the data that was deleted or modified due to the SQL statement. You can use the file to ensure the accuracy and efficiency of data restoration.

Disadvantage: SQL flashback requires the following conditions to search for exact matches:

  • The PolarDB-X 1.0 instance version is 5.3.4-15378085 or later.

  • The custom ApsaraDB RDS for MySQL instance used by PolarDB-X 1.0 is V5.6 or later.

  • Exact match is enabled for SQL flashback before an improper operation. After exact match is enabled, the information about SQL statements is contained in the binary log of the custom ApsaraDB RDS for MySQL instance. This requires additional storage space in the custom ApsaraDB RDS for MySQL instance.

  • The TRACE_ID of the SQL statement is provided.

    Note

    By default, exact match is enabled for SQL flashback in databases created on instances whose versions are 5.3.4-15378085 or later. If an instance is of an earlier version, we recommend that you update the instance before you enable exact match. For more information, see Enable exact match.

Fuzzy match

SQL flashback searches for the matching event in the binary log of the custom ApsaraDB RDS for MySQL instance based on the information about an SQL statement that you provide, such as the time range, table name, and SQL statement type. Then, the system generates a restoration file.

Advantage: The fuzzy match option is supported on all instances and does not require specific instance versions or parameter settings.

Disadvantage: SQL flashback cannot search for the exact data that was deleted or modified. The restoration file also contains data changes that are made by other SQL operations. You need to filter the required data from the restoration file.

Roll back an SQL statement or use the original data

The SQL flashback feature of PolarDB-X 1.0 can generate rollback SQL statements or original SQL statements for data restoration. Before a restoration file is generated, you must select a restoration method based on your business scenario.

Rollback SQL statement

An SQL statement that is generated to roll back the INSERT, UPDATE, or DELETE operations of the matching events in the binary log in reverse order.

  • The reverse operation of INSERT is DELETE.

  • The reverse operation of DELETE is REPLACE.

  • The reverse operation of UPDATE is UPDATE SET value = the value before the update.

How to use: Execute the rollback SQL statements on the original table to restore data by overwriting the existing data.

Scenario: If the data to be restored does not cause conflicts or conflicting data can be overwritten, we recommend that you use rollback SQL statements.

Original SQL statement

An SQL statement that is generated to copy the data before the INSERT, UPDATE, or DELETE operations of the matching events in the binary log in sequential order.

  • The copy operation of INSERT is INSERT.

  • The copy operation of DELETE is INSERT.

  • The copy operation of UPDATE is INSERT.

How to use: Execute the original SQL statements on a temporary table to write the original data before the improper operation, compare the original data with the existing data, and then analyze the data conflicts to determine the final data.

Scenario: If the data to be restored conflicts with the current data, we recommend that you use original SQL statements.