All Products
Search
Document Center

PolarDB:Database and table restoration: Restore data to a previous point in time

Last Updated:Oct 19, 2023

You can restore the data of a database or table to a previous point in time from a full data backup (snapshot) and redo logs. If you are aware of when data was accidentally deleted or modified, you can use this method to restore the data to a particular point in time before the accidental deletion or modification occurred.

Prerequisites

  • The instance has a backup set. To restore data to a previous point in time, you must first restore the data of a full backup that was created before the specified point in time. Then, you must restore the data generated after the backup that was created and before the specified point in time by using the redo logs. If you want to improve the restoration speed, you can enable enhanced backup to increase backup frequency.
  • 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: Check the point in time when data was deleted or modified accidentally

If you know when data was deleted or modified accidentally, you can skip this step. If you do not know the specific point in time when data was deleted or modified accidentally, you can choose one of the following methods to check.

Method 1: SQL Explorer

If the SQL Explorer feature of PolarDB for MySQL Cluster Edition is enabled, you can view audit logs to obtain the point in time when data was deleted or modified accidentally.

Note SQL Explorer is a paid service. You are charged for the storage space that is occupied by audit logs based on the retention period of the audit logs. For more information about SQL Explorer, see ️SQL Explorer and Audit.

You can check only SQL logs generated after the SQL Explorer feature is enabled. Therefore, if SQL Explorer is not enabled in the cluster, you can check the point in time when data was deleted or modified accidentally by using method 2.

Method 2: Obtain and parse binary logs remotely

  1. Enable the binary logging feature. For more information, see Enable binary logging.
    Note You must first enable binary logging for your cluster before you view and obtain binary logs. Otherwise, the human error message You are not using binary logging is returned.
  2. Install MySQL on your on-premises server and connect MySQL to your cluster through the MySQL client. For more information, see Use a client to connect to a cluster. This topic takes a Linux operating system as an example.
    1
  3. Run the following command on the client that is connected to your cluster to view binary logs:
    show binary logs;
    The following result is returned:
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000005 |      2639 |
    +------------------+-----------+
    1 row in set (0.00 sec)
  4. Run the following command on the client to obtain binary logs and save the logs to your on-premises server.
    This topic takes a Linux operating system as an example. To obtain binary logs and save the logs to your on-premises server, you must run the EXIT command to exit MySQL before you run the following command. 2Run the following command:
    mysqlbinlog -u <username> -p <password> -h <endpoint> --read-from-remote-server --raw mysql-bin.******
    ParameterDescriptionExample
    -uThe account name of your cluster. test_api
    -pThe password of your account. If you do not enter the password of your account, you will be required to enter the password after you run the preceding command. TestPwd123
    -hThe public endpoint of your cluster.
    Note
    • If the endpoint of your cluster uses the default port number 3306, you do not need to specify the port number. Otherwise, you must specify a port number after the endpoint.
    • You can connect to only the public-facing endpoints of the primary endpoint or cluster endpoints to remotely obtain binary logs. Cluster endpoints include the default cluster endpoint and custom cluster endpoints. For more information about how to apply for a public endpoint, see Manage the endpoints of a cluster.
    test-polardb.rwlb.rds.aliyuncs.com
    --rawYou can specify this parameter to print binary logs in the original format. The parsed data is not displayed. --raw
    mysql-bin.******The name of the binary log that you obtain by running the show binary logs; command. The name is displayed in the Log_name field. mysql-bin.000005
    Example:
    mysqlbinlog -utest_api -p -htest-polardb.rwlb.rds.aliyuncs.com --read-from-remote-server --raw mysql-bin.000005
  5. Run the following command to view the details of binary logs by using mysqlbinlog:
    mysqlbinlog -vv --base64-output=decode-rows mysql-bin.****** | more
    Note
    • -vv: allows you to view SQL statements and remarks.
    • --base64-output=decode-rows: allows you to parse binary logs.

    The following figure shows the details of a binary log.

    3
  6. After you obtain the binary log files, see mysqlbinlog-Utility for Processing Binary Log Files to parse binary logs.

Step 2: 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 Restore Databases/Tables dialog box, set Restore Type to Backup Timepoint based on your business needs, and Restoration Time to the point in time to which you want to restore data. The point in time that you select must be a point before the data was accidentally deleted or modified.
    2
    Note The time set for Restoration Time must be within the period of time specified for Restore To. Only data generated within the last seven days can be restored.
  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 3: Compare data in the original cluster

After all of the data is restored, you can log on to your cluster and compare and verify the data.

You can log on to the new cluster by using Data Management (DMS), other clients, or Alibaba Cloud command line interface (CLI). This topic describes how to log on to your cluster and verify data in 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.
    Basic information
  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 Instances Connected.
  4. In the Instances Connected list, click the cluster and double-click the database that you want to manage.
    1
  5. Identify the data that was accidentally deleted or modified and confirm whether it is restored, and check whether other data is consistent with what it was before the accidental deletion or modification occurred.