Recover Dropped Tables with Recycle Bin - AnalyticDB for MySQL

The table recycle bin protects against accidental data loss from DROP TABLE statements. Dropped tables move to a hidden database named ADB_RECYCLE_BIN and are retained for three days by default, giving you time to recover them before they are permanently deleted.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster running V3.2.3.0 or later

Note

To check the minor version of a Data Lakehouse Edition cluster, run SELECT adb_version();. To upgrade the minor version, contact technical support. For Data Warehouse Edition clusters, see Update the minor version of a cluster.

Limitations

Supported table types: internal tables with engine XUANWU or XUANWU_V2 only. External tables are not supported.
Tables in the recycle bin continue to consume disk storage.
Running DROP TABLE while INSERT OVERWRITE SELECT is in progress moves the table to the recycle bin and causes the INSERT OVERWRITE SELECT statement to fail.
Running DROP TABLE while BUILD is in progress moves the table to the recycle bin without interrupting the BUILD statement.
To run any recycle bin statement (SHOW, RESTORE, PURGE), you must have the DROP permission on the source table. Only PURGE, RESTORE, and SHOW statements are supported on tables in the recycle bin.

How it works

The first time you run a DROP TABLE statement, AnalyticDB for MySQL creates a hidden database named ADB_RECYCLE_BIN and moves the dropped table there. Subsequent DROP TABLE statements move tables to the same database.

Important

DROP TABLE ... FORCE permanently deletes the table and bypasses the recycle bin.

Retention and cleanup

By default, tables are retained in the recycle bin for three days (259,200,000 ms). After the retention period expires, AnalyticDB for MySQL automatically deletes them using a background cleanup thread that runs every 60 seconds. You can also run the PURGE statement to manually clean up the recycle bin. For more information, see the Delete tables from the recycle bin section.

Important

Set an appropriate retention period to avoid exhausting disk storage.

Naming convention

Tables from different databases are stored in a single recycle bin. To ensure unique names, each table is renamed using the format SourceDatabaseName_SourceTableName_Timestamp.

Example: A customer table from the adb_demo database, dropped on January 1, 2024 at 00:00:00, is renamed adb_demo_customer_1704038400000.

Note

If the combined name exceeds the maximum length, the source database name and table name are truncated in sequence and may appear incomplete.
If the same table name is dropped from the same database again, it is stored under a different timestamp.

Configure the recycle bin

Use SET ADB_CONFIG to configure the recycle bin.

Parameter	Default	Description
`DROP_FORCE`	`FALSE`	Set to `TRUE` to permanently delete tables on `DROP TABLE`, bypassing the recycle bin. Set to `FALSE` to move tables to the recycle bin.
`RECYCLE_BIN_EXPIRED_TIME`	`259200000` (3 days)	Retention period in milliseconds. Applies only to tables dropped after this setting is changed. Existing tables in the recycle bin retain their original retention period.
`ENABLE_RECYCLE_BIN_CLEAN_EXPIRED_TABLE`	`TRUE`	Enable or disable the background cleanup thread. Do not disable this unless required for troubleshooting.
`RECYCLE_BIN_CLEAN_EXPIRED_TABLE_INTERVAL`	`60000` (60 seconds)	Interval in milliseconds at which the cleanup thread runs.

Change the retention period

SET adb_config RECYCLE_BIN_EXPIRED_TIME=<RetentionPeriodInMilliseconds>;

Important

The new retention period applies only to tables dropped after this change. Tables already in the recycle bin retain their original expiration time.

Example: Set the retention period to two days:

-- 2 days = 2 × 24 × 3600 × 1000 = 172,800,000 ms
SET adb_config RECYCLE_BIN_EXPIRED_TIME=172800000;

Query tables in the recycle bin

Syntax

-- List all tables in the recycle bin
SHOW RECYCLE_BIN ALL;

-- List tables from a specific source database and table name
SHOW RECYCLE_BIN TABLE <SourceDatabaseName>.<SourceTableName>;

-- List tables by source table name (across all databases)
SHOW RECYCLE_BIN TABLE <SourceTableName>;

-- List all tables from a specific source database
SHOW RECYCLE_BIN DATABASE <SourceDatabaseName>;

Examples

List all tables in the recycle bin:

SHOW RECYCLE_BIN ALL;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | adb_demo_orders_1720086094102   | adb_demo         | orders          | 2024-07-04 17:41:37 | 2024-07-07 17:41:37 |
| testUser | ADB_RECYCLE_BIN | testdb_customer_1720085752664   | testdb           | customer        | 2024-07-04 17:35:56 | 2024-07-07 17:35:56 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

List a specific table from `adb_demo.customer`:

SHOW RECYCLE_BIN TABLE adb_demo.customer;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

List all versions of the `customer` table across all databases:

SHOW RECYCLE_BIN TABLE customer;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | testdb_customer_1720085752664   | testdb           | customer        | 2024-07-04 17:35:56 | 2024-07-07 17:35:56 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

List all tables from the `adb_demo` database:

SHOW RECYCLE_BIN DATABASE adb_demo;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | adb_demo_orders_1720086094102   | adb_demo         | orders          | 2024-07-04 17:41:37 | 2024-07-07 17:41:37 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

Restore tables from the recycle bin

Restore rules

Restored tables use their original names from before they were dropped.
If the source database already contains a table with the same name, rename, delete, or move that existing table before restoring.
If multiple versions of a table exist in the recycle bin (same source database and table name, different timestamps), RESTORE RECYCLE_BIN ALL restores the version with the latest timestamp and reports an error for the remaining versions.

Syntax

-- Restore all tables to their source databases
RESTORE RECYCLE_BIN ALL;

-- Restore a specific table using its recycle bin name
RESTORE RECYCLE_BIN TABLE <RecycleBinTableName>;

The <RecycleBinTableName> is the name shown in the tableName column of SHOW RECYCLE_BIN output (format: SourceDatabaseName_SourceTableName_Timestamp).

Restore a specific version of a table

When multiple versions of a dropped table exist in the recycle bin, use the following steps to restore a specific version:

Query the recycle bin to find all versions and their recycle bin names:
```
SHOW RECYCLE_BIN TABLE <SourceTableName>;
```
Identify the target version by comparing the recycledTime column with the timestamp suffix in tableName.
Restore the specific version using its recycle bin name:
```
RESTORE RECYCLE_BIN TABLE <RecycleBinTableName>;
```

Example: To restore the customer table as it existed before the drop on 2024-07-04 17:42:00:

RESTORE RECYCLE_BIN TABLE adb_demo_customer_1720086117277;

Delete tables from the recycle bin

Use PURGE to permanently delete tables from the recycle bin before they expire.

Syntax

-- Permanently delete all tables from the recycle bin
PURGE RECYCLE_BIN ALL;

-- Permanently delete a specific table from the recycle bin
PURGE RECYCLE_BIN TABLE <RecycleBinTableName>;

Example

Permanently delete the adb_demo_customer_1720086117277 table:

PURGE RECYCLE_BIN TABLE adb_demo_customer_1720086117277;