PolarDB:Cluster Restoration: Point-in-Time Restore

Scope

• A backup set must exist. Point-in-time restore first restores a full data backup from before the selected time and then applies Redo logs to restore incremental data up to the selected time. To improve restoration speed, enable enhanced backup to shorten the backup cycle and increase backup density.

• If binary logs for the specified restoration time have not yet been backed up locally, replay the binary logs. The source cluster must be active.

• Perform data restoration during off-peak business hours.

Notes

The restored cluster includes the original cluster's data and account information but not its parameter settings.

Step 1: Get the Time of the Erroneous Operation

If you know the exact time of the erroneous operation, skip this step. Otherwise, use one of the following methods to identify it.

Method 1: SQL Explorer

For PolarDB for MySQL Cluster Edition clusters, if SQL Explorer is enabled, you can review audit logs to identify when the erroneous operation occurred.

SQL Explorer only supports viewing SQL logs generated after the feature is enabled. If SQL Explorer is not enabled for the cluster, use Method 2 to identify the time of the erroneous operation.

Method 2: Remotely Retrieve and Parse Binary Logs

1. Enable binary logging for the cluster. For detailed steps, see Enable binary logging.

   Note

   Enable binary logging for the cluster before retrieving or viewing binary logs. Otherwise, the error message You are not using binary logging appears.

2. Install MySQL on your local server and connect to the cluster using the MySQL client. For detailed steps, see Connect to a Database Cluster. This topic uses Linux as an example.

   

3. In the client connected to the cluster, execute the following command to view the binary log file list of the destination cluster:

   show binary logs;

   The following result is returned:

   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000005 |      2639 |
   +------------------+-----------+
   1 row in set (0.00 sec)

4. On the client, execute the following command to remotely retrieve binary logs and save them locally.

   This topic uses Linux as an example. Exit MySQL first, then retrieve binary logs and save them locally.Execute the following statement:

   mysqlbinlog -u<username> -p<password> -h<endpoint> --read-from-remote-server --raw mysql-bin.******

   Parameter	Description	Example value
   -u	The account name in the cluster.	test_api
   -p	The password for the account. If left blank, you are prompted to enter it after executing this command.	TestPwd123
   -h	The public endpoint of the cluster. Note If your cluster endpoint uses the default port 3306, you can omit the port number. Otherwise, add the 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 information about how to request a public endpoint, see Manage Endpoints.	test-polardb.rwlb.rds.aliyuncs.com
   --read-from-remote-server	Reads binary logs from the MySQL server, not from local log files.	--read-from-remote-server
   --raw	Prints the retrieved binary log file in its original data format, without displaying parsed data.	--raw
   mysql-bin.******	The name of the destination binary log file obtained using the show binary logs; command, which is Log_name.	mysql-bin.000005

   Example:

   mysqlbinlog -utest_api -p -htest-polardb.rwlb.rds.aliyuncs.com --read-from-remote-server --raw mysql-bin.000005

5. Use the mysqlbinlog tool to view the binary log file content:

   mysqlbinlog -vv --base64-output=decode-rows mysql-bin.****** | more

   Note

   • -vv: View specific SQL statements and comments.

   • --base64-output=decode-rows: Parse the binary log file.

   The specific binary log content is shown in the red box in the following figure:

   

6. After retrieving the binary log file content, you can parse the binary logs.

Step 2: Restore Data to a New Cluster

After identifying the time of the erroneous operation, perform the following steps to restore the entire cluster to a new cluster.

1. Go to the cluster product page. In the left navigation pane, choose Settings and Management > Backup and Restoration.

2. On the Backup and Restoration page, click Point-in-time Restoration.

   

3. On the Clone Instance page, specify the restoration time and configure parameters for the new cluster.

   1. Select the Billing Method for the new cluster.

      Note

      If you do not plan to use the new cluster after restoration and will continue using the original cluster, select Pay-as-you-go as the billing method.

      If you plan to use the new cluster directly after restoration, keep the billing method consistent with the original cluster or adjust it as needed.

   2. For Operation Type, select Restore to a Past Time. In Backup Time Point, select the time before the erroneous operation.

      

      Note

      You can restore data to a specific time with one-second precision.

4. Configure other cluster parameters.

   Note

   If you do not plan to use the new cluster after restoration and will continue using the original cluster, use default settings. Its specifications and settings do not affect the restoration process.

   If you plan to use the new cluster directly after restoration, keep these parameters consistent with the original cluster or adjust them as needed.

5. Read and accept the Terms of Service. Click Buy Now.

6. On the Payment page, confirm the order information. Click Order.

Step 3: Compare Data in the New Cluster

After the full cluster data is restored to the new cluster, log on to the new cluster and compare and validate the data.

You can log on to the new cluster using DMS, other clients, or the command line. This topic describes how to log on to the cluster using DMS for validation. For information about other methods, see Connect to a Database Cluster.

1. In the upper-right corner of the Basic Information page of the new cluster, click Log on to Database.

   

2. In the dialog box that appears, enter the database account and database password created in the original cluster. Click Log On.

3. After logging on to DMS, refresh the page. In the left navigation pane, click Logged-on Instances.

4. In the Logged-on Instances list, click the name of the destination cluster. Find and double-click the name of the destination database to switch to it for management.

   

5. Locate the data affected by the erroneous operation. Confirm that it is restored to its state before the operation and that other data remains consistent with its state before the operation.

After verifying the data, if you want to use the new cluster directly, connect your applications to the endpoint of the new cluster and configure whitelists and other parameters to ensure normal business operations. If you prefer to continue using the original cluster, proceed to Step 4: Migrate the restored data from the new cluster to the original cluster.

Step 4: Migrate Data from the New Cluster to the Original Cluster

After confirming the data in the new cluster, if you still want to use the original cluster, use DTS to migrate data from the new cluster back to the original cluster.

1. Log on to the Data Transmission Service console. In the left navigation pane, click Data Migration.

2. At the top of the Migration Tasks List page, select the region where the destination cluster is located.

   

3. In the upper-right corner of the page, click Create Migration Task.

4. Configure the connection information for the source and destination databases of the migration task.

   The source database is the new cluster. The destination database is the original cluster where the erroneous operation occurred.

5. After completing the configurations, click Authorize Whitelist and Go to Next Step in the lower-right corner of the page.

   Note

   This step automatically adds the IP addresses of DTS servers to the whitelists of the source and destination clusters to ensure connectivity.

6. Select the migration type and migration objects.

   

   Configuration	Description
   Migration Type	Check Schema Migration and Full Data Migration.
   Migration Objects	In the Migration Objects box, click the objects to be migrated. Then, click the icon to move them to the Selected Objects box. Important Migration objects can be selected at the granularity of databases, tables, and columns.

7. After completing the configurations, click Precheck and Start in the lower-right corner of the page. After the precheck passes, click Next.

8. In the Purchase Configuration Confirmation dialog box, select Link Specification and accept the Data Transmission Service (Pay-as-you-go) Terms.

9. Click Purchase and Start. The migration task starts.

Step 5: Release the Cluster

After the migration task completes, the original cluster is restored to the selected time. If you still want to use the original cluster, release the new cluster to avoid unnecessary costs.

Alternatively, after validation and restoration, if you plan to use the new cluster directly, release the original cluster after switching services and configuring the new cluster.

1. On the Clusters page, find the destination cluster. In the Actions column on the right, click More > Release.

   

2. In the Release Cluster dialog box that appears, select a backup retention policy.

   Note

   If you choose to continue using the original cluster and have confirmed successful data restoration and migration, select Delete all backup sets of this cluster immediately when deleting the cluster when releasing the new cluster.

   

3. Click OK.