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. To use this feature, you can install 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 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 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 mechanism

      When you execute a TRUNCATE TABLE statement to delete a table, ApsaraDB RDS moves the deleted table to the recycle bin. Then, ApsaraDB RDS creates an empty table in the same location by using the same schema as the deleted table. The empty table resides as the deleted table.

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

      • If no relationships exist between an object and the deleted tables, ApsaraDB RDS 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, 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 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 this 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 execute DROP statements on 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 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 your 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, ApsaraDB RDS 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, ApsaraDB RDS 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. 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
    Deletes the table from the recycle bin manually. 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 your RDS instance and the recycle bin.

    Example:

    call dbms_recycle.purge_table('__innodb_1063');
  • restore_table

    Restores 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 that you can configure to use this method.

    Parameter Description
    RECYCLE_TABLE The name of the table in the original database.
    Note If you specify 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 table in the destination database.
    Note To run the preceding command, you must have the ALTER_ACL and DROP_ACL permissions on the __recycle_bin__ database. In addition, you must have the CREATE_ACL and INSERT_ACL permissions on the table in the destination database.

    Example:

    mysql> call dbms_recycle.restore_table('__innodb_1063','testDB','testTable');