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

Step 1: Set up the environment

  1. Make sure that 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. Make sure that the tables in the RDS instance are not encrypted by Transparent Data Encryption (TDE). If tables in the RDS instance are encrypted by 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. Obtain a computer or a server that runs a Linux 64-bit operating system. Make sure that the MySQL service is installed on the computer or the server and that the computer or the server runs the same MySQL version as the RDS instance.
    Note Make sure that no other services are running on the MySQL service.
  4. Install Percona XtraBackup on the computer or the server.
    • If the RDS instance runs MySQL 5.7, MySQL 5.6, or MySQL 5.5, install Percona XtraBackup 2.4 on the computer or the server.
    • If the RDS instance runs MySQL 8.0, install Percona XtraBackup 8.0 on the computer or the server.
  5. Install qPress on the computer or the server. qPress is an extraction tool.
    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 2: Download the physical backup file that you want to use

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. Open the Backup and Restoration page and click the Data Backup tab.
  3. Change the default time range. This step is required if you want to view the backup files that were generated eight days ago. The default time range spans the most recent eight days.
  4. Find the physical backup file that you want to use. In the Actions column, click Download Instance Backup.
    Download Instance Backup may not be displayed due to the following reasons:
  5. In the dialog box that appears, copy the URL that you can use to download the physical backup file.
    Note
    • A free quota for backup downloads over the Internet is provided. If the amount of traffic that you consume to download backup files over the Internet exceeds the free quota, you are charged for the excess traffic that you consume. For more information, see Billing.
    • If your Elastic Compute Service (ECS) instance resides in the same virtual private cloud (VPC) as the RDS instance, you can use the internal URL to download the logical backup file. This download method is faster and more stable.
  6. 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 3: 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.
    ## If the RDS instance runs MySQL 5.6 or MySQL 5.7, run the following command:
    innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data
    
    ## If the RDS instance runs MySQL 8.0, run the following commands:
    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 successful
    • 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 tables in the RDS instance are encrypted by TDE. For more information, see the "Step 1: Set up the environment" 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 4: 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 official 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.

    AppArmor is a built-in security program that is provided in an Ubuntu operating system. If you are using 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 message
  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

  • After the 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 required by the format of the data backup file. For more information, see the "Step 3: 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