OceanBase 闪回查询

🕗 发布于 2024-11-17 09:34 oceanbase dba 数据库

前言

在OB中，drop表可以通过回收站或者以往的备份恢复来还原单表。当delete数据时，由于delete操作的对象不会进入回收站，此时需要通过闪回查询功能查看delete的数据，以便后续恢复

本次实验版本为 OceanBase 4.2.1.8，MySQL租户。参考OB社区 banjin 老师文章进行测试验证

undo_retention

该参数作用为系统应保留的多版本数据范围，单位为秒。属于租户级别变量，默认为1800秒。当出现delete误删除数据时，可以通过该参数进行闪回查询，恢复误删除数据，可以根据实际业务需求适当更改该参数值

obclient [tpcc]> ALTER SYSTEM SET undo_retention=1800;
Query OK, 0 rows affected (0.129 sec)


obclient [tpcc]> SHOW PARAMETERS LIKE 'undo_retention';
+-------+----------+-----------+----------+----------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone  | svr_type | svr_ip    | svr_port | name           | data_type | value | info                                                                                                                                                                           | section | scope  | source  | edit_level        |
+-------+----------+-----------+----------+----------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone3 | observer | 10.0.0.63 |     2882 | undo_retention | NULL      | 1800  | the low threshold value of undo retention. The system retains undo for at least the time specified in this config when active txn protection is banned. Range: [0, 4294967295] | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 10.0.0.62 |     2882 | undo_retention | NULL      | 1800  | the low threshold value of undo retention. The system retains undo for at least the time specified in this config when active txn protection is banned. Range: [0, 4294967295] | TENANT  | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------+----------+----------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
2 rows in set (0.009 sec)

官方解释如下

DML闪回查询

准备测试数据

obclient [tpcc]> create table banjin_flash (id int ,name varchar(10),dizhi varchar(10),primary key (id));
Query OK, 0 rows affected (0.568 sec)


obclient [tpcc]> insert into banjin_flash values (1,'zhangsan','北京');
Query OK, 1 row affected (0.135 sec)


obclient [tpcc]> insert into banjin_flash values (2,'lisi','上海');
Query OK, 1 row affected (0.002 sec)


obclient [tpcc]> insert into banjin_flash values (3,'wangwu','天津');
Query OK, 1 row affected (0.002 sec)


obclient [tpcc]> insert into banjin_flash values (4,'zhaoliu','河北');
Query OK, 1 row affected (0.002 sec)


obclient [tpcc]>

更改数据

记录更改数据时的时间，便于后续闪回恢复

obclient [tpcc]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-16 14:25:10 |
+---------------------+
1 row in set (0.002 sec)


obclient [tpcc]> update banjin_flash set dizhi = '湖南' where name='lisi';
Query OK, 1 row affected (0.010 sec)
Rows matched: 1  Changed: 1  Warnings: 0


obclient [tpcc]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-16 14:25:30 |
+---------------------+
1 row in set (0.001 sec)


obclient [tpcc]> delete from banjin_flash;
Query OK, 4 rows affected (0.004 sec)


obclient [tpcc]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-16 14:25:50 |
+---------------------+
1 row in set (0.001 sec)


obclient [tpcc]>

闪回数据

1.可以看到由于之前删除了表中的数据，在当前状态查看不到表中任何数据

2.在 2024-11-16 14:25:30 时间段 dizhi 为上海的数据已经被修改为湖南

3.在 2024-11-16 14:25:10 时间段表中的数据为初始时候的状态

obclient [tpcc]> select * from banjin_flash;
Empty set (0.002 sec)


obclient [tpcc]>
obclient [tpcc]>
----  在 2024-11-16 14:25:30 时间段 dizhi 为上海的数据已经被修改为 湖南
obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:30') * 1000;
+------+----------+--------+
| id   | name     | dizhi  |
+------+----------+--------+
|    1 | zhangsan | 北京   |
|    2 | lisi     | 湖南   |
|    3 | wangwu   | 天津   |
|    4 | zhaoliu  | 河北   |
+------+----------+--------+
4 rows in set (0.002 sec)
---  在 2024-11-16 14:25:10 时间段 表中的数据为初始时候的状态
obclient [tpcc]>
obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:10') * 1000;
+------+----------+--------+
| id   | name     | dizhi  |
+------+----------+--------+
|    1 | zhangsan | 北京   |
|    2 | lisi     | 上海   |
|    3 | wangwu   | 天津   |
|    4 | zhaoliu  | 河北   |
+------+----------+--------+
4 rows in set (0.001 sec)


obclient [tpcc]>
obclient [tpcc]>

过程中有DDL的闪回

表增字段后的闪回效果

可以看到如果后续对表进行了alter操作，闪回查询时新加字段被Default 值补齐，如果没有default值会用null补齐

obclient [tpcc]> alter table banjin_flash add column dianhua decimal(11) default 1;
Query OK, 0 rows affected (0.178 sec)


obclient [tpcc]>
obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:30') * 1000;
+------+----------+--------+---------+
| id   | name     | dizhi  | dianhua |
+------+----------+--------+---------+
|    1 | zhangsan | 北京   |       1 |
|    2 | lisi     | 湖南   |       1 |
|    3 | wangwu   | 天津   |       1 |
|    4 | zhaoliu  | 河北   |       1 |
+------+----------+--------+---------+
4 rows in set (0.015 sec)


obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:10') * 1000;
+------+----------+--------+---------+
| id   | name     | dizhi  | dianhua |
+------+----------+--------+---------+
|    1 | zhangsan | 北京   |       1 |
|    2 | lisi     | 上海   |       1 |
|    3 | wangwu   | 天津   |       1 |
|    4 | zhaoliu  | 河北   |       1 |
+------+----------+--------+---------+
4 rows in set (0.001 sec)


obclient [tpcc]> alter table banjin_flash add column dianhua1 decimal(11) ;
Query OK, 0 rows affected (0.083 sec)


obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:30') * 1000;
+------+----------+--------+---------+----------+
| id   | name     | dizhi  | dianhua | dianhua1 |
+------+----------+--------+---------+----------+
|    1 | zhangsan | 北京   |       1 |     NULL |
|    2 | lisi     | 湖南   |       1 |     NULL |
|    3 | wangwu   | 天津   |       1 |     NULL |
|    4 | zhaoliu  | 河北   |       1 |     NULL |
+------+----------+--------+---------+----------+
4 rows in set (0.015 sec)


obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:10') * 1000;
+------+----------+--------+---------+----------+
| id   | name     | dizhi  | dianhua | dianhua1 |
+------+----------+--------+---------+----------+
|    1 | zhangsan | 北京   |       1 |     NULL |
|    2 | lisi     | 上海   |       1 |     NULL |
|    3 | wangwu   | 天津   |       1 |     NULL |
|    4 | zhaoliu  | 河北   |       1 |     NULL |
+------+----------+--------+---------+----------+
4 rows in set (0.001 sec)


obclient [tpcc]>

表删除字段后的闪回效果

可以看到如果对表进行了 alter 删除字段操作后，无法使用闪回还原数据

obclient [tpcc]> alter table banjin_flash drop column dianhua1;
Query OK, 0 rows affected (0.835 sec)


obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:10') * 1000;
ERROR 1412 (HY000): Unable to read data -- Table definition has changed
obclient [tpcc]>
obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:25:30') * 1000;
ERROR 1412 (HY000): Unable to read data -- Table definition has changed
obclient [tpcc]>
obclient [tpcc]>

truncate 闪回效果

重新初始化数据，发现truncate操作后也无法进行闪回数据

obclient [tpcc]> create table banjin_flash (id int ,name varchar(10),dizhi varchar(10),primary key (id));
Query OK, 0 rows affected (0.106 sec)


obclient [tpcc]> insert into banjin_flash values (1,'zhangsan','北京');
Query OK, 1 row affected (0.025 sec)


obclient [tpcc]> insert into banjin_flash values (2,'lisi','上海');
Query OK, 1 row affected (0.003 sec)


obclient [tpcc]> insert into banjin_flash values (3,'wangwu','天津');
Query OK, 1 row affected (0.053 sec)


obclient [tpcc]> insert into banjin_flash values (4,'zhaoliu','河北');
Query OK, 1 row affected (0.002 sec)


obclient [tpcc]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-16 14:44:19 |
+---------------------+
1 row in set (0.001 sec)


obclient [tpcc]> update banjin_flash set dizhi = '湖南' where name='lisi';
Query OK, 1 row affected (0.014 sec)
Rows matched: 1  Changed: 1  Warnings: 0


obclient [tpcc]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-16 14:44:37 |
+---------------------+
1 row in set (0.001 sec)


obclient [tpcc]> truncate table banjin_flash;
Query OK, 0 rows affected (0.099 sec)


obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:44:19') * 1000;
ERROR 1412 (HY000): Unable to read data -- Table definition has changed
obclient [tpcc]>
obclient [tpcc]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-11-16 14:44:37') * 1000;
ERROR 1412 (HY000): Unable to read data -- Table definition has changed
obclient [tpcc]>

总结

1.当出现delete操作时，可以根据 OB的undo_retention功能 进行闪回查询查看误删除的数据

2.后续过程中如果出现 删除字段 以及 truncate表操作 无法进行闪回数据

3.truncate 操作的对象既不会进入回收站也无法通过闪回查询，生产环境一定要谨慎操作

原文地址：https://blog.csdn.net/weixin_68243500/article/details/143817425

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：网络设备驱动与网络子系统，有区别吗？
下一篇：java Collections 详解

在Google Cloud Platform (GCP) 中用Pub/Sub+Dataflow+BigQuery构建数据处理仓库
Pub/Sub：作为消息队列，Pub/Sub 用于接收和传输实时数据流。它能够处理高吞吐量的消息，并将数据分发到多个订阅者。Dataflow：用于数据处理和转换。Dataflow 支持流式和批处理，可
阅读更多2024-11-18
【备忘录】Gin框架
Gin是一个轻量级、高性能的Go语言Web框架，其设计灵感来源于Martini，但相较于Martini，其性能提升了约40倍。Gin基于HTTP路由构建，并提供了丰富的中间件支持，非常适合用于构建高并
阅读更多2024-11-18
系统编译问题
cd “PWD/BUILDE。
阅读更多2024-11-18
C++: string(二)
1 我们这章学习了string的成员函数，在https://legacy.cplusplus.com/网站中可以把接口分为按不同标准分成好多类(如下面Capacity,Element access)，
阅读更多2024-11-18
机器学习基础05_随机森林&线性回归
机器学习；随机森林；线性回归；最小二乘法
阅读更多2024-11-18
AIGC中的图像生成：基于GAN的实现
接下来，我们定义生成器和判别器的网络结构。# 生成器nn.ReLU(),nn.ReLU(),nn.ReLU(),nn.Linear(1024, 28 * 28), # MNIST图像大小nn.Tanh
阅读更多2024-11-18
MYSQL 修改表的结构
在项目的实际开发中，随着版本的迭代和需求的变更，经常会对表结构进行调整，比如向现有表中添加列，删除列，或者修改某列的列名、数据类型或长度，这时就需要对表进行修改操作。RENAME [TO | AS]
阅读更多2024-11-18
基于YOLOv8深度学习的智慧农业野生蓝莓成熟度小目标检测系统研究与实现(PyQt5界面+数据集+训练代码)
随着智慧农业技术的不断进步，传统农业逐渐向自动化、智能化转型，特别是在果实成熟度检测领域，精确、及时的检测手段已成为提升农业生产效率和优化资源配置的重要工具。
阅读更多2024-11-18
Linux防火墙
表是用于网络地址转换（Network Address Translation，NAT）的表之一，用于修改数据包的源 IP 地址、目标 IP 地址或端口号，以实现网络地址的映射和转换。iptables
阅读更多2024-11-18
LeetCode39：组合总和
这个代码其实就是和前面的组合总是是一样的，但是呢，主要一点是，这个for循环里面的backtracking的不是i + 1了，而是i,因为当前i可以去取当前的元素，而i + 1是不能去当前的元素的。
阅读更多2024-11-18