PolarDB for MySQL:Method 2 for database and table restoration: Restore data to a point in time

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

• Only PolarDB Cluster Edition supports database and table restoration, and one of the following clusters must be used:

  • A cluster of PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.25 or later. For more information, see Upgrade the cluster version.
  • A cluster of PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.8 or later.
  • A cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.14 or later.
  • A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.0 or later.

  Note If the current version of your cluster does not support the database and table restoration feature, you can restore the data to the new cluster as set out in Method 1 for cluster restoration: Restore from a backup set or Method 2 for full restoration: point-in-time restoration, and then migrate the data to the original cluster.
• The database and table restoration feature is not supported on PolarDB Single Node Edition,X-Engine Edition, or Multi-master Cluster (Database/Table) Edition.
• Database and table restoration is not supported on clusters with transparent data encryption (TDE) enabled.
• Clusters in the Global Database Network (GDN) do not support the database and table restoration feature.

Precautions

• To restore data to a point in time, you can restore databases and tables only from level-1 backups, but not from level-2 backups.
• 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 cluster restoration: Restore from a backup set and Method 2 for full restoration: point-in-time restoration.
• The database and table restoration task fails when you restore a database or table in the following scenarios:
  • You specify an existing database name or table name in the original cluster.
  • From the time when the backup set is generated to the time when the backup set is restored, the restored table is deleted or a database or table with the same name as the destination database or table exists in the cluster.
• You can restore at most 100 tables at a time. If you restore a database, all tables in the database are restored.
  Note 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 cluster restoration: Restore from a backup set and Method 2 for full restoration: point-in-time restoration.
• The database and table restoration feature is applicable only to clusters that store 50,000 tables or less. If the cluster stores more than 50,000 tables, this feature cannot be used.
• 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 upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
3. Find the cluster and click the cluster ID.
4. In the left-side navigation pane, choose Settings and Management > Backup and Restore.
5. On the Backup and Restore page, click Restore Databases/Tables.
6. In the dialog box that appears, set Restore Type to Backup Timepoint and configure Restoration Time.
   

   Note The point in time specified by Restore To must fall within the time range that is specified by Restoration Time. The full backup set that is most recent to the specified point in time must contain the table that you want to restore. This way, the Backup Timepoint restore type can be used. The time range specified by the Restore To parameter is determined by the value of Log Retention Period (Days). The default value of the Log backup retention parameter is 7 days.
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