数据恢复的典型方式
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';