SQL flashback

Background

The SQL flashback feature in PolarDB-X provides row-level data restoration to reverse incorrect SQL operations.

If you run an incorrect SQL statement, such as an INSERT, UPDATE, or DELETE statement on PolarDB-X, you can use the SQL flashback feature. By providing information about the incorrect operation, SQL flashback can find matching events in the binary logging (Binlog) file and generate a restoration file. You can then download the file to restore the data.

SQL flashback supports two strategies to locate data from incorrect SQL statements: fuzzy match and exact match. It can also automatically select a matching strategy. For more information, see Exact match and fuzzy match.

To support different scenarios, SQL flashback provides two data restoration methods: rollback SQL statements and raw SQL statements.

Benefits

• Simple operations: You can easily configure the feature and retrieve lost data by providing information about the incorrect SQL statement.
• Flexible restoration methods: SQL flashback provides two methods for different scenarios, rollback SQL statements and raw SQL statements, which offer flexible restoration options.
• SQL-level exact match: This feature provides SQL-level exact matching for data affected by incorrect operations, which improves the accuracy of data restoration.

Limits and notes

• Restoration files generated by SQL flashback are saved for 7 days by default. You should download the files as soon as possible after they are generated.
• The exact match feature of SQL flashback has the following requirements:
  • The SQL flashback exact match feature was enabled before the erroneous SQL statement was executed.
  • You must provide the TRACE_ID of the incorrect SQL statement.

Generate a restoration file

1. Log on to the PolarDB-X console.

2. In the top navigation bar, select the region where the instance is deployed.

3. On the Instances page, click the PolarDB-X 2.0 tab.

4. Find the instance that you want to manage and click its ID.

5. In the navigation pane on the left, click Data Restoration > SQL Flashback.
6. Enter the basic information of the incorrect SQL statement. The database and time range are required parameters. You can specify the table name, TRACE_ID, and SQL type as needed.

   Parameter description:

   • SQL running time range: The approximate time range when the incorrect SQL statement was run. The start time must be earlier than the statement's execution start time, and the end time must be later than its execution end time. For efficient restoration, specify a time range that is as narrow as possible.
   • TRACE_ID: PolarDB-X assigns a unique TRACE_ID to each executed SQL statement. You can obtain the TRACE_ID of the incorrect SQL statement from the SQL Audit feature of PolarDB-X.
7. Click Dry Run to check whether a Binlog file exists within the specified time range.
   • If a Binlog file exists in the specified time range, the dry run is successful.
   • If no Binlog file exists in the specified time range, the dry run fails. Data cannot be restored.
8. Select a restoration method. You can select Rollback SQL Statement or Raw SQL Statement. For more information about how to select a restoration method, see Rollback SQL statements and raw SQL statements.
9. After the SQL flashback task is complete, information about the task is displayed. Click the download button to download the restoration file and restore your data.

   The restoration file is saved for 7 days by default. You should download the file as soon as possible.

Locate the incorrect SQL statement

1. In the console, choose Diagnostics and Optimization > SQL Audit and Analysis.
   Note If you have not enabled SQL Audit, see Enable SQL Audit and Analysis.
2. Use SQL Audit and Analysis to locate the audit record of the incorrect SQL statement. For more information about how to quickly locate the incorrect SQL statement, see Log analysis.
3. Obtain the sql_time, table_name, sql_type, and trace_id fields from the audit record of the incorrect SQL statement.

Exact match and fuzzy match

SQL flashback uses the information that you provide about an incorrect SQL statement to find matching events in the Binlog file and generate the corresponding restoration SQL statements. SQL flashback supports two matching strategies for Binlog events: exact match and fuzzy match. You do not need to select a matching strategy. PolarDB-X SQL flashback automatically detects and selects the optimal matching strategy. If you do not provide a TRACE_ID, fuzzy match is used. If you provide a TRACE_ID and your instance supports exact match, exact match is used. Otherwise, the strategy is downgraded to fuzzy match. You are notified of the strategy used after the flashback task is complete.

This strategy finds the exact events in the Binlog file that correspond to the incorrect SQL statement and generates a restoration file.

Pros: The restoration file contains only the data affected by the incorrect SQL statement, which ensures the accuracy and efficiency of data restoration. You can run the file directly to restore the data.

This strategy uses information about the incorrect SQL statement, such as the running time range, table name, and SQL type, to find matching events in the Binlog file and generate a restoration file.

Pros: This strategy is supported by all instances and does not depend on the instance version or parameter settings.

Cons: This strategy cannot find the exact data affected by the incorrect operation. The restoration file may contain data modified by other SQL statements. You must manually filter the file to find the data that you need.

Enable exact match

1. Log on to the cloud-native distributed database console and go to the parameter settings page of the target instance. For more information, see Set parameters.
2. Make sure that the binlog_rows_query_log_events parameter is set to ON. The default value is ON.

Rollback SQL statements and raw SQL statements

To support different business scenarios, PolarDB-X SQL flashback provides two restoration methods: generating rollback SQL statements and generating raw SQL statements. Before you generate a restoration SQL file, select a restoration method based on your scenario.

The following figure shows an example of a rollback SQL statement and a raw SQL statement that are generated by SQL flashback for an incorrect operation.

This method traverses the matching events in the Binlog file in reverse order. It generates SQL statements by reversing the INSERT, UPDATE, and DELETE events.

• The reverse of an INSERT statement is a DELETE statement.
• The reverse of a DELETE statement is a REPLACE statement.
• The reverse of an UPDATE statement is an UPDATE statement that sets the value to its previous state.

How to use: Run the statements on the original table to overwrite the existing data for restoration.

Scenarios: This method is suitable if the data to be restored has no conflicts, or if you can accept that some conflicting data will be overwritten.

This method traverses the matching events in the Binlog file in sequential order. It generates SQL statements by creating a mirror image of the full records for INSERT, UPDATE, and DELETE events.

• The mirror image of an INSERT statement is an INSERT statement.
• The mirror image of a DELETE statement is an INSERT statement.
• The mirror image of an UPDATE statement is an INSERT statement with the value before the change.

How to use: Run the statements on a temporary table to write the raw data from before the incorrect operation. Compare the raw data with the existing data and analyze any conflicts to determine the final data.

Scenarios: This method is suitable if the data to be restored conflicts with the current data.