063、故障处理之快速恢复数据

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

目的：尽快修复数据，恢复业务

快速恢复相关技术对比

常用备份恢复技术

数据快速恢复原理

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

1. 查询ddl操作时间戳

admin show ddl jobs

2. 设置tidb_snapshot

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

3. 执行flashback 命令恢复数据

flashback table target_table_name [TO new_table_name]

recover table

• 适用于drop 操作

recover table table_name;

dumpling – snapshot

1. 确认目标数据恢复的时间戳
2. 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

3. 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> select * from trun_tab;
+------+
| c    |
+------+
|    7 |
|    8 |
|    9 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
9 rows in set (0.00 sec)mysql>