ApsaraDB RDS:Restore the backup file of an ApsaraDB RDS for MySQL instance to a self-managed MySQL instance database by point in time

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.

• The self-managed MySQL instance must meet the following requirements:

  • The major engine version of the self-managed MySQL instance must be the same as that of the RDS for MySQL instance.

    Note

    MySQL 5.5 and MySQL 5.6 are different major 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.

  • The minor engine version of the self-managed MySQL instance must be equal to or later than that of the RDS for MySQL instance.

    • If the RDS for MySQL instance runs MySQL 5.6, the minor engine version of the self-managed MySQL instance must be later than 5.6.16.

    • If the RDS for MySQL instance runs MySQL 5.5, the minor engine version of the self-managed MySQL instance must be later than 5.5.18.

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.

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

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

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

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

5. Run the following command to view the 689.log file and confirm the point in time:

   vi 689.log

6. 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="YYYY-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="2016-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.
   --start-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". 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". 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

What do I do if the ERROR 1227 (42000) at line 7: Access denied; you need (at least one of) the SUPER privilege(s) for this operation error message is returned when I perform incremental backup for an RDS instance that runs MySQL 5.7 or MySQL 8.0?

This error indicates that the MySQL server denies the access requests because the current user does not have the required permissions. You can contact your database administrator or use a privileged account to grant permissions to the current user.

Application scope

RDS for MySQL instances