PolarDB:Database and table restoration: Restore data to a point in time

Prerequisites

A backup set must be available. Point-in-time recovery first restores a full data backup that was created before your selected point in time. Then, it applies incremental data from Redo logs to restore the data to the selected point in time. To accelerate the recovery, you can enable enhanced backup. This shortens the backup cycle and increases backup density.

Scenarios

Usage notes

• Point-in-time recovery restores databases or tables to a specific point in time. After the recovery is complete, the data in the tables is identical to the data at that point in time.

• You can restore databases and tables only from level-1 backups. You cannot restore from level-2 backups.

• Only the tables you select are restored. Make sure to select all tables that you need to restore.

  Note

  If you are unsure which tables to restore, perform a full cluster restoration.

• You can use the database and table restoration feature even if the cluster contains more than 50,000 tables, including system tables.

• If you choose to restore specific tables instead of a full database, you can restore up to 100 tables at a time. If you choose to restore a full database, all tables within that database are restored.

  Note

  To restore many tables, perform a full cluster restoration.

• The database and table restoration feature does not restore triggers.

• The database and table restoration feature does not restore foreign keys.

• 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.

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 Connect to a cluster. This topic takes a Linux operating system as an example.
   
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. Run 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 Manage the endpoints of a cluster.	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.

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

Step 2: Start the database and table restoration

1. Log on to the PolarDB console, click Clusters in the navigation pane on the left, select the Region where the cluster is deployed, and then click the cluster ID to open the cluster details page.

2. In the left-side navigation pane, choose Settings and Management > Backup and Restoration, and then click Restore Databases/Tables.

3. In the dialog box that appears, set the Restoration Type parameter to Point in Time and set the Restoration Time parameter to the point in time to which you want to restore data.

   

   Note

   The point in time you specify for the Restoration Time parameter must fall within the range of the Restore To parameter. To use the Point in Time restoration method, make sure that the full backup set that is closest to the specified point in time contains the tables that you want to restore. The time range of the Restore To parameter depends on the value of the Log Retention Period (Days) parameter. The default value is seven days.

   Select a restoration speed option based on your business requirements. The restoration speed options include Quick, Standard, and Secure. Each option consumes a different level of input/output operations per second (IOPS) to restore data to the current cluster. The estimated restoration duration for each restoration speed option can be found in the reference test data for database and table restoration speed.

   • Quick: consumes a large number of IOPS (approximately 60%) for restoring data to the current cluster. We recommend you select this option during off-peak hours.

   • Standard (recommended): consumes a moderate number of IOPS (approximately 30%) for restoring data to the current cluster.

   • Secure: consumes a small number of IOPS (approximately 15%) for restoring data to the current cluster. This may significantly slow down the restoration process.

4. On the left side of the Databases and Tables to Restore section, select the database that you want to restore. On the right side, select the tables that you want to restore.

   

   Note

   • If you do not specify a name for a destination database or table, the system automatically generates a new database or table name by suffixing the name of the original database and table with _backup. For example, if the name of an original table is test, the destination table is automatically named test_backup.

   • If you do not select a table after you select a database, all tables in the database are restored.

5. Verify that you have accurately identified and selected the databases or tables you want to restore, and then click OK.

Step 3: Compare and verify the data

After the database and table restoration is complete, log on to your cluster to compare and verify the data.

You can log on to the cluster using Data Management Service (DMS), another client, or the command line. This topic describes how to log on to the cluster using DMS to verify the data. For information about other methods, see Connect to a database cluster.

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

   

2. In the dialog box that appears, enter the Database Account and Database Password for the cluster, and then click Log On.

3. After you log on to DMS, refresh the page. In the navigation pane on the left, click Logged-in Instance.

4. In the Logged-in Instance list, click the name of the target cluster. Find and double-click the name of the target database to manage it.

   

5. Find the data that was affected by the incorrect operation. Confirm that the data has been restored to its state before the incorrect operation and that other data is also consistent with its state before the operation.