Database and table restoration: Point-in-time recovery

Prerequisites

A backup set is available. Point-in-time recovery first restores a full data backup taken before the selected point in time to the cluster. It then uses redo logs to restore incremental data up to the selected point in time. To increase the restoration speed, you can enable enhanced backup, which shortens the backup cycle and increases the backup density.

Scenarios

Usage notes

• Point-in-time restoration refers to selecting a specific point in time to restore database tables. After the restoration, the database tables contain only the data as of that point in time. In other words, the table data in the database is consistent with the data at that point in time.

• Currently, the "Restore to a past point in time" method of database and table restoration supports only restoration from a past point in time of a level-1 backup. Restoration from a level-2 backup is not supported.

• Database and table restoration fails in the following scenarios.

  • The specified database name or table name already exists in the original cluster.

  • Between the time when the backup set is generated and the restoration point in time, the table to be restored is deleted, or the cluster contains a database or table with the same name as the destination database or table.

• Database and table restoration restores only the specified tables. Make sure that you select all the tables that you want to restore.

  Note

  If you cannot determine all the tables involved, restore the historical data of the entire cluster to a new cluster and then migrate the data back to the original cluster. For more information, see Restore from a backup set and Restore a cluster to a point in time.

• If you do not restore an entire database, you can restore up to 100 tables of the database at a time. If you restore an entire database, you can restore all tables in the database.

  Note

  • Restoring too many tables at a time takes a long time, so do not restore too many tables at once.

  • If you need to restore a large number of tables, restore the full historical data to a new cluster instead. For detailed steps, see Restore from a backup set and Restore a cluster to a point in time.

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

  Note

  • This feature is currently in canary release. To use it, you can Contact us to join the allowlist and enable the feature.

  • Query the number of tables, including system tables, in the cluster. Run the following SQL statement:

    SELECT COUNT(*) FROM information_schema.tables;

  • Query the number of system tables in the cluster. Run the following SQL statement:

    SELECT COUNT(*) FROM information_schema.tables WHERE table_schema IN ('sys', 'performance_schema', 'mysql', 'information_schema', '__recycle_bin__');

• Database and table restoration does not support restoring triggers. If the original table has a trigger, the trigger is not restored.

• Database and table restoration does not support restoring foreign keys. If the original table has a foreign key, the foreign key is not restored.

• Restore data during off-peak hours.

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: Start database and table restoration

1. Log in to the PolarDB console，In the navigation pane on the left, click Clusters. Select the Region where the cluster is deployed, and then click the cluster ID to go to the cluster details page。

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

3. In the dialog box that appears, set Restoration Type to Point in Time, and select the required point in time for Restoration Time.

   Note

   • Restoration Time must fall within the range specified by Restore To. In addition, the full backup set closest to the selected point in time must contain the table to be restored for the Point in Time restoration feature to work properly. The range of restorable points in time depends on the retention period specified by Log Retention Period (Days) in the backup settings. The default value is 7 days.

   • Based on IOPS consumption, database and table restoration provides three restoration speed configurations: Quick, Standard, and Secure. Select a configuration based on your requirements. For the estimated time required by each restoration speed configuration, see Reference for database and table restoration speed tests.

     • Quick: Restoring data to the current cluster can consume a large amount of IOPS. Select this configuration during off-peak hours.

     • Standard (recommended for specifications with more than 4 cores): Restoring data to the current cluster can consume some IOPS.

     • Secure (recommended for specifications with 4 cores or fewer): Restoring data to the current cluster consumes only a small amount of IOPS but may significantly reduce the restoration speed.

     For clusters with specifications of 4 cores or fewer, make sure that the settings of innodb_io_capacity and innodb_io_capacity_max match the specifications. For cluster specifications and the maximum supported IOPS, see Compute node specifications for Enterprise Edition and Compute node specifications for Standard Edition, and use the Secure restoration speed.

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

   Note

   • If you do not specify new database or table names, the system automatically generates them by appending _backup to the original database or table name. For example, if the original table is test, the automatically generated table name is test_backup.

   • If you select a target database but do not specify tables, all data in the database is restored by default.

5. After you confirm that the databases and tables that you want to restore are selected, click OK.

Step 3: Compare data

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

You can log on to the cluster by using Data Management Service (DMS), another client, or the command line. This topic describes how to log on to the cluster by using DMS to verify the data. For information about other methods, see Connect to a 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 the Database Password of the cluster, and then click Search.

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 name of the target cluster, and then find and double-click the name of the target database to switch to the target database for management.

5. Locate the data affected by the accidental operation. Check whether the data is restored to its state before the operation, and whether other data also matches that state.