How to recover after a DELETE statement?

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

Original topic: delete语句后怎么恢复?

| username: 烂番薯0

Dear experts, I have a question. Without enabling binlog and performing br backup, I executed a delete statement to delete the entire table. Can I use recover table to restore it? If not, how can I recover the data?

| username: Jolyne | Original post link

Before the GC runs, you can see the previous data through the snapshot.

| username: 烂番薯0 | Original post link

How exactly do I operate it, boss?

| username: Jolyne | Original post link

Reading Historical Data Through the System Variable tidb_snapshot

| username: Jolyne | Original post link

I have done this before, but the premise is that the GC has not deleted the data.

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

The function allows you to use the FLASHBACK TABLE statement to restore tables and data that have been deleted by DROP or TRUNCATE within the Garbage Collection (GC) lifetime.
FLASHBACK TABLE table_name [TO other_table_name]

| username: zhanggame1 | Original post link

  1. Reading historical data through the system variable tidb_snapshot

Set a special environment variable, which is a session scope variable, meaning to read the latest version before this time.

set @@tidb_snapshot="2016-10-08 16:45:26";

Clear it:

set @@tidb_snapshot="";

After setting @@tidb_snapshot, executing the select statement will query the data at this point in time.

You can also use:

select * from table1 as of timestamp '2023-07-28 10:24:00';

However, you cannot use insert into select * from to insert data from a flashback query. You can use dumpling to export the table at a specified time.

Set the GC time through tidb_gc_life_time:

set global tidb_gc_life_time=48h;

Check the queryable time range:

SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';
  1. Use the dumping tool to export data at a specific time

For example, to back up the dev_vegas2.game_draw table at a specific time 2023-07-08 10:50:45 with a where condition:

tiup dumpling -uroot -p "XXXX" -P 3390 -h --filetype sql -t 8 -o "/tmp/dumpling" -r 200000 -F256MiB -B dev_vegas2 -T dev_vegas2.game_draw --snapshot "2023-07-28 10:50:45" --where "draw_id = 789"
| username: dba远航 | Original post link

During the Garbage Collection (GC) lifetime (default 10 minutes), use FLASHBACK TABLE table_name [TO other_table_name]

| username: oceanzhang | Original post link

Dumpling adds a snapshot parameter, allowing you to export data from a specified time period.

| username: andone | Original post link

This depends on whether the GC has run.

| username: forever | Original post link

This command is always ready, if data is accidentally deleted, call life first :grin:

| username: TiDBer_gxUpi9Ct | Original post link

How to operate this?

| username: 随缘天空 | Original post link

The flashback command can restore deleted tables and databases, provided that the GC has not yet cleaned up the data. By default, this means that the data can be restored within 10 minutes after the deletion operation.

| username: andone | Original post link

Perform recovery using FLASHBACK TABLE.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.