oceanbase实践查询闪回

原创 forever 2024-11-29

428

在工作中会遇到误删数据的操作，作为dba救火队员就需要掌握数据恢复的方法，生产无小事，操作需谨慎，对数据库恢复的工具可以不用但是要会

OceanBase 数据库提供了记录级别的闪回查询（Flashback Query）功能，该功能允许用户获取某个历史版本的数据。我们学习一下闪回查询以备不时之需

闪回查询通过 undo_retention 来控制转储中保留的多版本数据范围，当 undo_retention 的值为 0 时，表示未开启多版本转储，即转储文件仅保留当前最新版本的行数据。当 undo_retention 的值大于 0 时，表示开启多版本转储，并且转储文件保留这段时间（单位为秒）以内的多版本行数据。在遇到误删数据时可以先增长undo_retention 参数保留时间，等恢复数据后再改回默认

默认值	1800，单位为秒。
取值范围	[0, 4294967295]

一、前置工作

修改undo_retention 保留时间，为了完整测试我们也打开回收站

#调整undo_retention保存时间
obclient [test]> ALTER SYSTEM SET undo_retention=1800;
Query OK, 0 rows affected (0.004 sec)

obclient [test]> SHOW PARAMETERS LIKE 'undo_retention';
+-------+----------+-----------------+----------+----------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+
| zone  | svr_type | svr_ip          | svr_port | name           | data_type | value | info                                                                                                                                                                           | section | scope  | source  | edit_level        | default_value | isdefault |
+-------+----------+-----------------+----------+----------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+
| zone1 | observer | 192.168.150.116 |     2882 | undo_retention | INT       | 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 | 1800          |         1 |
+-------+----------+-----------------+----------+----------------+-----------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+---------------+-----------+
1 row in set (0.004 sec)

#打开回收站，打开后需要重新登录连接
bclient [test]> SET GLOBAL recyclebin = on;
Query OK, 0 rows affected (0.002 sec)

obclient [test]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.001 sec)

二、DML闪回查询

1、准备表和数据

obclient [test]> create table banjin_flash (id int ,name varchar(10),dizhi varchar(10),primary key (id));
insert into banjin_flash values (1,'zhangsan','北京');
insert into banjin_flash values (2,'lisi','上海');
insert into banjin_flash values (3,'wangwu','天津');
Query OK, 0 rows affected (0.050 sec)

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

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

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

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

2、对表进行更改

对表进行更改，更改后记录当前时间内now()，便于恢复

obclient [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-20 17:31:13 |
+---------------------+
1 row in set (0.000 sec)

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

obclient [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-20 17:31:30 |
+---------------------+
1 row in set (0.000 sec)

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

obclient [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-20 17:31:52 |
+---------------------+
1 row in set (0.000 sec)

3、闪回数据


obclient [test]> select * from banjin_flash;
Empty set (0.001 sec)

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:31:30') * 1000;
+----+----------+--------+
| id | name     | dizhi  |
+----+----------+--------+
|  1 | zhangsan | 北京   |
|  2 | lisi     | 湖南   |
|  3 | wangwu   | 天津   |
|  4 | zhaoliu  | 河北   |
+----+----------+--------+
4 rows in set (0.000 sec)

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:31:13') * 1000;
+----+----------+--------+
| id | name     | dizhi  |
+----+----------+--------+
|  1 | zhangsan | 北京   |
|  2 | lisi     | 上海   |
|  3 | wangwu   | 天津   |
|  4 | zhaoliu  | 河北   |
+----+----------+--------+
4 rows in set (0.000 sec)

可以看到两个时间点的闪回分别恢复了不同的数据版本，第一个恢复到了delete之前四行数据，第二个恢复到了update之前的数据

恢复后我们可以插入到备份表做后续操作

三、过程中有DDL的闪回

1、对表增字段后的闪回闪回

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

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:44:43') * 1000;
+----+----------+--------+---------+
| id | name     | dizhi  | dianhua |
+----+----------+--------+---------+
|  1 | zhangsan | 北京   |       1 |
|  2 | lisi     | 湖南   |       1 |
|  3 | wangwu   | 天津   |       1 |
|  4 | zhaoliu  | 河北   |       1 |
+----+----------+--------+---------+
4 rows in set (0.002 sec)

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

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:44:43') * 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)

对表增减字段后的闪回,闪回后我们可以看到新加字段被Default 值补齐，如果没有default值会用null补齐

2、对表删除字段后的恢复


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

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:44:43') * 1000;
ERROR 1412 (HY000): Unable to read data -- Table definition has changed

删除字段后恢复会报错就无法恢复了，ERROR 1412 (HY000): Unable to read data -- Table definition has changed，

3、删表后的闪回

obclient [test]> drop table banjin_flash;
Query OK, 0 rows affected (0.022 sec)

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:51:30') * 1000;
ERROR 1146 (42S02): Table 'test.banjin_flash' doesn't exist

删除表后不能直接恢复会报表不存在

我们可以再回收站找到表，先把表恢复，再闪回查询

obclient [test]> select * from banjin_flash;
Empty set (0.001 sec)

obclient [test]> show recyclebin;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                    | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+--------------------------------+---------------+-------+----------------------------+
| __recycle_$_1_1729417897979024 | banjin_flash  | TABLE | 2024-10-20 17:51:37.978166 |
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.002 sec)

obclient [test]> FLASHBACK TABLE __recycle_$_1_1729417897979024 TO BEFORE DROP;
Query OK, 0 rows affected (0.033 sec)

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:51:30') * 1000;
+----+----------+--------+
| id | name     | dizhi  |
+----+----------+--------+
|  1 | zhangsan | 北京   |
|  2 | lisi     | 上海   |
|  3 | wangwu   | 天津   |
|  4 | zhaoliu  | 河北   |
+----+----------+--------+
4 rows in set (0.008 sec)

四、truncate的闪回

truncate属于DDL，但是它有是一个比较特殊的DDL，所以单独列出来，我们测试truncate后的闪回

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

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

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

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

obclient [test]> 
obclient [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-20 18:42:47 |
+---------------------+
1 row in set (0.000 sec)

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

obclient [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-20 18:42:48 |
+---------------------+
1 row in set (0.000 sec)

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

obclient [test]> SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 18:42:47') * 1000;
ERROR 1412 (HY000): Unable to read data -- Table definition has changed

在truncate表后，做数据闪回也会报ERROR 1412 (HY000): Unable to read data -- Table definition has changed，truncate也属于DDL

查看下官网对truncate的定义及truncate的操作：

执行 TRUNCATE 语句，必须具有表的删除（DROP）权限。它属于 DDL 语句。

TRUNCATE TABLE 语句与 DELETE FROM 语句有以下不同：

•TRUNCATE 操作删除并重新创建表，这比逐行删除要快得多，特别是对于大表。

•TRUNCATE TABLE 语句执行结果显示影响行数始终显示为 0 行。

•使用 TRUNCATE TABLE 语句，表管理程序不记得最后被使用的 AUTO_INCREMENT 值，但是会从头开始计数。

•TRUNCATE 语句不能在进行事务处理和表锁定的过程中进行，如果使用，将会报错。

•只要表定义文件是合法的，则可以使用 TRUNCATE TABLE 把表重新创建为一个空表，即使数据或索引文件已经被破坏。

虽然操作时删除并重建，但是不会记录回收站，所以谨慎操作

obclient [test]> TRUNCATE TABLE BANJIN_FLASH;
Query OK, 0 rows affected (0.042 sec)

obclient [test]> show recyclebin;
Empty set (0.002 sec)

五、命令总结

#调整undo_retention保存时间
ALTER SYSTEM SET undo_retention=1800;

#查看undo_retention参数
SHOW PARAMETERS LIKE 'undo_retention';

#打开回收站
bclient [test]> SET GLOBAL recyclebin = on;

#查看回收站状态
SHOW VARIABLES LIKE 'recyclebin';

#闪回查询
SELECT * FROM banjin_flash AS OF SNAPSHOT time_to_usec('2024-10-20 17:31:13') * 1000;
#也可以用官网分开写
SELECT time_to_usec('2024-10-20 06:42:40') * 1000;
SELECT * FROM banjin_flash AS OF SNAPSHOT 1729377760000000000;

#查看回收站
show recyclebin;

#恢复回收站表
FLASHBACK TABLE __recycle_$_1_1729417897979024 TO BEFORE DROP;

#表和数据
create table banjin_flash (id int ,name varchar(10),dizhi varchar(10),primary key (id));
insert into banjin_flash values (1,'zhangsan','北京');
insert into banjin_flash values (2,'lisi','上海');
insert into banjin_flash values (3,'wangwu','天津');
insert into banjin_flash values (4,'zhaoliu','河北');

#数据操作
select now();
update banjin_flash set dizhi = '湖南' where name='lisi';
select now();
delete from banjin_flash;
select now();

alter table banjin_flash add column dianhua decimal(11) default 1;

alter table banjin_flash drop column dianhua;

oceanbase oceanbase布道师墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者