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

Prerequisites

The version of the PolarDB cluster is ApsaraDB PolarDB MySQL-compatible edition 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.

Features

  • 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 table objects that are deleted and moves these objects to a specific directory of the recycle bin. Objects that are not related to table objects:

      • Objects that are not related to the deleted tables: The system determines whether to retain the objects based on the statement that you execute. These objects are not recycled.
      • Objects that are attached to the tables and may modify the data of the tables: The system deletes the objects, which include triggers and foreign keys. Column statistics are moved to the recycle bin together with the tables, but are not deleted.
    • Purge tables

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

  • Permission control

    When a PolarDB cluster starts, 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 this database.

    You cannot execute the DROP TABLE statement on the 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 have 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. This ensures that the names are unique.
    "__" + <Storage Engine> + <SE private id>
    The following table describes the parameters of the feature.
    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, the value of this parameter in InnoDB is table id.
  • Independent recycling

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

    Note The storage usage of your cluster varies based on the retention period that you specify.

Precautions

  • If the __recycle_bin__ database and the tables that you want to recycle are in different file systems, the DROP TABLE operation moves files across tablespaces, which is time-consuming.
  • If the tables that you want to recycle are in a general tablespace that stores multiple tables, the files of the tablespace are not moved when you recycle one of the tables.

Parameter

Before you use the recycle bin feature, you must configure the required parameters. The following table describes the parameters of this feature.

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. Default value: OFF. You can enable the recycle bin at the session level or global level.
recycle_bin_retention The maximum retention period of the data in the recycle bin. Valid values: 86400 to 1209600. Unit: seconds. Default value: 604800. This value is equivalent to 7 days.

Manage the recycle bin

PolarDB provides the following statements that you can use to manage the recycle bin:

  • You can execute the following statement to view 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 query 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 the 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.
    RECYCLED_TIME The time at which the table was moved to the recycle bin.
    PURGE_TIME The estimated time at which the table is purged from the recycle bin.
  • You can execute the following statement to purge a specific 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 have the DROP permission on the original tables and the tables in the recycle bin.
    Example
    mysql> CALL DBMS_RECYCLE.purge_table('__innodb_1063');
  • You can execute the following statement to restore a table from the recycle bin:
    CALL DBMS_RECYCLE.restore_table('RECYCLE_TABLE','DEST_DB','DEST_TABLE');
    Note Only the clusters of ApsaraDB PolarDB MySQL-compatible edition 8.0 Cluster Edition whose revision versions are 8.0.1.1.12 or later allow you to restore tables from the recycle bin by executing the restore_table statement. For more information about how to check the version, see Query the kernel version number.
    The following table describes the parameters of the feature.
    Parameter Description
    RECYCLE_TABLE The name of the table that you want to restore from the recycle bin.
    Note If you specify only this parameter, the data of the original table is restored.
    DEST_DB The destination database of the table that you want to restore.
    DEST_TABLE The new name of the recovered table.
    Note To execute the preceding statement, your account must have 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');