ApsaraDB RDS:Restore the data of an ApsaraDB RDS for MySQL instance to a self-managed MySQL database from a physical backup file

How it works

1. Download a physical backup file from the ApsaraDB RDS console.

2. Decompress the file using qpress and xbstream.

3. Run Percona XtraBackup to restore the data to a new data directory on the self-managed MySQL host.

4. Start the self-managed MySQL process using the restored data directory.

5. Connect to the database and verify the data.

Limitations

• Local disks only. Physical backup files are available only for RDS instances that use Premium Local SSDs. If your instance uses cloud disks, see Restore the data of an ApsaraDB RDS for MySQL instance to a self-managed MySQL instance by using snapshot backup files.

• Full restore only. This procedure restores the entire backup. To restore specific databases or tables, see Restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance.

• Linux only. The restore target must be a MySQL database running on Linux.

• No TDE. Tables encrypted with Transparent Data Encryption (TDE) cannot be restored using this procedure. Decrypt all tables before downloading the backup file. To check, go to Data Security > TDE on the instance details page.

• No MGR clusters. This procedure does not support RDS clusters using MySQL group replication (MGR) mode.

Prerequisites

Before you begin, make sure that:

• Your RDS instance runs MySQL 8.0, 5.7, 5.6, or 5.5 on RDS High-availability Edition with Premium Local SSDs. Check these details on the Basic Information page of the instance.

• No tables in the instance are encrypted with TDE.

• The RAM user you are using has permission to download backup files. For details, see Authorize a RAM user with read-only permissions to download backup files.

• The self-managed MySQL database runs the same major version as your RDS instance (for example, both run MySQL 8.0).

• The self-managed MySQL database runs on Linux. The examples in this topic use CentOS 7.9 64-bit.

Potential impacts

• If other services are running in the self-managed MySQL database, they become unavailable while the restore is in progress.

• The restore writes data to a new data directory and does not affect the existing data in the self-managed database.

Billing

• Manual backups consume backup storage. Excess storage beyond the free quota is billed. See Billable items and pricing for backup storage.

• Downloading over the Internet generates traffic charges if the traffic exceeds the free quota. To avoid these charges, download the backup file using an internal URL when the self-managed MySQL host and the RDS instance are in the same region and VPC. See Download charges.

Prepare the environment

sudo mkdir /var/mysql_bkdata
sudo chown -R $USER:$USER /var/mysql_bkdata

sudo mkdir /var/mysql_newdata
sudo chown -R $USER:$USER /var/mysql_newdata

sudo find / -name my.cnf

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm
sudo yum localinstall -y percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

# Download the qpress executable
wget "https://help-static-aliyun-doc.aliyuncs.com/file-manage-files/zh-CN/20230406/flxd/qpress-11-linux-x64.tar"

# Extract the archive
tar -xvf qpress-11-linux-x64.tar

# Grant execute permissions
sudo chmod 775 qpress

# Make qpress available system-wide
sudo cp qpress /usr/bin

Step 1: Download the backup file

1. Log in to the ApsaraDB RDS console. In the top navigation bar, select the region where your RDS instance resides, then click the instance ID.

2. In the left-side navigation pane, click Backup and Restoration.

3. Choose Base Backups > Data Backup. Find the physical backup file to download and click Download Instance Backup in the Actions column.

   • If no physical backup files are available, create one first. See Manually back up an ApsaraDB RDS for MySQL instance.

   • If the Advanced Download page appears, your instance uses cloud disks and this procedure does not apply. See Restore using snapshot backup files instead.

4. In the Download Instance Backup Set dialog box, click Copy Internal URL or Copy Public URL.

   Important

   - Internal URL: The self-managed MySQL host and the RDS instance must be in the same VPC. Cross-region VPCs and classic network-to-VPC configurations are not supported. - Public URL: Internet traffic charges apply if you exceed the free quota. - A download URL is valid for 1 hour after it is generated. Refresh the page to get a new URL if it expires.

5. On the Linux host, run the following command to download the backup file. Replace the URL with the one you copied.

   - Enclose the URL in single quotation marks so the shell treats it as a literal string. - Replace test_xb.qp with your preferred filename, but keep the same extension as in the download URL. - Physical backup file extensions: _xb.qp or _qp.xb for MySQL 8.0/5.7/5.6; tar.gz for MySQL 5.5. - Do not modify or delete a physical backup file. If you need to modify the data, restore it to the self-managed database first.

   wget -c 'https://****.bak.rds.aliyuncs.com/****_xb.qp?****' -O test_xb.qp

Step 2: Decompress the backup file

Run the decompression command that matches your backup file extension. Replace the filename and directory with your actual values.

`_xb.qp` files

# MySQL 8.0
qpress -do test_xb.qp | /u01/xtrabackup80/bin/xbstream -x -v -C /var/mysql_bkdata/

# MySQL 5.5 / 5.6 / 5.7
qpress -do test_xb.qp | xbstream -x -v -C /var/mysql_bkdata/

`_qp.xb` files

# Step 1: Parse the file
cat test_qp.xb | xbstream -x -v -C /var/mysql_bkdata/

# Step 2: Decompress
## MySQL 5.5 / 5.6 / 5.7
innobackupex --decompress --remove-original /var/mysql_bkdata/

## MySQL 8.0
/u01/xtrabackup80/bin/xtrabackup --decompress --remove-original --target-dir=/var/mysql_bkdata/

`.tar.gz` files

tar -izxvf test.tar.gz -C /var/mysql_bkdata/

`.xb.gz` files

# MySQL 8.0
gzip -d -c test.xb.gz | /u01/xtrabackup80/bin/xbstream -x -v -C /var/mysql_bkdata/

# MySQL 5.5 / 5.6 / 5.7
gzip -d -c test.xb.gz | xbstream -x -v -C /var/mysql_bkdata/

Step 3: Restore data

Before restoring, stop the self-managed MySQL database. Check for running MySQL processes and terminate them:

ps -ef | grep '[m]ysql'
sudo kill -9 <PID>

Follow the steps for your MySQL version.

/u01/xtrabackup80/bin/xtrabackup --defaults-file=/var/mysql_bkdata/backup-my.cnf \
  --prepare --target-dir=/var/mysql_bkdata/

sudo vim /etc/my.cnf

datadir = /var/mysql_newdata

chown -R mysql:mysql /var/mysql_newdata

sudo xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/var/mysql_bkdata/

innobackupex --defaults-file=/var/mysql_bkdata/backup-my.cnf --apply-log /var/mysql_bkdata/

sudo vim /etc/my.cnf

datadir = /var/mysql_newdata
innodb_undo_tablespaces=2
innodb_undo_directory=/var/mysql_newdata

cat /var/mysql_bkdata/backup-my.cnf | grep innodb_undo_tablespaces

sudo innobackupex --defaults-file=/etc/my.cnf --copy-back /var/mysql_bkdata/

innobackupex --defaults-file=/var/mysql_bkdata/backup-my.cnf --apply-log /var/mysql_bkdata/

sudo vim /usr/my.cnf

datadir = /var/mysql_newdata

sudo innobackupex --defaults-file=/usr/my.cnf --copy-back /var/mysql_bkdata/

innobackupex --defaults-file=/var/mysql_bkdata/backup-my.cnf --apply-log /var/mysql_bkdata/

sudo vim /etc/my.cnf

datadir = /var/mysql_newdata
innodb_log_file_size=1048576000

cat /var/mysql_bkdata/backup-my.cnf | grep innodb_log_file_size

sudo innobackupex --defaults-file=/etc/my.cnf --copy-back /var/mysql_bkdata/

sudo rm -rf /var/mysql_newdata/*

Step 4: Start the database

apt install -y apparmor-utils
aa-complain /usr/sbin/mysqld

USE mysql;
ALTER TABLE proc ENGINE=myisam;
ALTER TABLE event ENGINE=myisam;
ALTER TABLE func ENGINE=myisam;

SET SQL_MODE='ALLOW_INVALID_DATES';

getenforce

Step 5: Verify the restoration

1. Connect to the self-managed MySQL database:

   If you have forgotten the credentials, add --skip-grant-tables to the startup command (step 4) to bypass the permission check. Reset the credentials after logging in.

   mysql -u<username> -p<password>

2. Verify that the expected databases are present:

   SHOW DATABASES;

   If you can see only system databases or a subset of databases, restart the self-managed database and reconnect using the root account or the privileged account from the original RDS instance.

3. If data timestamps are in the wrong time zone, set time_zone on the self-managed database to match the RDS instance. If the RDS time_zone parameter is set to system, look up the region of the RDS instance and use the corresponding time zone.

Troubleshooting connection errors:

Error: `Access denied for user 'XXX'`

Check whether the username and password of the account used to connect to the database are correct. The account must have been created on the RDS instance.

Password after restore:

• MySQL 5.7 or 8.0: The root password is the same as on the RDS instance.

• MySQL 5.5 or 5.6: Reset the root password. See MySQL documentation on resetting permissions.

What's next

• To restore full data or individual databases and tables to another RDS instance, see Restore full data or Restore individual databases and tables.

• For a full overview of restoration methods, see Overview of data restoration methods.