Data definition language (DDL) statements cannot be rolled back. If a table is unintentionally deleted by using a DROP TABLE statement, the table data may be lost. Alibaba Cloud provides the recycle bin feature that allows you to temporarily store deleted tables. You can specify a retention period within which you can retrieve the deleted tables. In addition, Alibaba Cloud provides the DBMS_RECYCLE package that is used to manage the deleted tables in the recycle bin.

Parameters

The following table describes the parameters that you must 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 a specific session. You can reconfigure this parameter in the ApsaraDB for RDS console.
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 for RDS console.
recycle_scheduler Specifies whether to enable the thread that is used to asynchronously delete tables from the recycle bin. This parameter is temporarily unavailable.
recycle_scheduler_interval The polling interval that is followed by the thread to asynchronously delete tables from the recycle bin. Unit: seconds. Default value: 30. This parameter is temporarily unavailable.
recycle_scheduler_purge_table_print Specifies whether to log the operations that are performed by the thread to asynchronously delete tables from the recycle bin. This parameter is temporarily unavailable.

Introduction

  • Recycling and deletion
    • Recycling

      When you execute a TRUNCATE TABLE statement to delete a table, the system moves the deleted table to the recycle bin. Then, the system creates an empty table that has the same structure as the deleted table. The empty table resides in the same location as the deleted table.

      When you execute a DROP TABLE or DROP DATABASE statement to delete a table or a database, the system moves only the deleted tables to the recycle bin. The system deletes the other objects based on the following policies:

      • If no relationships exist between an object and the deleted tables, the system determines whether to retain the object based on the executed statement.
      • If an object is based on the deleted tables and may cause modifications to the data in these tables, the system deletes the object. These objects include triggers and foreign keys. The system does not delete column statistics. These statistics are stored to the recycle bin with the deleted 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 that is specified by the recycle_bin_retention parameter. If a table in the recycle bin is large, the system starts another background thread to asynchronously delete the large table.

  • Permission control

    When you start your RDS instance that runs MySQL, a database named __recycle_bin__ is initialized to store the data that is moved to the recycle bin. The __recycle_bin__ database is a system database. You cannot modify or delete the database.

    You cannot delete tables from the recycle bin by executing DROP TABLE statements. 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 delete tables from your RDS instance and the recycle bin by executing DROP TABLE statements.
  • 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, Alibaba Cloud 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 recycle bin configuration that you specify on an RDS instance is applied only to that instance. Therefore, the recycle bin configuration that you specify on your primary RDS instance will not be applied to its 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 separately.

    Note The storage usage of an RDS instance varies based on the retention period that you specify on that instance.

Precautions

  • After you execute a DROP TABLE statement to delete a table, the system may migrate the related data file from the tablespace that stores the table. This applies if the __recycle_bin__ database and the table reside in different file systems. In addition, this process is time-consuming.
  • A general tablespace may store more than one table. If you execute a DROP TABLE statement to delete a table from a general tablespace, the system does not migrate the related data file from the general tablespace.

Prerequisites

Your RDS instance runs MySQL 8.0.

Manage the recycle bin

AliSQL provides the following two management methods in the DBMS_RECYCLE package:
  • show_tables
    Displays all of the tables that are temporarily stored in the recycle bin. The following code snippet is an example of the show_tables method:
    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
    Manually deletes a table from the recycle bin. The following code snippet is an example of the purge_table method:
    call dbms_recycle.purge_table('<TABLE>');
    Note
    • The TABLE variable specifies the new name of the table after the table is moved to the recycle bin.
    • The account that you use must have the permissions to delete tables from your RDS instance and the recycle bin by executing DROP TABLE statements.

    Example:

    mysql> call dbms_recycle.purge_table('__innodb_1063');
    Query OK, 0 rows affected (0.01 sec)