All Products
Search
Document Center

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

Last Updated:Jan 15, 2024

Database and table restoration is a process that restores only specified databases or tables in a cluster. For example, assume that 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 and 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 a database or table to an earlier 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 Update the minor engine 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 whose revision version is 8.0.1.1.14 or later.

    • A cluster of PolarDB for MySQL 8.0 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, and Multi-master Cluster (Database/Table) Edition.

  • Clusters in the Global Database Network (GDN) do not support the database and table restoration feature.

  • You cannot restore tables that contain global secondary indexes.

  • The database and table restoration feature allows you to restore only tables that use InnoDB as the engine.

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

  • The database and table restoration feature is not supported on clusters of Single Node Basic Edition (discontinued).

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

  • 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
  • You can use the database table restoration feature even when a cluster contains more than 50000 tables (including system tables).

    Note
    • Query the number of tables (including system tables) in a cluster. In this example, the following SQL statement is used:

      SELECT COUNT(*) FROM information_schema.tables;
    • Query the number of system 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 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 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.

    2

    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.

    1

    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

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 for MySQL cluster.