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 deleted or modified accidentally, 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.
  • Your PolarDB cluster must be one of the following versions:
    • A PolarDB for MySQL 5.6 cluster whose kernel minor version is 5.6.1.0.24 or later
    • A PolarDB for MySQL 5.7 cluster whose kernel minor version is 5.7.1.0.8 or later

Usage notes

  • You can restore only the specified tables. You must confirm that you have selected all of 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.
  • Only clusters that have no more than 50,000 tables allow you to restore databases or tables. You cannot restore databases or tables of a cluster if the cluster has more than 50,000 tables. You can restore up to 100 tables at a time. When you restore a database, all of its tables are restored.
    Note If you need 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.
  • The database and table restoration feature does not restore triggers. If the original table has a trigger, the trigger will not be restored.
  • The database and table restoration feature does not restore foreign keys. If the original table has a foreign key, the foreign key will not 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.

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.******
    Parameter Description Example
    -u The account name of your cluster. test_api
    -p The 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
    -h The 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 Apply for a cluster endpoint or a primary endpoint.
    test-polardb.rwlb.rds.aliyuncs.com
    --raw You 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 > Backup and Restore.
  2. On the Backup and Restore page, click Restore Databases/Tables.
  3. In the dialog box that appears, you can select Backup Timepoint in the Restore Type section based on your business needs, and select the point in time that you want to restore data to in the Restoration Time section. The point in time that you select must be a time before the data was accidentally deleted or modified.
    2
    Note The time selected in the Restoration Time section must be within the period of time specified in the Restore To section. Only the data generated in the last seven days can be restored.
  4. On the left section of the Databases and Tables to Restore page, select the destination database, and select the destination table on the right side.
    1
    Note
    • If you select a database but do not specify tables, all data in the database is restored.
    • After you select a database or a table, the system automatically generates the name of the new database or table by adding the _backup suffix to the name of the original database or table. For example, if the name of the original table is test, then the new table is named test_backup. You can also customize the names of new databases and tables.
  5. Click OK.

Step 3: Compare data in the original cluster

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 in the DMS console. For more information about other methods, see Connect to a cluster.

  1. In the upper-right corner of the Overview 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. Identify 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.