ApsaraDB RDS:Recycle bin

Prerequisites

Parameters

The following table describes the parameters that you can configure for the recycle bin feature.

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.

• 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 the call 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');