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

Your RDS instance runs one of the following database engine versions:
  • MySQL 8.0
  • MySQL 5.7

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 log the operations performed by the thread that is used to asynchronously delete tables from the recycle bin. This parameter is temporarily unavailable.
Note To prevent disk space from being exhausted, we recommend that you set the loose_recycle_bin_retention parameter to an optimal value and the loose_recycle_scheduler parameter to ON.

Introduction

  • Recycling and deletion
    • Recycling
      When you execute a TRUNCATE TABLE statement to truncate a table, ApsaraDB RDS moves the truncated table to the recycle bin. Then, ApsaraDB RDS creates an empty table in the original location of the truncated table by using the same schema as the truncated table.
      Note The recycling mechanism is supported only when your RDS instance runs MySQL 8.0.

      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 of the recycle bin for the primary RDS instance 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 Super-user credentials are required to run the restore_table command. Therefore, you cannot run this command. If you need this command to be run, you must submit a ticket.

    Example:

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