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

How it works

PolarDB creates new databases and tables in the original cluster. Your existing data is never overwritten, deleted, or modified. You can optionally rename the restored objects during the process—for example, restoring db1 into a new database named db2.

The restoration runs concurrently with normal cluster access. It consumes additional computing resources, so expect a temporary increase in CPU utilization and input/output operations per second (IOPS) during the operation.

Prerequisites

Before you begin, ensure that:

• Your cluster runs Enterprise Edition or Standard Edition at a supported revision version (see Supported editions and versions)

• The databases and tables you want to restore use the InnoDB storage engine

• The backup set is a level-1 backup set (level-2 backup sets are not supported)

• The database or table names you want to restore do not already exist in the cluster (the task fails if names conflict)

Supported editions and versions

Database and table restoration requires a minimum revision version depending on the capabilities you need.

• Basic features: The minimum version required to use database and table restoration.

• GDN primary cluster/new restoration process: The minimum version required to use the feature on a Global Database Network (GDN) primary cluster, or to benefit from the speed optimization in the new restoration process.

The new restoration process speeds up restoration to the original cluster. For details on the mechanism and timing, see Overall flow and estimated time.

To check your cluster's revision version, go to the Basic Information page and look in the Configuration Information section.

Limitations

Unsupported cluster types:

• Multi-master Cluster (Limitless) Edition

• Secondary clusters in a GDN

Clusters with more than 50,000 tables cannot use this feature if either of the following applies:

• The storage type is enterprise solid-state drive (ESSD)

• The cluster has no read-only (RO) nodes

If your cluster has more than 50,000 tables—including system tables—a version of this feature is available in canary release. To enable it, contact us to be added to the allowlist. To check your table count, run the following SQL statements:

-- Total tables (including system tables)
SELECT COUNT(*) FROM information_schema.tables;

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

Unsupported table types:

• Tables with a global secondary index (GSI)

• Tables with a columnstore index (IMCI)

• Tables that use storage engines other than InnoDB

• Tables archived as cold data

What is not restored:

• Triggers

• Foreign keys

Per-operation limits:

• When restoring specific tables from a database, you can select up to 100 tables per operation. Restoring a large number of tables in a single operation can take significant time—consider a full restoration to a new cluster for large-scale recovery.

• When restoring an entire database (no individual tables selected), all tables in that database are restored.

• If you are unsure which tables to restore, perform a full restoration to a new cluster and migrate only the required data back. 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 your cluster does not support database and table restoration, perform a full restoration to a new cluster, then migrate the data back to the source cluster.

Restore databases and tables from a backup set

1. Log in to the PolarDB console. In the left navigation pane, click Clusters. Select the region where the cluster is deployed, find the target cluster, and click its ID.

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

3. In the dialog box, set Restoration Type to Backup Set and select a backup set.

   

   Select an IOPS consumption level based on your workload. Higher IOPS consumption speeds up restoration but competes with ongoing read/write traffic. For estimated restoration durations, see Database and table restoration speed test data reference.

   Configuration	IOPS consumed	When to use
   Quick	~60%	Off-peak hours when the cluster has IOPS headroom; avoid during peak hours as it may affect ongoing read/write performance
   Regular (recommended)	~30%	Standard business hours; balances restoration speed with minimal impact on running workloads
   Secure	~15%	Peak hours when protecting existing read/write performance is the priority; restoration takes significantly longer

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

   If you do not specify new names, the system appends _backup to the original names—for example, a table named test becomes test_backup. If you select a database without selecting any tables, all tables in that database are restored.

   

5. Confirm your selection and click OK.

Monitor and verify the restoration

After clicking OK, go to Settings and Management > Backup and Restoration to monitor the task status. When the task completes, the restored databases and tables appear in your cluster under the names you specified (or with the _backup suffix if no new name was given).

After restoration, check the following:

• Triggers: Triggers are not restored. If any restored tables originally had triggers, recreate them manually.

• Foreign keys: Foreign keys are not restored. If any restored tables originally had foreign keys, recreate them manually.

• Data integrity: Run queries against the restored tables to confirm the expected data is present.

API reference