DDL statements cannot be rolled back. If a table is deleted by accident, the data may be lost. Alibaba Cloud provides the recycle bin feature to temporarily store deleted tables. You can set a retention period to define a time period in which you can retrieve data. The DBMS_RECYCLE package can be installed to use the recycle bin feature.

Parameters

The following table describes the configuration parameters of the recycle bin feature.

Parameter Description
recycle_bin Specifies whether to enable the recycle bin feature. You can choose to enable this feature for a session or globally.
recycle_bin_retention The time to retain deleted data in the recycle bin. Unit: seconds. Default value: 604,800 (seven days).
recycle_scheduler Specifies whether to enable the thread that performs asynchronous deletion.
recycle_scheduler_interval The polling interval of the asynchronous deletion thread. Unit: seconds. Default value: 30.
recycle_scheduler_purge_table_print Specifies whether to display the logs generated during the asynchronously deleting process.

Introduction

  • Recycling and deleting mechanism
    • Recycling mechanism
      When the DROP TABLE/DATABASE statement is executed, related table objects are stored to the corresponding directory of the recycle bin. The deleting policies for other objects are as follows:
      • Objects that are not related to tables can be directly deleted by using statements. These objects are not recycled.
      • Objects that are attached to tables and may modify table data are directly deleted. Such objects include triggers and foreign keys. Column statistics are not deleted and are stored in the recycle bin together with the table.
    • Deleting mechanism

      The recycle bin will start a backend thread to asynchronously delete table objects stored in the recycle bin that exceed the recycle_bin_retention period. If a table contains a large volume of data, another thread is started to asynchronously delete the table.

  • Permissions

    When an ApsaraDB RDS for MySQL instance is started, a __recycle_bin__ database is created to store the recycle bin data. You cannot modify or delete the __recycle_bin__ database because it is a system database.

    You can delete tables from the recycle bin by executing the call dbms_recycle.purge_table('<TABLE>'); statement rather than the DROP TABLE statement.

    Note Your instance account must have the DROP permissions on the original tables and the tables in the recycle bin.
  • Naming conventions for tables in the recycle bin
    The deleted tables that are stored in the __recycle_bin__ database are from various databases and may have the same name. To ensure that each name in the recycle bin is unique, the tables are renamed in the following format for identification.
    "__" + <Storage Engine> + <SE private id>

    The following table describes the parameters.

    Parameter Description
    Storage Engine The name of the storage engine of the table.
    SE private id The unique value generated by the storage engine for the table. For example, the unique value of an InnoDB table is the ID of the table.
  • Independent recycling

    The recycle bin configurations only take effect for the current instance, and do not affect the secondary instance, read-only instance, and disaster recovery instance that are backed up from the primary instance by using the binlog. For example, you can specify the retention period for deleted tables as seven days for the primary instance, and 14 days for the secondary instance.

    Note The space usage may vary between instances because of different retention periods.

Precautions

  • If the __recycle_bin__ database and the table to be deleted are in different file systems, the data files in the tablespace must be migrated after the DROP TABLE statement is executed, which takes a long time.
  • A general tablespace can store multiple tables. If one of the tables is deleted, related data files in the tablespace are not migrated.

Prerequisites

The instance version is ApsaraDB RDS for MySQL 8.0.

Manage the recycle bin

AliSQL provides two management interfaces in the DBMS_RECYCLE package. They are described as follows:
  • show_tables
    Displays all tables temporarily stored in the recycle bin. The statement is as follows:
    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 recycle bin data.
    TABLE The name of the table after the table is stored to the recycle bin.
    ORIGIN_SCHEMA The name of the database to which the table belongs.
    ORIGIN_TABLE The name of the table before being recycled.
    RECYCLED_TIME The time when the table is 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. The statement is as follows:
    call dbms_recycle.purge_table('<TABLE>');
    Note
    • TABLE specifies the name of the table after the table is stored to the recycle bin.
    • Your instance account must have the DROP permissions on the original tables and the tables in the recycle bin.

    Example:

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