All Products
Search
Document Center

PolarDB:Table recycle bin

Last Updated:Feb 20, 2024

Data definition language (DDL) operations cannot be rolled back. Data loss may occur due to accidental operations. For example, data may be lost when a user calls the DROP TABLE operation. PolarDB for MySQL 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 PolarDB for MySQL cluster is a PolarDB for MySQL 8.0.1 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 for MySQL 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 for MySQL 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

Before you use the recycle bin feature, you must configure the following parameters: loose_recycle_bin specifies whether to enable the recycle bin, loose_recycle_bin_retention specifies the maximum retention period of the data in the recycle bin, and loose_recycle_scheduler specifies whether to enable the thread that is used to asynchronously purge the recycle bin. For more information, see Specify cluster and node parameters. The following table describes the parameters.

Parameter

Level

Description

loose_recycle_bin

Global and session

Specifies whether to enable the recycle bin. Default value: OFF. Valid values:

  • ON

  • OFF

loose_recycle_bin_retention

Global

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.

loose_recycle_scheduler

Global

Specifies whether to enable the thread that is used to asynchronously purge the recycle bin. Default value: OFF. Valid values:

  • ON

  • OFF

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