mysql闪回查询写法

转载

mob64ca1404476b 2024-09-06 07:53:25

文章标签 mysql闪回查询写法数据库闪回数据恢复回滚段 文章分类 MySQL 数据库

文章目录

前言
一、认识闪回？
二、启用步骤

1.检查是否开启闪回功能
2.开启数据库闪回功能
3.查看回滚段记录保存时间

三、测试数据闪回

1、构造测试数据
2、删除数据测试闪回功能

总结

前言

数据库的闪回功能可以说是DBA的大杀器之一，当数据库被错误操作导致数据丢失时，通过闪回可以非常方便快速的恢复数据，在日常运维，故障处理、"数据恢复炫技"有着广泛的应用，学好闪回再也不怕数据被误删啦。

一、认识闪回？

当用户操作不慎导致错误的删改数据时，非常希望有一种简单快捷的方式可以恢复数据。闪回技术，就是为了用户可以迅速处理这种数据逻辑损坏的情况而产生的。
闪回技术主要是通过回滚段存储的 UNDO 记录来完成历史记录的还原。设置ENABLE_FLASHBACK 为 1 后，开启闪回功能。 DM 会保留回滚段一段时间，回滚段保留的时间代表着可以闪回的时间长度。由 UNDO_RETENTION 参数指定。
开启闪回功能后， DM 会在内存中记录下每个事务的起始时间和提交时间。通过用户指定的时刻，查询到该时刻的事务号，结合当前记录和回滚段中的 UNDO 记录，就可以还原出特定事务号的记录。即指定时刻的记录状态。从而完成闪回查询。闪回查询功能完全依赖于回滚段管理，对于 DROP 等DDL误操作由于不记录到回滚段所以不能恢复。

二、启用步骤

1.检查是否开启闪回功能

数据库的闪回功能默认是关闭状态，需要手工开启后才可以支持闪回。

查询确认方式如下：
0-关闭，1-打开

SQL> select PARA_NAME,PARA_VALUE,PARA_TYPE from v$dm_ini where para_name like '%FLASHBACK%';

行号     PARA_NAME        PARA_VALUE PARA_TYPE
---------- ---------------- ---------- ---------
1          ENABLE_FLASHBACK 0          SYS

已用时间: 2.347(毫秒). 执行号:1003.

2.开启数据库闪回功能

代码如下（示例）：

SQL> alter system set 'enable_flashback'=1 both;
DMSQL 过程已成功完成
已用时间: 61.680(毫秒). 执行号:1004.

SQL> select PARA_NAME,PARA_VALUE,PARA_TYPE from v$dm_ini where para_name like '%FLASHBACK%';

行号     PARA_NAME        PARA_VALUE PARA_TYPE
---------- ---------------- ---------- ---------
1          ENABLE_FLASHBACK 1          SYS

已用时间: 2.111(毫秒). 执行号:1005.

3.查看回滚段记录保存时间

由于回滚段会自动清理，所以需要延长回滚记录的保留时间，单位是秒。回滚段参数太大容易引发回滚段膨胀，需要合理的规划和设置。

查询sql如下：

SQL> select PARA_NAME,PARA_VALUE,PARA_TYPE from v$dm_ini where para_name like 'UNDO_RETENTION';

行号     PARA_NAME      PARA_VALUE PARA_TYPE
---------- -------------- ---------- ---------
1          UNDO_RETENTION 90.000000  SYS

已用时间: 3.267(毫秒). 执行号:1008.

查询结果可以看到，系统默认只有90秒，超过90秒的回滚段会被自动清理，这个时间太短了，完全不能满足实际的需求，所以需要把这个数值调大。

我们这边设置为86400，也就是24小时内误删除的数据都可以进行闪回恢复。

SQL> ALTER SYSTEM SET 'undo_retention'=86400 BOTH;
DMSQL 过程已成功完成
已用时间: 3.528(毫秒). 执行号:1009.

SQL> select PARA_NAME,PARA_VALUE,PARA_TYPE from v$dm_ini where para_name like 'UNDO_RETENTION';

行号     PARA_NAME      PARA_VALUE   PARA_TYPE
---------- -------------- ------------ ---------
1          UNDO_RETENTION 86400.000000 SYS

已用时间: 2.956(毫秒). 执行号:1010.

三、测试数据闪回

1、构造测试数据

SQL> create table t_01 (id int，name varchar(128));
操作已执行
已用时间: 45.205(毫秒). 执行号:1013.
SQL> insert into t_01 values (110000,'北京市'),(120000,'天津市'),(130000,'河北省');
影响行数 3

已用时间: 0.448(毫秒). 执行号:1016.
SQL> commit;
操作已执行
已用时间: 0.875(毫秒). 执行号:1017.
SQL> select * from t_01;

行号     ID          NAME
---------- ----------- ---------
1          110000      北京市
2          120000      天津市
3          130000      河北省

已用时间: 0.809(毫秒). 执行号:1018.

2、删除数据测试闪回功能

SQL> select sysdate();

行号     SYSDATE()
---------- -------------------
1          2022-02-16 13:46:37

已用时间: 0.477(毫秒). 执行号:1019.
SQL> delete t_01;
影响行数 3

已用时间: 0.727(毫秒). 执行号:1020.
SQL> commit;
操作已执行
已用时间: 0.862(毫秒). 执行号:1021.
SQL> select * from t_01;
未选定行

已用时间: 0.372(毫秒). 执行号:1022.

这时数据已经被删除。

通过闪回时间戳的方式查询到上一个未执行删除语句时间点的状态。通过 WHEN TIMESTAMP 子句，

SQL> select * from t_01 WHEN TIMESTAMP '2022-02-16 13:46:37';

行号     ID          NAME
---------- ----------- ---------
1          110000      北京市
2          120000      天津市
3          130000      河北省

已用时间: 0.734(毫秒). 执行号:1023.
#通过 WHEN TIMESTAMP 只是查询到某一个时点表的状态，表实际并没有恢复，需要将数据重新导入进行恢复。
SQL> select * from t_01;
未选定行

已用时间: 0.251(毫秒). 执行号:1024.
SQL> insert into t_01 select * from t_01 WHEN TIMESTAMP '2022-02-16 13:46:37';
影响行数 3

已用时间: 0.626(毫秒). 执行号:1025.
SQL> select * from t_01;

行号     ID          NAME
---------- ----------- ---------
1          110000      北京市
2          120000      天津市
3          130000      河北省

已用时间: 0.240(毫秒). 执行号:1026.
SQL> commit;
操作已执行
已用时间: 0.767(毫秒). 执行号:1027.