Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

Table Recycle Bin

Updated on 2025-02-11 GMT+08:00

TaurusDB provides table recycle bin. After this function is enabled, the DROP TABLE statement that meets conditions does not directly delete a specified table. Instead, the table is temporarily stored in the recycle bin. When the maximum retention period expires, the table is automatically deleted in the backend.

You can change the retention period of a deleted table in the recycle bin. You can also restore or permanently delete a table from the recycle bin at any time.

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. If you want to retain the __recyclebin__ database for regular use, submit a service ticket.
  • 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 the recycle bin.
    • If a DRS full synchronization task is used to replicate tables in the recycle bin to the destination instance, the tables in the recycle bin cannot be queried or restored by running call dbms_recyclebin.show_tables() and call dbms_recyclebin.restore_table() on the destination instance.
    • DRS does not synchronize session-level recycle bin switches of a single DROP TABLE statement. Instead, it determines whether the DROP TABLE statement temporarily stores tables to the recycle bin on the destination instance based on the global-level recycle bin parameters of the destination instance.
    • If the binlog record mode of the recycle bin on the source instance is ORIGIN or TRANSLATE, but the destination instance supports the recycle bin and the IP address of the the destination instance is configured in the DRS task, DRS incremental synchronization will not synchronize the restore_table and purge_table operations in the recycle bin.

    If replication is interrupted because the recycle bin is enabled, reset the task or submit a service ticket.

  • 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.
    NOTE:

    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.

NOTE:
  • 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>

NOTE:

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.
    NOTE:
    • 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, show_tables and restore_table 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 by replaying restore_table and purge_table on the source instance and enabling automatic backend cleanup. Alternatively, if the kernel version is 2.0.60.241200 or later, you can run purge_table to manually clear the tables.
  • 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.
    NOTE:

    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.
      NOTE:
      • 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`;

      NOTE:

      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. This reduces the risk of replication interruptions due to the destination instance's lack of support for 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');

      NOTE:
      • 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');

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback