tencent cloud

Feedback

Recycle Bin

Last updated: 2024-10-09 10:34:54
    The Recycle Bin feature supports placing dropped or truncated tables into the Recycle Bin, to facilitate data restoration when needed. It can not only avoid accidental deletion of tables but also mitigate impacts of DROP TABLE operations on I/O.

    Supported Versions

    The kernel version should be 3.1.15 or later for TXSQL 8.0.

    Applicable Scenarios

    It is used to place dropped or truncated tables into the Recycle Bin, preventing failure to restore data after accidental deletion.

    Use Instructions

    Parameter Descriptions

    Parameter Name
    Dynamic
    Type
    Default Value
    Valid Values/Value Range
    Description
    txsql_recycle_bin_enabled
    yes
    bool
    OFF
    ON/OFF
    Enables or disables the Recycle Bin feature.
    txsql_recycle_bin_db_not_visible
    yes
    bool
    ON
    ON/OFF
    Controls whether the __txsql_recycle_bin__ database is invisible to non-root Tencent users.
    txsql_recycle_scheduler_interval
    yes
    ulong
    0
    0-18446744073709551615
    The time interval for automatic cleanup of the Recycle Bin. Unit: seconds. The default value is 0, indicating no automatic cleanup.
    txsql_recycle_bin_retention
    yes
    ulong
    604800
    0-18446744073709551615
    Determines how long a table remains in the Recycle Bin before it is automatically cleaned up. Unit: seconds. The default value is 604800 seconds (one week).
    txsql_recycle_bin_max_size
    yes
    ulong
    18446744073709551615
    0-18446744073709551615
    Sets the maximum capacity of the Recycle Bin. An error is reported when this capacity is exceeded.
    txsql_drop_if_exceed_recycle_limit
    yes
    bool
    OFF
    ON/OFF
    Controls whether to delete tables directly when the Recycle Bin capacity is exceeded. If it is set to OFF, an error is directly reported without deleting tables when the maximum capacity of the Recycle Bin is exceeded. If it is set to ON, tables are directly deleted with a warning message displayed when the maximum capacity is exceeded.

    New Database Description

    __txsql_recycle_bin__: The database used to store dropped tables. When an instance starts, it initializes a database named __txsql_recycle_bin__, which is used as a dedicated database for the Recycle Bin. __txsql_recycle_bin__ is a system-level database and cannot be directly modified or deleted.
    For a table in the Recycle Bin, you cannot directly execute DROP TABLE statements, but can use the following syntax for clearance:
    clear <TABLE_NAME> from recycle_bin;
    Or
    call sys.recycle_bin_purge_table('<RECYCLE_NAME>');
    <TABLE_NAME>: The name of the table before entering the Recycle Bin.
    <RECYCLE_NAME>: The name of the table within the Recycle Bin.

    New System Table Descriptions

    mysql.recycle_bin_info: It is used to display information about a table in the Recycle Bin. The table is defined as follows:
    CREATE TABLE IF NOT EXISTS recycle_bin_info (
    table_name varchar(64) NOT NULL, -- The name of the table after being recycled in the Recycle Bin
    origin_schema VARCHAR(64) NOT NULL, -- The database where the table was located before being recycled
    origin_table VARCHAR(64) NOT NULL, -- The name of the table before being recycled
    drop_time timestamp NOT NULL, -- The time when the table was dropped
    purge_time timestamp NOT NULL, -- The time when the table will be cleaned up
    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 purge_time is exceeded, the table in the Recycle Bin is cleaned up. You can set the expiration time of a dropped table through the parameter txsql_recycle_bin_retention. This variable indicates that tables placed into the Recycle Bin will be cleaned up by an automatic cleanup thread after txsql_recycle_bin_retention seconds.
    Note:
    purge_time of a table = drop_time + txsql_recycle_bin_retention.

    Support for TRUNCATE TABLE

    Tables truncated can be placed into the Recycle Bin.
    TRUNCATE TABLE limits:
    Temporary tables are not supported.
    Tables with foreign keys are not supported.
    Data directories and index directories with table attributes are not supported.

    Support for DROP TABLES IF EXISTS

    DROP TABLES IF EXISTS is supported. Existing tables are placed into the Recycle Bin, but no action is taken for non-existent tables.

    Support for DROP DATABASE

    After a database is dropped, the view, event, trigger, procedure, function, and sequence information will be lost. The Recycle Bin only ensures that data is recycled, but the above information may be lost.

    Syntax Description

    Use set global txsql_recycle_bin_enabled=on; with the root account to enable the Recycle Bin feature.
    After the Recycle Bin feature is successfully enabled, all dropped tables in the instance will be placed into the __txsql_recycle_bin__ database. You can use show recycle_bin; or select mysql.recycle_bin_info; to view information about all tables recycled in the Recycle Bin.
    restore db.table from recycle_bin; is used to restore a specified table from the Recycle Bin. If there are multiple tables with the same name, the most recently deleted one is restored.
    restore db.table from recycle_bin with {timestamp('expr')} {recycle_name $recycle_name}; is used to restore a table with a specified name and deletion time from the Recycle Bin if a timestamp is specified. If recycle_name is specified, a table is found with the unique recycle_name instead of the preceding db and table and then restored as db.table. Therefore, this command can be used to restore a table to another schema or modify the name of a restored table.
    restore database db_name from recycle_bin; is used to restore all tables in a specified database. If there are tables with the same name, an error is reported.
    clear recycle_bin; is used to clear the entire Recycle Bin.
    clear recycle_bin {all} {db.table} {before timestamp $time}; is used to clear a table with a specified name or tables deleted before a specified time. If there are multiple tables with the specified name, the oldest one is deleted. If all is specified, all tables with the same name are deleted.
    show recycle_bin; is used to display information about the Recycle Bin.
    drop ... without recycle_bin; is used to force delete a table rather than placing it into the Recycle Bin.
    After the triggering condition (txsql_recycle_scheduler_interval > 0 && event_scheduler==on && txsql_recycle_bin_enabled==on) of txsql_recycle_scheduler_interval is met, the automatic cleanup thread for the Recycle Bin will scan a table for the record with the smallest purge_time at intervals specified by txsql_recycle_scheduler_interval. If purge_time > now(), the table is cleaned up.
    
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support