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

Overview

The database and table restoration feature of PolarDB does not overwrite or delete existing databases or tables in the cluster, or directly write data to existing databases or tables in the cluster. The feature creates new databases or tables in the cluster. You can specify new database names or table names during the restoration process to restore data to the new databases or tables. For example, you can specify db2 as the destination database to restore backup data from db1 to db2.

Database and table restoration does not interrupt access to the cluster but may consume the computing resources of the cluster, which increases the CPU utilization and IOPS of the cluster.

Precautions

Usage notes

• You can restore databases and tables only from level-1 backup sets.

• 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 the tables that you want to restore, we recommend that you restore all data in the current cluster to a new cluster and then migrate the data to the current cluster. For more information, see Method 1 for full restoration: Restore data from a backup set and Method 2 for full restoration: point-in-time restoration.

• If you specify a database or table name that already exists in the cluster during the restoration process, the task fails.

• If you want to restore only parts of a database, you can restore up to 100 tables in the database at the same time. If you restore an entire database, all tables in the database are restored.

  Note

  • A long period of time is required to restore a large number of tables at the same time. We recommend that you do not restore a large number of tables at the same time.

  • To restore a large number of tables, we recommend that you restore all data in your cluster 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 in a cluster that contains more than 50,000 tables (including system tables).

  Note

  • The feature is in canary release. To use the feature, contact us.

  • To query the number of tables in a cluster, including system tables, 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__');

• When you use the database and table restoration feature to restore a table, the triggers in the table cannot be restored.

• When you use the database and table restoration feature to restore a table, the foreign keys in the table cannot be restored.

Procedure

1. Log on to the PolarDB console.

2. In the left-side navigation pane, click Clusters.

3. In the upper-left corner, select the region in which the cluster is deployed.

4. Find the cluster and click its ID.

5. In the left-side navigation pane, choose Settings and Management > Backup and Restoration.

6. On the Backup and Restoration page, click Restore Databases/Tables.

7. In the dialog box that appears, set the Restoration Type parameter to Backup Set and select the backup set that you want to use from the backup set drop-down list.

   

8. On the left side of the Databases and Tables to Restore section, select the database that you want to restore. On the right side, select the tables that you want to restore.

   

   Note

   • If you do not specify a name for a database or table that you want to restore, the system automatically generates a new database or table name by suffixing the name of the original database and table with _backup. For example, if the original table is test, a new table named test_backup is automatically generated.

   • If you do not select a table after you select a database, all tables in the database are restored.

9. Click OK.

Related API operations