Database/Table Recycle Bin

• 2.0.57.240900 ≤ Kernel version < 2.0.63.250300

  Naming rule: __<storage_engine_name>_<db_name>_<table_name>_<id>

  Constraints: If <db_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 database name or table name is truncated.

  Table 1 Parameter description

  Parameter	Description
  storage_engine_name	Only InnoDB is supported.
  db_name	Database name.
  table_name	Table name.
  id	Unique ID automatically generated by the recycle bin system. It ensures that the table names do not conflict after tables with the same name are moved to the recycle bin.

• Kernel version ≥ 2.0.63.250300

  Naming rule: __<storage_engine_name>_<db_name>_<table_name>_<id>

  Constraints:

  • Only one version of tables with the same <db_name> and <table_name> can be saved in the recycle bin within 1 second. If tables with the same <db_name> and <table_name> are recycled multiple times within 1 second, the subsequent operations will be executed 1 second later.
  • The table name generated in the recycle bin must meet MySQL naming rules. If the original database name and table name are too long and the generated table name exceeds the maximum length of a MySQL table name, executing a DROP TABLE, DROP DATABASE, or TRUNCATE TABLE statement on the table will directly delete the table without moving it to the recycle bin.
  • If a DROP TABLE, DROP DATABASE, or TRUNCATE TABLE statement is executed on a table whose <db_name> or <table_name> contains at signs (@), the table will be directly deleted without being moved to the recycle bin.

  Table 2 Parameter description

  Parameter	Description
  storage_engine_name	Only InnoDB is supported.
  db_name	Database name.
  table_name	Table name.
  id	Unique ID automatically generated by the recycle bin system. It ensures that the table names do not conflict after tables with the same name are moved to the recycle bin.

When you create an instance of version 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 database/table recycle bin, executing a DROP TABLE, DROP DATABASE, or TRUNCATE TABLE statement does not directly delete a table or database. Instead, the table or database is temporarily moved to database __recyclebin__ with a new name.

After a table is moved to the recycle bin, its triggers and foreign keys are permanently deleted and cannot be restored.

• Automatic cleanup: After automatic backend cleanup is enabled, the primary node of the instance creates a backend clearance thread to automatically clear tables that have reached the maximum retention period (rds_recycle_bin_retention). By default, tables are retained for three days. Tables that are automatically cleared by the backend will be permanently deleted and cannot be restored.

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

• Manual cleanup: You can run the purge command to manually clear a table from the recycle bin. After the command is executed, the table is permanently deleted and cannot be restored.

call dbms_recyclebin.show_tables();

| SCHEMA         | TABLE               | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
| __recyclebin__ | test_db@t1@67ad67cb | test_db       | t1           | 2025-02-13 11:32:27 | 2025-02-16 11:32:27 |
| __recyclebin__ | test_db@t2@67ad67cb | test_db       | t2           | 2025-02-13 11:32:27 | 2025-02-16 11:32:27 |

You can create a new table with the same structure as the original table and execute the INSERT INTO ... SELECT ... statement to restore data in the original table to the new table.

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 task (such as DRS and DR instances), do not use call dbms_recyclebin.restore_table to restore tables in the recycle bin. Use INSERT INTO ... SELECT ... instead, which can reduce the risk of replication interruption.

Example:

call dbms_recyclebin.show_tables();
+----------------+---------------------+---------------+--------------+---------------------+---------------------+
| SCHEMA         | TABLE               | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
+----------------+---------------------+---------------+--------------+---------------------+---------------------+
| __recyclebin__ | test_db@t1@67ad67cb | test_db       | t1           | 2025-02-13 11:32:27 | 2025-02-16 11:32:27 |
| __recyclebin__ | test_db@t2@67ad67cb | test_db       | t2           | 2025-02-13 11:32:27 | 2025-02-16 11:32:27 |
+----------------+---------------------+---------------+--------------+---------------------+---------------------+

INSERT INTO `test_db`.`t1` SELECT * FROM `__recyclebin__`.`test_db@t1@67ad67cb`;

call dbms_recyclebin.restore_table('TABLE_NAME');

Parameter description:

TABLE_NAME: name of the table after the table was moved to the recycle bin

Example:

Restore table test_db@t1@67ad67cb from the recycle bin to the original database test_db and retain the original table name t1.

call dbms_recyclebin.restore_table('test_db@t1@67ad67cb');

call dbms_recyclebin.restore_table('TABLE_NAME', 'DEST_DB', 'DEST_TABLE');

Parameter description:

• TABLE_NAME: name of the table after the table was moved to the recycle bin
• DEST_DB: database you want to restore the table to
• DEST_TABLE: name of the restored table

Constraints:

• 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 (').

Example:

Restore table test_db@t1@67ad67cb from the recycle bin to database test_db2 and specify the name of the restored table as t3.

call dbms_recyclebin.restore_table('test_db@t1@67ad67cb','test_db2','t3');

• Restore all tables with the original database specified from the recycle bin to the original database.

  call dbms_recyclebin.restore_db('DB_NAME');

  Parameter description:

  DB_NAME: original database name of the table in the recycle bin

  Example:

  Restore all tables whose original database is test_db from the recycle bin to the original database test_db and retain the original table names.

  call dbms_recyclebin.restore_db ('test_db');

• Restore all tables with the original database specified from the recycle bin to a new database.

  call dbms_recyclebin.restore_db ('DB_NAME', 'DEST_DB');

  Parameter description:

  • DB_NAME: original database name of the table in the recycle bin
  • DEST_DB: database you want to restore the table to

  Example:

  Restore all tables whose original database is test_db from the recycle bin to a new database test_db2 and retain the original table names.

  call dbms_recyclebin.restore_db('test_db', 'test_db2');

call dbms_recyclebin.purge_table('TABLE_NAME');

Parameter description:

TABLE_NAME: name of the table after the table was moved to the recycle bin

Example:

Manually clear table test_db@t1@67ad67cb in the recycle bin.

call dbms_recyclebin.purge_table('test_db@t1@67ad67cb');