Restore databases or tables from a backup set - PolarDB

Database and table restoration is a process that restores only specified databases or tables in a cluster. For example, you are the database administrator of a gaming company. You can use the database and table restoration feature to restore the data of a player or a group of players. You can restore databases and tables by using two methods: restore from a backup set or restore to an earlier point in time. This topic describes how to restore specified databases or tables 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

Supported versions

Note

For information about how to check the database engine version of your cluster, see Query an engine version.

A PolarDB for MySQL cluster whose Database Edition is Enterprise Edition and Edition is Cluster Edition supports the database and table restoration feature if the cluster runs one of the following database engine versions:
- 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 primary cluster of a Global Database Network (GDN) whose Database Edition is Enterprise Edition supports the database and table restoration feature if the cluster runs one of the following database engine versions:
- PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.42 or later.
- PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.30 or later.
- PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.44 or later.
- PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.25.3 or later.
A cluster whose Database Edition is Enterprise Edition supports the new database and table restoration version if the cluster runs one of the following database engine versions:
- PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.42 or later.
- PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.30 or later.
- PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.44 or later.
- PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.25.3 or later.
Note
Compared with the original database and table restoration version, the new database and table restoration version can restore data to the original cluster at a faster speed. For more information, see Overall process and estimated time.

Limits

PolarDB clusters whose edition is Multi-master Cluster (Database/Table) do not support the database and table restoration feature.
You cannot use database and table restoration to restore tables that have global secondary indexes.
If a PolarDB for MySQL cluster does not have read-only nodes and the number of tables in the cluster exceeds 50,000, you cannot use the database and table restoration feature.
You cannot use database and table restoration on secondary clusters of a GDN.
You cannot use database and table restoration to restore In-Memory Column Indexes (IMCIs).
You cannot use database and table restoration to restore tables archived as cold data.
You can use database and table restoration only to restore tables that use the InnoDB storage engine.

Note

To restore databases or tables on a cluster that does not support database and table restoration, you can first perform a full restoration of the data into a new cluster by using one of the following methods: 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. Then, migrate data from the new cluster to your original cluster. For information about how to migrate data between clusters, see Migrate data between PolarDB for MySQL clusters.

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: Restore data to an earlier point in time.
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 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 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

Log on to the PolarDB console.
In the left-side navigation pane, click Clusters.
In the upper-left corner, select the region in which the cluster is deployed.
Find the cluster and click its ID.
In the left-side navigation pane, choose Settings and Management > Backup and Restoration
On the Backup and Restoration page, click Restore Databases/Tables.
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.
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 destination database or table, 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 name of an original table is test, the destination table is automatically named test_backup.
- If you do not select a table after you select a database, all tables in the database are restored.
Click OK.

Related API operations

Operation	Description
DescribeMetaList	Queries the metadata of databases or tables that can be restored.
RestoreTable	Restores the databases or tables of a PolarDB cluster.