ApsaraDB RDS:Native flashback

Prerequisites

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

• Minor engine version is 20210930 or later. For more information, see Update the minor engine version.

• innodb_rds_flashback_task_enabled is set to ON.

• innodb_undo_retention is set to a value greater than 0 (in seconds).

Syntax

The AS OF TIMESTAMP clause extends standard SELECT syntax:

SELECT ... FROM table_name
  AS OF TIMESTAMP expression;

The timestamp expression accepts multiple formats:

Parameters

All four parameters have global scope.

To disable native flashback completely, set both innodb_rds_flashback_task_enabled to OFF and innodb_undo_retention to 0.

Query historical data

The following example recovers data after an accidental bulk update.

1. Record the current timestamp and verify the table contents:

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

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

1. An accidental update overwrites all rows:

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)

1. Use AS OF TIMESTAMP to query data before the update:

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)

If the specified timestamp falls outside the undo retention period, the query returns error 7545:

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 data from a point in time

After verifying the historical data, restore it by copying it into a new table and swapping the table names.

1. Create a temporary table with the same schema as the original:

MySQL [mytest]> create table mt1_tmp like mt1;
Query OK, 0 rows affected (0.03 sec)

1. Insert the historical data into the temporary table:

MySQL [mytest]> insert into mt1_tmp
             -> select * from mt1 AS OF
             -> TIMESTAMP '2020-10-14 15:44:09';
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

1. Verify the data in the temporary table:

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

1. Stop all reads and writes to the original table, then swap the table names:

MySQL [mytest]> rename table mt1 to mt1_bak,
             -> mt1_tmp to mt1;
Query OK, 0 rows affected (0.02 sec)

1. Verify the restored data:

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

Limitations

• Supported only for InnoDB tables.

• Consumes additional undo tablespace. Control storage usage through innodb_undo_space_supremum_size.

• Returns data at the closest available point in time to the specified timestamp. Exact matches are not guaranteed.

• Tables on which DDL operations have been performed cannot be queried or restored. For example, a dropped table cannot be recovered through native flashback.

• Powered by AliSQL and relies on InnoDB undo records. Once undo records are purged -- either by exceeding the retention period or the undo tablespace size limit -- the corresponding historical data is no longer accessible.