Some very good enterprise level functions of the database are "military for a thousand days, military for a while". For example, the Recycle Bin function in Oracle 10g can play the role of special soldiers in special circumstances. For example, when you delete a table space or a user schema, you may delete many tables, including some you don't want to delete.

There are still many examples. In the early years, many DBA s used graphic tools to access the database, and the connected user permissions were relatively high. When querying a large number of data causes the graphic tool to lose its response for a short time, it is easy to hit a few more keyboards or a few more mouse clicks. When the graphic tool responds, it is found that some tables or some users have disappeared.

As mentioned above, we can also see some intentional malicious operations, such as destructive operations caused by the unfriendly relationship between employees and the company. At this point, you can check whether there are any deleted tables in the Oracle database recycle bin.

AliSQL is a branch carefully built by Alibaba cloud RDS MySQL team, which has greatly improved and made breakthroughs in performance, function and stability. In the version released in December 2019, the Recycle Bin function is also included, which can be enabled only by setting one parameter:

mysql> set global recycle_bin=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'recycle_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recycle_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Next let's create a table, insert some data, and perform functional testing and validation. As follows:

mysql> use test;
Database changed
mysql> create table t_recycle_bin_demo (col1 int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_recycle_bin_demo values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from t_recycle_bin_demo;
+------+
| col1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Suppose this is a very important table, and it is accidentally dropped by Drop, let's see how to retrieve the data of this table. As follows:
*Please swipe left and right

mysql> drop table t_recycle_bin_demo;
Query OK, 0 rows affected (0.01 sec)
mysql> call dbms_recycle.show_tables();
+-----------------+---------------+---------------+--------------------+---------------------+---------------------+
| SCHEMA          | TABLE         | ORIGIN_SCHEMA | ORIGIN_TABLE       | RECYCLED_TIME       | PURGE_TIME          |
+-----------------+---------------+---------------+--------------------+---------------------+---------------------+
| __recycle_bin__ | __innodb_1073 | test          | t_recycle_bin_demo | 2020-02-27 06:48:24 | 2020-03-05 06:48:24 |
+-----------------+---------------+---------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from `__recycle_bin__`.`__innodb_1073`;
+------+
| col1 |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

You can see that when AliSQL turns on the recycle bin function, the deleted table is moved to the "Recycle Bin" database. You can read out the deleted data directly through the Select statement for recovery, so as to retrieve your important data. Unlike Oracle, the "Recycle Bin" database access requires explicit authorization operations, which can prevent the owner of the table (possibly a malicious person) from synchronously cleaning up the table in the recycle bin. For example, if I log in with a normal user, I will report a permission error as follows:
*Please swipe left and right

mysql> select * from `__recycle_bin__`.`__innodb_1073`;
ERROR 1142 (42000): SELECT command denied to user 'test'@'localhost' for table '__innodb_1073'
mysql> call dbms_recycle.purge_table('__innodb_1073');
ERROR 1142 (42000): DROP command denied to user 'test'@'localhost' for table '__innodb_1073'

You can see that through the recycle bin function of AliSQL and the carefully planned permission management mechanism, you can effectively handle the accidental or malicious deletion of tables to ensure your data security.