经历过Oracle的闪回都觉得这是一个救命的功能

• MySQL数据库要想实现这个功能，需要做很多人工的操作。
• 很多数据库都可以利用日志、实时备份、延迟复制等各种组合拳解决Drop table的问题。
• 这就使得类似闪回的功能非常人性化了

Oracle的闪回这里不介绍了

• 能找过过去一定时间内的数据或者drop的表。

看看TiDB的闪回

• mysql> select * from xxg limit 10;
  ±—±-------±-----+
  | id | m | n |
  ±—±-------±-----+
  | 1 | Window | 1600 |
  | 2 | Window | 1600 |
  | 3 | Window | 1600 |
  | 4 | Window | 1600 |
  | 5 | Window | 602 |
  | 6 | Window | 1600 |
  | 7 | Window | 602 |
  | 8 | Window | 1600 |
  | 9 | Window | 602 |
  | 10 | Window | 1600 |
  ±—±-------±-----+
  10 rows in set (0.00 sec)

• 抽样获取10行数据。

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-09 16:13:38 |
±--------------------+
1 row in set (0.00 sec)

• 获取当前时间

mysql> delete from xxg where id=1;
Query OK, 1 row affected (0.01 sec)

• 删除ID=1的一行数据

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-09 16:14:18 |
±--------------------+
1 row in set (0.01 sec)

• 获取当前时间

mysql> update xxg set n=300 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

• 更新ID=2的一行数据

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-09 16:14:43 |
±--------------------+
1 row in set (0.00 sec)

mysql> select * from xxg limit 10;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 300 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
±—±-------±-----+
10 rows in set (0.01 sec)

• 现在数据已经改变了。接下来要看闪回能做什么和不能做什么了。

mysql> select * from xxg as of timestamp ‘2025-06-09 16:12:50’ limit 10;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 1 | Window | 1600 |
| 2 | Window | 1600 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
±—±-------±-----+
10 rows in set (0.01 sec)

• 可以查询到最开始的数据状态。

mysql> select * from xxg as of timestamp ‘2025-06-09 16:14:30’ limit 10;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 1600 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
±—±-------±-----+
10 rows in set (0.01 sec)

• 也可以查询到只删除一条数据的时间点的状态。

mysql> select * from xxg as of timestamp ‘2025-06-09 16:14:50’ limit 10;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 300 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
±—±-------±-----+
10 rows in set (0.00 sec)

mysql> select * from xxg limit 10;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 300 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
±—±-------±-----+
10 rows in set (0.01 sec)

• 当然最后时间也就是现在。

开始数据恢复

mysql> create table t1 as select * from xxg as of timestamp ‘2025-06-09 16:12:50’ limit 10;
ERROR 1105 (HY000): ‘CREATE TABLE … SELECT’ is not implemented yet

• 不支持我们传统的SQL

mysql> create table t1 as select * from xxg where 1=2;
ERROR 1105 (HY000): ‘CREATE TABLE … SELECT’ is not implemented yet

• 即使是做建立一个表结构这样也不行。

mysql> create table t1 as select * from xxg;
ERROR 1105 (HY000): ‘CREATE TABLE … SELECT’ is not implemented yet
mysql>
mysql> show create table xxg\G
*************************** 1. row ***************************
Table: xxg
Create Table: CREATE TABLE xxg (
id int NOT NULL AUTO_INCREMENT,
m varchar(10) DEFAULT NULL,
n int DEFAULT NULL,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=12329325
1 row in set (0.00 sec)

• 只能根据原表的表结构，老老实实的建议个空表。

mysql> create table t1 (
-> id int NOT NULL AUTO_INCREMENT,
-> m varchar(10) DEFAULT NULL,
-> n int DEFAULT NULL,
-> PRIMARY KEY (id));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into t1 select * from xxg as of timestamp ‘2025-06-09 16:12:50’ limit 10;
ERROR 8135 (HY000): can not set different time in the as of

• 采用insert into的方法，不支持。

• 需要用的命令是import into，这里注意后面还要带一个from。特殊的语法。

mysql> import into t1 from select * from xxg as of timestamp ‘2025-06-09 16:12:50’ limit 10;
ERROR 9006 (HY000): GC life time is shorter than transaction duration, transaction starts at 2025-06-09 16:12:50 +0800 CST, GC safe point is 2025-06-09 16:24:55.441 +0800 CST

• 终于解决了语法问题。带来了一个姓问题。显示生命时间太短。想起了我之前Oracle闪回建表的时候，ogg下游undo不足的场景。
• 查到了数据保留时间，默认10分钟。我个人觉得的太短了。正式环境怎么也应该一个小时。

mysql> show variables like ‘tidb_gc_life_time’;
±------------------±------+
| Variable_name | Value |
±------------------±------+
| tidb_gc_life_time | 10m0s |
±------------------±------+
1 row in set (0.00 sec)

• 目前先按照10分钟的进行实践。

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-09 16:50:44 |
±--------------------+
1 row in set (0.00 sec)

mysql> delete from xxg where id<21;
Query OK, 10 rows affected (0.01 sec)

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-09 16:50:58 |
±--------------------+
1 row in set (0.00 sec)

mysql> select * from xxg where id<21;
Empty set (0.01 sec)

mysql> select * from xxg where id<22;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 21 | Window | 1600 |
±—±-------±-----+
1 row in set (0.00 sec)

mysql> import into t1 from select * from xxg as of timestamp ‘2025-06-09 16:45:50’ where id<21;
Query OK, 19 rows affected (0.92 sec)
Records: 19, ID: e8fe2d19-0061-41a3-a4d0-6eab82fd6bbb

mysql> select * from t1;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 300 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
| 12 | Window | 1600 |
| 13 | Window | 602 |
| 14 | Window | 1600 |
| 15 | Window | 1600 |
| 16 | Window | 1600 |
| 17 | Window | 602 |
| 18 | Window | 1600 |
| 19 | Window | 602 |
| 20 | Window | 1600 |
±—±-------±-----+
19 rows in set (0.01 sec)

• 闪回建表成功。

• 下面尝试一下drop和truncate。Oracle的闪回是不支持truncate的。
  mysql> select now();
  ±--------------------+
  | now() |
  ±--------------------+
  | 2025-06-09 16:53:06 |
  ±--------------------+
  1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.52 sec)

mysql> truncate table t2;
Query OK, 0 rows affected (0.52 sec)

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2025-06-09 16:53:56 |
±--------------------+
1 row in set (0.00 sec)

mysql> flashback table t1;
Query OK, 0 rows affected (0.56 sec)

mysql> flashback table t2;
ERROR 1050 (42S01): Table ‘t2’ already exists
mysql> flashback table t2 to t3;
Query OK, 0 rows affected (0.54 sec)

• truncate的闪回要注意，表结构还在。不能直接flashback。需要重命名定向一下。

mysql> select * from t1;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 300 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
| 12 | Window | 1600 |
| 13 | Window | 602 |
| 14 | Window | 1600 |
| 15 | Window | 1600 |
| 16 | Window | 1600 |
| 17 | Window | 602 |
| 18 | Window | 1600 |
| 19 | Window | 602 |
| 20 | Window | 1600 |
±—±-------±-----+
19 rows in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> select * from t3;
±—±-------±-----+
| id | m | n |
±—±-------±-----+
| 2 | Window | 300 |
| 3 | Window | 1600 |
| 4 | Window | 1600 |
| 5 | Window | 602 |
| 6 | Window | 1600 |
| 7 | Window | 602 |
| 8 | Window | 1600 |
| 9 | Window | 602 |
| 10 | Window | 1600 |
| 11 | Window | 1600 |
| 12 | Window | 1600 |
| 13 | Window | 602 |
| 14 | Window | 1600 |
| 15 | Window | 1600 |
| 16 | Window | 1600 |
| 17 | Window | 602 |
| 18 | Window | 1600 |
| 19 | Window | 602 |
| 20 | Window | 1600 |
±—±-------±-----+
19 rows in set (0.01 sec)

• 时间完毕

注意点

• 闪回的生命周期
• import的语法