Shulou(Shulou.com)06/01 Report--

Author

Huang Zhong, technical expert of Ali Cloud Database

Some of the very good enterprise features of the database are "keep troops for a thousand days, use troops for a while", such as the Recycle Bin (Recycle Bin) function in Oracle 10g, which can play the function of special forces in special circumstances, such as when you delete a tablespace, a user (Schema), you may delete a lot of tables, including some tables that you do not want to delete.

There are still many such examples. in the early years, a lot of DBA used graphical tools to access the database, and the user rights they connected to were relatively high. when querying a large amount of data caused the graphical tools to temporarily lose their response, it was easy to hit the keyboard or click a few more mouse clicks, and when the graphical tools responded, they found that some tables or some users had disappeared.

What I mentioned above is an unintentional mistake, and you can also see some deliberate malicious actions, such as destructive actions caused by the unfriendly relationship between employees and the company. At this point, you can check to see if there are any deleted tables in the Oracle database recycle bin.

AliSQL is a branch carefully built by Aliyun's RDS MySQL team, which has made great improvements and breakthroughs in performance, function and stability. Recycle Bin is also included in the version released in December 2019, which can be enabled by setting only one parameter:

* swipe left and right to read 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:

* swipe left and right to read 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)

Assuming that this is an important table and is accidentally dropped by Drop, let's take a look at how to retrieve the data from this table. As follows: * swipe left and right to read 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 enables the Recycle Bin function, the deleted table is moved to the "_ _ recycle_bin__" database. You can directly access and read the deleted data through the Select statement to recover your important data. Unlike Oracle, "_ _ recycle_bin__" database access requires explicit authorization, which prevents table owners (possibly malicious ones) from synchronously cleaning up tables in the Recycle Bin. For example, when I log in with an ordinary user, a permission error will be reported, as follows: * swipe left and right to read 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 well-planned rights management mechanism, you can effectively deal with the accidental or malicious deletion of tables and ensure the security of your data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.