SQL Flashback

Background

The PolarDB-X 1.0 SQL flashback feature recovers data at the row level to reverse accidental SQL operations.

You can use the SQL flashback feature if you accidentally run a data modification statement (such as INSERT, UPDATE, or DELETE) on a PolarDB-X 1.0 instance. SQL flashback uses the information you provide about the operation to find matching events in the binary log and generate a restoration file, which you can then download to recover your data.

SQL flashback supports two strategies to locate lost data: fuzzy match and exact match. It can also automatically select the best strategy. For more information, see Key concepts.

To accommodate different use cases, SQL flashback provides two recovery methods: rollback SQL and original SQL.

Benefits

• Easy to use: Simply provide some basic information about the accidental SQL operation to recover your lost data.

• Fast and lightweight: This feature is independent of the backup policy of ApsaraDB RDS for MySQL. You can quickly recover data as long as log backup for ApsaraDB RDS for MySQL was enabled before the accidental operation.

• Flexible recovery methods: Offers both rollback SQL and original SQL to suit different recovery scenarios.

• Precise, SQL-level matching: Precisely locates data from the accidental SQL operation to ensure accurate recovery.

Limitations and notes

• SQL flashback relies on the binary log of your ApsaraDB RDS for MySQL instance, so log backup must be enabled. Because these logs have a limited retention period, generate the restoration file as soon as possible after an accidental operation.

• SQL flashback stores generated restoration files for seven days by default. Download the file promptly after it is generated.

• The exact match feature has the following prerequisites:

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

  • The underlying ApsaraDB RDS for MySQL instance used by PolarDB-X 1.0 runs MySQL 5.6 or later.

  • You must have enabled the exact match feature before the accidental SQL operation occurred.

  • You must provide the TRACE_ID of the accidental SQL operation.

• To ensure data recovery accuracy, the exact match feature is enabled by default for new databases created on PolarDB-X 1.0 instances of version 5.3.4-15378085 or later. When enabled, this feature adds information about executed SQL statements to the ApsaraDB RDS for MySQL binary log, which consumes additional storage space. To use the exact match feature, upgrade your PolarDB-X 1.0 instance and then enable the feature. For more information, see Enable exact match.

Generate a restoration file

To familiarize yourself with the SQL flashback feature, you can follow these steps with a sample SQL statement. The resulting restoration file helps you understand how the feature works.

1. Log on to the PolarDB-X console.

2. Select the region where your instance is located.

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

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

5. Follow the on-screen instructions to fill in the basic information about the accidental SQL operation, including the database, Time Range, Table name, TRACE_ID, and SQL statement type. For information about how to obtain these details, see Locate the accidental SQL operation.

   Parameter descriptions:

   • Time Range: Specifies the approximate time frame when the accidental SQL operation occurred. The start time must be before the operation began, and the end time must be after it finished. For best recovery efficiency, set a precise time range, ideally within 5 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 that was run in error from the PolarDB-X 1.0 SQL audit.

6. Click Precheck to verify if the ApsaraDB RDS for MySQL binary log is available for the specified time range.

   1. If the binary log is not available for the specified time range, the precheck fails, and data cannot be recovered.

   2. If the binary log is available, the precheck passes.

7. Select a recovery method. You can choose either rollback SQL or original SQL. For more information about choosing a method, see Rollback SQL and original SQL.

8. Click Generate SQL to create an SQL flashback task. The SQL Flashback page displays the status of the running task.

9. When the task completes, the page displays details such as whether exact match was used and the number of restored data rows. You can click Download to get the restoration file and proceed with data recovery.

   The restoration file is stored for seven days by default. Download it promptly.

Locate the accidental SQL operation

1. Select the database where the accidental operation occurred.

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

3. Use SQL Audit and Analysis to find the audit record of the accidental SQL operation.

4. Obtain the sql_time, table_name, sql_type, and trace_id fields from the data of the incorrect SQL operation. In the search bar, filter the audit logs based on the __topic__ and sql_type conditions. After you locate the target record of the incorrect SQL operation, take note of the values of the sql_time, sql_type, table_name, and trace_id fields for the subsequent SQL flashback operation.

Enable exact match

Prerequisite: Your PolarDB-X 1.0 instance must be version 5.3.4-15378085 or later. If your instance is an earlier version, we recommend that you upgrade it before you enable this feature.

1. Log on to the PolarDB-X console and go to the Parameter Settings page of your instance. For more information, see Parameter Settings.

2. Set the SQL flashback exact match parameter to ON for the target database.

Key concepts

SQL flashback searches the ApsaraDB RDS for MySQL binary log for matching events based on the information you provide about an accidental operation, and then generates the corresponding recovery SQL. Depending on the instance version and parameter settings, SQL flashback uses one of two matching strategies: exact match or fuzzy match. You do not need to select a strategy. PolarDB-X 1.0 automatically detects and uses the optimal strategy, and notifies you after the flashback task is complete.

Exact Match

This strategy precisely identifies the events in the ApsaraDB RDS for MySQL binary log that correspond to the accidental SQL operation and uses them to generate a restoration file.

Advantage: The restoration file contains only the data affected by the accidental operation. You can use it directly, which ensures high accuracy and efficiency.

Fuzzy Match

This strategy searches the ApsaraDB RDS for MySQL binary log for events that match the general information provided, such as the time range, table name, and SQL type, and then generates a restoration file.

Advantage: This strategy is supported on all instances and does not depend on specific instance versions or parameter settings.

Disadvantage: This strategy cannot precisely isolate the data changes from the accidental operation. The restoration file may include data changes from other SQL operations. You must manually filter the file to find the data you need to restore.

Rollback SQL and original SQL

To support different business scenarios, the PolarDB-X 1.0 SQL flashback feature offers two recovery methods: generating rollback SQL and generating original SQL. Before you generate the restoration file, you must choose the appropriate method based on your use case.

Rollback SQL Statement

Generates SQL by traversing the matching events in the binary log in reverse order and performing reverse operations on INSERT, UPDATE, and DELETE events.

• The inverse of an INSERT operation is DELETE.

• The inverse of a DELETE operation is REPLACE.

• The inverse of an UPDATE operation is another UPDATE that sets the values back to what they were before the change.

Usage: Execute on the Source Table to perform an overwrite recovery on the existing data.

Scenario: Recommended when the data to be restored does not conflict with existing data, or when you can accept overwriting conflicting data.

Original SQL Statement

Sequentially scans the matching events in the binary log and generates SQL statements by creating a mirror of the full record for each INSERT, UPDATE, and DELETE event.

• An INSERT operation is mirrored as an INSERT of the same data.

• A DELETE operation is mirrored as an INSERT of the deleted data.

• An UPDATE operation is mirrored as an INSERT of the data from before the update.

Usage: On the Temporary Tables, write the original data from before the incorrect operation, compare it with the existing data, and analyze any conflicts to determine the final data.

Scenario: Recommended when the data to be restored conflicts with the current data.