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, nor does it directly write data into databases and tables in the cluster. Instead, new databases and tables are created in the cluster. By specifying new names for databases or tables during the restoration process, you can 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.

Supported versions and Limitations

Supported versions

• Both Enterprise Edition and Standard Edition of PolarDB for MySQL support restoring databases and tables from backup sets. The following are specific supported versions and limitations.

  • PolarDB Enterprise Edition: Cluster Edition clusters that run MySQL 5.6, 5.7, 8.0.1, or 8.0.2 support this feature.

    • PolarDB for MySQL 5.6: the revision version must be 5.6.1.0.25 or later.

    • PolarDB for MySQL 5.7: the revision version must be 5.7.1.0.8 or later.

    • PolarDB for MySQL 8.0.1: the revision version must be 8.0.1.1.14 or later.

    • PolarDB for MySQL 8.0.2: the revision version must be 8.0.2.2.0 or later.

  • PolarDB Standard Edition:

    • PolarDB for MySQL 5.7 on x86 architecture: the revision version must be 5.7.1.0.30 or later.

    • PolarDB for MySQL 8.0.1 on x86 architecture: the revision version must be 8.0.1.1.38.2 or later.

    • PolarDB for MySQL 8.0.2 on x86 architecture: the revision version must be 8.0.2.2.21 or later.

    • PolarDB for MySQL 8.0.1 on the ARM-based Yitian processor: the revision version must be 8.0.1.1.41 or later.

    • If your cluster uses cloud disks and contains 50,000 or more tables, it cannot be restored from backup sets.

• The primary cluster of a GDN supports restoring databases and tables from backup sets. The following are requirements for the cluster version. The requirements apply to both Enterprise Edition and Standard Edition.

  • PolarDB for MySQL 5.6: does not support this feature.

  • PolarDB for MySQL 5.7: the revision version must be 5.7.1.0.30 or later.

  • PolarDB for MySQL 8.0.1: the revision version must be 8.0.1.1.38.2 or later.

  • PolarDB for MySQL 8.0.2: the revision version must be 8.0.2.2.21 or later.

• Clusters that meet the following version requirements support optimized restoration of databases and tables from backup sets. The requirements apply to both Enterprise Edition and Standard Edition.

  • PolarDB for MySQL 5.6: does not support optimized restoration of databases and tables from backup sets.

  • PolarDB for MySQL 5.7: the revision version must be 5.7.1.0.30 or later.

  • PolarDB for MySQL 8.0.1: the revision version must be 8.0.1.1.38.2 or later.

  • PolarDB for MySQL 8.0.2: the revision version must be 8.0.2.2.21 or later.

  Note

  Optimized restoration of databases and tables from backup sets offers faster restoration speed. For information about how this feature works and how fast it is, see Overall process and estimated time.

Limitations

• Multi-master Cluster (Database/Table) Edition clusters do not support database or table restoration from backup sets.

• Tables with global secondary indexes cannot be restored from backup sets.

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

• Secondary clusters of a GDN do not support restoration from backup sets.

• In-memory column indexes cannot be restored from backup sets.

• Tables archived as cold data cannot be restored from backup sets.

• Only tables that use the InnoDB storage engine can be restored from backup sets.

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 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 this feature, follow the guide on Contact us.

  • You can execute the following statement to query the total number of tables in a cluster (system tables included).

    SELECT COUNT(*) FROM information_schema.tables;

  • You can execute the following statement to query the total number of tables in a cluster.

    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 to which you want to connect 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