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

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

Precautions

• To restore data to an earlier 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 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.

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

• 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

1. Log on to the PolarDB console.

2. In the upper-left corner, select the region where 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 Restoration Type to Point in Time and set Restoration Time to select the point in time to which you want to restore data.

   

   Note

   The Restoration Time value must meet the limits of the Restore To parameter. To use the Point in Time restoration method, you must also select a full backup set that is closest to the specified point in time. The time range of the Restore To parameter is dependent on the Log Retention Period (Days) value. 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 table 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.

8. Click OK.

Related API operations