ApsaraDB RDS:Recycle bin

Prerequisites

Recycle bin parameters

The recycle bin feature includes five parameters:

How it works

• Recycling and purging mechanism

  • Recycling mechanism

    A TRUNCATE TABLE statement moves the original table to a dedicated recycle bin directory and creates a new table with the same structure in its place.

    Note

    This feature is supported only in the following versions:

    • RDS for MySQL 8.4

    • RDS for MySQL 8.0 with a minor engine version of 20200331 or later

    A DROP TABLE or DROP DATABASE statement moves only the related table objects to a dedicated recycle bin directory. Other objects are handled as follows:

    • The operation does not recycle objects unrelated to the table. They are either retained or deleted based on the specific operation statement.

    • The operation deletes dependent objects that can modify table data, such as triggers and foreign keys. However, it does not purge column statistics but moves them to the recycle bin along with the table.

  • Purging mechanism

    The recycle bin starts a background thread to asynchronously purge table objects that have exceeded the retention period set by the loose_recycle_bin_retention parameter. If the purge encounters a large table, it starts another background thread to delete the table asynchronously.

• Permissions

  When an RDS for MySQL instance starts, it initializes a database named __recycle_bin__, which is exclusively used by the recycle bin. __recycle_bin__ is a system database that you cannot directly modify or delete.

  Although you cannot directly run a drop table statement on tables in the recycle bin, you can use the call dbms_recycle.purge_table('<TABLE>'); command to purge them.

  Note

  The account requires the DROP permission on both the original table and the table in the recycle bin.

• Table naming convention in the recycle bin

  The recycle bin consolidates tables from different databases into the single __recycle_bin__ database. To ensure unique table names, the recycle bin uses the following format:

  "__" + <Storage Engine> + <SE private id>

  The following table describes the components of the naming format.

  Parameter	Description
  Storage engine	The name of the storage engine.
  SE private id	A unique ID generated by the storage engine for each table. For example, in the InnoDB engine, this is the table ID.

• Independent recycling

  Recycle bin settings affect only the local instance. They do not propagate to nodes with log replication enabled, such as secondary instances, read-only instances, and disaster recovery instances. For example, you can configure a 7-day retention period on a primary instance and a 14-day retention period on a secondary instance.

  Note

  Different retention periods can result in significant differences in disk space usage between instances.

Usage notes

• If the recycle bin database and the table to be recycled reside on different file systems, running a drop table statement initiates a data file migration that can be time-consuming.

• When a table shares a general tablespace with other tables, recycling that table does not move the related data file.

Manage the recycle bin

AliSQL provides three functions in the DBMS_RECYCLE package to manage the recycle bin:

• View tables in the recycle bin

  To view all tables in the recycle bin, use the following command from the DBMS_RECYCLE package:

  call dbms_recycle.show_tables();

  Example:

  mysql> 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)

  Parameter	Description
  SCHEMA	The name of the database for the recycle bin.
  TABLE	The name of the table in the recycle bin.
  ORIGIN_SCHEMA	The original database name.
  ORIGIN_TABLE	The original table name.
  RECYCLED_TIME	The time when the table was moved to the recycle bin.
  PURGE_TIME	The time when the table is scheduled to be purged from the recycle bin.

• Manually purge a table from the recycle bin

  To manually purge a table from the recycle bin, use the following command from the DBMS_RECYCLE package:

  call dbms_recycle.purge_table('<TABLE>');

  Note

  • <TABLE> is the name of the table in the recycle bin.

  • The account requires the DROP permission on both the original table and the table in the recycle bin.

  Example:

  call dbms_recycle.purge_table('__innodb_1063');

• Restore a table from the recycle bin

  • To restore a table from the recycle bin, use the interface provided by the DBMS_RECYCLE package.

    The command is as follows:

    call dbms_recycle.restore_table('<RECYCLE_TABLE>','<DEST_DB>','<DEST_TABLE>');

    The following table describes the parameters.

    Parameter	Description
    RECYCLE_TABLE	The name of the table in the recycle bin to restore. Note If you specify only this parameter, the table is restored to its original location with its original name.
    DEST_DB	The name of the destination database.
    DEST_TABLE	The name of the destination table.

    Note

    The restore_table command requires the SUPER permission. Because user accounts are not granted this permission, you cannot manually run this command.

    Example:

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

  • Restore data from a table in the recycle bin by using INSERT ... SELECT.

    First, query all tables in the recycle bin to find the name of the table that you want to restore in the __recycle_bin__ database. Then, create a destination table with the same structure and use an INSERT ... SELECT statement to import the data into the destination table. For example:

    mysql> call dbms_recycle.show_tables();
    +-----------------+---------------+---------------+--------------+---------------------+---------------------+
    | SCHEMA          | TABLE         | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
    +-----------------+---------------+---------------+--------------+---------------------+---------------------+
    | __recycle_bin__ | __innodb_1132 | sbtest        | sbtest1      | 2024-07-31 15:08:56 | 2024-08-07 15:08:56 |
    +-----------------+---------------+---------------+--------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    
    mysql> CREATE TABLE `db1`.`t1` (
        ->   `id` int NOT NULL AUTO_INCREMENT,
        ->   `k` int NOT NULL DEFAULT '0',
        ->   `c` char(120) NOT NULL DEFAULT '',
        ->   `pad` char(60) NOT NULL DEFAULT '',
        ->   PRIMARY KEY (`id`),
        ->   KEY `k_1` (`k`)
        -> ) ENGINE=InnoDB AUTO_INCREMENT=400001 DEFAULT CHARSET=utf8mb3;
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    
    mysql> insert into `db1`.`t1` select * from `__recycle_bin__`.`__innodb_1132`;
    Query OK, 400000 rows affected (2.76 sec)
    Records: 400000  Duplicates: 0  Warnings: 0