12.OB4.0闪回技术

最新推荐文章于 2024-07-18 18:43:19 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-07-18 18:43:19 发布

阅读量254

点赞数

分类专栏：数据库运维-OceanBase 文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_43346403/article/details/129402570

版权

数据库运维-OceanBase 专栏收录该内容

39 篇文章 5 订阅

订阅专栏

1.从回收站中恢复被删除的租户 tenant1。
FLASHBACK TENANT tenant1 TO BEFORE DROP;

2.从回收站中恢复删除的数据库

CREATE DATABASE da;
DROP DATABASE da;
SHOW RECYCLEBIN;
FLASHBACK DATABASE __recycle_$_2_1678272920718024 TO BEFORE DROP;

MySQL [sjzt]> SET recyclebin = on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sjzt]> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MySQL [sjzt]> CREATE DATABASE da;
Query OK, 1 row affected (0.03 sec)

MySQL [sjzt]> DROP DATABASE da;
Query OK, 0 rows affected (0.34 sec)

MySQL [sjzt]> SHOW RECYCLEBIN;
+--------------------------------+---------------+----------+----------------------------+
| OBJECT_NAME                    | ORIGINAL_NAME | TYPE     | CREATETIME                 |
+--------------------------------+---------------+----------+----------------------------+
| __recycle_$_2_1678272920718024 | da            | DATABASE | 2023-03-08 18:55:20.718326 |
+--------------------------------+---------------+----------+----------------------------+
1 row in set (0.01 sec)

MySQL [sjzt]> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oceanbase          |
| sjzt               |
| test               |
| test2              |
| test3              |
+--------------------+
7 rows in set (0.02 sec)

MySQL [sjzt]> FLASHBACK DATABASE __recycle_$_2_1678272920718024 TO BEFORE DROP;
Query OK, 0 rows affected (0.04 sec)

MySQL [sjzt]> show databases; 
+--------------------+
| Database           |
+--------------------+
| da                 |
| information_schema |
| mysql              |
| oceanbase          |
| sjzt               |
| test               |
| test2              |
| test3              |
+--------------------+
8 rows in set (0.02 sec)

3.从回收站中恢复被删除的表 t1，重命名为 t2，并查看索引的恢复情况

CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT,c3 INT);
ALTER TABLE t1 ADD INDEX ind2 (c2) USING BTREE;
ALTER TABLE t1 ADD INDEX ind3 (c3) USING BTREE;
SHOW INDEX FROM t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| t1    |          1 | ind2     |            1 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
| t1    |          1 | ind3     |            1 | c3          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
2 rows in set (0.05 sec)

ALTER TABLE t1 DROP INDEX ind2;
SHOW RECYCLEBIN;  --空的。
DROP TABLE t1;
SHOW RECYCLEBIN;
MySQL [sjzt]> SHOW RECYCLEBIN;
+--------------------------------+-------------------+-------+----------------------------+
| OBJECT_NAME                    | ORIGINAL_NAME     | TYPE  | CREATETIME                 |
+--------------------------------+-------------------+-------+----------------------------+
| __recycle_$_2_1678273209100904 | __idx_500301_ind3 | INDEX | 2023-03-08 19:00:09.100506 |
| __recycle_$_2_1678273209102992 | t1                | TABLE | 2023-03-08 19:00:09.102240 |
+--------------------------------+-------------------+-------+----------------------------+

FLASHBACK TABLE __recycle_$_2_1678273209102992 TO BEFORE DROP RENAME TO t2;
MySQL [sjzt]> SHOW INDEX FROM t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| t2    |          1 | ind3     |            1 | c3          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
1 row in set (0.04 sec)

4.总结

OB MySQL租户实现了Oracle里面的闪回技术，非常不错的恢复功能。

闪回表

闪回数据库

闪回租户