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

Introduction

The database and table restoration feature provided by PolarDB does not overwrite or delete existing databases and tables in the original cluster, nor does it write data into existing databases and tables. Instead, it creates new databases and tables in the original cluster. During database and table restoration, you can specify new database names or table names to restore the backup data of db1 to db2.

During database and table restoration, normal access to the databases in the original cluster is not affected. However, the restoration may consume computing resources of the original cluster, which can cause an increase in CPU utilization and IOPS of the cluster.

Scenarios

Usage notes

• Restoration from a backup set in the database and table restoration feature only supports restoration from first-level backup sets. Restoration from second-level backup sets is not supported.

• When you perform database and table restoration, if the specified database name or table name already exists in the original cluster, the restoration fails.

• Database and table restoration restores only specified tables. Make sure that you have selected all the tables that need to be restored.

  Note

  If you cannot determine all the tables involved, we recommend that you restore the historical data of the entire cluster to a new cluster and then migrate the data to the original cluster. For more information, see Restore from a backup set and Restore a cluster to a point in time.

• If you do not restore the entire database, up to 100 tables can be restored at a time for each database. If you restore the entire database, all tables in the database are restored.

  Note

  • If too many tables are restored at a time, the restoration takes a long time. Therefore, we recommend that you do not restore too many tables at a time.

  • If you need to restore a large number of tables, we recommend that you restore the full historical data to a new cluster. For detailed operation steps, see Restore from a backup set and Restore a cluster to a point in time.

• You can use the database and table restoration feature even when the cluster has more than 50,000 tables (including system tables).

  Note

  • This feature is currently in the phased rollout stage. To use this feature, you can Contact us to add the cluster to the whitelist and enable this feature.

  • Query the number of tables (including system tables) in the cluster. The SQL statement is as follows:

    SELECT COUNT(*) FROM information_schema.tables;

  • Query the number of system tables in the cluster. The SQL statement is as follows:

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

• Database and table restoration does not support restoring triggers. If the original table has a trigger configured, the trigger is not restored.

• Database and table restoration does not support restoring foreign keys. If the original table has a foreign key configured, the foreign key is not restored.

• We recommend that you perform data restoration during off-peak hours.

Procedure

1. Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

2. In the left-side navigation pane, choose Settings and Management > Backup and Restoration and click Restore Databases/Tables.

3. In the dialog box that appears, set Restoration Type to Backup Set, and select the destination backup set from the backup set list.

   Note

   The database and table restoration feature provides three speed configurations based on IOPS consumption: Quick, Standard, and Secure. Select a configuration based on your business requirements. For the estimated restoration duration of each speed configuration, see Reference for database and table restoration speed tests.

   • Quick: When data is being restored to the current cluster, a large amount of IOPS may be consumed. We recommend that you select this configuration during off-peak hours.

   • Standard (recommended for clusters with more than 4 cores): When data is being restored to the current cluster, a moderate amount of IOPS may be consumed.

   • Secure (recommended for clusters with 4 cores or fewer): When data is being restored to the current cluster, only a small amount of IOPS is consumed, but the restoration speed may be significantly affected.

   For clusters with 4 cores or fewer, we recommend that you verify that innodb_io_capacity and innodb_io_capacity_max are set to values that match the cluster specification. For the maximum supported IOPS of each cluster specification, see Compute node specifications for Enterprise Edition and Compute node specifications for Standard Edition. We recommend that you use the Secure speed for restoration.

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

   Note

   • If you do not specify new database names or table names, the system appends _backup to the original database names or table names to automatically generate new database names and table names. For example, if the original table name is test, the automatically generated new table name is test_backup.

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

5. After confirming that you have selected the databases and tables that you want to restore, click OK.

API reference