tencent cloud

Feedback

TDSQL-C for MySQL

Flashback Query

Last updated: 2024-12-26 18:28:37

Overview

Maloperations may occur in the process of database Ops and severely affect the business. Rollback and cloning are common recovery methods for maloperations, but they are error-prone and time-consuming in case of minor data changes and urgent troubleshooting, and are uncontrollable in recovery time when dealing with major data changes. The TXSQL team has developed and implemented the flashback query feature for the InnoDB engine. It allows you to query the historical data before a maloperation with a simple SQL statement and query the data at a specified time point through specific SQL syntax. This greatly saves the data query and recovery time and enables fast data recovery for better business continuity.

Supported Versions

Kernel version: TDSQL-C for MySQL 5.7 2.1.13.001 and later.
Kernel version: TDSQL-C for MySQL 8.0 2.1.13.001 and later.

Use Cases

The flashback query feature is used to quickly query the historical data after a maloperation during database Ops. Notes:
Flashback query is supported only for InnoDB physical tables but not views, other engines, or functions without actual columns such as last_insert_id().
Only second-level flashback query is supported, and the accuracy cannot be fully guaranteed. If there are multiple changes within one second, any of them may be returned.
Flashback query is supported only for primary keys (or GEN_CLUST_INDEX).
Flashback query cannot be used in prepared statements or stored procedures.
Flashback query does not support DDL. If you perform DDL on a table (such as TRUNCATE TABLE, which should be recovered through the recycle bin), the results obtained by flashback query may not be as expected.
In the same statement, if multiple flashback query times are specified for the same table, the earliest time will be selected.
Due to the time difference between the read-write and read-only instances, if you specify the same time for flashback query, the results obtained for the instances may be different.
Enabling the flashback query feature will delay undo log cleanup and increase the memory usage. We recommend that you not set Innodb_backquery_window to a large value (preferably between 900 and 1,800), especially for instances with frequent business access requests.
If the database instance restarts or crashes, the historical information before the restart or crash cannot be queried. The specified time should be within the supported range (which can be viewed through the status variables Innodb_backquery_up_time and Innodb_backquery_low_time by running show status like '%backquery%').

Use Limits

Flashback query provides a new AS OF syntax. You can set the Innodb_backquery_enable parameter to ON to enable the flashback query feature and then query data at the specified time. The syntax involved is as follows:
SELECT ... FROM <table name>
AS OF TIMESTAMP <time>;
Sample of querying data at the 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() |
‌+---------------------+
| 2023-08-17 15:50: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 '2023-08-17 15:50:01';
‌+------+------+
| id | c1 |
‌+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
‌+------+------+
4 rows in set (0.00 sec)
Sample of creating a table from historical data
create table t3 select * from t1 as of timestamp '2023-08-17 15:50:01';
Sample of inserting historical data into a table
insert into t4 select * from t1 as of timestamp '2023-08-17 15:50:01';
Flashback query also supports Persistence, allowing the periodic persistence of readview information in flashback query memory to a physical table. This enables the execution of flashback query even after a restart. This capability requires enabling flashback query and turning on the flashback query persistence switch (turn on persistence switch: set innodb_backquery_persistent = ON); otherwise, it is invalid.
Note:
To use the flashback query persistence capability, the kernel version should be TDSQL-C for MySQL 8.0 3.1.15 or later.

Parameter Description

The following table lists the configurable parameters of the flashback query feature.
Parameter
Scope
Type
Default Value
Value Range/Valid Values
Restart Required
Description
innodb_backquery_enable
Global
Boolean
OFF
ON/OFF
No
The switch of the flashback query feature
‌innodb_backquery_window
Global
Integer
900
1–86400
No
The time range for flashback query in seconds. The larger the value of this parameter, the longer the historical data query time supported for flashback query, and the more storage space used by the undo tablespace.
‌innodb_backquery_history_limit
Global
Integer
8000000
1–9223372036854476000
No
The length of the undo linked list for flashback query. If this value is exceeded, Innodb_backquery_window will be ignored and a purge will be triggered until the historical linked list length is lower than this value.
innodb_backquery_persistent
Non-global paramet
Boolean
OFF
ON/OFF
No
The switch of flashback query persistence.
Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support
Hong Kong, China
+852 800 906 020 (Toll Free)
United States
+1 844 606 0804 (Toll Free)
United Kingdom
+44 808 196 4551 (Toll Free)
Canada
+1 888 605 7930 (Toll Free)
Australia
+61 1300 986 386 (Toll Free)
EdgeOne hotline
+852 300 80699
More local hotlines coming soon