TiDB实战篇-数据丢失快速恢复

最新推荐文章于 2024-05-30 15:39:45 发布

工作变成艺术

最新推荐文章于 2024-05-30 15:39:45 发布

阅读量619

点赞数 1

分类专栏： TiDB入门到小工文章标签： tidb

本文链接：https://blog.csdn.net/S1124654/article/details/130455078

版权

TiDB入门到小工专栏收录该内容

45 篇文章 1 订阅

订阅专栏

数据恢复的典型方式

MVCC原理

数据恢复的前提GC

数据快速恢复的方法

tidb_snapshot方法

会话级别

DDL操作

dumping

实操

DML快照恢复方法

创建测试表

#创建测试表
mysql -h192.168.66.10 -P4000 -uroot -ptidb
use test;
create table snap_tab(c int);
insert into snap_tab values(1),(2),(3);
select * from snap_tab;
select now();

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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-05-01 12:43:13 |
+---------------------+
1 row in set (0.00 sec)

模拟误操作

#模糊错误的操作
select now();

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-05-01 12:49:32 |
+---------------------+
1 row in set (0.00 sec)

begin;
update snap_tab set c=22 where c=2;
commit;
select * from snap_tab;

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

恢复数据

#恢复数据
#查看安全点
select * from mysql.tidb where variable_name='tikv_gc_safe_point';
select * from mysql.tidb where variable_name= 'tikv_gc_life_time';
#避免操作过慢
update mysql.tidb set variable_value= '36h' where variable_name='tikv_gc_life_time';

set @@tidb_snapshot="2023-05-01 12:49:32";
select * from snap_tab;

mysql> set @@tidb_snapshot="2023-05-01 12:49:32";
Query OK, 0 rows affected (0.00 sec)

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

#如果上面业务看到数据没有什么问题，把tidb_snapshot设置成当前的时间，为空就是当前时间
set @@tidb_snapshot="";
select * from snap_tab;

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)
#然后把数据改过来就行了
update snap_tab set c=2 where c=22;
#还原到最初的设置
update mysql.tidb set variable_value= '10m0s' where variable_name='tikv_gc_life_time';
select * from mysql.tidb where variable_name= 'tikv_gc_life_time';
select * from snap_tab;


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

mysql> update mysql.tidb set variable_value= '10m0s' where variable_name='tikv_gc_life_time';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

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

DDL数据恢复

添加模拟数据

#创建测试表
mysql -h192.168.66.10 -P4000 -uroot -ptidb
use test;
create table trun_tab(c int);
insert into trun_tab values(1),(2),(3);
select * from trun_tab;
mysql> select * from trun_tab;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

模式删除数据

#模式删除数据
truncate table trun_tab;
insert into trun_tab values(4),(5),(6);
truncate table trun_tab;
insert into trun_tab values(7),(8),(9);
select * from trun_tab;
mysql> select * from trun_tab;
+------+
| c    |
+------+
|    7 |
|    8 |
|    9 |
+------+
3 rows in set (0.01 sec)

恢复数据

#恢复数据
admin show ddl jobs where table_name='trun_tab';

mysql> admin show ddl jobs where table_name='trun_tab';
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE       | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE  |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
|    103 | test    | trun_tab   | truncate table | public       |         1 |      100 |         0 | 2023-05-01 13:15:57 | 2023-05-01 13:15:57 | 2023-05-01 13:15:58 | synced |
|    101 | test    | trun_tab   | truncate table | public       |         1 |       98 |         0 | 2023-05-01 13:15:57 | 2023-05-01 13:15:57 | 2023-05-01 13:15:57 | synced |
|     99 | test    | trun_tab   | create table   | public       |         1 |       98 |         0 | 2023-05-01 13:01:12 | 2023-05-01 13:01:12 | 2023-05-01 13:01:13 | synced |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
3 rows in set (0.01 sec)

#查看安全点的时间
select * from mysql.tidb where variable_name='tikv_gc_safe_point';
#设置gc清理时间
update mysql.tidb set variable_value= '36h' where variable_name='tikv_gc_life_time';
select * from mysql.tidb where variable_name= 'tikv_gc_life_time';

#设置第一个truncate table往前一点点的恢复时间
set @@tidb_snapshot="2023-05-01 13:15:55";
select * from trun_tab;
mysql> select * from trun_tab;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

#使用dumping工具把丢失的数据找回
tiup dumpling -uroot -ptidb -P4000 --host 192.168.66.10 --filetype sql -o /tmp/test -r 200000 -F 256MiB -T test.trun_tab --snapshot "2023-05-01 13:15:55";
cd /tmp/test/
[root@master test]# cat test.trun_tab.0000000010000.sql 
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40101 SET NAMES binary*/;
INSERT INTO `trun_tab` VALUES
(1),
(2),
(3);
source /tmp/test/test.trun_tab.0000000010000.sql

#恢复上一个truncate数据
方法一：重复使用dumpling设置不同的snapshot
tiup dumpling -uroot -ptidb -P4000 --host 192.168.66.10 --filetype sql -o /tmp/test1 -r 200000 -F 256MiB -T test.trun_tab --snapshot "2023-05-01 13:15:58";
方法二:
set @@tidb_snapshot="";
flashback table trun_tab to trun_tab_02;
select * from trun_tab_02;

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

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

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

#恢复默认设置
update mysql.tidb set variable_value= '10m0s' where variable_name='tikv_gc_life_time';
select * from mysql.tidb where variable_name= 'tikv_gc_life_time';