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.
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.
NoteMySQL 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
NoteThe 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
Notemysql-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
andmysql-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
NoteFor 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.NoteTo 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