Use recycle bin - ApsaraDB RDS - Alibaba Cloud Documentation Center

DDL operations such as DROP TABLE and TRUNCATE TABLE cannot be rolled back. A single accidental statement can destroy data permanently. The recycle bin feature intercepts these operations and holds dropped or truncated tables in a system database (__recycle_bin__) for a configurable retention period, giving you a recovery window without requiring a full backup restore.

Supported versions

MySQL version	Minimum minor engine version
MySQL 8.0	20191225
MySQL 5.7	20210430

TRUNCATE TABLE recycling requires MySQL 8.0 with minor engine version 20200331 or later.

Parameters

Configure the following parameters in the ApsaraDB RDS console.

Parameter	Default	Description
`loose_recycle_bin`	OFF	Enables the recycle bin. Configurable per instance or per session.
`loose_recycle_bin_retention`	604800 (7 days)	Retention period for tables in the recycle bin, in seconds.
`loose_recycle_scheduler`	OFF	Enables the background thread that asynchronously purges expired tables.
`loose_recycle_scheduler_interval`	30	Polling interval for the purge thread, in seconds. Temporarily unavailable.
`loose_recycle_scheduler_purge_table_print`	OFF	Enables logging for asynchronous purge operations. Temporarily unavailable.

Important

To prevent disk space exhaustion, set loose_recycle_bin_retention to match your recovery window and set loose_recycle_scheduler to ON.

How it works

Recycling behavior

TRUNCATE TABLE: The original table moves to the recycle bin. A new empty table with the same schema is created in the original location.

DROP TABLE / DROP DATABASE: Only objects directly associated with the dropped table are recycled:

Triggers and foreign keys are deleted, not recycled.
Column statistics are moved to the recycle bin with the dropped table.
Objects unrelated to the dropped table are not recycled.

Automatic purging

A background thread purges tables that have exceeded the retention period set by loose_recycle_bin_retention. For large tables, a separate background thread handles the purge asynchronously.

System database

AliSQL creates a system database named __recycle_bin__ when the RDS instance starts. This database cannot be modified or deleted. Use call dbms_recycle.purge_table('<TABLE>'); to remove a table from the recycle bin — DROP TABLE does not work on recycled tables.

The account must have DROP permissions on both the RDS instance and the __recycle_bin__ database.

Table naming in the recycle bin

Tables from different databases may share the same name. To guarantee uniqueness, the recycle bin renames each recycled table using the format:

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

For InnoDB tables, the SE private id is the table ID. For example, a recycled InnoDB table with ID 1063 is stored as __innodb_1063.

Independent recycling

Recycle bin configurations are instance-specific. Settings on the primary instance do not propagate to secondary, read-only, or disaster recovery instances. A 7-day retention period on the primary instance and a 14-day retention period on a secondary instance are managed independently, and storage usage varies per instance accordingly.

Limitations

Limitation	Details
Cross-filesystem migration	If `__recycle_bin__` and the source table reside on different file systems, `DROP TABLE` triggers a data file migration across file systems. This can be time-consuming for large tables.
General tablespace	For tables stored in a general tablespace, `DROP TABLE` does not migrate the data file out of the tablespace.

Manage the recycle bin

AliSQL provides the DBMS_RECYCLE package with three operations for managing recycled tables.

View recycled tables

call dbms_recycle.show_tables();

Example output:

+-----------------+---------------+---------------+--------------+---------------------+---------------------+
| 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 |
+-----------------+---------------+---------------+--------------+---------------------+---------------------+

Column	Description
`SCHEMA`	Database in the recycle bin that holds the recycled table.
`TABLE`	Name of the table in the recycle bin.
`ORIGIN_SCHEMA`	Original database the table was dropped from.
`ORIGIN_TABLE`	Original table name before it was dropped.
`RECYCLED_TIME`	Timestamp when the table was moved to the recycle bin.
`PURGE_TIME`	Scheduled timestamp for automatic deletion.

Purge a table

Permanently delete a specific table from the recycle bin.

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

<TABLE> is the table name in the recycle bin (the TABLE column in show_tables() output), not the original table name.

The account must have DROP permissions on both the RDS instance and the __recycle_bin__ database.

Example:

call dbms_recycle.purge_table('__innodb_1063');

Restore a table

Two methods are available to restore a recycled table.

Method 1: Use restore_table (requires superuser credentials)

Super-user credentials are required to run the restore_table command. Therefore, you cannot run this command.

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

Parameter	Description
`RECYCLE_TABLE`	Name of the table in the recycle bin. Specify only this parameter to restore the table to its original location with its original name.
`DEST_DB`	Destination database for the restored table.
`DEST_TABLE`	Name for the restored table in the destination database.

Example:

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

Method 2: Use INSERT ... SELECT (no superuser credentials required)

Use this method if you do not have superuser credentials.

Identify the table to restore.

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 |
+-----------------+---------------+---------------+--------------+---------------------+---------------------+

Create a destination table with the same schema as the recycled table.

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;

Copy the data from the recycle bin into the destination table.

INSERT INTO `db1`.`t1` SELECT * FROM `__recycle_bin__`.`__innodb_1132`;