Data definition language (DDL) statements cannot be rolled back. If tables are unintentionally deleted by using DROP TABLE statements, data in the tables may be lost. PolarDB provides the recycle bin feature. You can use this feature to temporarily store the deleted tables to your recycle bin. You can also specify a retention period during which you can retrieve the deleted tables.

Prerequisites

The PolarDB cluster version is PolarDB for MySQL 8.0 and the revision version is 8.0.1.1.2 or later.

Considerations

  • The database that stores the data moved to the recycle bin and the table to be recycled may reside in different file systems. In this case, the .ibd files are moved to the directory of the recycle bin after you execute a DROP TABLE statement. This is time-consuming.
  • If the table to be recycled resides in a general tablespace that stores multiple tables, the related .ibd file is not moved from the general tablespace when you recycle one of the tables.

Introduction to the recycle bin

  • Recycling and deletion
    • Recycling

      When you execute a DROP TABLE statement to delete a table or execute a DROP DATABASE statement to delete a database, the system retains and moves only the related table objects to the __recycle_bin__ directory. 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 and the deleted tables are stored in the recycle bin.
    • 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 a PolarDB cluster is started, 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 system 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 to delete tables from the recycle bin must have the DROP permissions on the original tables and the tables that are moved to the recycle bin.
  • Table naming in the recycle bin
    Tables that are 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

    For example, you can specify a 7-day retention period for your primary node and a 14-day retention period for read-only nodes.

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

Parameters

Before you use the recycle bin feature, you must specify the following parameters.

Parameter Description
recycle_bin Specifies whether to enable the recycle bin feature. You can enable this feature globally or for sessions.
recycle_bin_retention The retention period during which data in the recycle bin can be retained. Valid values: 1 to 31536000. 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.

Manage the recycle bin

PolarDB provides the following statements for you to manage the recycle bin:

  • You can execute the following statement to display all the tables that are temporarily stored in the recycle bin:
    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 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.
  • You can execute the following statement to manually delete a table from the recycle bin:
    DBMS_RECYCLE.purge_table('table_name')
    Note
    • The table_name parameter specifies the new name of the table after the table is moved to the recycle bin.
    • The account that you use to delete tables from the recycle bin must have the DROP permissions on the original tables and the tables that are moved to the recycle bin.

    Example

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