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 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.
For more information about how to update the minor version of a cluster, see Minor version update.
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.
If the current cluster does not support the database and table restoration feature, you can restore data from the cluster to a new cluster by using two full restoration methods and then migrate the data to the original cluster. For more information about the two full restoration methods, 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. For more 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.
NoteIf 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.
NoteIt 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).
NoteThe 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
Log on to the PolarDB console.
In the top navigation bar, select the region in which the cluster that you want to manage is deployed.
Find the cluster and click its ID.
In the left-side navigation pane, choose .
On the Backup and Restoration page, click Restore Databases/Tables.
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.
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.
NoteIf 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 istest
, a new table namedtest_backup
is automatically generated.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 |
Queries the metadata of the database or table that you want to restore. | |
Restores the databases or tables of a PolarDB cluster. |