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
- 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. |
- 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');