Data definition language (DDL) operations cannot be rolled back. Data loss may occur due to user errors, such as the DROP TABLE operation. PolarDB provides the recycle bin feature and temporarily stores deleted tables to the recycle bin. You can customize the retention period during which the deleted tables can be recovered.

Prerequisites

The version of the PolarDB cluster is PolarDB for MySQL 8.0 and the revision version is 8.0.1.1.2 or later. For more information about how to check the version, see Query the kernel version number.

Introduction to the recycle bin

  • Recycle and purge tables
    • Recycle tables

      When you execute the DROP TABLE statement to delete a table or the DROP DATABASE statement to delete a database, PolarDB retains only the relevant table objects and moves them to the specific directory of the recycle bin. All the other objects are deleted based on the following rules:

      • For the objects that are not related to the deleted tables, the system determines whether to retain the objects based on the executed statement. These objects are not recycled.
      • For the attached objects that may modify data of tables, the system deletes these objects, such as triggers and foreign keys. Column statistics are not deleted and are moved to the recycle bin with tables.
    • Purge tables

      The recycle bin starts a background thread to asynchronously purge tables that have been stored longer than the time period specified by recycle_bin_retention. For tables with a large amount of data, the system starts another background thread to asynchronously purge these tables.

  • Permissions

    When a PolarDB cluster is started, a database named __recycle_bin__ is created as the database of the recycle bin. The __recycle_bin__ database is a system database. You cannot modify or delete it.

    You cannot execute the DROP TABLE statement on tables in the recycle bin. However, you can execute the CALL DBMS_RECYCLE.purge_table('table name'); statement to purge these tables.

    Note The account that you use to purge tables must be granted the DROP permission on the original tables and the tables in the recycle bin.
  • How to name tables in the recycle bin
    The recycle bin recycles tables from different databases to the __recycle_bin__ database. The recycled tables must be named in the following format to make sure that the names are unique.
    "__" + <Storage Engine> + <SE private id>

    The following table describes the parameters.

    Parameter Description
    Storage Engine The name of the storage engine.
    SE private id The unique value that is generated by the storage engine to identify a table. For example, this parameter specifies the ID of a table in InnoDB.
  • 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.

Considerations

  • If the __recycle_bin__ database of the recycle bin and the tables to be recycled are in different file systems, the DROP TABLE operation will move the tablespace files, which is time-consuming.
  • If the tables to be recycled are in a general tablespace that stores multiple tables, the related tablespace files are not moved when you recycle one of the tables.

Parameters

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

Note For more information about how to modify cluster parameters, see Specify cluster parameters.
Parameter Description
recycle_bin Specifies whether to enable the recycle bin. The default value is OFF. The feature covers the session level and the global level.
recycle_bin_retention The retention period of the data in the recycle bin. Valid values: 86400 to 1209600. Unit: seconds. Default value: 604800 (seven days).

Manage the recycle bin

PolarDB provides the following statements to manage the recycle bin:

  • Displays all tables that are temporarily stored in the recycle bin.
    CALL DBMS_RECYCLE.show_tables()

    Example

    Execute the following statement to view all tables in the recycle bin:

    mysql> CALL DBMS_RECYCLE.show_tables();
    The following result is returned:
    +-----------------+---------------+---------------+--------------+---------------------+---------------------+
    | 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 schema of the recycle bin.
    TABLE The name of the table after a table was moved to the recycle bin.
    ORIGIN_SCHEMA The schema of the original table before the table was moved to the recycle bin.
    ORIGIN_TABLE The original name of the table before the table was moved to the recycle bin.
    RECYCLED_TIME The time when the table was moved to the recycle bin.
    PURGE_TIME The estimated time when the table will be purged from the recycle bin.
  • You can execute the following statement to manually purge a specified table from the recycle bin.
    CALL DBMS_RECYCLE.purge_table('TABLE_NAME')
    Note
    • The TABLE_NAME parameter specifies the name of the table after the table was moved to the recycle bin.
    • The account that you use to purge tables must be granted the DROP permission on the original tables and the tables in the recycle bin.

    Example

    mysql> CALL DBMS_RECYCLE.purge_table('__innodb_1063');
  • Restores a table from the recycle bin.
    CALL DBMS_RECYCLE.restore_table('RECYCLE_TABLE','DEST_DB','DEST_TABLE');

    The following table describes the parameters.

    Parameter Description
    RECYCLE_TABLE The name of the table to be recovered from the recycle bin.
    Note If you specify only this parameter, data will be restored to the original table.
    DEST_DB The destination database for the recovered table.
    DEST_TABLE The new name for the recovered table.
    Note To execute the preceding statement, the account must be granted the ALTER_ACL and DROP_ACL permissions on the __recycle_bin__ database, and the CREATE_ACL and INSERT_ACL permissions on the destination table.

    Example

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