Data definition language (DDL) statements cannot be rolled back. For example, if developers or operations and maintenance (O&M) engineers drop a table by using a DROP TABLE statement, the data of the table may be lost. Alibaba Cloud provides the recycle bin feature, which allows you to temporarily store the tables that are dropped. You can specify a retention period within which you can retrieve the dropped tables. Alibaba Cloud also provides the DBMS_RECYCLE package to help you manage the dropped tables in the recycle bin.
Prerequisites
- MySQL 8.0 with a minor engine version of 20191225 or later
- MySQL 5.7 with a minor engine version of 20210430 or later
Parameters
The following table describes the parameters that you can configure for the recycle bin feature.
Parameter | Description |
---|---|
loose_recycle_bin | Specifies whether to enable the recycle bin feature. You can enable this feature for your RDS instance or for a specific session. You can reconfigure this parameter in the ApsaraDB RDS console. Default value: OFF. |
loose_recycle_bin_retention | The period for which you want to retain tables in the recycle bin. Unit: seconds. Default value: 604800. The default value indicates seven days. You can reconfigure this parameter in the ApsaraDB RDS console. |
loose_recycle_scheduler | Specifies whether to enable the thread that is used to asynchronously delete tables from the recycle bin. You can reconfigure this parameter in the ApsaraDB RDS console. Default value: OFF. |
loose_recycle_scheduler_interval | The polling interval followed by the thread that is used to asynchronously delete tables from the recycle bin. Unit: seconds. Default value: 30. This parameter is temporarily unavailable. |
loose_recycle_scheduler_purge_table_print | Specifies whether to record the logs that are generated when the system asynchronously clears the recycle bin. Default value: OFF. This parameter is temporarily unavailable. |
Introduction
- Recycling and deletion
- Recycling When you execute the
TRUNCATE TABLE
statement to truncate a table, ApsaraDB RDS moves the truncated table to the recycle bin. Then, ApsaraDB RDS creates an empty table that uses the same schema as the truncated table in the original directory of the truncated table.Note This mechanism is supported only for RDS instances that run MySQL 8.0 with a minor engine version of 20200331 or later.When you execute a
DROP TABLE or DROP DATABASE
statement to drop a table or a database, ApsaraDB RDS moves only the dropped tables to the recycle bin. ApsaraDB RDS manages all the other objects based on the following policies:- If no relationships are established between an object and the dropped tables, ApsaraDB RDS determines whether to retain the object based on the executed statement. ApsaraDB RDS does not move the objects to the recycle bin.
- If an object is based on the dropped tables and may cause modifications to the data in these tables, ApsaraDB RDS deletes the object. These objects include triggers and foreign keys. ApsaraDB RDS does not delete column statistics. These statistics are stored to the recycle bin with the dropped tables.
- Deletion
The recycle bin starts a background thread to asynchronously delete tables from the recycle bin. These tables are stored in the recycle bin longer than the period of time that is specified by the recycle_bin_retention parameter. If the size of a table in the recycle bin is large, ApsaraDB RDS starts another background thread to asynchronously delete the table.
- Recycling
- Permission control
When you start your RDS instance, a database named __recycle_bin__ is created to store the data that is moved to the recycle bin. The __recycle_bin__ database is a system database, which cannot be modified or deleted.
You cannot execute
DROP TABLE
statements to delete tables from the recycle bin. However, you can use thecall dbms_recycle.purge_table('<TABLE>');
method to delete tables from the recycle bin.Note The account that you use must have the permissions to execute DROP statements on both your RDS instance and the recycle bin. - Table naming in the recycle bin Tables in the __recycle_bin__ database originate from different databases and may have the same name. To ensure that each table has a unique name in the recycle bin, ApsaraDB RDS implements the following naming conventions:
"__" + <Storage Engine> + <SE private id>
The following table describes the parameters in the naming conventions.
Parameter Description Storage Engine The name of the storage engine that is used by the table. SE private id The unique value that is generated by the storage engine to identify the table. For example, the unique value that is used to identify an InnoDB table is the ID of the table. - Independent recycling
The configurations of a recycle bin for an RDS instance apply only to that RDS instance. The configurations do not apply to the secondary, read-only, or disaster recovery RDS instances to which binary logs are replicated. For example, you can specify a 7-day retention period on your primary RDS instance and a 14-day retention period on the secondary RDS instances.
Note The storage usage of an RDS instance varies based on the retention period that you specify on the instance.
Precautions
- If the __recycle_bin__ database and a table reside in different file systems, ApsaraDB RDS may migrate the related data file from the tablespace that stores the table after you execute a
DROP TABLE
statement to drop the table. This process is time-consuming. - A general tablespace can store more than one table. If you execute a DROP TABLE statement to drop a table from a general tablespace, ApsaraDB RDS does not migrate the related data file from the general tablespace.
Manage the recycle bin
AliSQL provides the following management methods in the DBMS_RECYCLE package:
- show_tables This method is used to display all the tables that are temporarily stored in the recycle bin. To use this method, run the following command:
call dbms_recycle.show_tables();
Example:
mysql> call dbms_recycle.show_tables(); +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | SCHEMA | TABLE | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME | PURGE_TIME | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ | __recycle_bin__ | __innodb_1063 | product_db | t1 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1064 | product_db | t2 | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1065 | product_db | parent | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | | __recycle_bin__ | __innodb_1066 | product_db | child | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 | +-----------------+---------------+---------------+--------------+---------------------+---------------------+ 4 rows in set (0.00 sec)
Parameter Description SCHEMA The name of the database that stores the table after the table is moved to the recycle bin. TABLE The name of the table after the table is moved to the recycle bin. ORIGIN_SCHEMA The name of the database that stores the table before the table is moved to the recycle bin. ORIGIN_TABLE The name of the table before the table is moved to the recycle bin. RECYCLED_TIME The time when the table was moved to the recycle bin. PURGE_TIME The time when the table is expected to be deleted from the recycle bin. - purge_table This method is used to manually delete a table from the recycle bin. To use this method, run the following command:
call dbms_recycle.purge_table('<TABLE>');
Note- The TABLE parameter specifies the name of the table after the table is moved to the recycle bin.
- The account that you use must have the permissions to execute DROP statements on both your RDS instance and the recycle bin.
Example:
call dbms_recycle.purge_table('__innodb_1063');
- restore_table
This method is used to restore a table from the recycle bin. To use this method, run the following command:
call dbms_recycle.restore_table('<RECYCLE_TABLE>','<DEST_DB>','<DEST_TABLE>');
The following table describes the parameters for this method.
Parameter Description RECYCLE_TABLE The name of the table in the recycle bin. Note If you configure only this parameter, ApsaraDB RDS restores data to the original table.DEST_DB The name of the destination database to which you want to restore the table. DEST_TABLE The name of the destination table to which you want to restore the table. Note Superuser credentials are required to run the restore_table command. Therefore, you cannot run this command.Example:
mysql> call dbms_recycle.restore_table('__innodb_1063','testDB','testTable');