Use a physical backup file to restore an ApsaraDB RDS for MySQL instance to a user-created MySQL database

Precautions

For example, your RDS instance runs MySQL 5.7 in a Linux 7 operating system.

• Percona XtraBackup is installed in the operating system. You can download the Percona XtraBackup software package from the official Percona XtraBackup website.
  • MySQL 5.6 and earlier versions require that you install Percona XtraBackup 2.3. For more information, see Percona XtraBackup 2.3.
  • MySQL 5.7 requires that you install Percona XtraBackup 2.4. For more information, see Percona XtraBackup 2.4.
  • MySQL 8.0 requires that you install Percona XtraBackup 8.0. For more information, see Percona XtraBackup 8.0.
• If your RDS instance runs MySQL 5.6 and is created after February 20, 2019, your data backup files are saved as xbstream compressed packages with the _qp.xb extension after they are downloaded.
• The user-created MySQL database runs in a 64-bit Linux operating system and uses the same MySQL version as your RDS instance.

  Note You can only restore a data backup file of your ApsaraDB RDS for MySQL instance to a user-created MySQL database that runs in a Linux operating system.

Prerequisites

Procedure

1. Log on to the ApsaraDB for RDS console.
2. In the top navigation bar, select the region where the target RDS instance resides.
3. Find the target RDS instance and click its ID.
4. In the left-side navigation pane, click Backup and Restoration.
5. Click the Data Backup tab.
6. Specify a time range and click OK.
7. In the data backup file list, find the target data backup file, and click Download in the Actions column.
   Note If the Download button does not appear, make sure that your MySQL version and RDS edition support the download of physical backup files. For more information, see Download data and log backup files.
   
8. In the Download Instance Backup Set dialog box that appears, click .
9. Log on to your ECS instance.
10. Run the following command to download the data backup file:

    wget -c '<The external download URL of the data backup file>' -O <The name to use for the downloaded data backup file>

    Note

    • The -c parameter enables resumable download.
    • The -O parameter saves the downloaded data backup file based on its specified name. The specified file name must use the .tar.gz, .xb.gz, or _qp.xb extension that is included in the download URL.
11. Decompress the compressed package that you downloaded.
    Note

    • You can replace the example custom path /home/mysql/data with the save path that you use for real scenarios.
    • To decompress data backup files that are created by using the innobackupex tool, you must install qpress, which is a file archiver that you can download from the official QuickLZ website. After you download the qpress software package, run the following commands to perform an installation:

      tar xvf qpress-11-linux-x64.tar
      chmod 775 qpress
      cp qpress /usr/bin

    Physical backup files are downloaded in one of the following three formats:

    • tar compressed package with the .tar.gz extension
    • xbstream compressed package with the .xb.gz extension
    • xbstream compressed package with the _qp.xb extension
    Note If your RDS instance runs MySQL 5.6 and is created after February 20, 2019, your data backup files are saved as xbstream compressed packages with the _qp.xb extension after they are downloaded.

    To decompress a tar compressed package with the .tar.gz extension, run the following command:

    tar -izxvf <The name of the tar compressed package>.tar.gz -C /home/mysql/data

    To decompress an xbstream compressed package with the .xb.gz extension, run the following command:

    gzip -d -c <The name of the xbstream compressed package>.xb.gz | xbstream -x -v -C /home/mysql/data

    To decompress an xbstream compressed package with the _qp.xb extension, run the following commands:

    ## Unpack the xbstream compressed package. cat <The name of the xbstream compressed package>_qp.xb | xbstream -x -v -C /home/mysql/data
    
    ## Decompress the xbstream compressed package when your RDS instance runs MySQL 5.6 or 5.7.
    innobackupex --decompress --remove-original /home/mysql/data
    ## Decompress the xbstream compressed package when your RDS instance runs MySQL 8.0.
    xtrabackup --decompress --remove-original --target-dir=/home/mysql/data
                        

    Note The -C parameter specifies the directory to which the xbstream compressed package is decompressed. This parameter is optional. If you do not specify it, the file is decompressed to the current directory.
12. Run the following command to query the files that are generated from the decompression:

    ls -l /home/mysql/data

    After the command is executed, the system displays information similar to the following. The blue part indicates the databases that are available on your RDS instance at the time when the data backup file was generated.

    
13. Run the following commands to restore the data backup file to the user-created MySQL database:

    ## MySQL 5.6 or 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 information similar to the following, the data backup file is restored to the user-created MySQL database.

    
    Note The Percona XtraBackup version you use is based on your MySQL version:

    • MySQL 5.6 and earlier versions require that you install Percona XtraBackup 2.3. For more information, see Percona XtraBackup 2.3.
    • MySQL 5.7 requires that you install Percona XtraBackup 2.4. For more information, see Percona XtraBackup 2.4.
14. Edit the parameters in the backup-my.cnf file to ensure version compatibility.
    1. Run the following command to modify the backup-my.cnf file in text mode:

       vi /home/mysql/data/backup-my.cnf

    2. Comment out the following parameters. The following parameters are not supported by the user-created MySQL database:

       #innodb_log_checksum_algorithm
       #innodb_fast_checksum
       #innodb_log_block_size
       #innodb_doublewrite_file
       #rds_encrypt_data
       #innodb_encrypt_algorithm
       #redo_log_version
       #master_key_id

       Note

       • The MyISAM storage engine is incompatible with the InnoDB storage engine used by ApsaraDB for RDS. If the user-created 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

       • If the user-created MySQL database runs MyISAM, and the system reports storage engine-related error messages when you manage a system table, you must run the following command to change the storage engine of the system table:

         alter table <The name of the system table> engine=myisam;

    3. Press Esc, enter :wq, and press Enter to save the backup-my.cnf file.
15. Run the following command to change the owner of the backup-my.cnf file to a user who has permissions to manage the user-created MySQL instance:

    chown -R mysql:mysql /home/mysql/data

16. Run the following command to start the MySQL process:

    mysqld_safe --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &

    Note We recommend that you reset the password of the root user. For more information, see How to Reset the Root Password.

17. Run the following command to log on to the user-created MySQL database to verify that the MySQL process has started:

    mysql -uroot -p<The password used to log on to the user-created MySQL database>

    If the system displays information similar to the following, the MySQL process has started. This means that the required parameters have been commented out and the owner of the backup-my.cnf file has been changed.

    

FAQ

• In addition to data backup files, which other methods can I use to restore my ApsaraDB RDS for MySQL instance to a user-created MySQL database?

  You can use Alibaba Cloud Data Transmission Service (DTS) to migrate your ApsaraDB RDS for MySQL instance to a user-created MySQL database. For more information, see Migrate data from an ApsaraDB RDS for MySQL database to a user-created MySQL database.

• What do I do if the system reports errors when I download a data backup file?

  If you use the wget -c '<The external download URL of the data backup file>' -O <The name to use for the downloaded data backup file>.tar.gz command to download the data backup file, check that the download URL is enclosed in a pair of single quotation marks ('). This makes it easier for the system to identify the download URL.

• What do I do if the system reports errors when I decompress the data backup file that I downloaded?
  1. Check that the data backup file is a physical backup file.
  2. Check that the data backup file is saved with a valid extension (.tar.gz, .xb.gz, or _qp.xb).
  3. Check that you are running the correct command based on the format of the data backup file. For more information, see Step 11 in the "Procedure" section.
• How do I restore or migrate my ApsaraDB RDS for MySQL instance in the Basic Edition?

  Instances in the Basic Edition only support snapshot backups. If your ApsaraDB RDS for MySQL instance is in the Basic Edition, use one of the following two methods to perform a restore or migrate operation:

  • Migrate data to an ApsaraDB RDS for MySQL instance by using mysqldump.
  • Use DTS to export data from your ApsaraDB RDS for MySQL instance to your computer.
• Can I restore a data backup file of my ApsaraDB RDS for MySQL instance to another ApsaraDB RDS for MySQL instance?

  No, this operation is not supported. We recommend that you use DTS to migrate your ApsaraDB RDS for MySQL instance to another. For more information, see Migrate data between RDS instances.

References

• Restore the data of an ApsaraDB RDS for MySQL instance
• Restore individual databases and tables of an ApsaraDB RDS for MySQL instance
• Restore an ApsaraDB RDS for MySQL instance across regions