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. This specifies the period that 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 need to configure for the recycle bin feature.

Parameter Description
recycle_bin Specifies whether to enable the recycle bin feature. You can enable this feature for your RDS instance or for a specific session.
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.
recycle_scheduler Specifies whether to enable the thread that is used to asynchronously delete tables from the recycle bin.
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.
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.

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 is used to replace the deleted table in the location where the deleted table previously resides.

      When you execute a DROP TABLE or DROP DATABASE statement to delete a table or a database, the system moves only the related 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 of these tables, the system deletes the object. Such objects include triggers and foreign keys. Column statistics are not deleted. The statistics are stored in the recycle bin with the deleted tables.
    • Deletion

      The recycle bin starts a background thread to asynchronously delete tables that have been stored in the recycle bin longer than the period 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 an ApsaraDB RDS for MySQL instance is started, a database named __recycle_bin__ is initialized to store the data 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 call the call dbms_recycle.purge_table('<TABLE>'); interface to delete a table from the recycle bin.

    Note The account that you use must have the permissions to delete both the original tables and the tables in the recycle bin.
  • Table naming in the recycle bin
    Tables stored 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 the 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 of 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 takes effect only on your primary RDS instance. This configuration does not affect the secondary, read-only, and disaster recovery instances to which binary logs are replicated. For example, you can specify a 7-day retention period for your primary RDS instance and a 14-day retention period for the secondary instances.

    Note The storage space usage varies based on the specified retention period.

Precautions

  • If the __recycle_bin__ database and the table to be recycled reside in different file systems, executing a DROP TABLE statement will migrate the related data file from the tablespace that stores the table. This is a complex and time-consuming process.
  • If the table to be recycled resides in a general tablespace that stores multiple tables, recycling the table will 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 interfaces in the DBMS_RECYCLE package:
  • show_tables
    Displays all of the tables that are temporarily stored in the recycle bin. The interface 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 tables moved to the recycle bin.
    TABLE The new name of the table after the table is moved to the recycle bin.
    ORIGIN_SCHEMA The name of the database that hosts the table before the table is moved to the recycle bin.
    ORIGIN_TABLE The original 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 scheduled to be deleted from the recycle bin.
  • purge_table
    Manually deletes a table from the recycle bin. The interface is as follows:
    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 both 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)