Feature Introduction
During database Ops, misoperations may occur, potentially causing severe business impacts. When such incidents affect operations, common recovery methods include rollback and cloning. However, for minor data changes or urgent fixes, these approaches are error-prone and time-consuming, with uncontrollable recovery times for large datasets.
The TXSQL team designed and implemented the flashback query feature on the Innodb engine. Using simple SQL statements, historical data prior to misoperations can be retrieved. This allows querying data at specific timestamps through dedicated SQL syntax, significantly reducing data retrieval and recovery time. Consequently, post-misoperation data can be rapidly restored, enabling swift business continuity.
Supported Versions
Kernel version MySQL 5.7 20230601 and above.
Kernel version MySQL 8.0 20220331 and above.
Applicable Scenarios
The flashback query feature is used to quickly query historical data after misoperations during database Ops.
When the feature is used, note the following:
Only supports physical tables in Innodb; does not support views or other engines, nor functions like last_insert_id() that lack corresponding actual columns.
Supports only second-level flashback queries and does not guarantee 100% accuracy. If multiple changes occur within one second, any one of them may be retrieved.
Flashback query only supports primary keys (or GEN_CLUST_INDEX).
Does not support usage in prepared statements and stored procedures.
DDL is not supported. If DDL operations are performed on a table (such as truncate table, which is recommended to be restored via the recycle bin), the results obtained through flashback query may not meet expectations.
In the same statement, if multiple flashback query times are specified for the same table, the time farthest from the current query time will be selected.
Due to the time difference between primary and secondary instances, flashback queries performed at the same specified time may yield different results.
Enabling flashback query will delay undo log cleanup and increase memory usage. It is not recommended to set the Innodb_backquery_window parameter too large (suggested range: 900 to 1800), especially for instances with heavy business traffic.
If the database instance restarts or crashes, historical information before it restarts or crashes cannot be queried. You need to specify a time within the supported range (the supported range can be checked via the status variables Innodb_backquery_up_time and Innodb_backquery_low_time by executing show status like '%backquery%').
Usage Instructions
The flashback query feature introduces the new AS OF syntax. Set the Innodb_backquery_enable parameter to ON in the parameter settings to enable the flashback query feature, allowing you to query data at specific times using this dedicated syntax. The syntax is as follows:
SELECT ... FROM <table_name>
AS OF TIMESTAMP <time>;
Example: Querying at a Specified Time
MySQL [test]> create table t1(id int,c1 int) engine=innodb;
Query OK, 0 rows affected (0.06 sec)
MySQL [test]> insert into t1 values(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
MySQL [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2024-10-18 16:01:01 |
+---------------------+
1 row in set (0.00 sec)
MySQL [test]> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)
MySQL [test]> select * from t1;
+------+------+
| id | c1 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)
MySQL [test]> select * from t1 as of timestamp '2024-10-18 16:01:01';
+------+------+
| id | c1 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------+
4 rows in set (0.00 sec)
Example: Creating a Table Through Historical Data
create table t3 select * from t1 as of timestamp '2024-10-18 16:01:01';
Example: Inserting Historical Data into a Table
insert into t4 select * from t1 as of timestamp '2024-10-18 16:01:01';
Parameter Description
The following table lists the descriptions of configurable parameters for the flashback query feature.
|
| | | | | | The switch for the flashback query feature. |
| | | | | | The time range supported for flashback queries, unit: seconds. The larger this parameter value, the longer the historical data query period supported by flashback queries, while the storage space occupied by the undo tablespace will also increase. |
Innodb_backquery_history_limit | | | | | | The length limit of the undo historical linked list. If it exceeds the set value, it will ignore Innodb_backquery_window and trigger purge until the length falls below the set value. |