How do I restore the backup file of an ApsaraDB RDS for MySQL instance to a self-managed MySQL instance database by point in time? - ApsaraDB RDS

This topic describes how to restore the incremental data of an ApsaraDB RDS for MySQL to a self-managed MySQL instance that resides on an on-premises device or an Elastic Compute Service (ECS) instance to a specific point in time.

Note

This method is suitable for incremental restoration. For more information about instance-level restoration methods, see Restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL database or Restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance.
For more information about data restoration methods, see Overview of data restoration methods.

Usage notes

This topic describes only how to restore incremental data. Before you restore incremental data, you must create and configure your self-managed MySQL instance. You must configure your self-managed MySQL instance and replace variables in the commands that are involved based on your business requirements.
The configuration of your self-managed MySQL instance affects the result of the data restoration.
Before you restore data, make sure that the MySQL version of your self-managed MySQL instance is later than or equal to the major engine version of the RDS instance. For example, if your RDS instance runs MySQL 5.6, the MySQL version of your self-managed MySQL instance must be later than MySQL 5.6.16. If your RDS instance runs MySQL 5.5, the MySQL version of your self-managed MySQL instance must be later than MySQL 5.5.18.
Note
MySQL 5.5 and MySQL 5.6 are different database engine versions. You cannot restore data from an RDS instance that runs MySQL 5.5 to a self-managed MySQL instance that runs MySQL 5.6.

Restore incremental backup data of an RDS instance to a self-managed MySQL instance

In this example, MySQL 5.6.20 is used. The installation package is in the tar.gz format.

Create and authorize a user that can remotely log on to the self-managed MySQL instance.
You need to create a user that can remotely log on to the self-managed MySQL instance and grant the user the required permissions. For more information, see official MySQL documentation.
Run the following command to view the binary log position in the backup file that is generated by Percona XtraBackup and determine the start position for incremental restoration:
```
cat xtrabackup_binlog_info
```
Note
The xtrabackup_binlog_info directory is subject to the actual configuration. You can run the find command to obtain the directory.
Sample command output:
```
mysql-bin.000688  531167  66ef5f51-94f3-11e5-98cf-40a8f034c4d0:1-405320,
72fb7cf6-94f3-11e5-98cf-a0d3c1f98c98:1-3082798,
79f98899-5d2d-11e4-a7c9-ecf4bbc08418:1-365786,
7e88493e-5d2d-11e4-a7c9-ecf4bbc06cb8:1,
a9285f36-9d56-11e4-8a2c-d89d672a9530:1-29549875,
ac2d9725-9d56-11e4-8a2c-d89d672af420:1-4838217
```
Note
mysql-bin.000688 is the start binary log file of the application, and 531167 indicates the start position.
Run the following command to download the binary log file of the RDS instance that has the same ID as the instance for which the backup file is generated from the ApsaraDB RDS console:
```
wget -c "URL to download the log backup file" -O <Log file name>
```
In this example, mysql-bin.000688 and mysql-bin.000689 are used.
```
wget -c "http://rdslog-hz-v3-3az.oss-cn-hangzhou.aliyuncs.com/custins71349641/hostins26******/mysql-bin.000688?Expires=16951*******" -O mysql-bin.000688 
```
Note
For more information about how to obtain the download URL of a log backup file, see Download the backup files of an ApsaraDB RDS for MySQL instance.
Run the following command to determine the point in time to which incremental data is restored:
```
mysqlbinlog -v --base64-output=decode-rows mysql-bin.0006XX > 689.log
```
Run the following command to view the 689.log file and confirm the point in time:
```
vi 689.log
```

Run the following command to restore incremental data to the specified point in time:

mysqlbinlog binlog_file1 binlog_file2 ... binlog_filen --start-position=xxxx  --stop-datetime="YY-mm-dd hh:mm:ss" | mysql -uuser -p<$Password> -P<$Port> -h<$Host_IP>

Example:

mysqlbinlog mysql-bin.000688 mysql-bin.000689 --start-position=531167 --stop-datetime="16-05-16 18:05:03" | mysql -uuser -p<$Password> -P<$Port> -h<$Host_IP>

The following table describes the parameters.

Parameter	Description
`binlog_file1 binlog_file2 ... binlog_filen`	The binary log file that is used for restoration. You can specify multiple files. You must replace `binlog_file1 binlog_file2 ... binlog_filen` with the actual names of binary log files.
`--start-position=xxxx`	The start position of the binary log file from which you want to restore data. The position is stored in the first binary log file that you specified. You must replace `xxx` with the actual position.
`--stop-position=xxxx`	The end position of the binary log file from which you want to restore data. The position is stored in the last binary log file that you specified. You must replace `xxx` with the actual position.
`--stop-datetime="YYYY-MM-DD HH:MM:SS"`	The start time of the restoration. The data is restored from the first binary log event that occurred at or after the start time. The time is based on the local time zone. The time format must be acceptable to MySQL, such as "2016-05-16 08:01:05" or "16-05-16 08:01:05". You must replace `"YYYY-MM-DD HH:MM:SS"` with the actual point in time.
`--stop-datetime="YYYY-MM-DD HH:MM:SS"`	The end time of the restoration. The data restoration stops at the first binary log event that occurred at or after the end time. The time is based on the local time zone. The time format must be acceptable to MySQL, such as "2016-05-16 08:01:05" or "16-05-16 08:01:05". You must replace `"YYYY-MM-DD HH:MM:SS"` with the actual point in time.
`-p<$Password>`	The password of the self-managed MySQL instance. You must replace `<$Password>` with the actual password. Do not add spaces between `-p` and `<$Password>`.
`-P<$Port>`	The port number of the self-managed MySQL instance. You must replace `<$Port>` with the actual port number.
`-h<$Host_IP>`	The IP address of the host on which the self-managed MySQL instance resides. You must replace the `<$Host_IP>` with the actual host IP address.

Note

To determine the range of binary log events that you want to restore, you can use the pair of --start-position and --stop-position to specify positions or the pair of --start-datetime and --stop-datetime to specify points in time.

FAQ

If the following error is reported during the incremental backup of your RDS instance that runs MySQL 5.7 or MySQL 8.0, the MySQL server denies the access requests because the current user does not have the required permissions. In this case, you must use the skip-grant-tables parameter to disable authentication for MySQL and then grant the user the required permissions.

ERROR 1227 (42000) at line 7: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Application scope

ApsaraDB RDS for MySQL