How to back up or 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

Note

Take note of the following items:

Before you perform high-risk operations such as modifying the specifications or data of an Alibaba Cloud instance, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.
Before you modify the specifications or data of an Alibaba Cloud instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backups for the instance. For example, you can enable log backups for an ApsaraDB RDS instance.
If you have granted specific users the permissions on sensitive information, such as usernames and passwords, or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity.

If your RDS instance runs one of the following database engine versions and RDS editions, you can restore individual databases or tables of your RDS instance. For more information, see Restore the 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 a table of an ApsaraDB RDS for MySQL instance.

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 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:
Note
Note: If the mysqldump plug-in is not installed on the ECS instance, you can run the following command to install the mysqldump plug-in: rpm -ivh https://downloads.mysql.com/archives/get/p/23/file/MySQL-client-5.6.21-1.rhel5.x86_64.rpm
```
mysqldump -h [$Host] -u [$User] -P [$Port] -p --opt --default-character-set=utf8 --triggers --hex-blob [$Database_Name] > /tmp/db_name.sql
```
Note
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 plug-in 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 plug-in starts to restore the specified database. After the backup is complete, the following or similar information is displayed:

Use data backup files to restore the databases of your RDS instance

Data backup files 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
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 click the instance ID. In the left-side navigation pane, click Backup and Restoration, click Restore in the Action column of the required data backup file 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