Skip to content

Latest commit

 

History

History
106 lines (66 loc) · 5.01 KB

sql-statement-flashback-table.md

File metadata and controls

106 lines (66 loc) · 5.01 KB
Raw
title summary aliases
FLASHBACK TABLE
Learn how to recover tables using the `FLASHBACK TABLE` statement.
/docs/dev/sql-statements/sql-statement-flashback-table/
/docs/dev/reference/sql/statements/flashback-table/

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.

Use the following command to query the TiDB cluster's tikv_gc_safe_point and tikv_gc_life_time. 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.

{{< copyable "sql" >}}

select * from mysql.tidb where variable_name in ('tikv_gc_safe_point','tikv_gc_life_time');

Syntax

{{< copyable "sql" >}}

FLASHBACK TABLE table_name [TO other_table_name]

Synopsis

FlashbackTableStmt:

FlashbackTableStmt

TableName:

TableName

FlashbackToNewName:

FlashbackToNewName

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:

    {{< copyable "sql" >}}

    DROP TABLE t;

    {{< copyable "sql" >}}

    FLASHBACK TABLE t;

  • Recover the table data dropped by the TRUNCATE operation. Because the truncated table t still exists, you need to rename the table t to be recovered. Otherwise, an error will be returned because the table t already exists.

    {{< copyable "sql" >}}

    TRUNCATE TABLE t;

    {{< copyable "sql" >}}

    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:

  1. TiDB searches the recent DDL history jobs and locates the first DDL operation of the DROP TABLE or the truncate table type on table t. If TiDB fails to locate one, an error is returned.
  2. 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 DROP or TRUNCATE operation has been cleaned up by the GC and an error is returned.
  3. TiDB uses the starting time of the DDL job as the snapshot to read historical data and read table metadata.
  4. TiDB deletes GC tasks related to table t in mysql.gc_delete_range.
  5. TiDB changes name in 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 t.

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.

Note:

You cannot use FLASHBACK statements to restore the same deleted table multiple times, because the ID of the restored table is the same ID of the dropped table, and TiDB requires that all existing tables must have a globally unique table ID.

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.