网站仿站建设,赚钱的软件,找个公司做网站需要注意什么条件,wordpress主题友情链接设置数据丢失快速恢复的重要性
目的#xff1a;尽快修复数据#xff0c;恢复业务
快速恢复相关技术对比
常用备份恢复技术
数据快速恢复原理
MVCC 是TiDB数据库原生的一项功能#xff0c;默认使用无需配置#xff0c;它使用多个历史快照的方式来维护数据在某个时间点对并…数据丢失快速恢复的重要性
目的尽快修复数据恢复业务
快速恢复相关技术对比
常用备份恢复技术
数据快速恢复原理
MVCC 是TiDB数据库原生的一项功能默认使用无需配置它使用多个历史快照的方式来维护数据在某个时间点对并发访问的一致性。
数据恢复前置条件 - GC
SET GLOBAL tidb_gc_life_time 60m
-- 查询GC已经清理的时间点
select * from mysql.tidb where variable_nametikv_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_snapshotyyyy-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/xxLighting 导入数据
实验
通过设置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 c22 where c2;
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_value72h where variable_nametikv_gc_life_time;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 05、恢复数据
设置恢复的数据时间点
mysql set tidb_snapshot2023-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_nametikv_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 doesnt 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_value72h where variable_nametikv_gc_life_time;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0mysql 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_snapshot2023-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[roottidb2 ~]# more /tmp/test/test.trun_tab
test.trun_tab.0000000010000.sql
test.trun_tab-schema.sql
[roottidb2 ~]# more /tmp/test/test.trun_tab.0000000010000.sql
/*!40014 SET FOREIGN_KEY_CHECKS0*/;
/*!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: 0mysql 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
