ApsaraDB RDS:Native Flashback

Feature description

Unintended operations during database O&M may cause serious impacts on your business. In this case, 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

• Your RDS instance runs MySQL 8.0 on RDS Basic Edition, RDS High-availability Edition, or RDS Cluster Edition.

• The minor engine version of your RDS instance is 20210930 or later. For more information about how to view or update the minor engine version, see Update the minor engine version.

Usage notes

• The native flashback feature is supported only for InnoDB tables.

• The native flashback feature consumes additional undo tablespaces. You can configure the innodb_undo_space_supremum_size parameter to specify an undo tablespace. For more information, see Parameters.

• The native flashback feature returns the data at the point in time that is closest to the specified point in time but 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 restore a table. The following code shows the AS OF syntax:

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

The Expression parameter specifies the point in time to which you want to restore 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.

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 to complete data restoration.
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)