TiDB的闪回

经历过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 TABLExxg( idint NOT NULL AUTO_INCREMENT, mvarchar(10) DEFAULT NULL, nint 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的语法