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

Introduction

PolarDB database and table restoration does not overwrite or delete existing databases and tables in the original cluster, and does not write data into them. Instead, it creates new databases and tables in the original cluster. You can specify new database or table names during restoration and restore the backup data of db1 to db2 by specifying databases and tables.

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

Scenarios

Usage notes

• For database and table restoration, the restore-from-backup-set method supports only backup sets of level-1 backups. Restoring from a level-2 backup is not supported.

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

• Database and table restoration restores only the specified tables. Make sure that you select all the tables that you want to restore.

  Note

  If you cannot determine all the tables involved, restore the historical data of the entire cluster to a new cluster and then migrate the data back 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 an entire database, you can restore up to 100 tables of the database at a time. If you restore an entire database, you can restore all tables in the database.

  Note

  • Restoring too many tables at a time takes a long time, so do not restore too many tables at once.

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

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

  Note

  • This feature is currently in canary release. To use it, you can Contact us to join the allowlist and enable the feature.

  • Query the number of tables, including system tables, in the cluster. Run the following SQL statement:

    SELECT COUNT(*) FROM information_schema.tables;

  • Query the number of system tables in the cluster. Run the following SQL statement:

    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, the trigger is not restored.

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

• Restore data 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 target backup set from the backup set list.

   Note

   Based on IOPS consumption, database and table restoration provides three restoration speed configurations: Quick, Standard, and Secure. Select a configuration based on your requirements. For the estimated time required by each restoration speed configuration, see Reference for database and table restoration speed tests.

   • Quick: Restoring data to the current cluster can consume a large amount of IOPS. Select this configuration during off-peak hours.

   • Standard (recommended for specifications with more than 4 cores): Restoring data to the current cluster can consume some IOPS.

   • Secure (recommended for specifications with 4 cores or fewer): Restoring data to the current cluster consumes only a small amount of IOPS but may significantly reduce the restoration speed.

   For clusters with specifications of 4 cores or fewer, make sure that the settings of innodb_io_capacity and innodb_io_capacity_max match the specifications. For cluster specifications and the maximum supported IOPS, see Compute node specifications for Enterprise Edition and Compute node specifications for Standard Edition, and use the Secure restoration speed.

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

   Note

   • If you do not specify new database or table names, the system automatically generates them by appending _backup to the original database or table name. For example, if the original table is test, the automatically generated table name is test_backup.

   • If you select a target database but do not specify tables, all data in the database is restored by default.

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

Related API operations