概述
本实验通过设置tidb_snapshot参数来读历史数据进行后台数据恢复；并用dumpling --snapshot和FLASHBACK TABLE恢复多次TRUNCATE的数据。
一、通过设置tidb_snapshot参数进行数据恢复
1.创建实验表，并写入实验数据
[root@node1 ~]# mysql -uroot -P4000 -h192.168.59.27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 409
Server version: 5.7.25-TiDB-v6.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test3;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table snap_tab(c int);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into snap_tab values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from snap_tab;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-04-09 16:38:17 |
+---------------------+
1 row in set (0.00 sec)2.模拟数据更新，修改一行数据
mysql> update snap_tab set c=22 where c=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from snap_tab;
+------+
| c |
+------+
| 1 |
| 22 |
| 3 |
+------+
3 rows in set (0.00 sec)3.确定是否满足GC要求
mysql> select * from mysql.tidb where variable_name = 'tikv_gc_safe_point';
+--------------------+-------------------------+--------------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE | COMMENT |
+--------------------+-------------------------+--------------------------------------------------------------+
| tikv_gc_safe_point | 20220409-16:26:58 +0800 | All versions after safe point can be accessed. (DO NOT EDIT) |
+--------------------+-------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.tidb where variable_name = 'tikv_gc_life_time';
+-------------------+----------------+----------------------------------------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE | COMMENT |
+-------------------+----------------+----------------------------------------------------------------------------------------+
| tikv_gc_life_time | 10m | All versions within life time will not be collected by GC, at least 10m, in Go format. |
+-------------------+----------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)4.开始设置tidb_snapshot参数，查看之前时间点数据
mysql> set @@tidb_snapshot="2022-04-09 16:38:17";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from snap_tab;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)查询结果为修改前的数据，可以根据之前的数据修改当前数据。
5.清空session的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> 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> select * from snap_tab;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)二、使用dumpling --snapshot和FLASHBACK TABLE恢复多次TRUNCATE的数据
1.创建测试表并写入数据
mysql> create table trun_tab (c int);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into trun_tab values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from trun_tab;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)2.模拟truncate误操作，连续操作两次
mysql> select * from trun_tab;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> truncate table trun_tab;
Query OK, 0 rows affected (0.14 sec)
mysql> select * from trun_tab;
Empty set (0.01 sec)
mysql> insert into trun_tab values (4),(5),(6);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from trun_tab;
+------+
| c |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
mysql> truncate table trun_tab;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into trun_tab values (7),(8),(9),(10);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from trun_tab;
+------+
| c |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
+------+
4 rows in set (0.00 sec)3.分别将第一次及第二次truncate前的数据找到，并恢复到原表trun_tab表中，查看truncate发生的时间点，修改GC保存时间。
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 |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
| 508 | test3 | trun_tab | truncate table | public | 448 | 505 | 0 | 2022-04-09 17:22:07 | 2022-04-09 17:22:07 | 2022-04-09 17:22:07 | synced |
| 506 | test3 | trun_tab | truncate table | public | 448 | 503 | 0 | 2022-04-09 17:21:35 | 2022-04-09 17:21:35 | 2022-04-09 17:21:35 | synced |
| 504 | test3 | trun_tab | create table | public | 448 | 503 | 0 | 2022-04-09 17:21:03 | 2022-04-09 17:21:04 | 2022-04-09 17:21:04 | synced |
+--------+---------+------------+----------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+
4 rows in set (0.02 sec)
mysql> select * from mysql.tidb where variable_name='tikv_gc_safe_point';
+--------------------+-------------------------+--------------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE | COMMENT |
+--------------------+-------------------------+--------------------------------------------------------------+
| tikv_gc_safe_point | 20220409-17:03:58 +0800 | All versions after safe point can be accessed. (DO NOT EDIT) |
+--------------------+-------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update mysql.tidb set variable_value='1h' where variable_name='tikv_gc_life_time';
Query OK, 1 row affected (0.01 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 | 1h | All versions within life time will not be collected by GC, at least 10m, in Go format. |
+-------------------+----------------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)4.开始恢复数据，因为flashback只能恢复最近一次truncate的数据，第一次truncate的数据需要dumpling --snapshot来恢复
mysql> set @@tidb_snapshot="2022-04-09 17:21:30";
Query OK, 0 rows affected (0.01 sec)
mysql> select * from trun_tab;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> exit
Bye
[root@node1 ~]# tiup dumpling -uroot -P4000 -h192.168.59.28 --filetype sql -o /tmp -T test3.trun_tab --snapshot "2022-04-09 17:21:30";
tiup is checking updates for component dumpling ...
A new version of dumpling is available:
The latest version: v6.0.0
Local installed version:
Update current component: tiup update dumpling
Update all components: tiup update --all
The component `dumpling` version is not installed; downloading from repository.
download https://tiup-mirrors.pingcap.com/dumpling-v6.0.0-linux-amd64.tar.gz 32.50 MiB / 32.50 MiB 100.00% 9.64 MiB/s
Starting component `dumpling`: /root/.tiup/components/dumpling/v6.0.0/dumpling /root/.tiup/components/dumpling/v6.0.0/dumpling -uroot -P4000 -h192.168.59.28 --filetype sql -o /tmp -T test3.trun_tab --snapshot 2022-04-09 17:21:30
Release version: v6.0.0
Git commit hash: 36a9810441ca0e496cbd22064af274b3be771081
Git branch: heads/refs/tags/v6.0.0
Build timestamp: 2022-03-31 10:27:29Z
Go version: go version go1.18 linux/amd64
[2022/04/09 17:31:35.315 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v6.0.0] ["Git Commit Hash"=36a9810441ca0e496cbd22064af274b3be771081] ["Git Branch"=heads/refs/tags/v6.0.0] ["Build timestamp"="2022-03-31 10:27:29"] ["Go Version"="go version go1.18 linux/amd64"]
[2022/04/09 17:31:35.321 +08:00] [INFO] [version.go:362] ["detect server version"] [type=TiDB] [version=6.0.0]
[2022/04/09 17:31:35.333 +08:00] [INFO] [client.go:392] ["[pd] create pd client with endpoints"] [pd-address="[192.168.59.29:2379,192.168.59.28:2379,192.168.59.27:2379]"]
[2022/04/09 17:31:35.337 +08:00] [INFO] [base_client.go:332] ["[pd] update member urls"] [old-urls="[http://192.168.59.29:2379,http://192.168.59.28:2379,http://192.168.59.27:2379]"] [new-urls="[http://192.168.59.27:2379,http://192.168.59.28:2379,http://192.168.59.29:2379]"]
[2022/04/09 17:31:35.337 +08:00] [INFO] [base_client.go:350] ["[pd] switch leader"] [new-leader=http://192.168.59.28:2379] [old-leader=]
[2022/04/09 17:31:35.337 +08:00] [INFO] [base_client.go:105] ["[pd] init cluster id"] [cluster-id=7021398084574750333]
[2022/04/09 17:31:35.337 +08:00] [INFO] [client.go:687] ["[pd] tso dispatcher created"] [dc-location=global]
[2022/04/09 17:31:35.339 +08:00] [INFO] [dump.go:1384] ["generate dumpling gc safePoint id"] [id=dumpling_1649496695339034846]
[2022/04/09 17:31:35.346 +08:00] [INFO] [dump.go:103] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"azblob\":{\"endpoint\":\"\",\"account-name\":\"\",\"account-key\":\"\",\"access-tier\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"192.168.59.28\",\"Port\":4000,\"Threads\":4,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp\",\"StatusAddr\":\":8281\",\"Snapshot\":\"2022-04-09 17:21:30\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"6.0.0\",\"HasTiKV\":true},\"Rows\":0,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"2022-04-09 17:21:30\"},\"Tables\":null,\"CollationCompatible\":\"loose\"}"]
[2022/04/09 17:31:35.411 +08:00] [INFO] [collector.go:237] ["backup success summary"] [total-ranges=3] [ranges-succeed=3] [ranges-failed=0] [total-take=16.697424ms] [total-kv-size=74B] [average-speed=4.432kB/s] [total-rows=3]
[2022/04/09 17:31:35.412 +08:00] [INFO] [main.go:80] ["dump data successfully, dumpling will exit now"]查看导出数据，并恢复到表中
[root@node1 ~]# cat /tmp/test3.trun_tab.000000000.sql
/*!40101 SET NAMES binary*/;
INSERT INTO `trun_tab` VALUES
(1),
(2),
(3);
[root@node1 ~]# mysql -uroot -P4000 -h192.168.59.27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 411
Server version: 5.7.25-TiDB-v6.0.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from trun_tab;
+------+
| c |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
+------+
4 rows in set (0.00 sec)
mysql> source /tmp/test3.trun_tab.000000000.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from trun_tab;
+------+
| c |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
| 1 |
| 2 |
| 3 |
+------+
7 rows in set (0.00 sec)使用flashback恢复最近一次truncate的数据到表中
mysql> flashback table trun_tab to trun_tab_02;
Query OK, 0 rows affected (1.29 sec)
mysql> select * from trun_tab;
+------+
| c |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
| 1 |
| 2 |
| 3 |
+------+
7 rows in set (0.00 sec)
mysql> select * from trun_tab_02;
+------+
| c |
+------+
| 4 |
| 5 |
| 6 |
+------+
3 rows in set (0.00 sec)
mysql> insert into trun_tab select * from trun_tab_02;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from trun_tab;
+------+
| c |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
10 rows in set (0.00 sec)「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
