FLASHBACK TABLE
The FLASHBACK TABLE
syntax is introduced since TiDB 4.0. You can use the FLASHBACK TABLE
statement to restore the tables and data dropped by the DROP
or TRUNCATE
operation within the Garbage Collection (GC) lifetime.
The system variable tidb_gc_life_time
(default: 10m0s
) defines the retention time of earlier versions of rows. The current safePoint
of where garbage collection has been performed up to can be obtained with the following query:
SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';
As long as the table is dropped by DROP
or TRUNCATE
statements after the tikv_gc_safe_point
time, you can restore the table using the FLASHBACK TABLE
statement.
Syntax
FLASHBACK TABLE table_name [TO other_table_name]
Synopsis
- FlashbackTableStmt
- TableName
- FlashbackToNewName
FlashbackTableStmt ::=
'FLASHBACK' 'TABLE' TableName FlashbackToNewName
TableName ::=
Identifier ( '.' Identifier )?
FlashbackToNewName ::=
( 'TO' Identifier )?
Notes
If a table is dropped and the GC lifetime has passed, you can no longer use the FLASHBACK TABLE
statement to recover the dropped data. Otherwise, an error like Can't find dropped / truncated table 't' in GC safe point 2020-03-16 16:34:52 +0800 CST
will be returned.
Pay attention to the following conditions and requirements when you enable TiDB Binlog and use the FLASHBACK TABLE
statement:
- The downstream secondary cluster must also support
FLASHBACK TABLE
. - The GC lifetime of the secondary cluster must be longer than that of the primary cluster.
- The delay of replication between the upstream and downstream might also cause the failure to recover data to the downstream.
- If an error occurs when TiDB Binlog is replicating a table, you need to filter that table in TiDB Binlog and manually import all data of that table.
Example
Recover the table data dropped by the
DROP
operation:DROP TABLE t;FLASHBACK TABLE t;Recover the table data dropped by the
TRUNCATE
operation. Because the truncated tablet
still exists, you need to rename the tablet
to be recovered. Otherwise, an error will be returned because the tablet
already exists.TRUNCATE TABLE t;FLASHBACK TABLE t TO t1;
Implementation principle
When deleting a table, TiDB only deletes the table metadata, and writes the table data (row data and index data) to be deleted to the mysql.gc_delete_range
table. The GC Worker in the TiDB background periodically removes from the mysql.gc_delete_range
table the keys that exceed the GC lifetime.
Therefore, to recover a table, you only need to recover the table metadata and delete the corresponding row record in the mysql.gc_delete_range
table before the GC Worker deletes the table data. You can use a snapshot read of TiDB to recover the table metadata. For details of snapshot read, refer to Read Historical Data.
The following is the working process of FLASHBACK TABLE t TO t1
:
- TiDB searches the recent DDL history jobs and locates the first DDL operation of the
DROP TABLE
or thetruncate table
type on tablet
. If TiDB fails to locate one, an error is returned. - TiDB checks whether the starting time of the DDL job is before
tikv_gc_safe_point
. If it is beforetikv_gc_safe_point
, it means that the table dropped by theDROP
orTRUNCATE
operation has been cleaned up by the GC and an error is returned. - TiDB uses the starting time of the DDL job as the snapshot to read historical data and read table metadata.
- TiDB deletes GC tasks related to table
t
inmysql.gc_delete_range
. - TiDB changes
name
in the table's metadata tot1
, and uses this metadata to create a new table. Note that only the table name is changed but not the table ID. The table ID is the same as that of the previously dropped tablet
.
From the above process, you can see that TiDB always operates on the metadata of the table, and the user data of the table has never been modified. The restored table t1
has the same ID as the previously dropped table t
, so t1
can read the user data of t
.
The FLASHBACK TABLE
operation is done by TiDB obtaining the table metadata through snapshot read, and then going through the process of table creation similar to CREATE TABLE
. Therefore, FLASHBACK TABLE
is, in essence, a kind of DDL operation.
MySQL compatibility
This statement is a TiDB extension to MySQL syntax.