All Products
Search
Document Center

How do I back up and restore the database of an apsaradb RDS for MySQL instance?

Last Updated: Sep 30, 2020

Introduction

This article describes how to back up and restore the database of an RDS MySQL instance.

Background

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

If you want to restore an apsaradb RDS for MySQL instance to any one of the following versions, see restore individual databases or tables for an RDS for MySQL instance. If you use DBS to restore data to an instance that has other editions of database, you can use DBS to restore a single table. For more information, see single-table restoration.

  • 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 databases

  1. Install the MySQL client on the ECS instance of the Linux.
    Description: You can also directly use yum install mysql command to install the MySQL client.
    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.
      rpm -ivh http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
  2. Run the following command to back up the database of the apsaradb for 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] indicates the database logon User.
    • [$Port] is the database Port number.
    • [$Host] is the database address.
    • [$Database_Name] is the database name.
    mysqldump completes the backup operation automatically after you enter the password. A similar output is displayed. For more information about the backup, see mysqldump options for apsaradb RDS for MySQL.
  3. Run the following command to confirm that the SQL file is successfully generated.
    ll /tmp/db_name.sql
  4. To restore data from the source RDS instance, run the following command based on the backup file.
    mysql -h [$Host] -u [$User] -P [$Port] -p [$Database_Name] < /tmp/db_name.sql
    A similar output is displayed, indicating that the recovery operation is completed after you enter the password.

Restore data by using backup sets

All data in the RDS backup set is backed up. For more information about how to restore data, see the following documents.

  1. Install the MySQL client on the ECS instance of the Linux.
    Description: You can also directly use yum install mysql command to install the MySQL client.
    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.
      rpm -ivh http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
  2. Login RDS console click the target instance ID, Select backup and restoration click the icon in the actions column of the target backup set restore to create a new RDS instance.
  3. After you make sure that the data in the new RDS instance is valid, you can migrate the data to the original RDS instance. For more information, see migrate data between RDS instances.

Application scope

  • ApsaraDB RDS for MySQL