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

Background information

The database and table restoration feature in PolarDB does not overwrite or delete existing databases and tables in the original cluster, or write data to the databases and tables in the original cluster. Instead, new databases and tables are created in the original cluster. You can specify new database names or table names during the restoration precess to restore the backup data of db1 to db2.

During database and table restoration, the original cluster can still be accessed. However, the computing resources of the original cluster may be consumed. As a result, the CPU utilization and IOPS of the cluster increase.

Limits

The database and table restoration feature is supported on PolarDB for MySQL Enterprise Edition and Standard Edition.

• Only PolarDB for MySQL Enterprise Edition Cluster Edition clusters of the following versions support the database and table restoration feature:

  • PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.25 or later.

  • PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.8 or later.

  • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.14 or later.

  • PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.0 or later.

  • The database and table restoration feature is available only for tables that use the InnoDB storage engine.

• Only PolarDB Standard Edition clusters of the following version support the database and table restoration feature:

  • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.38.2 or later.

  • The CPU architecture of your cluster is X86. The database and table restoration feature is not supported for the YiTian ARM architecture.

  • The database and table restoration feature cannot be used to restore more than 50,000 tables in a cluster that uses cloud disks.

  • The database and table restoration feature cannot be enabled for a cluster for which the Transparent Data Encryption (TDE) feature is enabled. For more information about the TDE feature, see Configure TDE for a PolarDB for MySQL cluster.

• The database and table restoration feature is not supported on PolarDB Single-node Edition, X-Engine Edition, and Multi-master Cluster (Database/Table) Edition.

• Clusters in a Global Database Network (GDN) do not support the database and table restoration feature.

• You cannot restore tables that contain global secondary indexes.

• If a cluster that does not have read-only nodes contains more than 50,000 tables, the database and table restoration feature is not supported.

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 only the tables that you want to restore.

  Note

  If you are unable to select 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: Restore data to an earlier point in time.

• If you specify an existing database name or table name in the original cluster, the database and table restoration task fails.

• If you do not restore a database, you can restore a maximum of 100 tables in the database at a time. If you restore a database, all tables in the database are restored.

  Note

  • It takes a very long time to restore a large number tables at a time. Therefore, we recommend that you do not restore too many tables at a 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 table restoration feature even when a cluster contains more than 50,000 tables (including system tables).

  Note

  • The database table restoration feature is in the canary release. To use the feature, you can contact the technical support to obtain required permissions.

  • Query the number of tables (including system tables) in a cluster. In this example, the following SQL statement is used:

    SELECT COUNT(*) FROM information_schema.tables;

  • Query the number of system tables in a cluster. In this example, the following SQL statement is used:

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

• You cannot use the database and table restoration feature to restore triggers. If the table that you restore contains a trigger, the trigger cannot be restored.

• You cannot use the database and table restoration feature to restore foreign keys. If the table that you restore contains a foreign key, the foreign key cannot be restored.

Procedure

1. Log on to the PolarDB console.

2. In the top navigation bar, select the region in which the cluster that you want to manage is deployed.

3. Find the cluster and click its ID.

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

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

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

   

7. 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 table that you want to restore.

   

   Note

   • If you do not specify a database name or a table name, the system automatically generates a new database name or table name by suffixing the original database name or table name with _backup. 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.

8. Click OK.

Related API operations