Restore a database or table from a backup set - PolarDB - Alibaba Cloud Documentation Center

Database and table restoration is a process that restores only specified databases or tables in a cluster. For example, assume that 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 a point in time. This topic describes how to restore a specified database or table from a backup set.

Background information

The database and table restoration feature of PolarDB does not overwrite or delete existing databases or tables in the cluster, or directly write data into existing databases or tables in the cluster. The feature creates new databases or tables in the cluster. You can specify the names of new databases or tables during the restoration process to restore data to the new databases or tables. For example, you can specify db2 as the destination database name to restore backup data from 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.

Precautions

Supported versions

Note

For information about how to check the 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 database and table restoration. The following information describes the specific version requirements and limits.
- PolarDB for MySQL 5.6: The revision version must be 5.6.1.0.25 or later.
- PolarDB for MySQL 5.7: The revision version must be 5.7.1.0.8 or later.
- PolarDB for MySQL 8.0.1: The revision version must be 8.0.1.1.14 or later.
- PolarDB for MySQL 8.0.2: The revision version must be 8.0.2.2.0 or later.
The primary cluster of a GDN supports restoring databases and tables from backup sets. A cluster of Enterprise Edition must meet the following version requirements:
- PolarDB for MySQL 5.6: The revision version must be 5.6.1.0.42 or later.
- PolarDB for MySQL 5.7: The revision version must be 5.7.1.0.30 or later.
If you want to use the new database and table restoration version on a cluster whose Database Edition is Enterprise Edition and Edition is Cluster Edition, ensure that the cluster meets the following version requirements:
- PolarDB for MySQL 5.6: The revision version must be 5.6.1.0.42 or later.
- PolarDB for MySQL 5.7: The revision version must be 5.7.1.0.30 or later.
- PolarDB for MySQL 8.0.1 does not support the new database and table restoration version.
- PolarDB for MySQL 8.0.2 does not support the new database and table restoration version.
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 Multi-master Cluster (Database/Table) Edition clusters do not support the database or table restoration feature.
Tables with global secondary indexes cannot be restored from backup sets.
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.
Secondary clusters of a GDN do not support restoration from backup sets.
In-memory column indexes cannot be restored from backup sets.
Tables archived as cold data cannot be restored from backup sets.
Only tables that use the InnoDB storage engine can be restored from backup sets.

Note

If your cluster does not support database and table restoration, use one of the following methods to fully restore data into a new cluster: Method 1 for full restoration: Restore data from a backup set or Method 2 for full restoration: Restore data to an earlier point in time. Then, migrate data to your original cluster. For information about how to migrate data, see Migrate data between PolarDB for MySQL clusters.

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 uncertain about which tables you want to restore, we recommend that you fully restore data into a new cluster and then migrate the data to the original 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 perform a full restoration. For information about the step-by-step guides, 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 and table restoration feature is in canary release. To enable the feature, Contact us to obtain technical support.
- You can execute the following statement to query the total number of tables in a cluster (system tables included). In this example, the following SQL statement is used:
```
SELECT COUNT(*) FROM information_schema.tables;
```
- You can execute the following statement to query the total number of 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

Log on to the PolarDB console.
In the left-side navigation pane, click Clusters.
In the upper-left corner, select the region where 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 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, assume the original table is named test, a table named test_backup is automatically created.
- 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 PolarDB databases or tables.