PolarDB:Database and table recovery: Point-in-time recovery

Scope of application

A backup set is available. Point-in-time restoration first restores the most recent full backup taken before the selected point in time to the cluster, and then replays redo logs to apply incremental data up to the selected point in time. To speed up restoration, you can enable enhanced backup to shorten the backup interval and increase 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, which means the table data in the database is consistent with the data at that point in time.

• The "Restore to a past point in time" mode in the database and table restoration feature only supports restoration from a past point in time of a first-level backup. Restoration from a second-level backup is not supported.

• The database and table restoration operation 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 was 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 specified tables. Make sure that you have selected all the tables that need to be restored.

  Note

  If you cannot determine all the tables involved, we recommend that you restore the historical data of the entire cluster to a new cluster and then migrate the data 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 the entire database, up to 100 tables can be restored at a time for each database. If you restore the entire database, all tables in the database are restored.

  Note

  • If too many tables are restored at a time, the restoration takes a long time. Therefore, we recommend that you do not restore too many tables at a time.

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

• You can use the database and table restoration feature even when the cluster has more than 50,000 tables (including system tables).

  Note

  • This feature is currently in the phased rollout stage. To use this feature, you can Contact us to add the cluster to the whitelist and enable this feature.

  • Query the number of tables (including system tables) in the cluster. The SQL statement is as follows:

    SELECT COUNT(*) FROM information_schema.tables;

  • Query the number of system tables in the cluster. The SQL statement is as follows:

    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 configured, the trigger is not restored.

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

• We recommend that you perform data restoration 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 point in time to which you want to restore the Restoration Time.

   Note

   • The point in time you select for Restoration Time must fall within the range of Restore To. At the same time, the table to be restored must exist in the most recent full backup set taken before this point in time for the Point in Time feature to work properly. The range of restorable points in time is subject to the Log Retention Period (Days) period in the backup settings, which is 7 days by default.

   • The database and table restoration feature provides three speed configurations based on IOPS consumption: Quick, Standard, and Secure. Select a configuration based on your business requirements. For the estimated restoration duration of each speed configuration, see Reference for database and table restoration speed tests.

     • Quick: When data is being restored to the current cluster, a large amount of IOPS may be consumed. We recommend that you select this configuration during off-peak hours.

     • Standard (recommended for clusters with more than 4 cores): When data is being restored to the current cluster, a moderate amount of IOPS may be consumed.

     • Secure (recommended for clusters with 4 cores or fewer): When data is being restored to the current cluster, only a small amount of IOPS is consumed, but the restoration speed may be significantly affected.

     For clusters with 4 cores or fewer, we recommend that you verify that innodb_io_capacity and innodb_io_capacity_max are set to values that match the cluster specification. For the maximum supported IOPS of each cluster specification, see Compute node specifications for Enterprise Edition and Compute node specifications for Standard Edition. We recommend that you use the Secure speed for restoration.

4. In the Databases and Tables to Restore section, select the destination database on the left side and the destination tables on the right side.

   Note

   • If you do not specify new database names or table names, the system appends _backup to the original database names or table names to automatically generate new database names and table names. For example, if the original table name is test, the automatically generated new table name is test_backup.

   • If you select a destination database but do not specify tables, all tables in the database are restored by default.

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

Step 3: Verify restored data

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

You can log on to the cluster through DMS or by using another client or the CLI. This topic describes how to log on to the cluster through DMS for verification. For other methods, see Connect to a cluster.

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

5. Locate the accidentally operated data and verify whether it has been restored to the state before the accidental operation, and whether other data is also consistent with the state before the accidental operation.