All Products
Search
Document Center

PolarDB:Cluster restoration: Restore data to a previous point in time

Last Updated:Oct 12, 2023

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 can be restored to a new cluster. The parameter settings of the original cluster cannot be 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

For a cluster of PolarDB for MySQL Cluster Edition, after the SQL Explorer feature is enabled, you can use this feature to view audit logs and obtain the points in time for accidental operations.

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 enable binary logging for the cluster before you view and obtain binary log records. Otherwise, the You are not using binary logging error message 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. In the example provided in this topic, a Linux OS is used.
    1
  3. Run the following command on the client that is connected to your cluster to view binary logs:
    show binary logs;
    The following information 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 log records and save the log records to your local 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 the account. If you do not specify the password, you are required to enter the password after you run the preceding command. TestPwd123
    -hThe public endpoint of the 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 only to the public primary endpoint or a public cluster endpoint to remotely obtain binary log records. The cluster endpoint can be the default cluster endpoint or a custom cluster endpoint. For more information about how to apply for a public endpoint, see Manage the endpoints of a cluster.
    test-polardb.rwlb.rds.aliyuncs.com
    --read-from-remote-serverSpecifies that binary logs are read from the MySQL server instead of from a local log file. --read-from-remote-server
    --rawSpecifies that the binary log records are displayed in the original format. The parsed data is not displayed. --raw
    mysql-bin.******The name of the binary log file that you obtain by executing the show binary log records; statement. The name is displayed in the Log_name field. mysql-bin.000005
    Examples:
    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 log records in the binary log files by using mysqlbinlog:
    mysqlbinlog -vv --base64-output=decode-rows mysql-bin.****** | more
    Note
    • -vv: queries SQL statements and remarks.
    • --base64-output=decode-rows: parses the log records in the binary log files.

    The example provided in the following figure shows the details of binary log records.

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

Step2: 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 > Restoration.
  2. On the Restoration page, click Point-in-time Restoration.
    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.
      Restoration by 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 select the ApsaraDB for MongoDB (Subscription) Terms of Service, and then 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 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 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 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.

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. 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.
    Region
  3. In the upper-right corner of the page, click Create Migration Task.
  4. Configure the source and destination databases.
    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 the source and destination databases
  5. In the lower-right corner of the page, click Set Whitelist and Next.
    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. Select migration types and the objects to migrate.
    Select the objects to be migrated and the migration types
    ParameterDescription
    Migration TypesSelect Schema Migration and Full Data Migration.
    Select the objects that you want to migrate

    Select one or more objects from the Available section and click the Rightwards arrow icon to add the objects to the Selected section.

    Important You can select columns, tables, or databases as the objects to migrate.
  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, 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 the task during full data migration. Otherwise, the data migrated to the destination database may be incomplete. You can wait until the full data 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.
    Clusters
  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
  3. Click OK.