PolarDB:Method 2 for database and table restoration: Restore to a point in time

Introduction

PolarDB provides a database and table restoration feature that creates new databases and tables in the original cluster. This feature does not overwrite, delete, or write data to existing databases and tables. During the restoration process, you can specify new names for the databases or tables. For example, you can restore backup data from db1 to a new database named db2.

The restoration process does not affect normal access to the original cluster. However, it may consume computing resources and increase the CPU utilization and input/output operations per second (IOPS) of the cluster.

Scenarios

Usage notes

• Point-in-time recovery for databases and tables is supported only for level-1 backups. It is not supported for level-2 backups.

• Only the tables that you specify are restored. Make sure that you select all tables that you want to restore.

  Note

  If you cannot determine which tables to restore, we recommend that you perform a full restoration of your cluster to a new cluster. You can then migrate the required data back to the original cluster. For more information, see Method 1 for full restoration: Restore data from a backup set and Method 2 for full restoration: Restore data to an earlier point in time.

• The database and table restoration task fails in the following scenarios:

  • A database or table with the specified name already exists in the original cluster.

  • Between the time the backup set was created and the specified point in time for restoration, the table to be restored was deleted, or a database or table with the same name as the destination already exists in the cluster.

• If you choose to restore specific tables from a database, you can restore up to 100 tables at a time. If you choose to restore an entire database, all tables in that database are restored.

  Note

  • Restoring many tables at once can take a long time. We recommend that you do not restore too many tables in a single operation.

  • To restore many tables, we recommend that you perform a full restoration to a new cluster. For more information, see Method 1 for full restoration: Restore data from a backup set and Method 2 for full restoration: Restore data to an earlier point in time.

• You can use the database and table restoration feature even if a cluster contains more than 50,000 tables, including system tables.

  Note

  • This feature is currently in canary release. To use this feature, you can contact us to be added to the whitelist.

  • To query the number of tables, including system tables, in a cluster, execute the following SQL statement:

    SELECT COUNT(*) FROM information_schema.tables;

  • To query the number of system tables in a cluster, execute the following SQL statement:

    SELECT COUNT(*) FROM information_schema.tables WHERE table_schema IN ('sys', 'performance_schema', 'mysql', 'information_schema', '__recycle_bin__');

• The database and table restoration feature does not restore triggers. If an original table has a trigger, the trigger is not restored.

• The database and table restoration feature does not restore foreign keys. If an original table has a foreign key, the foreign key is not restored.

Procedure

1. Log on to the PolarDB console. In the navigation pane on the left, click Clusters. Select the region where the cluster is located, and then click the cluster ID to go to the cluster details page.

2. In the navigation pane on the left, choose Settings and Management > Backup and Restoration. Click Restore Databases/Tables.

3. In the dialog box that appears, set Restoration Type to Point in Time and select a point in time for Restoration Time.

   

   Note

   • The point in time you select for Restoration Time must be within the Restore To. To use the Point in Time restoration feature, the full backup set closest to the specified point in time must contain the tables to be restored. The restorable time range depends on the Log Retention Period (Days) period, which is 7 days by default.

   • The database and table restoration feature provides three configurations based on IOPS consumption: Quick, Regular, and Secure. You can select a configuration based on your actual requirements. For the estimated restoration duration of each configuration, see Database and table restoration speed test data reference.

     • Quick: Consumes many IOPS (about 60%). Select this option during off-peak hours.

     • Regular (Recommended): Consumes a moderate number of IOPS (about 30%).

     • Secure: Consumes a small number of IOPS (about 15%). This may significantly slow down the restoration.

4. In the Databases and Tables to Restore section, select the source database on the left and the source tables on the right.

   

   Note

   • If you do not specify new names, the system automatically creates names by adding the _backup suffix to the original names. For example, if the original table is named test, the new table is named test_backup.

   • If you select a database but do not select any tables, all tables in the database are restored by default.

5. After you confirm the selected databases and tables, click OK.

Related API operations