PolarDB：Method 2 for database and table restoration: Restore data to an earlier point in time

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

Usage notes

• You can restore databases or tables to an earlier point in time only from level-1 backups.

• 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: point-in-time restoration.

• The database and table restoration task fails if you restore databases or tables in the following scenarios:

  • The name that you specify for a destination database or table is identical to one that exists 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.

• 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 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 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

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region in which the cluster is deployed.

3. Find the cluster and click its ID.

4. In the left-side navigation pane, choose Settings and Management > Backup and Restoration.

5. On the Backup and Restoration page, click Restore Databases/Tables.

6. In the dialog box that appears, set the Restoration Type parameter to Point in Time and set the Restoration Time parameter to the point in time to which you want to restore data.

   

   Note

   The point in time you specify for the Restoration Time parameter must fall within the range of the Restore To parameter. To use the Point in Time restoration method, make sure that the full backup set that is closest to the specified point in time contains the tables that you want to restore. The time range of the Restore To parameter depends on the value of the Log Retention Period (Days) parameter. The default value is seven 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 tables that you want to restore.

   

   Note

   • If you do not specify a name for a database or table that you want to restore, 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 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