PolarDB:Cluster Recovery: Point-in-Time Recovery (PITR)

Prerequisites

• Point-in-Time Recovery (PITR) works by first restoring a full data backup (a snapshot) created before the specified time point, and then applying incremental data from the redo logs up to that point. To accelerate the recovery process, you can enable enhanced backup to shorten the backup interval and increase backup frequency.

• If the redo logs for the specified recovery point have not yet been backed up from the local node, they must be replayed. The source cluster must be available during this process.

• We recommend that you perform data recovery during off-peak hours.

Important

The restored cluster contains the data and account information of the source cluster but does not include its parameter settings.

Step 1: Identify the time of misoperation

If you already know the time of the accidental operation, you can skip this step. If you are unsure of the exact time, follow these instructions to determine it.

Method 1: SQL Explorer

If you have enabled SQL Explorer for your PolarDB for MySQL Cluster Edition cluster, you can use this feature to view the audit log and determine the time of the accidental operation.

SQL Explorer can only display SQL logs that were generated after the feature was enabled. If SQL Explorer was not enabled on the cluster at the time of the incident, use Method 2.

Method 2: Fetch and parse binary logs remotely

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

   Note

   You must enable binary logging for the cluster before you can view and fetch the binary log. Otherwise, a You are not using binary logging error is returned.

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

   root@xxx:~# mysql -h xxx-polardb.rwlb.rds.aliyuncs.com -P3306 -utest_api -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is xxx
   Server version: 8.0.13 Source distribution
   Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates.  Other names may be trademarks of their respective
   owners.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   mysql>

3. In the connected MySQL client, run the following command to view the list of binary log files for the target cluster:

   show binary logs;

   The command returns the following output:

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

4. Remotely fetch the binary log and save it to your local machine.

   This topic uses a Linux system as an example. First, run exit to quit the MySQL client. Then, run the following command to fetch the binary log.

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

   Parameter	Description	Example
   -u	The username of the database account in the cluster.	test_api
   -p	The password for the account. If you leave this empty, you will be prompted to enter it after running the 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, you must append the port number to the endpoint. Currently, you can remotely fetch the binary log only through the public endpoint of the primary node or a cluster endpoint (including the default and custom cluster endpoints). To learn how to apply for a public endpoint, see Manage endpoints.	test-polardb.rwlb.rds.aliyuncs.com
   --read-from-remote-server	Indicates that the binary log is read from the MySQL server, not from a local log file.	--read-from-remote-server
   --raw	Indicates that the fetched binary log file is printed in its original format instead of as parsed data.	--raw
   mysql-bin.******	The name of the target binary log file, as listed in the Log_name column from the show binary logs; command.	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 contents of the binary log file by using the mysqlbinlog tool:

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

   Note

   • -vv: Displays specific SQL statements and comments.

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

   The output is similar to the following:

   [root@iZxxx ~]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000110 | more
   /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
   /*!40019 SET @@session.max_insert_delayed_threads=0*/;
   /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
   DELIMITER /*!*/;
   # at 4
   #160217 23:04:37 server id 2802943055  end_log_pos 107  Start: binlog v 4, server v 5.5.18.1-log created 160217 23:04:37
   # at 107
   #160217 23:04:38 server id 2802943055  end_log_pos 171  Query    thread id=584632    exec time=0    error code=0
   SET TIMESTAMP=1xxx8/*!*/;
   SET @@session.pseudo_thread_id=584632/*!*/;
   SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
   SET @@session.sql_mode=2097152/*!*/;
   SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
   /*!\C utf8 *//*!*/;
   SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
   SET @@session.lc_time_names=0/*!*/;
   SET @@session.collation_database=DEFAULT/*!*/;
   BEGIN
   /*!*/;
   # at 171

6. After you retrieve the contents of the binary log file, you can parse the binary log.

Step 2: Restore data to a new cluster

After you determine the time of the accidental operation, follow these steps to restore the cluster's data to a new cluster.

1. Go to the cluster details page. In the left-side 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 recovery point in time and configure the parameters for the new cluster.

   1. Select a Pay mode for the new cluster.

      Note

      If you plan to continue using the original cluster after the recovery and the new cluster is only for temporary use, select Pay-As-You-Go.

      If you plan to use the new cluster, you can use the same billing method as the original cluster or choose a different one.

   2. For Operation Type, select Restore to Point in Time. In the Restore To field, select the time to which you want to restore the cluster. This must be a time before the accidental operation occurred.

      Note

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

4. Configure other cluster parameters.

   Note

   If you intend to continue using the original cluster after recovery and the new cluster is only for temporary use, we recommend using the default settings. The specifications and settings do not affect the recovery process.

   If you plan to use the new cluster, you can use the same parameters as the original cluster or adjust them as needed.

5. Read and select the checkbox to agree to the terms of service, and then click Buy Now.

6. On the Pay page, confirm the order details and click Purchase.

Step 3: Compare data in the new cluster

After the data is restored, log on to the new cluster to compare and verify it.

You can log on to the new cluster by using DMS, other clients, or the command line. This topic describes how to use DMS to log on and verify data. For other methods, see Connect to a database cluster.

1. On the Basic Information page for the new cluster, click Log on to Database in the upper-right corner.

2. In the dialog box that appears, enter the Database Account and Database Password from the original cluster, and click Search. For Database Type, select POLARDB-MySQL. For Instance Region and Instance ID, select the information corresponding to the new cluster.

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 new cluster's name, then find and double-click the target database to manage it.

5. Locate the data that was affected by the accidental operation. Confirm that it has been restored to its previous state and that all other data is also correct.

After data verification, if you plan to use the new cluster permanently, switch your applications to the new cluster's endpoint and apply necessary configurations, such as setting the whitelist and parameters. This ensures your services run as expected. If you want to continue using the original cluster, proceed to Step 4: Migrate data to the original cluster.

Step 4: Migrate data to the original cluster

After verifying the data in the new cluster, if you want to continue using the original cluster, you can use Data Transmission Service (DTS) to migrate the data from the new cluster back to the original cluster.

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

2. At the top of the Migration Tasks 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.

   The source database is the new cluster. The destination database is the original cluster where the accidental operation occurred. For both the source and destination databases, configure the Instance Type (select PolarDB), Instance Region, PolarDB Instance ID, Database Account, and Database Password. Click Test Connection to confirm that the connection is successful, and then click Set Whitelist and Next.

5. After completing the configuration, click Set Whitelist and Next in the lower-right corner of the page.

   Note

   This step automatically adds the IP addresses of the DTS servers to the whitelists of the source and destination clusters to ensure that the DTS servers can connect to both clusters.

6. Select the migration types and migration objects.

   Keep the default values for other settings. For Map Name Change, select Do not perform batch name changes. Set Retry Time After Connection Failure to 720 minutes. For Copy Temporary Tables During DMS_ONLINE_DDL, select No. After you complete the configuration, click Precheck and Start.

   Parameter	Description
   Migration Types	Select the Schema Migration and Full Data Migration checkboxes.
   Migration Objects	In the Available box, click the objects that you want to migrate, and then click the icon to move them to the Selected box. Important You can select migration objects at the database, table, or column level.

7. After you complete the configuration, click Precheck in the lower-right corner. After the precheck passes, click Next.

8. In the Confirm Settings dialog box, select a Channel Specifications and select the Data Transmission Service (Pay-As-You-Go) Service Terms checkbox.

9. Click Buy and Start to begin the data migration task.

Step 5: Release the temporary cluster

After the migration task is complete, the original cluster is restored to the selected point in time. At this point, you can release the new cluster to avoid unnecessary costs.

Alternatively, if you decide to use the new cluster permanently after verifying the restored data, you can release the original cluster after you switch your applications to the new cluster and complete its configuration.

1. On the Clusters page, find the cluster that you want to release. In the Actions column, choose More > Release.

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

   Note

   If you are releasing the temporary new cluster and have confirmed that the data recovery and migration to the original cluster were successful, we recommend that you select Delete all backup sets of the cluster immediately when you delete the cluster.

   The Retain Backups setting provides three options: retain all backup sets of the cluster permanently, automatically back up and permanently retain the backup (selected by default), or delete all backup sets of the cluster immediately. Retaining backups incurs additional fees. You can delete backups at any time to save costs. After you make your selection, click OK.

3. Click OK.