Restoring Backup Data from an ApsaraDB RDS for MySQL 5.7 Instance to a User-created Database Instance

By Tian Jie

Recently, some users reported issues in restoring backup data from an ApsaraDB RDS for MySQL 5.7 instance to a user-created database instance. This article aims to address all such issues by demonstrating a restoration example.

1) Local Environment

1.1 Install MySQL DB

To facilitate migration and installation, this article employs the independent TAR version of the Linux OS, as shown in the following figure. Click here to download mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz.

Consider the following installation procedure.

1.2 Install XtraBackup

Restoring data from an ApsaraDB RDS for MySQL 5.7 instance to a user-created database instance requires XtraBackup 2.4. Click here to download Percona-XtraBackup-2.4.13-r3e7ca7c-el6-x86_64-bundle.tar, as shown in the following figure.

Follow the installation procedure shown below.

2) Restoration Procedure

2.1 Restore Backup Sets

The ApsaraDB for RDS console provides backup sets for ApsaraDB for RDS (RDS) instances, which are available for download over a public network or standard internal network.

This article uses an ApsaraDB RDS for MySQL 5.7 High-availability instance and the hins5937443_data_20190122042427.tar.gz file.

The following snippet shows the specific procedure.

2.2 Start an Instance

After restoring the backup set file, pull up the MySQL process and log on to the MySQL instance.

Adjust the parameter configuration file, create the root@localhost super permission account, and set a password for this account.

Now, my.cnf as shown below.

ApsaraDB RDS for MySQL does not itself provide super permissions for application-side database accounts. Therefore, even if a high-permission account named root (root@'%') is created in the ApsaraDB for RDS console, it's still require creating a local user with super permission to restore data to a user-created database instance.

The following snippet shows the procedure for creating a local user with super permissions.

3) Postscript

This article is a detailed supplement to Use a physical backup file to restore data of an ApsaraDB RDS MySQL instance to a user-created database.

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.