PolarDB for MySQL:Table recycle bin

Prerequisites

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

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.

• Permissions

  When a PolarDB cluster starts, a database named __recycle_bin__ is initialized 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 moves tables from different databases to the __recycle_bin__ database. The recycled tables must be named in the following format to ensure 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, the value of this parameter in InnoDB is table id.

• Independent recycling

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

  Note The storage space for the recycle bin depends 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.

Usage

Manage the recycle bin

PolarDB provides the following statements to manage the recycle bin:

• show_tables
  You can execute the following statement to view all tables that are temporarily stored in the recycle bin:

  call dbms_recycle.show_tables()

  Example:

  call dbms_recycle.show_tables();
  +-----------------+---------------+---------------+--------------+---------------------+---------------------+
  | 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.

  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.

• purge_table
  This method is used to manually delete a 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');

• restore_table
  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 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.
  • 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.

  The following table describes the 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:

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