All Products
Search
Document Center

PolarDB:Database and table restoration: Restore data from a backup set

Last Updated:Oct 19, 2023

If you want to restore data to a previous point in time and the point in time is when a backup set (snapshot) was created, you can restore the data from the backup set (snapshot) that was accidentally deleted or modified.

Prerequisites

  • Your database has a backup set (snapshot) before the data was accidentally deleted or modified.
  • 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 Upgrade versions.

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

  • Database and table restoration is not supported on clusters with transparent data encryption (TDE) enabled.
  • Clusters in the Global Database Network (GDN) do not support the database and table restoration feature.

Precautions

  • 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 cannot determine all the related tables that you want to restore, we recommend that you restore all the historical data of the entire cluster by using a cluster restoration method.
  • The database and table restoration feature is applicable only to clusters that store 50,000 tables or less, including system tables. If the cluster stores more than 50,000 tables, this feature cannot be used. You can restore up to 100 tables at a time. If you restore a database, all tables in the database are restored.
    Note If you want to restore a large number of tables, we recommend that you restore all the historical data of the entire cluster by using a cluster restoration method.
  • 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.
  • We recommend that you restore data during off-peak hours.

Step 1: Start restoration

  1. In the left-side navigation pane on the Overview page, choose Settings and Management > Restoration.
  2. On the Restoration page, click Restore Databases/Tables.
  3. In the dialog box that appears, set Restore Type to Backup Set and select the backup set from which you want to restore data in the backup set drop-down list.
    1
  4. 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 select a table after you select a database, all tables in the database are restored.
    • After you select a database or table, the system automatically generates the name of the new database or table by appending the _backup suffix to the name of the selected database or table. For example, if the name of the selected table is test, the new table is named test_backup. You can also change the name of the new database or table.
  5. Click OK.

Step 2: Compare data

After all the data has been restored, you need to log on to your cluster and compare and verify the data.

You can log on to the new cluster by using DMS, other clients, or Alibaba Cloud command line interface (CLI). This topic describes how to log on to your cluster and verify data on the DMS console. For more information about other methods, see Connect to a cluster.

  1. In the upper-right corner of the Basic Information page, click Log on to Database.
    Overview
  2. In the dialog box that appears, specify database account and database password that are created in your cluster, and click Login.Logon page
  3. After you log on to DMS, refresh the page. In the left-side navigation pane, click Logged in instance.
  4. In the Logged in instance list, click the cluster and double-click the database that you want to manage.
    1
  5. Locate the data that was deleted or modified accidentally and confirm whether it has been restored, and check whether other data is consistent with what it was before the accidental deletion or modification occurred.