How do I back up and restore the databases of an ApsaraDB RDS for MySQL instance? - ApsaraDB RDS

Overview

This topic describes how to back up and restore the databases of an ApsaraDB RDS for MySQL instance.

Description

If your RDS instance runs one of the following database engine versions and RDS editions, you can restore individual databases or tables for your RDS instance. For more information, see Restore individual databases and tables of an ApsaraDB RDS for MySQL instance. If your RDS instance does not run one of the following database engine versions and RDS editions and Database Backup (DBS) is used to back up the data of your RDS instance, you can restore individual databases or tables of your RDS instance by using DBS. For more information, see Restore data by database or table.

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 on RDS High-availability Edition

Use the mysqldump plug-in to back up and restore a database of your RDS instance

Install the MySQL client on an Elastic Compute Service (ECS) instance that runs a Linux operating system.
Note
Note You can use the yum install mysql command to directly install the MySQL client on the ECS instance.
1. Run the following command to download the MySQL client:
```
wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
```
2. Run the following command to install the MySQL client:
```
sudo rpm -ivh http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
```
Run the following command to back up a database of your RDS instance:
```
mysqldump -h [$Host] -u [$User] -P [$Port] -p --opt --default-character-set=utf8 --triggers --hex-blob [$Database_Name] > /tmp/db_name.sql
```
Note
- [$User] is the username of the account that is authorized to manage the specified database of your RDS instance.
- [$Port] is the port number that is used to connect to your RDS instance.
- [$Host] is the endpoint that is used to connect to your RDS instance.
- [$Database_Name] is the name of the database that your want to back up.
After you enter the password of the specified account, the mysqldump extension starts to back up the specified database. After the backup is complete, the following or similar information is displayed: For more information, see RDS MySQL mysqldump options.
Run the following command to check whether a file that is suffixed by sql is generated:
```
ll /tmp/db_name.sql
```
Optional. Run the following command to restore the data of the specified database by using the SQL file:
```
mysql -h [$Host] -u [$User] -P [$Port] -p [$Database_Name] < /tmp/db_name.sql
```
After you enter the password of the specified account, the mysqldump extension starts to restore the specified database. After the restoration is complete, the following or similar information is displayed:

Use backup sets to restore the databases of your RDS instance

Backup sets of your RDS instance contain all data of your RDS instance. If you want to restore the data of your RDS instance, you can perform the following steps:

Install the MySQL client on an ECS instance that runs a Linux operating system.
Note
You can use the yum install mysql command to directly install the MySQL client on the ECS instance.
1. Run the following command to download the MySQL client:
```
wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
```
2. Run the following command to install the MySQL client:
```
sudo rpm -ivh http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
```
Log on to the ApsaraDB RDS console, find your RDS instance, and then click the instance ID. In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Restore in the Action column of the required backup set to create an RDS instance.
After you confirm that the data of the new RDS instance is normal, you can migrate the required data from the new RDS instance to the original RDS instance. For more information, see Migrate data between RDS instances.

Applicable scope

ApsaraDB RDS for MySQL