This topic describes how to use open source Percona XtraBackup to restore a physical backup file of an ApsaraDB RDS for MySQL instance to a user-created MySQL database.
- For information about how to back up an ApsaraDB RDS for MySQL instance, see Back up the data of an RDS instance.
- Percona XtraBackup is not supported in Windows operating systems. For information about how to back up and restore an ApsaraDB RDS for MySQL instance that runs in a Windows operating system, see Migrate data to an RDS MySQL instance by using mysqldump.
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
- MySQL 8.0 in the High-availability Edition (with local SSDs)
- MySQL 5.7 in the High-availability Edition (with local SSDs)
- MySQL 5.6
- MySQL 5.5
Procedure
- Log on to the ApsaraDB for RDS console.
- In the top navigation bar, select the region where the target RDS instance resides.
- Find the target RDS instance and click its ID.
- In the left-side navigation pane, click Backup and Restoration.
- Click the Data Backup tab.
- Specify a time range and click OK.
- 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.
- In the Download Instance Backup Set dialog box that appears, click
.
- Log on to your ECS instance.
- 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.
- 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. - 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.
- 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.
- Edit the parameters in the backup-my.cnf file to ensure version compatibility.
- Run the following command to modify the backup-my.cnf file in text mode:
vi /home/mysql/data/backup-my.cnf
- 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;
- 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:
- Press Esc, enter
:wq
, and press Enter to save the backup-my.cnf file.
- Run the following command to modify the backup-my.cnf file in text mode:
- 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
- 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. -
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?
- Check that the data backup file is a physical backup file.
- Check that the data backup file is saved with a valid extension (.tar.gz, .xb.gz, or _qp.xb).
- 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.