数据丢失快速恢复的重要性

目的：尽快修复数据，恢复业务
在这里插入图片描述

快速恢复相关技术对比

常用备份恢复技术
在这里插入图片描述

数据快速恢复原理

MVCC 是TiDB数据库原生的一项功能，默认使用无需配置，它使用多个历史快照的方式来维护数据在某个时间点对并发访问的一致性。
在这里插入图片描述

数据恢复前置条件 - GC

SET GLOBAL tidb_gc_life_time = "60m"
-- 查询GC已经清理的时间点
select * from mysql.tidb where variable_name='tikv_gc_safe_point'

在这里插入图片描述

数据快速恢复操作方式

dml方式
- tidb_snapshot 参数
ddl方式
- flashback table ;
- recover table
dml + ddl 方式
- dumpling 工具

设置tidb_snapshot 参数来读取历史数据

步骤一：查看历史数据
set @@tidb_snapshot=‘2020-10-10 10:10:10’
步骤二：对检索的目标结果进行二次处理，应用到目标业务表

flashback table

查询ddl操作时间戳

admin show ddl jobs

设置tidb_snapshot

set @@tidb_snapshot='yyyy-mm-dd hh24:mi:ss'

执行flashback 命令恢复数据

flashback table target_table_name [TO new_table_name]

recover table

适用于drop 操作

recover table table_name;

dumpling – snapshot

确认目标数据恢复的时间戳
dumpling 备份目标数据历史记录

dumpling -h 172.xx.xx.xx -P 4000 -uroot -p -t 32 -F 64MiB -B target_db --snapshot 'yyyy-mm-dd hh24:mi:ss' -o /tmp/xx

Lighting 导入数据

实验

通过设置tidb_snapshot 参数来读取历史数据后进行恢复
1、建表造数

mysql> create table snap_tab(c int);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into snap_tab values(1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from snap_tab;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

2、查看当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-07-16 18:36:20 |
+---------------------+
1 row in set (0.01 sec)

3、修改数据

mysql> update snap_tab set c=22 where c=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from snap_tab;
+------+
| c    |
+------+
|    1 |
|   22 |
|    3 |
+------+
3 rows in set (0.00 sec)

4、查看gc是否满足要求

查看当前gc的还原点
mysql> select * from mysql.tidb where variable_name = 'tikv_gc_safe_point';
+--------------------+-------------------------+--------------------------------------------------------------+
| VARIABLE_NAME      | VARIABLE_VALUE          | COMMENT                                                      |
+--------------------+-------------------------+--------------------------------------------------------------+
| tikv_gc_safe_point | 20230716-08:58:06 -0400 | All versions after safe point can be accessed. (DO NOT EDIT) |
+--------------------+-------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

可以调整gc的范围，放置数据被覆盖
mysql> update mysql.tidb set variable_value='72h' where variable_name='tikv_gc_life_time';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5、恢复数据

设置恢复的数据时间点
mysql> set @@tidb_snapshot='2023-07-16 18:36:20'; 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from snap_tab;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

6、清空tidb_snapshot,查看当前时间点

mysql> set @@tidb_snapshot='';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from snap_tab;
+------+
| c    |
+------+
|    1 |
|   22 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> select * from mysql.tidb where variable_name='tikv_gc_life_time';
+-------------------+----------------+----------------------------------------------------------------------------------------+
| VARIABLE_NAME     | VARIABLE_VALUE | COMMENT                                                                                |
+-------------------+----------------+----------------------------------------------------------------------------------------+
| tikv_gc_life_time | 72h            | All versions within life time will not be collected by GC, at least 10m, in Go format. |
+-------------------+----------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

恢复被Truncate的表
通过设置tidb_snapshot参数来读取历史数据，并用dumpling --snapshot和flashback table恢复被多次truncate 的数据。

1、造数
为了实验效果，多次插入数据和多次截断

mysql> create table trun_tab(c int);
Query OK, 0 rows affected (0.51 sec)

mysql> 
mysql> insert into trun_tab values(1),(2),(3);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from trun_tab;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> truncate table trn_tab;
ERROR 1146 (42S02): Table 'test.trn_tab' doesn't exist
mysql> truncate table trun_tab;
Query OK, 0 rows affected (0.84 sec)

mysql> select * from trun_tab;
Empty set (0.13 sec)

mysql> insert into trun_tab values(4),(5),(6);
Query OK, 3 rows affected (0.33 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from trun_tab;
+------+
| c    |
+------+
|    4 |
|    5 |
|    6 |
+------+
3 rows in set (0.01 sec)

mysql> truncate table trun_tab;
Query OK, 0 rows affected (0.63 sec)

mysql> insert into trun_tab values(7),(8),(9);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from trun_tab;
+------+
| c    |
+------+
|    7 |
|    8 |
|    9 |
+------+
3 rows in set (0.01 sec)

2、确认是否满足GC要求
通过admin show ddl jobs 查看两次Truncate操作发生的时间

mysql> admin show ddl jobs;
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE       | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE  |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
|    119 | test    | trun_tab   | truncate table | public       |         1 |      116 |         0 | 2023-07-16 18:50:52 | 2023-07-16 18:50:52 | 2023-07-16 18:50:52 | synced |
|    117 | test    | trun_tab   | truncate table | public       |         1 |      114 |         0 | 2023-07-16 18:50:17 | 2023-07-16 18:50:17 | 2023-07-16 18:50:18 | synced |
|    115 | test    | trun_tab   | create table   | public       |         1 |      114 |         0 | 2023-07-16 18:49:16 | 2023-07-16 18:49:16 | 2023-07-16 18:49:17 | synced |
|    113 | test    | snap_tab   | create table   | public       |         1 |      112 |         0 | 2023-07-16 18:35:50 | 2023-07-16 18:35:50 | 2023-07-16 18:35:50 | synced |
|    111 | test    | t1         | add index      | public       |         1 |      107 |     10000 | 2023-07-09 04:14:28 | 2023-07-09 04:14:28 | 2023-07-09 04:14:32 | synced |
|    110 | test    | t1         | drop index     | none         |         1 |      107 |         0 | 2023-07-09 04:14:12 | 2023-07-09 04:14:12 | 2023-07-09 04:14:12 | synced |
|    109 | test    | t1         | add index      | public       |         1 |      107 |     10000 | 2023-07-09 04:12:25 | 2023-07-09 04:12:25 | 2023-07-09 04:12:29 | synced |
|    108 | test    | t1         | create table   | public       |         1 |      107 |         0 | 2023-07-09 03:56:43 | 2023-07-09 03:56:43 | 2023-07-09 03:56:43 | synced |
|    106 | test    | t3         | create table   | public       |         1 |      105 |         0 | 2023-07-09 03:41:32 | 2023-07-09 03:49:20 | 2023-07-09 03:49:20 | synced |
|    104 | test    | t1         | drop table     | none         |         1 |       99 |         0 | 2023-07-09 03:39:38 | 2023-07-09 03:49:20 | 2023-07-09 03:49:20 | synced |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
10 rows in set (0.25 sec)

可以调整gc interval time，避免数据的mvcc历史版本清理掉，避免数据恢复

mysql> update mysql.tidb set variable_value='72h' where variable_name='tikv_gc_life_time';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> set session tidb_snapshot=“2023-07-16 18:50:16”;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from trun_tab;
±-----+
| c |
±-----+
| 1 |
| 2 |
| 3 |
±-----+
3 rows in set (0.00 sec)

mysql> exit

3、开始恢复数据

mysql> set session tidb_snapshot="2023-07-16 18:50:16";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from trun_tab;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

恢复第一次Truncate的数据

mysql> flashback table trun_tab to trun_tab_01;
ERROR 1105 (HY000): can not execute write statement when 'tidb_snapshot' is set
因为flashback 语句无法与set tidb_snapshot一起使用。

tiup dumpling -uroot -P4000 -h192.168.16.13 -pAa123ab! --filetype sql -o /tmp/test -r 200000 -F 256MiB -T test.trun_tab --snapshot "2023-07-16 18:50:16"

[root@tidb2 ~]# more /tmp/test/test.trun_tab
test.trun_tab.0000000010000.sql
test.trun_tab-schema.sql
[root@tidb2 ~]# more /tmp/test/test.trun_tab.0000000010000.sql 
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40101 SET NAMES binary*/;
INSERT INTO `trun_tab` VALUES
(1),
(2),
(3);

mysql> source /tmp/test/test.trun_tab.0000000010000.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from trun_tab;
+------+
| c    |
+------+
|    7 |
|    8 |
|    9 |
|    1 |
|    2 |
|    3 |
+------+
6 rows in set (0.01 sec)

6、恢复第二次Truncat的数据

mysql> flashback table trun_tab to trun_tab_02;
Query OK, 0 rows affected (0.31 sec)

mysql> select * from trun_tab_02;
+------+
| c    |
+------+
|    4 |
|    5 |
|    6 |
+------+
3 rows in set (0.01 sec)

mysql> insert trun_tab select * from trun_tab_02; 
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from trun_tab;
+------+
| c    |
+------+
|    7 |
|    8 |
|    9 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
9 rows in set (0.00 sec)

mysql>