专栏 - TiDB中快速恢复被Truncate 的表

一、原理：

1、通过设置 tidb_snapshot 参数来检验历史数据 2、使用dumpling进行数据导出 3、恢复数据

二、准备环境

1、安装集群 1pd+1kv+1tidb-server+1监控

tiup cluster deploy cluster-s1 v5.3.0 /data1/data99_tmp/backuptidb/tidb-cluster-s1.yaml --user root -p

tiup cluster list 
tiup cluster start cluster-s1
tiup cluster display cluster-s1

配置yaml

# # Global variables are applied to all deployments and used as the default value of
# # the deployments if a specific deployment value is missing.
global:
 user: "tidb"
 ssh_port: 22
 deploy_dir: "/tidb-deploys1"
 data_dir: "/tidb-datas1"


server_configs:
 tidb:
   log.slow-threshold: 300
 tikv:
   readpool.storage.use-unified-pool: false
   readpool.coprocessor.use-unified-pool: true
 pd:
   replication.enable-placement-rules: true
   replication.location-labels: ["host"]
# tiflash:
#   logger.level: "info"

pd_servers:
 - host: 10.0.2.15

tidb_servers:
 - host: 10.0.2.15
   #port: 4001
   #status_port: 10081

tikv_servers:
 - host: 10.0.2.15
   port: 30160
   status_port: 30180
   config:
     server.labels: { host: "logic-host-31" }

 #tiflash_servers:
# - host: 10.0.2.15

monitoring_servers:
 - host: 10.0.2.15

grafana_servers:
 - host: 10.0.2.15

2、检测dashboard 3、检测grafana

三、准备数据

3.1、初始化环境

mysql> create table trun_task (id int,name varchar(20));

mysql> insert into trun_task(id,name) values (1,'t1'), (2,'t2'), (3,'t3');

mysql> select * from trun_task;

mysql> select * from trun_task;
+------+------+
| id   | name |
+------+------+
|    1 | t1   |
|    2 | t2   |
|    3 | t3   |
+------+------+
3 rows in set (0.01 sec)

mysql>

3.2、模拟 Truncate 表

1、第一次truncate表

-- truncate 表
mysql>truncate table trun_task;
mysql>select * from trun_task;
-- 插入表覆盖
mysql>insert into trun_task(id,name) values (12,'t12'), (15,'t15'), (17,'t17');
mysql>select * from trun_task;
mysql>select * from trun_task;
+------+------+
| id   | name |
+------+------+
|   12 | t12  |
|   15 | t15  |
|   17 | t17  |
+------+------+
3 rows in set (0.01 sec)

2、第二次truncate表

truncate table trun_task;
select * from trun_task;

-- 插入表覆盖
insert into trun_task(id,name) values (32,'t32'), (35,'t15'), (37,'t37'),(1,'tttt11111');
select * from trun_task;
/*
+------+-----------+
| id   | name      |
+------+-----------+
|   32 | t32       |
|   35 | t15       |
|   37 | t37       |
|    1 | tttt11111 |
+------+-----------+
4 rows in set (0.01 sec)
*/

3.3 数据恢复

前提条件：

1)确认是否满足 GC 要求 admin show ddl jobs; -- 查看两次 Truncate 操作发生的时间

mysql>  admin show ddl jobs;  -- 查看两次 Truncate 操作发生的时间
+--------+---------+----------------------------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME                       | JOB_TYPE       | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | START_TIME          | END_TIME            | STATE  |
+--------+---------+----------------------------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
|     64 | test    | trun_task                        | truncate table | public       |         1 |       61 |         0 | 2022-01-05 12:21:30 | 2022-01-05 12:21:30 | synced |
|     62 | test    | trun_task                        | truncate table | public       |         1 |       59 |         0 | 2022-01-05 12:19:43 | 2022-01-05 12:19:43 | synced |
|     60 | test    | trun_task                        | create table   | public       |         1 |       59 |         0 | 2022-01-05 12:18:11 | 2022-01-05 12:18:11 | synced |
|     58 | test    | snap_user                        | create table   | public       |         1 |       57 |         0 | 2022-01-05 10:32:33 | 2022-01-05 10:32:33 | synced |
|     56 | mysql   | column_stats_usage               | create table   | public       |         3 |       55 |         0 | 2022-01-05 10:25:05 | 2022-01-05 10:25:06 | synced |
|     54 | mysql   | capture_plan_baselines_blacklist | create table   | public       |         3 |       53 |         0 | 2022-01-05 10:25:05 | 2022-01-05 10:25:05 | synced |
|     52 | mysql   | global_grants                    | create table   | public       |         3 |       51 |         0 | 2022-01-05 10:25:05 | 2022-01-05 10:25:05 | synced |
|     50 | mysql   | stats_fm_sketch                  | create table   | public       |         3 |       49 |         0 | 2022-01-05 10:25:04 | 2022-01-05 10:25:05 | synced |
|     48 | mysql   | schema_index_usage               | create table   | public       |         3 |       47 |         0 | 2022-01-05 10:25:04 | 2022-01-05 10:25:04 | synced |
|     46 | mysql   | stats_extended                   | create table   | public       |         3 |       45 |         0 | 2022-01-05 10:25:04 | 2022-01-05 10:25:04 | synced |
+--------+---------+----------------------------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+--------+
10 rows in set (0.03 sec)

2)查看当前 GC 保留的 safe point

mysql> SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';
+--------------------+-------------------------+--------------------------------------------------------------+
| VARIABLE_NAME      | VARIABLE_VALUE          | COMMENT                                                      |
+--------------------+-------------------------+--------------------------------------------------------------+
| tikv_gc_safe_point | 20220105-12:10:07 +0800 | All versions after safe point can be accessed. (DO NOT EDIT) |
+--------------------+-------------------------+--------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> UPDATE mysql.tidb SET VARIABLE_VALUE = '720h' WHERE VARIABLE_NAME = 'tikv_gc_life_time';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM mysql.tidb WHERE variable_name = 'tikv_gc_safe_point';
+--------------------+-------------------------+--------------------------------------------------------------+
| VARIABLE_NAME      | VARIABLE_VALUE          | COMMENT                                                      |
+--------------------+-------------------------+--------------------------------------------------------------+
| tikv_gc_safe_point | 20220105-12:10:07 +0800 | All versions after safe point can be accessed. (DO NOT EDIT) |
+--------------------+-------------------------+--------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_life_time';
+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| tidb_gc_life_time | 720h0m0s |
+-------------------+----------+
1 row in set (0.05 sec)

3.3.1 恢复trun_task 第一次truncate 之前的数据到 trun_task1表

1、思路：

通过恢复 tidb_snap_shot的时间点在第一次truncate之前的时间点来恢复数据

2、步骤

1）确定tidb_snapshot admin show ddl jobs where table_name='trun_task'; 获取第一次truncate的时间点为 2022-01-05 12:19:43，所以选定时间tidb_snapshot的时间点为“ 2022-01-05 12:19:42” set session tidb_snapshot="2022-01-05 12:19:42"; 或 set @@tidb_snapshot="2022-01-05 12:19:42"; select * from trun_task;

mysql> select * from trun_task;
+------+------+
| id   | name |
+------+------+
|    1 | t1   |
|    2 | t2   |
|    3 | t3   |
+------+------+
3 rows in set (0.02 sec)


set @@tidb_snapshot="";

验证数据是否正确

导出脚本首先创建表：

show create table trun_task;

 CREATE TABLE `trun_task` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 

-- 修改表名并执行表【新开mysql client链接创建】
CREATE TABLE `trun_task1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

# 新开shell终端

tiup dumpling \
-u root \
-P 4000 \
--host 127.0.0.1 \
--filetype sql \
-o /data1/data99_tmp/backuptidb/trun_task/ \
-r 200000 \
-F 256MiB \
-T test.trun_task \
--snapshot "2022-01-05 12:19:42"

导入数据到trun_task1表中导出成功后进入/data1/data99_tmp/backuptidb/trun_task/ 目录针对数据文件进行表名替换处理

$ cp test.trun_task.0000000010000.sql  trun_task1.sql
$ sed -i 's/`trun_task`/`trun_task1`/g' trun_task1.sql

可以使用mysql的source命令导入脚本也可以使用功能lightning导入。这里使用mysql source导入

# 需要重新链接mysql客户端
mysql>source /data1/data99_tmp/backuptidb/trun_task/trun_task1.sql;
# 查看导入trun_task1表的数据
mysql> select * from trun_task1;
+------+------+
| id   | name |
+------+------+
|    1 | t1   |
|    2 | t2   |
|    3 | t3   |
+------+------+
3 rows in set (0.01 sec)

3.3.2 恢复trun_task 第二次truncate 之前的数据到 trun_task2表

admin show ddl jobs where table_name='trun_task';

FLASHBACK TABLE trun_task TO trun_task2

注意： FLASHBACK 只能执行一次，再次执行将报错 Table 'trun_task' already been flashback to 'trun_task2', can't be flashback repeatedly

查看trun_task2的数据

mysql> select * from trun_task2;
+------+------+
| id   | name |
+------+------+
|   12 | t12  |
|   15 | t15  |
|   17 | t17  |
+------+------+
3 rows in set (0.01 sec)

3.3.3 恢复2次 trun_task 所有数据到 trun_task 要求，不删除数据，重复的数据以id为唯一保留最新值。

思路：根据第二次truncate恢复的数据表trun_task2，与第一恢复的truncate数据表 trun_task1，把2个表union 与trun_task去差集插入到trun_task表中。 1、先看看三张表的数据各自是。

mysql> select * from trun_task1;
+------+------+
| id   | name |
+------+------+
|    1 | t1   |
|    2 | t2   |
|    3 | t3   |
+------+------+
3 rows in set (0.01 sec)

mysql> 
mysql> select * from trun_task2;
+------+------+
| id   | name |
+------+------+
|   12 | t12  |
|   15 | t15  |
|   17 | t17  |
+------+------+
3 rows in set (0.01 sec)

mysql> select * from trun_task;
+------+-----------+
| id   | name      |
+------+-----------+
|   32 | t32       |
|   35 | t15       |
|   37 | t37       |
|    1 | tttt11111 |
+------+-----------+
4 rows in set (0.01 sec)

2、确认要合并到trun_task的数据

-- 1）合并 trun_task2 和trun_task1中的数据，重复的以trun_task2为准。
select id,name from trun_task2 union select id,name from trun_task1 where id not in(select id from trun_task2);

-- 2）与trun_task取差集
select * from (select id,name from trun_task2 union select id,name from trun_task1 where id not in(select id from trun_task2)) where id not in (select id from trun_task);

-- 3) 差集插入trun_task
select * from trun_task;

insert into `trun_task` (id,name) select id,name from (select id,name from trun_task2 union select id,name from trun_task1 where id not in(select id from trun_task2)) where id not in (select id from trun_task);

select * from trun_task;

执行结果如下：

mysql> select * from trun_task;
+------+-----------+
| id   | name      |
+------+-----------+
|   32 | t32       |
|   35 | t15       |
|   37 | t37       |
|    1 | tttt11111 |
+------+-----------+
4 rows in set (0.01 sec)

mysql> 
mysql> insert into `trun_task` (id,name) select id,name from (select id,name from trun_task2 union select id,name from trun_task1 where id not in(select id from trun_task2)) where id not in (select id from trun_task);

Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from trun_task;
+------+-----------+
| id   | name      |
+------+-----------+
|   32 | t32       |
|   35 | t15       |
|   37 | t37       |
|    1 | tttt11111 |
|    2 | t2        |
|    3 | t3        |
|   15 | t15       |
|   12 | t12       |
|   17 | t17       |
+------+-----------+
9 rows in set (0.01 sec)

与预期一致

3.4 调整 GC interval time 为原值


-- 查看gc 的保留时间
SHOW GLOBAL VARIABLES LIKE 'tidb_gc_life_time';

UPDATE mysql.tidb SET VARIABLE_VALUE = '10m' WHERE VARIABLE_NAME = 'tikv_gc_life_time';

-- 查看gc 的保留时间
SHOW GLOBAL VARIABLES LIKE 'tidb_gc_life_time';

到此操作完成。