All Products
Search
Document Center

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

Last Updated:Dec 19, 2024

Database and table restoration is a process that restores only specified databases or tables in a cluster. For example, 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 or tables by using two methods: restore from a backup set or restore to an earlier point in time. This topic describes how to restore the data of databases or tables 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

Supported versions

Note

For information about how to check the database engine version of a cluster, see Query an engine version.

  • A PolarDB for MySQL cluster whose Database Edition is Enterprise Edition and Edition is Cluster Edition supports the database and table restoration feature if the cluster runs one of the following database engine versions:

    • 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 primary cluster of a GDN whose Database Edition is Enterprise Edition supports the database and table restoration feature if the cluster runs one of the following database engine versions:

    • PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.42 or later.

    • PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.30 or later.

    • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.44 or later.

    • PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.25.3 or later.

  • A cluster whose Database Edition is Enterprise Edition supports the new version of the database and table restoration feature if the cluster runs one of the following database engine versions:

    • PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.42 or later.

    • PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.30 or later.

    • PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.44 or later.

    • PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.25.3 or later.

    Note

    Compared with the original version of the database and table restoration feature, the new version can restore data to the cluster at a faster speed. For more information, see Overall process and estimated time.

Limits

  • PolarDB clusters whose edition is Multi-master Cluster (Database/Table) do not support the database or table restoration feature.

  • You cannot use the database and table restoration feature to restore tables that have global secondary indexes.

  • If the number of tables in a PolarDB for MySQL cluster that does not have read-only nodes exceeds 50,000, you cannot use the database and table restoration feature in the cluster.

  • You cannot use database and table restoration on secondary clusters of a GDN.

  • You cannot use database and table restoration to restore In-Memory Column Indexes (IMCIs).

  • You cannot use database and table restoration to restore tables archived as cold data.

  • You can only use database and table restoration to restore tables that use the InnoDB storage engine.

Note

To restore databases or tables on a cluster that does not support database and table restoration, you can first perform a full restoration of the data into a new cluster by using one of the following methods: Method 1 for full restoration: Restore data from a backup set or Method 2 for full restoration: Restore data to an earlier point in time. Then, migrate the data from the new cluster to your original cluster. For information about how to migrate data between clusters, see Migrate data between PolarDB for MySQL clusters.

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

    2

    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.

    1

    Note
    • If you do not specify a name for a destination 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, if the name of an original table is test, the destination table is automatically named test_backup.

    • 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

API operation

Description

DescribeMetaList

Queries the metadata of the database or table that you want to restore.

RestoreTable

Restores the databases or tables of a PolarDB cluster.