FLASHBACK DATABASE
TiDB v6.4.0 introduces the FLASHBACK DATABASE
syntax. You can use FLASHBACK DATABASE
to restore a database and its data that are deleted by the DROP
statement within the Garbage Collection (GC) life time.
You can set the retention time of historical data by configuring the tidb_gc_life_time
system variable. The default value is 10m0s
. You can query the current safePoint
, that is, the time point GC has been performed up to, using the following SQL statement:
SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';
As long as a database is deleted by DROP
after the tikv_gc_safe_point
time, you can use FLASHBACK DATABASE
to restore the database.
Syntax
FLASHBACK DATABASE DBName [TO newDBName]
Synopsis
- FlashbackDatabaseStmt
- FlashbackToNewName
FlashbackDatabaseStmt ::=
'FLASHBACK' DatabaseSym DBName FlashbackToNewName
FlashbackToNewName ::=
( 'TO' Identifier )?
Notes
If the database is deleted before the
tikv_gc_safe_point
time, you cannot restore the data using theFLASHBACK DATABASE
statement. TheFLASHBACK DATABASE
statement returns an error similar toERROR 1105 (HY000): Can't find dropped database 'test' in GC safe point 2022-11-06 16:10:10 +0800 CST
.You cannot restore the same database multiple times using the
FLASHBACK DATABASE
statement. Because the database restored byFLASHBACK DATABASE
has the same schema ID as the original database, restoring the same database multiple times leads to duplicate schema IDs. In TiDB, the database schema ID must be globally unique.When TiDB Binlog is enabled, note the following when you use
FLASHBACK DATABASE
:- The downstream secondary database must support
FLASHBACK DATABASE
. - The GC life time of the secondary database must be longer than that of the primary database. Otherwise, the latency between the upstream and the downstream might lead to data restoration failure in the downstream.
- If TiDB Binlog replication encounters an error, you need to filter out the database in TiDB Binlog and then manually import full data for this database.
- The downstream secondary database must support
Example
Restore the
test
database that is deleted byDROP
:DROP DATABASE test;FLASHBACK DATABASE test;Restore the
test
database that is deleted byDROP
and rename it totest1
:DROP DATABASE test;FLASHBACK DATABASE test TO test1;
MySQL compatibility
This statement is a TiDB extension to MySQL syntax.