How to Instantly Restore a Dropped Table in TiDB

Key Takeaways

• FLASHBACK TABLE recovers a dropped table in under a second with all data and schema intact.
• Recovery works at table, database, and cluster scope via FLASHBACK TABLE, FLASHBACK DATABASE, and FLASHBACK CLUSTER.
• Stale Read queries let you inspect any historical version of a table within the GC lifetime window.
• The default recovery window is 10 minutes (configurable via tidb_gc_life_time), so this feature complements — but doesn’t replace — regular backups.

Dropping a table by mistake is one of those worst-case scenarios every database operator dreads. Maybe you thought the table wasn’t in use anymore. Maybe you were connected to the wrong environment. Either way, the moment you realize what happened, the clock starts ticking.

With traditional databases like MySQL, recovering a dropped table means restoring from your last backup and rolling forward through binlogs to the point just before the drop. That process can take hours depending on the size of your data — and every minute counts when your application is down.

TiDB takes a fundamentally different approach. Thanks to its LSM-Tree storage architecture, TiDB can restore a dropped table instantly with a single SQL command — no backups, no binlog replay, no downtime.

How to Recover a Dropped Table in TiDB

Here’s what instant table recovery looks like in TiDB using FLASHBACK TABLE:

-- Verify the table exists
tidb> TABLE auth_tokens;
+------+
| id   |
+------+
| 1234 |
+------+
1 row in set (0.003 sec)

-- Accidentally drop the table
tidb> DROP TABLE auth_tokens;
Query OK, 0 rows affected (0.152 sec)

-- The table is gone
tidb> TABLE auth_tokens;
ERROR 1146 (42S02): Table 'test.auth_tokens' doesn't exist

-- Restore it instantly
tidb> FLASHBACK TABLE auth_tokens;
Query OK, 0 rows affected (0.137 sec)

-- Data fully recovered
tidb> TABLE auth_tokens;
+------+
| id   |
+------+
| 1234 |
+------+
1 row in set (0.003 sec)

The entire recovery takes a fraction of a second. No restore pipeline, no application downtime, no scramble to find your most recent backup.

FLASHBACK for Databases and Clusters

FLASHBACK TABLE operates on individual tables, but TiDB extends the same recovery capability across broader scopes:

• FLASHBACK DATABASE restores an entire dropped schema at once.
• FLASHBACK CLUSTER TO TIMESTAMP rolls back the full cluster to a specific point in time.

Both FLASHBACK TABLE and FLASHBACK DATABASE let you restore to an alternative name, which is useful when the original table or schema has already been recreated.

Querying Historical Data with Stale Reads

TiDB doesn’t just let you restore dropped tables. It also lets you query any version of a table within the garbage collection (GC) lifetime, including data and columns that have since been changed or removed:

SELECT * FROM my_table AS OF TIMESTAMP '2025-03-15 10:30:00';

You can also use AS OF TIMESTAMP with START TRANSACTION for read-only transactions, giving you a consistent snapshot of your data at any past point.

This time-travel capability extends to TiDB Dumpling, TiDB’s logical data export tool. With Dumpling, you can export not just the latest version of a table, but any earlier version within the GC window. This is useful for auditing, debugging, or recovering specific records without a full restore.

Limitations and Trade-Offs

While FLASHBACK TABLE is powerful, it’s not a substitute for backups. Backups protect you against scenarios that flashback can’t address, such as disk failures and software bugs that cause data corruption.

The flashback feature depends on TiDB’s GC lifetime setting (tidb_gc_life_time), which defaults to 10 minutes. You can only recover a dropped table if you act within this window. Many operators increase the GC lifetime to give themselves more room, but there are trade-offs to consider:

• Storage reclamation slows down. All dropped data is retained until it ages past the GC lifetime, so disk space isn’t freed as quickly after dropping tables or deleting rows.
• Query overhead increases. Read queries may need to skip over more deleted row versions (tombstones). The TiKV MVCC In-Memory Engine can help mitigate this, though it’s not enabled by default.

Why TiDB Can Restore Dropped Tables Instantly

TiDB’s ability to offer instant table recovery comes directly from its storage architecture. Understanding why requires a quick look at how TiKV — TiDB’s distributed SQL storage layer — manages data.

How TiKV Stores Data with LSM-Trees

TiKV stores table data as key-value pairs, with each row prefixed by a table and row identifier:

Key	Value
t_101_r_12345	{"test", 1000, "this is a test"}

Every key-value pair also carries a timestamp, generated by TiDB’s Placement Driver (PD) acting as a Timestamp Oracle (TSO). These timestamps identify both individual data versions and the transactions that created them.

Under the hood, each TiKV node uses RocksDB, which implements a Log Structured Merge-Tree (LSM-Tree). This is a fundamentally different structure from the B+Tree that MySQL’s InnoDB uses.

In an LSM-Tree, data flows through layers. New writes land in an in-memory MemTable (also persisted to disk for durability). When the MemTable fills up, it flushes to Level 0 on disk. Over time, a process called compaction merges data from one level into the next.

The key insight: Updates and deletes don’t modify data in place. An update writes a new version of the row with a newer timestamp. A delete writes a tombstone, a marker indicating the row was removed. Previous versions remain in storage until compaction cleans them up after the GC lifetime expires.

For example, consider these three operations:

INSERT INTO demo1 VALUES (123, 'abc', 'first test');
UPDATE demo1 SET description = 'second test' WHERE id = 123;
DELETE FROM demo1 WHERE id = 123;

Internally, TiKV stores all three versions:

Timestamp	Key	Value
100	t_1_r_123	abc, first test
150	t_1_r_123	abc, second test
160	t_1_r_123	<tombstone>

A transaction at timestamp 155 would see the row with second test as the description. A transaction at timestamp 100 would see first test. And this is exactly what makes flashback possible: The data is still there.

FLASHBACK TABLE identifies the timestamp of the drop operation and recovers the table’s state from just before that point. Because the old versions haven’t been garbage collected yet, the recovery is instantaneous.

Recovering a Dropped Table in MySQL vs. TiDB

MySQL with InnoDB doesn’t have an equivalent feature. When InnoDB drops a table (using the default file-per-table layout), it unlinks the tablespace file and evicts related pages from the Buffer Pool. To recover, you need to restore from a backup (using MySQL Enterprise Backup, Percona XtraBackup, or similar) and replay binlogs up to the point before the drop. This is a process that can take significant time depending on your data volume.

In theory, MySQL could keep dropped table files around temporarily. But this gets complicated in practice, especially because many teams use external schema migration tools like gh-ost or pt-online-schema-change, which create temporary tables during DDL operations. Retaining those would consume significant storage with no benefit.

A common workaround is to rename tables before dropping them (e.g., _delete_after_20251101__mytable) and then wait before actually deleting. This provides a manual safety net, but it doesn’t support per-schema recovery, doesn’t let you query historical versions of existing tables, and may require coordination with your application layer.

With TiDB, none of these workarounds are necessary. FLASHBACK TABLE gives you sub-second table recovery built directly into the database.

Try FLASHBACK TABLE Yourself

FLASHBACK TABLE is available in TiDB, along with FLASHBACK DATABASE, FLASHBACK CLUSTER, and Stale Read queries for inspecting historical data. You can find full syntax and examples in the FLASHBACK TABLE documentation.

The fastest way to see it in action is to spin up a free TiDB cluster, drop a table, and recover it in under a second. Start a free TiDB Cloud Starter cluster today and try it for yourself.