This topic describes how to restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL database.

Note

Prerequisites

  1. The RDS instance runs MySQL 8.0, MySQL 5.7, MySQL 5.6, or MySQL 5.5 on RDS High-availability Edition with local SSDs.
    Note You can download the physical backup files of the RDS instance only when the RDS instance meets this requirement. For more information about how to restore the data of an RDS instance that runs RDS Basic Edition, see the "FAQ" section of this topic.
  2. Tables in the RDS instance are not encrypted by using Transparent Data Encryption (TDE).
    Note If tables in the RDS instance are encrypted by using TDE, errors occur during the restoration process. We recommend that you decrypt the encrypted tables before you start a restoration task. For more information, see Decrypt a table.
  3. The RAM user that you want to use to log on to your RDS instance is granted the permissions to download backup files. For more information about how to grant permissions to a RAM user, see Grant backup file download permissions to a RAM user with read-only permissions.
  4. The self-managed MySQL database and the RDS instance run the same MySQL version, for example, MySQL 5.7.
    Note Make sure that no other services are running on top of the MySQL service.
  5. Percona XtraBackup is installed on the computer or server where the self-managed MySQL database resides.
  6. The qpress tool is installed on the computer or server where the self-managed MySQL database resides. The qpress tool is used for decompression. You can run the following commands to install qpress.
    wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"
    tar xvf qpress-11-linux-x64.tar
    chmod 775 qpress
    cp qpress /usr/bin

Step 1: Download the physical backup file that you want to use

  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, click Backup and Restoration.
  3. On the Data Backup tab of the page that appears, find the backup file that you want to download and click Download Instance Backup in the Actions column.
    Note By default, the ApsaraDB RDS console displays the backup files that were generated over the most recent eight days. If you want to view the backup files that were generated eight days ago, you must change the time range to query.
  4. On the Standard Download tab, click an URL in the Download URL section or click Internal URL or Download over Internet to download the physical backup file.
    Notice
    • If you use the internal URL to download the physical backup file, make sure that the server to which you log on and the RDS instance reside in the same virtual private cloud (VPC). If the server and the RDS instance reside in VPCs in different regions or if the server resides in the classic network whereas the RDS instance resides in a VPC, you cannot download the physical backup file by using the internal URL on the server.
    • I have learnt the billing rules for backup file download. is selected by default. If you use the external URL to download the physical backup file, you are charged for the excess Internet traffic that you consume. For more information, see Billing.
    • The download URLs are valid for only one hour. If the download URLs expire, you can refresh the page to obtain the latest download URLs.
    • We recommend that you do not modify or delete the content of the physical backup file. If you modify or delete the content of the physical backup file, the file may be damaged and cannot be restored. If you must modify the content of the physical backup file, we recommend that you restore the data of the RDS instance from the physical backup file to a self-managed database before you modify the content.
  5. On the computer or the server, run the following command to download the physical backup file:
    wget -c 'http://...' -O test1_qp.xb
    Note
    • You must replace http://... with the URL that you can use to download the physical backup file.
    • test1_qp.xb is the name that is used for the physical backup file after the physical backup file is downloaded. You can change the file name based on your business requirements. However, you must make sure that the extension of the file name remains unchanged.
    Download screenshot

Step 2: Decompress the physical backup file that you downloaded and restore data from the file that is generated from the decompression

  1. Create a directory that is used to store the file that is generated from the decompression on the computer or the server. For example, you can create a directory named /home/mysql/data.
    mkdir /home/mysql/data
  2. Decompress the physical backup file. The command that is used to decompress the physical backup file varies based on the extension of the file name.
    Extension Command used for decompression
    .tar.gz
    tar -izxvf test1.tar.gz -C /home/mysql/data
    .xb.gz
    gzip -d -c test1.xb.gz | xbstream -x -v -C /home/mysql/data
    _qp.xb
    ## Unpack the physical backup file.
    cat test1_qp.xb | xbstream -x -v -C /home/mysql/data
    
    ## Decompress the physical backup file.
    ### If the RDS instance runs MySQL 5.6 or MySQL 5.7, run the following command:
    innobackupex --decompress --remove-original /home/mysql/data
    ### If the RDS instance runs MySQL 8.0, run the following command:
    xtrabackup --decompress --remove-original --target-dir=/home/mysql/data
    _xb.qp
    qpress -do  test1_xb.qp  | xbstream -x -v -C /home/mysql/data
    Note You can replace test1 and /home/mysql/data in the preceding commands with the actual names and save paths of your physical backup file.
  3. Run the following command to query the file that is generated from the decompression:
    ls -l /home/mysql/data

    The system returns the following information, in which the information in blue indicates the databases whose data is contained in the physical backup file.

    View the file that is generated from the decompression
  4. Restore the data of the file that is generated from the decompression to the self-managed MySQL database.
    ## MySQL 5.6/5.7
    innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
    
    ## MySQL 8.0
    xtrabackup --prepare --target-dir=/home/mysql/data
    xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data
    • If the system displays the following or similar information, the data is restored to the self-managed MySQL database. Data restoration is successful
    • If the system returns xtrabackup: Unknown error 3613, update Percona XtraBackup to the latest version and try again.
    • If the system returns the following error, run the rm -rf /var/lib/mysql command to delete all files from the directory. Then, run the chown -R mysql:mysql /var/lib/mysql command to modify the permissions on the directory. Message indicating that the directory is not empty
    • If the system returns the following error, check whether the tables in the RDS instance are encrypted by using TDE. For more information, see the "Prerequisites" section of this topic. Data restoration failed
    Note The Percona XtraBackup version that you use must match the MySQL version of the RDS instance:

Step 3: Run MySQL

  1. Modify the backup-my.cnf file to ensure version compatibility.
    1. Run the following command to open the backup-my.cnf file in text mode:
      vi /home/mysql/data/backup-my.cnf
    2. Add the following parameter setting to the file:
      lower_case_table_names=1
    3. Comment out the following parameters, which are not supported by the self-managed MySQL database:
      #innodb_log_checksum_algorithm
      #innodb_fast_checksum
      #innodb_log_block_size
      #innodb_doublewrite_file
      #innodb_encrypt_algorithm
      #rds_encrypt_data
      #redo_log_version
      #master_key_id
      #server_uuid
      Note The MyISAM storage engine is incompatible with the InnoDB storage engine that is used by ApsaraDB RDS. If the self-managed MySQL database runs MyISAM, you must comment out the following parameters and add the skip-grant-tables parameter:
      #innodb_log_checksum_algorithm=strict_crc32
      #redo_log_version=1
      skip-grant-tables
    4. Press Esc, enter :wq, and then press Enter to save the backup-my.cnf file.
  2. Run the following command to change the owner of the backup-my.cnf file to a user who has the permissions to manage the self-managed MySQL database:
    chown -R mysql:mysql /home/mysql/data
  3. Run the following command to run MySQL:
    mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &
    Note
    • The following issues related to the password of the root user may occur:
      • If the RDS instance runs MySQL 5.5 or MySQL 5.6, you must reset the password of the root user of the RDS instance. For more information, see the open source MySQL documentation.
      • If the RDS instance runs MySQL 5.7 or MySQL 8.0, the password of the root user of the RDS instance is the same as the password of the root user of the self-managed MySQL database.
    • If an error is reported when you start MySQL, you can try to resolve the error by changing the storage engine. For more information, see the "FAQ" section of this topic.

    Common errors

    AppArmor is a built-in security program that is provided in an Ubuntu operating system. If you use an Ubuntu operating system, the system may report the error that is shown in the following figure. In this case, you must run the apt install -y apparmor-utils and aa-complain /usr/sbin/mysqld commands to modify the settings of the AppArmor security program.

    Error details
  4. Run the following command to log on to the self-managed MySQL database and verify that MySQL is running:
    mysql -u<The username of the account that is used to connect to the RDS instance> -p<The password of the preceding account>

    You can run the show databases; command to view the self-managed MySQL database and check whether the restoration task is successful.

    Successfully started

FAQ

  • How do I restore the data of my RDS instance over a specified time range to a self-managed MySQL database?

    You can download the log backup file that is generated over the specified time range in the ApsaraDB RDS console. Then, you can use the log backup file to restore the data of your RDS instance to a self-managed MySQL database. For more information, see Download the backup files of an ApsaraDB RDS for MySQL instance.

  • After I restore the data of my RDS instance from a physical backup file to a self-managed MySQL database, the time that is indicated by the time field of the data and the local time of the server on which the self-managed MySQL database is deployed belong to different time zones. Why? How do I make sure that the time that is indicated by the time field of the data and the local time belong to the same time zone?

    If the time zone of the self-managed MySQL database is different from that of your RDS instance, you must modify the value of the time_zone parameter of the self-managed MySQL database to keep it consistent with the value of the time_zone parameter of your RDS instance. If the time_zone parameter of your RDS instance is set to system, you must query the region where the RDS instance resides and set the time_zone parameter of the self-managed MySQL database to the time zone of the region.

  • After a restoration task is completed, what do I do if the "error 1105 Unknown error" message is displayed when I use the self-managed MySQL database?

    Execute the following SQL statements to convert the storage engine:

    use mysql;
    alter table proc engine=myisam;
    alter table event engine=myisam;
    alter table func engine=myisam;
  • I can use the data backup files that I downloaded to restore the data of my RDS instance to a self-managed MySQL database. Which other methods are available?

    You can use Data Transmission Service (DTS) to migrate the data of your RDS instance to a self-managed MySQL database.For more information, see Migrate data from an ApsaraDB RDS for MySQL instance to a self-managed MySQL database.

  • When I download a data backup file, why does the system report errors?

    If you run the following command to download the data backup file, check that the public URL is enclosed in a pair of single quotation marks ('): wget -c '<The public URL that you can use to download the data backup file>' -O <The name that you want to use for the data backup file after the file is downloaded>.tar.gz. The single quotation marks (') are used by the system to identify the public URL.

  • When I decompress the data backup file that I downloaded, what do I do if the system reports errors?
    1. Check whether the data backup file is a physical backup file.
    2. Check whether the data backup file is saved based on a valid file extension. Valid file extensions are .tar.gz, .xb.gz, and _qp.xb.
    3. Check whether you ran a valid command that is supported for the format of the data backup file. For more information, see Substep 2 in the "Step 2: Decompress the physical backup file that you downloaded and restore data from the file that is generated from the decompression" section of this topic.
  • How do I restore or migrate the data of my RDS instance if my RDS instance runs RDS Basic Edition?

    RDS instances that run RDS Basic Edition support only snapshot backups. If your RDS instance runs RDS Basic Edition, use one of the following two methods to restore or migrate the data:

  • Can I restore the data of my RDS instance from a data backup file that I downloaded to another RDS instance?

    This operation is not supported by ApsaraDB RDS. We recommend that you use DTS to migrate the data of your RDS instance to another RDS instance. For more information, see Migrate data between RDS instances.

References