Restoring Data of Deleted Tables

Scenarios

In database administration, accidental deletion of a database is a high-risk scenario that can lead to data loss. DDL operations, such as DROP DATABASE or TRUNCATE TABLE, directly remove physical data objects. These operations are irreversible, which means that data recovery depends entirely on full physical backups and incremental log. For large datasets, relying on traditional backup and restoration mechanisms may take hours.

To mitigate this, this section provides two restoration methods:

• If you can identify the deleted tables and the deletion time, you can perform table-level point-in-time recovery (PITR).
• If database/table recycle bin is enabled, TaurusDB moves deleted tables into a __recyclebin__ database for temporary storage. You can customize the maximum retention period for these deleted tables. Before this retention threshold expires, you can execute a stored procedure to rapidly recover the databases or tables from the recycle bin.

Operation Process

Precautions

Data on the original instance will be overwritten. The original instance will be unavailable during the restoration, so restore data during off-peak hours.

Step 1: Create a Database

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target instance name.
4. In the navigation pane, choose Databases.
5. Click Create Database. In the displayed dialog box, enter the database name principles and click OK.

Step 2: Create a Table and Insert Data

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, locate the instance and click Log In in the Operation column.

   Alternatively, click the instance name on the Instances page. On the displayed Basic Information page, click Log In in the upper right corner. The DAS login page is displayed.

4. Enter the database username and password and click Test Connection.
5. After the connection test is successful, click Log In. Then you can access and manage your databases.
6. On the top menu bar, choose SQL Operations > SQL Query.
7. Run the following SQL statements to create a table and insert data into it:
   Create a table and insert data in the database principles.

   CREATE TABLE `solid_principle` (
     `abbr` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
     `name_en` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
     `name_cn` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
     PRIMARY KEY (`abbr`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

   insert into solid_principle (abbr,name_en,name_cn) VALUES 
   ('S','Single Responsibility Principle','Single Responsibility Principle'),('O','Open Close Principle','Open Close Principle'),('L','Liskov Substitution Principle','Liskov Substitution Principle'),('I','Interface Segregation Principle','Interface Segregation Principle'),('D','Dependency Inversion Principle','Dependency Inversion Principle');

Step 3: Delete Table Data

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, locate the instance and click Log In in the Operation column.

   Alternatively, click the instance name on the Instances page. On the displayed Basic Information page, click Log In in the upper right corner. The DAS login page is displayed.

4. Enter the database username and password and click Test Connection.
5. After the connection test is successful, click Log In. Then you can access and manage your databases.
6. On the top menu bar, choose SQL Operations > SQL Query.
7. Select database principles and run the following SQL statement to delete table data.

   delete from `solid_principle`;

Step 4: Restore Table Data

Step 5: Check the Restoration Results

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, locate the instance and click Log In in the Operation column.
4. In the displayed dialog box, enter the password and click Test Connection.
5. After the connection test is successful, click Log In.
6. Check the table data of the database to verify if the restoration is complete.