Restore a table from the table recycle bin - PolarDB - Alibaba Cloud Documentation Center

You cannot roll back data definition language (DDL) operations. For example, if you accidentally delete a table by using the DROP TABLE statement, you cannot roll back the DDL operation and data loss may occur. PolarDB provides the table recycle bin feature that temporarily stores deleted tables. You can restore tables that are accidentally deleted from the table recycle bin.

Background information

For more information about the table recycle bin feature, see Table recycle bin.

Prerequisites

Your cluster runs PolarDB for MySQL 8.0 whose revision version is 8.0.1.1.2 or later.
The table recycle bin feature has been enabled before the data is accidentally deleted. To enable the table recycle bin feature, set the loose_recycle_bin parameter of the cluster to ON. For information about how to configure parameter values, see Configure cluster and node parameters.
The time that elapsed since the accidental deletion of the table does not exceed the maximum data retention period of the table recycle bin. You can use the loose_recycle_bin_retention parameter to specify the maximum data retention period of the table recycle bin (in seconds). For information about how to configure parameter values, see Configure cluster and node parameters.

Procedure

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

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)

The following table describes the parameters in the returned result.

Parameter	Description
SCHEMA	The schema of the table recycle bin.
TABLE	The name of the table after the table was moved to the table recycle bin.
ORIGIN_SCHEMA	The schema of the original table before the table was moved to the table recycle bin.
ORIGIN_TABLE	The original name of the table.
RECYCLED_TIME	The time when the table was moved to the table recycle bin.
PURGE_TIME	The estimated time when the table is purged from the recycle bin.

Execute the following statement to quickly restore a table from the table recycle bin:

CALL DBMS_RECYCLE.restore_table('RECYCLE_TABLE','DEST_DB','DEST_TABLE');

Note

You can execute the restore_table statement to quickly restore tables from the table recycle bin only if your cluster runs PolarDB for MySQL 8.0 whose revision version is 8.0.1.1.12 or later. To query the version of your cluster, see the "Query the engine version" section of the Engine versions topic.

The following table describes the parameters in the statement.

Parameter	Description
RECYCLE_TABLE	The name of the table that you want to restore from the table recycle bin. Note If you specify only this parameter, the table is restored to its state before it was deleted.
DEST_DB	The destination database to which you want to restore the table.
DEST_TABLE	The new name of the restored table.

Sample statement:

CALL dbms_recycle.restore_table('__innodb_1063','testDB','testTable');