PolarDB:Method 1 for database and table restoration: Restore data from a backup set

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

• You can restore databases and tables only from level-1 backup sets. Level-2 backup sets are not supported.

• 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 restoration task fails if a specified database or table name already exists in the original 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 deployed. Find the target cluster and click its ID.

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 Backup Set and select a backup set.

   Note

   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