063、故障处理之快速恢复数据-Toy模板网

这篇具有很好参考价值的文章主要介绍了063、故障处理之快速恢复数据。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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

目的：尽快修复数据，恢复业务
063、故障处理之快速恢复数据,TiDB从入门到精通,tidb,数据库

快速恢复相关技术对比

常用备份恢复技术
063、故障处理之快速恢复数据,TiDB从入门到精通,tidb,数据库

数据快速恢复原理

MVCC 是TiDB数据库原生的一项功能，默认使用无需配置，它使用多个历史快照的方式来维护数据在某个时间点对并发访问的一致性。
063、故障处理之快速恢复数据,TiDB从入门到精通,tidb,数据库

数据恢复前置条件 - GC

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

063、故障处理之快速恢复数据,TiDB从入门到精通,tidb,数据库

数据快速恢复操作方式

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的数据文章来源地址https://www.toymoban.com/news/detail-607450.html

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>