Data definition language (DDL) statements cannot be rolled back. If you accidentally delete a table, data loss may occur. For example, the DROP TABLE operation may cause data loss. PolarDB provides the table recycle bin feature. Deleted tables are transferred to Recycle Bin, so you can restore tables from Recycle Bin.

Background information

For more information about Recycle Bin, see Recycle bin.

Prerequisites

  • The version of your cluster must be PolarDB for MySQL 8.0 or later and the minor engine version of the PolarDB kernel must be 8.0.1.1.2 or later.
  • The recycle bin feature must be enabled before the data is deleted accidentally. To enable the feature, you can set the recycle_bin parameter to ON.
  • The point in time when the table is deleted accidentally does not exceed the maximum retention period of the data in Recycle Bin. You can specify the maximum retention period in the recycle_bin_retention parameter (unit: second).

Procedure

  1. Run the following command to view all tables in 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)

    The preceding returned results consist of the following parameters:

    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.
  2. Run the following command to restore a table in Recycle Bin:
    Note Only the clusters of PolarDB for MySQL 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 engine version.
    CALL DBMS_RECYCLE.restore_table('RECYCLE_TABLE','DEST_DB','DEST_TABLE');

    The preceding commands consist of the following parameters:

    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.
    Example:
    mysql> call dbms_recycle.restore_table('__innodb_1063','testDB','testTable');