Back up and restore databases on RDS for MySQL with mysqldump and backup sets - ApsaraDB RDS

ApsaraDB RDS for MySQL supports two approaches for database backup and restore: using mysqldump for logical backup and restore of individual databases, and using backup sets to restore a full instance. Choose based on your scenario.

Scenario	Approach
Back up or restore a single database without affecting other databases	Use mysqldump
Restore all data on the instance to a previous point in time	Restore from a backup set
Restore individual databases on instances not listed below	Use Database Backup (DBS)

For instances running one of the following configurations, you can restore individual databases or tables directly from the console. See Restore individual databases and tables.

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 mysqldump to back up and restore a database

mysqldump exports a database to an SQL file, which you can then restore to the same or a different RDS instance.

Prerequisites

Before you begin, make sure you have:

An Elastic Compute Service (ECS) instance running Linux, with network access to your RDS instance

The MySQL client installed on the ECS instance. Run yum install mysql to install it directly, or install it manually:

wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
sudo rpm -ivh http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm

An account with sufficient privileges on the database to back up. If you encounter permission errors, use the privileged account of your RDS for MySQL instance. For required permissions, see RDS MySQL mysqldump options.

Back up a database

Run the following command on your ECS instance to export the database to an SQL file:

mysqldump -h <host> -u <username> -P <port> -p \
  --opt \
  --default-character-set=utf8 \
  --triggers \
  --hex-blob \
  <database_name> > /tmp/db_name.sql

Replace the placeholders with your actual values:

Placeholder	Description
`<host>`	Endpoint of your RDS instance
`<username>`	Username of the account authorized to manage the database
`<port>`	Port number used to connect to your RDS instance
`<database_name>`	Name of the database to back up

Enter the account password when prompted. After the backup completes, the output looks similar to:

To confirm the SQL file was created:

ls -l /tmp/db_name.sql

Restore a database

Run the following command to restore the database from the SQL file:

mysql -h <host> -u <username> -P <port> -p <database_name> < /tmp/db_name.sql

Use the same placeholder values as the backup command. Enter the account password when prompted. After the restore completes, the output looks similar to:

Restore from a backup set

A backup set contains all data on your RDS instance. Restoring from a backup set creates a new RDS instance, and you then migrate the required data back to the original instance.

Prerequisites

Before you begin, make sure you have:

An ECS instance running Linux, with the MySQL client installed. See Prerequisites in the previous section.

Steps

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.
Find the backup set to restore from and click Restore in the Action column. This creates a new RDS instance with the data from that backup set.
Verify that the data on the new RDS instance is correct.
Migrate the required data from the new RDS instance to the original instance. See Migrate data between RDS instances.

Applicable scope

ApsaraDB RDS for MySQL