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
TRUNCATE operation within the Garbage Collection (GC) lifetime.
The system variable
10m0s) defines the retention time of earlier versions of rows. The current
safePoint of where garabage 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
TRUNCATE statements after the
tikv_gc_safe_point time, you can restore the table using the
FLASHBACK TABLE statement.
FLASHBACK TABLE table_name [TO other_table_name]
FlashbackTableStmt ::= 'FLASHBACK' 'TABLE' TableName FlashbackToNewName TableName ::= Identifier ( '.' Identifier )? FlashbackToNewName ::= ( 'TO' Identifier )?
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
- 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.
Recover the table data dropped by the
DROP TABLE t;
FLASHBACK TABLE t;
Recover the table data dropped by the
TRUNCATEoperation. Because the truncated table
tstill exists, you need to rename the table
tto be recovered. Otherwise, an error will be returned because the table
TRUNCATE TABLE t;
FLASHBACK TABLE t TO t1;
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 TABLEor the
truncate tabletype on table
t. 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 before
tikv_gc_safe_point, it means that the table dropped by the
TRUNCATEoperation 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
- TiDB changes
namein the table's metadata to
t1, 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 table
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
t1 can read the user data of
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.
This statement is a TiDB extension to MySQL syntax.