You can restore a cluster 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 entire cluster 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.
  • We recommend that you restore data during off-peak hours.

Precautions

Only the data and account information of the original cluster are restored to the new cluster. The parameters of the original cluster are not restored to the new cluster.

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 ApsaraDB PolarDB MySQL-compatible edition 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 PolarDB for MySQL 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 an 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: Restore data to a new cluster

After you obtain the point in time when data was deleted or modified accidentally, you can perform the following operations to restore the entire cluster to a new cluster.

  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 Point-in-time Restore.
    1
  3. On the Clone Instance page that appears, configure the point in time to which you want to restore data and the parameters of the new cluster.
    1. Select Billing Method for the new cluster.
      Note

      For example, you want to continue using the original cluster and the new cluster is only for temporary use. After the restoration, you can select Pay-As-You-Go in the Billing Method section.

      If you plan to use the new cluster after the restoration, you can set the billing method to that of the original cluster or adjust it as needed.

    2. Select Backup Timepoint in the Clone Source Type section and select the point in time to which you want to restore data in the Backup Timepoint section. The point in time you select must be a time before the data was deleted or modified accidentally.
      Restore data to a previous point in time
  4. Configure other parameters.
    Note

    If you do not intend to use the new cluster after the restoration, and continue using the original cluster, we recommend that you keep the default settings. The default specifications and settings will not affect the entire restoration process.

    If you plan to use the new cluster after the restoration, you can set the parameters to that of the original cluster or adjust it as needed.

  5. Read and accept the terms of service, and click Buy Now.
  6. On the Purchase page, confirm the information of the unpaid order and click Purchase.

Step 3: Compare data in the new cluster

After all of the data of your cluster has been restored to the new cluster, you need to log on to the new 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 PolarDB for MySQL cluster.

  1. In the upper-right corner of the Overview page of the new cluster, click Log On to Database.
    Overview
  2. In the dialog box that appears, specify database account and database password that are created in the original 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.

After you verify the data, if you want to directly use the new cluster, you need to connect your applications to the endpoint of the new cluster. Then, you must configure whitelists and other parameters in the new cluster to ensure that your business runs normally. If you want to continue using the original cluster, you need to perform Step 4: Migrate the restored data from the new cluster to the original cluster.

Step 4: Migrate the restored data from the new cluster to the original cluster

After you verify the data in the new cluster, if you want to continue using the original cluster, you can migrate the restored data from the new cluster to the original cluster.

  1. Log on to the Data Transmission Service (DTS) console. In the left-side navigation pane, click Data Migration.
  2. At the top of the Migration Tasks page, select the region where the destination cluster resides.
    Select a region
  3. In the upper-right corner of the page, click Create Migration Task.
  4. Configure source and destination clusters.
    The source cluster is the new cluster. The destination cluster is the cluster that you want to restore, in which data was deleted or modified accidentally. Configure source and destination clusters
  5. After you finish the preceding configuration, click Set Whitelist and Next in the lower-right corner of the page.
    Note In this step, DTS automatically adds the IP address of the DTS server to the whitelists of the source and destination clusters. This ensures that the DTS server can connect to the source and destination clusters as expected.
  6. Configure migration types and objects.
    Configure migration types and objects
    Parameter Description
    Migration Types Select Schema Migration and Full Data Migration.
    Available

    In the Available section, select the objects to be migrated. Then, click the Right arrow icon to add them to the Selected section.

    Notice You can select databases, tables, and columns as the objects to be migrated.
  7. In the lower-right corner of the page, click Precheck. After the precheck is completed, click Next.
  8. In the Confirm Settings dialog box that appears, specify the Channel Specification parameter and select Data Transmission Service (Pay-As-You-Go) Service Terms.
  9. Click Buy and Start to start the data migration task.
Note We recommend that you do not manually stop a data migration task. Otherwise, the data migrated to the destination instance will be incomplete. You need only to wait until the migration task is completed. The migration task automatically stops.

Step 5: Release the cluster that you no longer use

After the migration task is completed, the original cluster is restored to the selected point in time. If you want to continue using the original cluster, you can release the new cluster to avoid unnecessary costs.

However, if you plan to use the new cluster directly after the verification and restoration are completed, you can release the original cluster after you move your business to the new cluster and configure the cluster's settings.

  1. On the Clusters page, find the cluster that you want to release and choose More > Release in the Actions column.
    Cluster list
  2. In the Release Cluster dialog box that appears, select a backup retention policy.
    Note

    If you want to continue using the original cluster and you have confirmed that all data has been restored and migrated, we recommend that you select Delete all backup sets of the cluster immediately when you delete the cluster when releasing the new cluster.

    Release clusters
  3. Click OK.