Table Recycle Bin

Constraints

• The kernel version of your TaurusDB instance must be 2.0.57.240900 or later.
• If there is a database called __recyclebin__ in your instance, the pre-upgrade check may fail when you upgrade your instance to 2.0.57.240900 or later. To upgrade your instance, delete the __recyclebin__ database first. Of course, if you still want to retain the __recyclebin__ database as a regular database, submit an application by choosing Service Tickets > Create Service Ticket in the upper right corner of the management console.
• Table recycle bin is only available for regular InnoDB tables. It is unavailable for tables in shared tablespaces, tables with full-text indexes, temporary tables, non-InnoDB tables, tables with secondary engines, system catalogs, or hidden tables.
• When you use a DROP TABLE statement to delete multiple tables, ensure that these tables are regular InnoDB tables. Otherwise, the statement fails to be executed based on the setting of rds_recycle_bin_mode or all tables are permanently deleted.
• Table recycle bin only stores tables deleted using DROP TABLE statements. It means that other deletion statements will delete tables permanently, instead of moving tables to the recycle bin.
• If an instance has a binlog-based replication task (such as DRS and DR instances) and the binlog record mode of the recycle bin is ORIGIN, clearing or restoring tables in the recycle bin at the source end may cause replication errors or data inconsistency. You are advised to set the binlog record mode of the recycle bin to TRANSLATE.
• DRS does not fully support recycle bin. If replication is interrupted because the recycle bin is enabled, reset the task. Alternatively, contact technical support by choosing Service Tickets > Create Service Ticket in the upper right corner of the management console.
• Table recycle bin in version 2.0.57.240900 supports only tables with names composed of ASCII characters, such as English letters, numbers, and common punctuation marks. Other table name character types, such as Latin letters, Greek letters, and Chinese characters, will be supported in version 2.0.60.241200.
  

  If you try to recycle or restore tables whose names contain unsupported character types in version 2.0.57.240900, the connection may hang. In such cases, reboot the instance or perform a primary/standby switchover. Once the instance is recovered, disable the recycle bin and delete the tables.

Overview

When you create an instance of version is 2.0.57.240900 or later, or upgrade an existing instance to 2.0.57.240900 or later, TaurusDB will initialize database __recyclebin__. After you enable table recycle bin, tables specified in a DROP TABLE statement are temporarily moved to database __recyclebin__ and renamed.

• If tables specified in a DROP TABLE statement are not supported by the recycle bin, they will be directly deleted.
• If recycle bin is enabled for an instance and a table that has constraints with the same name is temporarily deleted, the table may fail to be moved to the recycle bin because database __recyclebin__ also has constraints with the same name. If the table fails to be moved to the recycle bin, check whether the deleted table has such constraints. If yes, delete the constraints and execute the DROP TABLE statement again to delete the table.

Tables in the recycle bin are named in the following format:

__<storage engine name>_<schema name>_<table name>_<id>

Ensure that <id> of each table in the recycle bin is unique. After tables with the same name are moved to the recycle bin, there will be no duplicate tables. If <schema name> or <table name> is longer than 10 characters, only the first 10 characters will be displayed in the recycle bin, and an underscore (_) will be added after <id> to indicate that the original schema name or table name is truncated.

• Enabling table recycle bin

  You can enable table recycle bin in either of the following ways:

  • Click the TaurusDB instance name. Choose Parameters in the navigation pane and reset the rds_recycle_bin_mode parameter.
  • Run a command in a session.

    Example:

    set rds_recycle_bin_mode=PRIORITY_RECYCLE_BIN;

  Table 1 lists some table recycle bin parameters. You can set them as required.

  Table 1 Table recycle bin parameters

  Parameter	Level	Description
  rds_recycle_bin_mode	Global, Session	Controls whether to enable recycle bin. Value: OFF (default value): Recycle bin is disabled. PRIORITY_RECYCLE_BIN: If a DROP statement contains both tables that support recycle bin and tables that do not support recycle bin, the statement fails and an error is reported. PRIORITY_DROP_TABLE: If a DROP statement contains both tables that support recycle bin and tables that do not support recycle bin, all tables are permanently deleted and cannot be restored.
  rds_recycle_scheduler	Global	Controls whether the backend automatically clears expired tables from the recycle bin. Value: OFF (default value): The backend does not automatically clear expired tables from the recycle bin. Instead, they are stored in the recycle bin for a long time. ON: The backend automatically clears expired tables from the recycle bin.
  rds_recycle_bin_retention	Global	Controls how many seconds tables in the recycle bin are stored for. Default value: 259200 (three days); value range: 0 to 2592000. If rds_recycle_scheduler is set to ON, and the retention period of tables in the recycle bin exceeds the parameter value, the tables will be automatically deleted and cannot be restored.
  rds_recycle_bin_binlog_mode	Global, Session	Controls how DDL statements related to the recycle bin are recorded in binlogs. Value: ORIGIN (default value): DDL statements related to the recycle bin are directly recorded in binlogs. TRANSLATE: DDL statements related to the recycle bin are converted to those supported by MySQL Community Edition and recorded in binlogs.

  

  • After the rds_recycle_bin_retention parameter is modified, the recycle bin recalculates the estimated time for automatically clearing all tables in the recycle bin based on the time they were first placed in the recycle bin.
  • When rds_recycle_bin_binlog_mode is set to TRANSLATE, any DROP TABLE and restore_table operations in the recycle bin will be recorded in the binlog as RENAME TABLE operations. Even if the destination is a TaurusDB instance, the show_tables, restore_table, and purge_table statements cannot be used for tables that are replicated to destination database __recyclebin__ through binlogs, and the recycle bin backend of the destination instance will not automatically clear those tables. Tables in the recycle bin of the destination instance can be restored and cleared only by replaying restore_table and purge_table at the source instance or enabling automatic backend cleanup.
• Viewing tables in the recycle bin

  You can run the show command to view table details in the recycle bin such as the current and original schema and table names, as well as the time when a table was moved to the recycle bin and when it will be automatically cleared.

  For details, see Examples.

• Restoring tables from the recycle bin

  You can run the restore command to restore a table from the recycle bin to the original table in the original schema or a specified table in a specified schema. After the restoration is successful, the table in the recycle bin is deleted and cannot be restored again.

  For details, see Examples.

• Enabling automatic backend cleanup

  After automatic backend cleanup is enabled, a backend cleanup thread will be created on the instance host to automatically clean tables that have reached the maximum retention period (three days by default). Tables cleared by the backend are permanently deleted and cannot be restored.

  You can enable automatic backend cleanup in the following way:

  Click the TaurusDB instance name. Choose Parameters in the navigation pane and change the value of rds_recycle_scheduler to ON.

  Table 2 Parameter description

  Parameter	Level	Description
  rds_recycle_scheduler	Global	Controls whether the backend automatically clears expired tables from the recycle bin. Value: OFF (default value): The backend does not automatically clear expired tables from the recycle bin. ON: The backend automatically clears expired tables from the recycle bin.

  

  For standby instances in a RegionlessDB cluster, the backend does not clear expired tables from the recycle bin.

• Clearing specified tables

  You can run the purge command to clear a table in the recycle bin. After the command is executed, the table is permanently deleted and cannot be restored.

  For details, see Examples.

• Controlling recycle bin permissions
  Deleted tables are moved to database __recyclebin__ in the recycle bin. To view, restore, or clear those tables, you need to run commands provided by the recycle bin and have the following permissions:

  • To view details about tables in the recycle bin, you must have the SELECT permission on the tables in __recyclebin__.
  • To clear specified tables in the recycle bin, you must have the DROP permission on the tables in __recyclebin__.
  • To restore specified tables in the recycle bin, you must have the ALTER and DROP permissions on the tables in __recyclebin__, and CREATE and INSERT permissions on the destination tables.
    

    • Tables in the recycle bin occupy the storage space of the instance until they are cleared. If you want to release the storage space, clear the tables in the recycle bin.
    • After a table is moved to the recycle bin, its triggers and foreign keys are permanently deleted and cannot be restored.

Examples

Table recycle bin provides the following commands to allow you to operate tables temporarily stored in the recycle bin.

• Viewing tables in the recycle bin

  call dbms_recyclebin.show_tables();

  Information similar to the following is returned.

  +----------------+--------------------------+---------------+--------------+---------------------+---------------------+
  | SCHEMA         | TABLE                    | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
  +----------------+--------------------------+---------------+--------------+---------------------+---------------------+
  | __recyclebin__ | __innodb_test_db_t1_1069 | test_db       | t1           | 2024-09-29 08:48:27 | 2024-10-02 08:48:27 |
  | __recyclebin__ | __innodb_test_db_t2_1070 | test_db       | t2           | 2024-09-29 08:48:44 | 2024-10-02 08:48:44 |
  +----------------+--------------------------+---------------+--------------+---------------------+---------------------+

  Table 3 Parameter description

  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 when the table was moved to the recycle bin.
  PURGE_TIME	The estimated time when the table is automatically cleared.

• Restoring tables from the recycle bin
  • Create a table with the same structure as the original table, and then execute the INSERT INTO ... SELECT ... statement to restore data to the new table.

    Example:

    Query the table whose original schema name is db and original table name is t1 in the recycle bin.

    call dbms_recyclebin.show_tables();

    Information similar to the following is returned.

    +----------------+--------------------------+---------------+--------------+---------------------+---------------------+
    | SCHEMA         | TABLE                    | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
    +----------------+--------------------------+---------------+--------------+---------------------+---------------------+
    | __recyclebin__ | __innodb_test_db_t1_1069 | db            | t1           | 2024-09-29 08:48:27 | 2024-10-02 08:48:27 |
    | __recyclebin__ | __innodb_test_db_t2_1070 | db            | t2           | 2024-09-29 08:48:44 | 2024-10-02 08:48:44 |
    +----------------+--------------------------+---------------+--------------+---------------------+---------------------+

    Based on the query results, the name of the table to be restored in the recycle bin is __innodb_test_db_t1_1069. Execute the INSERT INTO ... SELECT ... statement to restore the data of __innodb_test_db_t1_1069 in the recycle bin to the new table t1.

    INSERT INTO `db`.`t1` SELECT * FROM `__recyclebin__`.`__innodb_test_db_t1_1069`;

    

    Executing the INSERT INTO ... SELECT ... statement does not remove the data temporarily stored in the recycle bin. Data can be restored for multiple times, and the generated binlogs have the best compatibility. If an instance has a binlog-based replication link (such as DRS and DR instances), you are advised to use this method to restore data. This reduces the risk of replication interruption caused by reasons such as the destination does not support table recycle bin or insufficient user permissions.

  • Restore a table in the recycle bin to the original table in the original database.

    call dbms_recyclebin.restore_table('TABLE_NAME');

    Table 4 Parameter description

    Parameter	Description
    TABLE_NAME	The name of the table after the table was moved to the recycle bin.

    Example:

    Restore table __innodb_test_db_t1_1069 from the recycle bin to the original database test_db and retain the original table name t1.

    call dbms_recyclebin.restore_table('__innodb_test_db_t1_1069');

  • Restore a table from the recycle bin to a specified table in a specified database.
    call dbms_recyclebin.restore_table('TABLE_NAME', 'DEST_DB', 'DEST_TABLE');

    Table 5 Parameter description

    Parameter	Description
    TABLE_NAME	The name of the table after the table was moved to the recycle bin.
    DEST_DB	The database you want to restore the table to.
    DEST_TABLE	The name of the restored table.

    Example:

    Restore table __innodb_test_db_t1_1069 from the recycle bin to database test_db2 and specify the name of the restored table as t3.

    call dbms_recyclebin.restore_table('__innodb_test_db_t1_1069','test_db2','t3');

    

    • Before the restoration, ensure that the destination database exists, or the restoration will fail.
    • Before the restoration, ensure that there is no table with the same name in the destination database, or the restoration will fail.
    • When using the table recycle bin commands, ensure that there are no extra spaces before or after the database or table names inside single quotation marks (').

• Clearing a specified table in the recycle bin

  call dbms_recyclebin.purge_table('TABLE_NAME');

  Table 6 Parameter description

  Parameter	Description
  TABLE_NAME	The name of the table after the table was moved to the recycle bin.

  Example:

  Clear table __innodb_test_db_t1_1069 in the recycle bin.

  call dbms_recyclebin.purge_table('__innodb_test_db_t1_1069');