tencent cloud

TencentDB for MySQL

Recycle Bin

PDF
Focus Mode
Font Size
Last updated: 2026-01-19 12:42:48

Feature Introduction

The recycle bin feature supports moving tables on which drop table and truncate table operations are performed by users to the recycle bin, allowing data to be restored when needed. This avoids accidental table deletion by users and reduces the impact of drop table operations on I/O.

Supported Versions

Kernel version MySQL 8.0 20230630 and above.

Applicable Scenarios

Move tables on which drop table and truncate table operations are performed by users to the recycle bin. This avoids the problem that accidentally deleted data cannot be restored.

Usage Instructions

Supports Automatic Cleanup of Tables in the Recycle Bin

Prerequisites
The value of parameter cdb_recycle_scheduler_interval is greater than 0.
The value of parameter event scheduler is on.
Note:
After the parameter event scheduler is set to on, you can use the select * from information_schema.events; command to confirm running or non-running events in the current database instance.
If the value of parameter cdb_recycle_scheduler_interval has been changed, the event scheduler needs to be restarted for the new value to take effect.
The value of parameter cdb_recycle_bin_enabled is on.
Usage
When the prerequisites are met, the automatic cleanup thread supports cleaning up tables in the recycle bin. The thread runs every cdb_recycle_scheduler_interval seconds to determine whether tables in the recycle bin have exceeded the cdb_recycle_bin_retention duration. If exceeded, the corresponding tables in the recycle bin will be purged.

Parameter Description

Parameter Name
Dynamically Provisioned
Type
Default Value
Valid Value
Description
cdb_recycle_bin_enabled
yes
bool
OFF
ON/OFF
Enable status of the recycle bin feature.
cdb_recycle_bin_retention
yes
ulong
604800
0 - 4294966272
This parameter sets the duration (in seconds) after which tables moved to the recycle bin will be purged by the automatic cleanup thread. The default value is 604800 seconds (one week).
cdb_recycle_scheduler_interval
yes
ulong
0
0 - 4294966272
This parameter sets the scheduling interval (in seconds) for the automatic cleanup thread, indicating that automatic cleanup is triggered at regular intervals.
The default value is 0, indicating that the capability to automatically clean up tables in the recycle bin is not enabled.
When the value is greater than 0, the system will periodically initiate automatic cleanup tasks to remove tables that have exceeded the retention duration configured in cdb_recycle_bin_retention.
Note:
For the description and prerequisites of automatically cleaning up tables in the recycle bin, see Support for Automatic Cleanup of Tables in the Recycle Bin.

New Database Description

__cdb_recycle_bin__: database used to store dropped tables. During instance startup, a database named __cdb_recycle_bin__ is initialized as the dedicated database for the recycle bin. __cdb_recycle_bin__ is a system-level database and cannot be modified or deleted directly.
For tables in the recycle bin, you cannot directly execute the drop table statement, but you can use the following syntax to clear them.
clear <TABLE_NAME> from recycle_bin;
or
call sys.recycle_bin_purge_table('<RECYCLE_NAME>');
<TABLE_NAME>: name of a table before it is moved to the recycle bin.
<RECYCLE_NAME>: name of a table in the recycle bin.

New System Table Description

mysql.recycle_bin_info: displays relevant information on tables in the recycle bin. The table definition is as follows:
CREATE TABLE IF NOT EXISTS recycle_bin_info (
table_name varchar(64) NOT NULL, --Name of a table after it is removed to the recycle bin.
origin_schema VARCHAR(64) NOT NULL, --Database of a table before it is moved to the recycle bin.
origin_table VARCHAR(64) NOT NULL, --Name of a table before it is removed to the recycle bin.
drop_time timestamp NOT NULL, --Time when a table was dropped.
purge_time timestamp NOT NULL, --Time when a table will be cleared.
PRIMARY KEY(table_name),
KEY(purge_time),
KEY `idx_drop_time` (`drop_time`),
KEY `idx_schema_table` (`origin_schema`,`origin_table`)
) ENGINE=INNODB CHARACTER SET latin1 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC TABLESPACE=mysql
When the current time reaches the time specified by purge_time, the corresponding table in the recycle bin is cleared. You can set the clearing time for dropped tables with the parameter cdb_recycle_bin_retention. This variable determines that the corresponding table in the recycle bin will be cleared by the automatic cleanup thread after the duration (in seconds) specified by cdb_recycle_bin_retention.
Note:
purge_time of a table indicates that the table will be cleared after a retention period specified by cdb_recycle_bin_retention, starting from the time specified by drop_time.

Support for truncate table

Tables on which a truncate table operation is performed can be moved to the recycle bin.
truncate table limits:
Temporary tables are not supported.
Tables with foreign keys are not supported.
Data directories and index directories with the table attribute are not supported.

Support for drop tables if exists

drop tables if exists is supported. Tables that exist are moved to the recycle bin, while no operation is performed on tables that do not exist.

Support for drop database

When a drop database operation is performed, the view, event, trigger, procedure, function, and sequence information is lost. Therefore, the recycle bin only ensures that the data is retained. The view, event, trigger, procedure, function, and sequence information may be lost.

Syntax Description

After the recycle bin feature is enabled, all dropped tables in the instance are moved to the __cdb_recycle_bin__ database. You can execute show recycle_bin; or select mysql.recycle_bin_info; to view relevant information on all tables in the recycle bin.
restore db.table from recycle_bin; restores the specified table from the recycle bin. If multiple tables with the same name exist, the most recently deleted table is restored.
restore db.table from recycle_bin with {timestamp('expr')} {recycle_name $recycle_name}; restores the table corresponding to the specified name and deletion time from the recycle bin if timestamp is specified. If recycle_name is specified, db and table are not used for searching because recycle_name is unique. db.table indicates the table that is found by recycle_name and then restored. Therefore, this command can be used to restore tables to other schemas or modify the name of a restored table.
restore database db_name from recycle_bin; restores all tables in a specified database. If tables with the same name exist, an error is reported.
clear recycle_bin; clears the entire recycle bin.
clear recycle_bin {all} {db.table} {before timestamp $time}; clears a table with a specified name or before the time specified by timestamp. If multiple tables with the same name exist, the oldest one is deleted. If all is specified, all tables with the same name are deleted.
show recycle_bin; displays the recycle bin information.
drop ... without recycle_bin; can forcibly delete a table rather than remove it to the recycle bin.

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback