All Products
Search
Document Center

Use Apsaradb RDS for MySQL backup to restore data from a user-created database to a time point

Last Updated: Jul 14, 2020

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

 

Overview

You may want to incrementally restore data from the backup files of an ApsaraDB RDS for MySQL instance to your databases that are created on ECS instances or on-premises servers. In this process, data is restored to a point in time. The following sections provide an example.

 

Description

Alibaba Cloud reminds you that:

  • When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.

When you restore data to a point in time, consider the following points:

  • The procedure for restoring data to a user-created database on an ECS instance or on-premises server and whether the restoration is successful depend on the configurations of the user-created MySQL database.
  • This article describes only the procedure for incrementally restoring data to a user-created database on an on-premises server. You must modify commands based on your specific configurations.
  •  
  • This article uses the Community Edition of MySQL 5.6.20 in the tar.gz format as an example.
  • User-created MySQL must run the same or a later version than ApsaraDB RDS for MySQL. If ApsaraDB RDS for MySQL runs V5.6, user-created MySQL must be later than MySQL 5.6.16. If ApsaraDB RDS for MySQL runs V5.5, user-created MySQL must be later than MySQL 5.5.18.
  • You cannot restore data from an ApsaraDB RDS for MySQL V5.5 instance to your MySQL 5.6 database.

 

Download backup files

For more information, see Download data backup files and binary log files.

 

Restore data to a user-created database on an on-premises server

For more information, see How do I restore a downloaded RDS backup to a new RDS instance?

 

Incrementally restore data

  1. After you restore data to the user-created database on an on-premises server, log on to the database. Execute the following statements in sequence to create a root user for remote logon:
    grant all privileges on *.* to root@'%' identified by 'your_password';
    flush privileges;
    Note: [$Password] is the password of the user.
  2. Run the following command to determine where to apply binary logs for incremental restoration:
    cat xtrabackup_binlog_info
    The output is as follows:
    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 file, and 531167 is the log entry from which incremental restoration begins.
  3. Download binary log files from the console. The instance ID of these binary log files must be consistent with that of data backup files. For this example, download the mysql-bin.000688 and mysql-bin.000689 files.
  4. Run the following commands to decompress the downloaded files:
    tar xvpf mysql-bin.000688.tar
    tar xvpf mysql-bin.000689.tar
  5. Run the following command to determine the stop point in time for incremental restoration:
    mysqlbinlog -v --base64-output=decode-rows mysql-bin.000689 > 689.log
  6. Run the following command to view the 689.log file and confirm the point in time:
    vi 689.log
  7. Run the following command to perform incremental restoration:
    mysqlbinlog mysql-bin.000688 mysql-bin.000689 --start-position=531167 --stop-datetime="16-05-16 18:05:03" | mysql -uroot -p[$Password] -P[$Port] -h[$Host_IP]
    Note:
    • Do not add spaces between -p and [$Password]. Replace [$Password] with the actual password.
    • Set [$Host_IP] to a host IP address other than 127.0.0.1.
    • Set [$Port] to the port of your database.
    • The mysqlbinlog command is in the following format:
      mysqlbinlog binlog_file1 binlog_file2 ... binlog_filen --start-position=xxxx  --stop-datetime="YY-mm-dd hh:mm:ss" | mysql -uroot -pyour_password -Pmysql_port -hyour_host_ip
    • mysqlbinlog supports the following parameters:
      • --database db_name specifies the database for which you want to perform incremental restoration.
      • --start-position specifies the start position of the binary log for restoration. This position must exist in the first specified binary log file.
      • --stop-position specifies the stop position of the binary log for restoration. This position must exist in the last specified binary log file.
      • --start-datetime specifies the start point in time for restoration, which starts from the first event that was recorded in binary logs at or after this point in time.
        Use the local time zone and a datetime format that complies with MySQL syntax. Example: 2016-05-16 08:01:05 or 16-05-16 08:01:05
      • --stop-datetime specifies the stop point in time for restoration, which stops at the first event that was recorded in binary logs at or after this point in time.
        Use the local time zone and a datetime format that complies with MySQL syntax. Example: 2016-05-16 08:01:05 or 16-05-16 08:01:05

 

Application scope

  • ApsaraDB RDS for MySQL