Restore data from logical backup files of an ApsaraDB RDS for MySQL instance to a user-created database - RDS MySQL Database| Alibaba Cloud Documentation Center

This topic describes how to restore data from logical backup files. You can use the mysqldump utility that is included in MySQL to restore data from logical backup files.

Note

For more information about how to restore data from physical backup files to a user-created database, see Use a physical backup file to restore an ApsaraDB RDS for MySQL instance to a user-created MySQL database.
For more information about how to back up the data of an ApsaraDB RDS for MySQL instance, see Back up the data of an RDS instance.

Prerequisites

The 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

Demo environment

The user-created MySQL database is installed on a 64-bit Linux system. The version of the database is the same as that of the RDS instance. This topic describes how to restore data from logical backup files of an ApsaraDB RDS for MySQL 5.7 instance in the Linux 7 system.

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.
Select a time range and click OK.
In the data backup list, find the target backup files and click Download.

Note If Download is not displayed, check whether the version of the target RDS instance allows you to download logical backup files.
In the Download Instance Backup Set dialog box, click the copy icon next to Copy Public Endpoint.
Log on to the Linux system where the user-created database is installed and run the following command to download the logical backup files:
```
wget -c '<Public endpoint>' -O <Custom file name>.tar.gz
```
Note
- -c: enables resumable download.
- -O: saves the downloaded results as a specific file.
Run the following command to decompress the logical backup file package, which includes the compressed files of the default database and user-created database after decompression:
```
tar xvf <Custom file name>.tar
```
Decompress the compressed file of the database to be restored.
```
gzip-d <Compressed database file name>.gz
```

Log on to the user-created database to create an empty database.

mysql -uroot -p<Database password>
mysql> create database <Database name>;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye

Run the following command to import the .sql file into the empty database:

mysql -u root -p<Database password> <Empty database name> < ~/<Database name after decompression>.sql

Log on to the database and check for table data. If table data exists, the restoration is successful.

FAQ

Why does my RDS instance not have logical backup files?

The default backup mode is physical backup. If you need logical backup files, you must manually start logical backup. For more information, see Manually back up your RDS instance.