Restore the data of an ApsaraDB RDS for MySQL instance from a physical backup file to a self-managed MySQL database - RDS MySQL Database| Alibaba Cloud Documentation Center

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. You can use open source Percona XtraBackup to back up and restore the data of your RDS instance. The self-managed MySQL database is created on an Elastic Compute Service (ECS) instance.

Note

For more information about how to back up an ApsaraDB RDS for MySQL instance, see Back up the data of an RDS instance.
Percona XtraBackup does not support Windows operating systems. For more information about how to back up and restore an RDS instance that runs in a Windows operating system, see Migrate data to an RDS MySQL instance by using mysqldump.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:

MySQL 8.0 on RDS High-availability Edition (with local SSDs)
MySQL 5.7 on RDS High-availability Edition (with local SSDs)
MySQL 5.6
MySQL 5.5

Note RDS instances that run the Basic Edition support only snapshot backups. You cannot download snapshot backup files. For more information, see the " FAQ" section of this topic.

Precautions

In this topic, your RDS instance runs MySQL 5.7 in a Linux CentOS operating system, and the physical backup file that is used for the restoration is downloaded over the Internet.

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. After these packages are downloaded, their names are suffixed by the _qp.xb extension.
The self-managed MySQL database runs the same MySQL version as your RDS instance in a 64-bit Linux operating system.

Note Due to software limits, you can restore the data of your RDS instance from a data backup file only to a self-managed MySQL database that runs in a Linux operating system.

Before you begin

Before you start the restoration, complete the following preparations:

Install Percona XtraBackup 2.4.
- If you are using MySQL 5.7, MySQL 5.6 or earlier, you must install Percona XtraBackup 2.4. For more information, see the Percona XtraBackup 2.4 documentation.
- If you are using MySQL 8.0, you must install Percona XtraBackup 8.0. For more information, see the Percona XtraBackup 8.0 documentation.

Install the qpress tool.

The qpress tool is used to decompress the data backup files that are created by using the innobackupex tool. To install the qpress tool, run the following commands:

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

Procedure

Log on to the ApsaraDB RDS console.
In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
Find your RDS instance and click its ID.
In the left-side navigation pane, click Backup and Restoration.
Click the Data Backup tab.
Select a time range and click OK.
Find the data backup file that you want to download. Then, click Download in the Actions column.

Note If you cannot find the Download button, you must check that the MySQL version of your RDS instance supports the download of physical backup files. For more information, see Download the data and log backup files of an ApsaraDB RDS instance.
In the Download Instance Backup Set dialog box, click to the right of Copy Public URL.

Note If the ECS and RDS instances reside in the same virtual private cloud (VPC), you can use the internal URL. This download method is faster and more stable.
Log on to the ECS instance that hosts the self-managed MySQL database.
Run the following command to download the data backup file:
```
wget -c '<The public URL from which you can download the data backup file>' -O <The name that you want to use for the downloaded data backup file>. <The extension that is added to the name of the downloaded data backup file>
```
Example:
```
wget -c 'http://.../hinsxxxx_data_20201123215531_qp.xb?...' -O test1_qp.xb
```
Note
- The -c parameter specifies to enable resumable download.
- The -O parameter specifies to save the downloaded data backup file as a compressed package that uses the specified file name. The specified file name must be suffixed by the .tar.gz, .xb.gz, or _qp.xb extension that is provided in the public URL.
Decompress the downloaded data backup file.

Note You can replace the example custom path /home/mysql/data with the actual path. The following command is used to create the /home/mysql/data path: mkdir -p /home/mysql/data.

You can download a physical backup file by using one of the following three formats:
- tar compressed package whose name is suffixed by the .tar.gz extension
- xbstream compressed package whose name is suffixed by the .xb.gz extension
- xbstream compressed package whose name is suffixed by the _qp.xb extension
- qpress-generated xbstream compressed package whose name is suffixed by the _xb.qp extension
- To decompress a tar compressed package whose name is suffixed by the .tar.gz extension, run the following command:
```
tar -izxvf <The name of the tar compressed package> -C /home/mysql/data
```
  Example:
```
tar -izxvf test1.tar.gz -C /home/mysql/data
```
- To decompress an xbstream compressed package whose name is suffixed by the .xb.gz extension, run the following command:
```
gzip -d -c <The name of the xbstream compressed package> | xbstream -x -v -C /home/mysql/data
```
  Example:
```
gzip -d -c test1.xb.gz | xbstream -x -v -C /home/mysql/data
```
- To decompress an xbstream compressed package whose name is suffixed by the _qp.xb extension, run the following commands:
```
## Unpack the xbstream compressed package.
cat <The name of the xbstream compressed package> | xbstream -x -v -C /home/mysql/data

## Decompress the xbstream compressed package when your RDS instance runs MySQL 5.6 or MySQL 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
                    
```
  Examples:
```
## Unpack the xbstream compressed package.
cat test1_qp.xb | xbstream -x -v -C /home/mysql/data

## Decompress the xbstream compressed package when your RDS instance runs MySQL 5.6 or MySQL 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
                    
```
- To decompress a qpress-generated xbstream compressed package whose name is suffixed by the _xb.qp extension, run the following command:
```
qpress -do  <The name of the xbstream compressed package>  | xbstream -x -v -C /home/mysql/data
```
Note The -C parameter specifies the path to which the compressed package is decompressed. This parameter is optional. If you do not specify this parameter, the compressed package is decompressed to the current path.
Run the following command to query the file that is generated from the decompression:
```
ls -l /home/mysql/data
```
After the command is successfully run, the system displays information similar to the following figure. The blue parts indicate the databases that you have created on your RDS instance at the time when the data backup file was generated.
Run the following commands to restore the decompressed data backup file 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 information similar to the following figure, the data backup file is restored to the self-managed MySQL database.
- If the system returns the following error, you can run the rm -rf /var/lib/mysql command to clear the path. Then, you can run the chown -R mysql:mysql /var/lib/mysql command to modify the permissions on the path.
Note The Percona XtraBackup version that you use must match the MySQL version of your RDS instance:
- If you are using MySQL 5.7, MySQL 5.6 or earlier, you must install Percona XtraBackup 2.4. For more information, see the Percona XtraBackup 2.4 documentation.
- If you are using MySQL 8.0, you must install Percona XtraBackup 8.0. For more information, see the Percona XtraBackup 8.0.
Reconfigure the parameters in the backup-my.cnf file. This allows you 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:
```
lower_case_table_names=1
```
3. Comment out the following parameters that 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
  - If the self-managed MySQL database runs MyISAM and, at the same time, the system reports storage engine-related errors when you perform operations on 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;
4. Press Esc, enter :wq, and then press Enter to save the backup-my.cnf file.
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
```
Run the following command to start the MySQL process:
```
mysqld --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 the official MySQL documentation.

Common error:

The AppArmor security program is provided with an Ubuntu operating system. If you are using an Ubuntu operating system, the system may report the following error. 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.
Run the following command to log on to the self-managed MySQL database and verify that the MySQL process has started:
```
mysql -u<The username of the account that is used to manage your 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 is successful.

FAQ

I can use data backup files 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 database to a user-created 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 from which you can download the data backup file>' -O <The name that you want to use for the downloaded data backup file>.tar.gz. This provides an easy method for 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 that the downloaded data backup file is a physical backup file.
2. Check that the downloaded data backup file is saved based on a valid extension. Valid extensions are .tar.gz, .xb.gz, and _qp.xb.
3. Check that you have run a valid command that is required by the format of the downloaded data backup file. For more information, see Step 11 in the "Procedure" section of this topic.
How do I restore or migrate the data of my RDS instance that runs the Basic Edition?
RDS instances that run the Basic Edition support only snapshot backups. If your RDS instance runs the Basic Edition, use one of the following two methods to restore or migrate the data:
- Use mysqldump to migrate data from a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance
- Use DTS to export the data of your RDS instance to your computer.
Can I restore the data of my RDS instance from a downloaded data backup file 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.

Prerequisites

Precautions

Before you begin

Procedure

FAQ

References