This topic describes the native flashback feature. This feature allows you to query or restore the data at a specified point in time by executing SQL statements. This way, you can obtain the historical data at your earliest opportunity after accidental operations.

Background information

Accidental operations during database O&M may have a serious impact on your business. You can restore the data from binary log files. This restoration method is complicated, time-consuming, and prone to errors. You can also restore the data from data backup files. This restoration method requires additional database resources and may require a long period of time if you want to restore a large amount of data.

The native flashback feature of AliSQL is supported by InnoDB. You can query or restore the historical data within a short period of time by executing simple SQL statements. This way, you can ensure the stability of your database service.

Prerequisites

Precautions

  • The native flashback feature is supported only for InnoDB tables.
  • The native flashback feature consumes undo tablespaces. You can configure the INNODB_UNDO_SPACE_SUPREMUM_SIZE parameter to specify an undo tablespace. For more information, see the "Parameters" section of this topic.
  • The query results provided by the native flashback feature are the data at the point in time that is closest to the specified point in time. The native flashback feature cannot ensure that the query results exactly match the data at the specified point in time.
  • You cannot use the native flashback feature to query or restore the data of tables on which DDL operations have been performed. For example, you cannot use the native flashback feature to query the data of a deleted table.

Syntax

The native flashback feature provides a new AS OF syntax, which is used to specify the point in time to which you want to roll back a table. The AS OF syntax has the following rules:

SELECT ... FROM <The name of the table>
  AS OF TIMESTAMP <Expression>;

The expression specifies the point in time to which you want to roll back a table and supports multiple formats. Examples:

SELECT ... FROM tablename
  AS OF TIMESTAMP '2020-11-11 00:00:00';

SELECT ... FROM tablename
  AS OF TIMESTAMP now();

SELECT ... FROM tablename
  AS OF TIMESTAMP (SELECT now());

SELECT ... FROM tablename
  AS OF TIMESTAMP DATE_SUB(now(), INTERVAL 1 minute);

Parameters

The following table describes the parameters of the native flashback feature.

Parameter Description
INNODB_RDS_FLASHBACK_TASK_ENABLED
  • This parameter specifies whether to enable the native flashback feature.
  • Command format: --innodb-rds-flashback-task-enabled=#.
  • Parameter range: a global parameter.
  • Data type: BOOLEAN.
  • Default value: OFF.
  • Valid values: ON and OFF.
Note If you want to disable the native flashback feature, you must set this parameter to OFF and set the INNODB_UNDO_RETENTION parameter to 0.
INNODB_UNDO_RETENTION
  • This parameter specifies the retention period of undo records. Undo records that are generated after the retention period elapses cannot be queried. Unit: seconds.
  • Command format: --innodb-undo-retention=#.
  • Parameter range: a global parameter.
  • Data type: INTEGER.
  • Default value: 0.
  • Valid values: 0 to 4294967295.
Note
  • A larger value of this parameter indicates that the native flashback feature supports queries of earlier historical data and a larger amount of storage is occupied by the undo tablespace.
  • If you set the INNODB_RDS_FLASHBACK_TASK_ENABLED parameter to OFF, you must also set the INNODB_UNDO_RETENTION parameter to 0.
INNODB_UNDO_SPACE_SUPREMUM_SIZE
  • This parameter specifies the maximum disk space that can be occupied by the undo tablespace. Unit: MB. When the maximum size is reached, the undo records are forcibly deleted regardless of the value of the INNODB_UNDO_RETENTION parameter.
  • Command format: --innodb-undo-space-supremum-size=#.
  • Parameter range: a global parameter.
  • Data type: INTEGER.
  • Default value: 10240.
  • Valid values: 0 to 4294967295.
INNODB_UNDO_SPACE_RESERVED_SIZE
  • This parameter specifies the amount of disk space that is reserved for the undo tablespace. Unit: MB. If the value of the INNODB_UNDO_RETENTION parameter is not 0, a larger value of the INNODB_UNDO_SPACE_RESERVED_SIZE parameter indicates more undo records that can be reserved in the undo tablespace.
  • Command format: --innodb-undo-space-reserved-size=#.
  • Parameter range: a global parameter.
  • Data type: INTEGER.
  • Default value: 0.
  • Valid values: 0 to 4294967295.
Note If you set this parameter to a large value, a large number of undo records are reserved. As a result, the performance of your RDS instance decreases. We recommend that you set this parameter to 0.

Examples

# Obtain a point in time.
MySQL [mytest]> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-10-14 15:44:09 |
+---------------------+
1 row in set (0.00 sec)

# Query the data of a table.
MySQL [mytest]> select * from mt1;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

# Perform an update operation that does not contain the WHERE clause.
MySQL [mytest]> update mt1 set c1 = 100;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

MySQL [mytest]> select * from mt1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
|  2 |  100 |
|  3 |  100 |
|  4 |  100 |
|  5 |  100 |
+----+------+
5 rows in set (0.00 sec)

# Query the historical data at a point in time. A result is successfully returned.
MySQL [mytest]> select * from mt1 AS OF timestamp '2020-10-14 15:44:09';
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

# Query the historical data at a point in time that is not within the retention period of historical data. An error message is returned.
MySQL [mytest]> select * from mt1 AS OF timestamp '2020-10-13 14:44:09';
ERROR 7545 (HY000): The snapshot to find is out of range

# Restore the data of a table.
MySQL [mytest]> create table mt1_tmp like mt1; # Create a temporary table that uses the same schema as the original table.
Query OK, 0 rows affected (0.03 sec)  
MySQL [mytest]> insert into mt1_tmp
             -> select * from mt1 AS OF
             -> TIMESTAMP '2020-10-14 15:44:09'; # Write historical data from the original table to the temporary table. 
Query OK, 5 rows affected (0.01 sec) 
Records: 5  Duplicates: 0  Warnings: 0 
MySQL [mytest]> select * from mt1_tmp; # Verify the data in the temporary table. 
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec) 
MySQL [mytest]> rename table mt1 to mt1_bak,
             -> mt1_tmp to mt1; #(Before you perform this operation, you must stop the read and write operations on the original table.) Change the name of the original table to mt1_bak and change the name of the temporary table to the original name of the original table. The restoration process is complete.
Query OK, 0 rows affected (0.02 sec)
MySQL [mytest]> select * from mt1; # Verify that the data is correct after the restoration process. 
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+ 
5 rows in set (0.01 sec)