How to Recover Accidentally Deleted Data in TiDB

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB误删数据怎么恢复

| username: TiDBer_NxOGHZx6

If a table is deleted from the database and then a new identical table is created, can the data be recovered using flashback if it is still within the GC time period?

| username: Billmay表妹 | Original post link

What specific operations did you perform? Please share the deployment configuration diagram~

| username: TiDBer_QYr0vohO | Original post link

You can check this out: 专栏 - TiDB误删数据恢复 | TiDB 社区

| username: 胡杨树旁 | Original post link

Sure, first increase the GC time to prevent data from being GC’d. Set the snapshot to the time period closest to the misoperation using set session tidb_snapshot = ****, query the data, and restore the queried data as needed.

| username: tidb菜鸟一只 | Original post link

Sure, first roll back the snapshot to the point in time when the original table existed, then export the corresponding table. After canceling the snapshot, re-import the original table.

| username: TIDB-Learner | Original post link

You can use the flashback feature to recover data before GC.

| username: 像风一样的男子 | Original post link

You can still export data backups at a specified time before GC.
./dumpling --snapshot “2024-07-02 17:12:45”

| username: zhanggame1 | Original post link

This is simple, FLASHBACK TABLE table_name [TO other_table_name]
The above methods are too complicated.

| username: zhanggame1 | Original post link

FLASHBACK TABLE | PingCAP Documentation Center

| username: DBAER | Original post link

Sure. Generally, having a larger GC is better; otherwise, the recovery time from accidental operations can be a bit troublesome.

| username: linnana | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: yytest | Original post link

Check if MVCC has saved historical data.

| username: 呢莫不爱吃鱼 | Original post link

Increase the GC time to give yourself more leeway :see_no_evil:

| username: TiDBer_HUfcQIJx | Original post link

Flashback Table

| username: TiDBer_vJGTQABF | Original post link

Set the GC time a bit longer, it will come back.

| username: FutureDB | Original post link

The solution in this blog post is quite comprehensive.

| username: wangkk2024 | Original post link

The theory is feasible.

| username: 健康的腰间盘 | Original post link

Set GC to 12 hours

| username: 不想干活 | Original post link

First, increase the GC interval, then use flashback to restore.

| username: 这里介绍不了我 | Original post link

First, adjust the GC time to be longer. If the data has not been GC’d, you can use dumpling+lightning or flashback for operations later.