All Products
Search
Document Center

ApsaraDB RDS:Restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance

Last Updated:Oct 23, 2023

This topic describes how to use mysqldump to restore the data of an ApsaraDB RDS for MySQL instance from a logical backup file to a self-managed MySQL instance.

Note

Prerequisites

  • The RDS instance meets the following requirements:

    • The RDS instance runs MySQL 8.0, MySQL 5.7, MySQL 5.6, or MySQL 5.5.

    • The RDS instance runs RDS High-availability Edition.

    • The RDS instance uses local disks.

    Note

    You can go to the Basic Information page of the RDS instance to view the preceding information about the RDS instance.

  • A logical backup is complete on the RDS instance. For more information, see Use the automatic backup feature.

  • Tables in the RDS instance are not encrypted by using Transparent Data Encryption (TDE). If tables are encrypted by using TDE, errors occur during the restoration process. Before you restore the data of the RDS instance from a logical backup file, you must decrypt the encrypted tables. For more information, see Configure TDE.

Runtime environment

The self-managed instance is installed in a 64-bit Linux operating system and runs the same MySQL version as the RDS instance. In this topic, CentOS 7 and MySQL 5.7 are used as examples.

Procedure

  1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
  2. In the left-side navigation pane of the page that appears, click Backup and Restoration.

  3. On the Base Backups tab, click the Data Backup tab. Then, find the logical backup file that you want to download and click Download Instance Backup in the Actions column.

    Note
  4. In the Download Instance Backup File dialog box, click the 复制图标 icon on the right of Copy Public URL.

    Important
    • A free quota for backup downloads over the Internet is provided. If the amount of traffic that you consume to download backup files over the Internet exceeds the free quota, you are charged for the excess traffic that you consume. For more information, see Download the backup files of an ApsaraDB RDS for MySQL instance.

    • If your Elastic Compute Service (ECS) instance resides in the same virtual private cloud (VPC) as the RDS instance, you can use the internal URL to download the logical backup file. This download method is faster and more stable.

  5. Log on to the Linux operating system on which the self-managed instance runs. Then, run the following command to download the logical backup file:

    wget -c '<The public URL from which you can download the logical backup file>' -O <The file name to use for the downloaded backup file>.tar
    Note
    • The -c option enables the resumable download feature.

    • The -O option specifies that the downloaded logical backup file is saved based on the specified file name.

  6. Run the following command to decompress the downloaded logical backup file, which includes the compressed files of the default system databases and the compressed files of the databases that you created:

    tar xvf <The name of the downloaded logical backup file>.tar -C /tmp

    Examples:

    tar xvf hins123456.tar -C /tmp
    解压缩
    Note

    If an error message similar to This does not look like a tar archive is displayed, you must check whether the file that you downloaded is a logical backup file of the RDS instance.

  7. Decompress the compressed file of the database that you want to restore. The name of the compressed file is suffixed by .sql.gz.

    gzip -d /tmp/The name of the compressed file of the database that you want to restore

    Examples:

    gzip -d /tmp/testdata_datafull_202012101615_160xxxxxx.sql.gz
    Note

    The .sql file that is generated during the decompression process will be imported in Step 10.

  8. Run the following commands to log on to the self-managed instance and create an empty database:

    Note

    The user that is used in the subsequent steps must have the permissions to execute all SQL statements in the .sql file.

    mysql -u user -p<The password of the database>
    create database <The name of the empty database>;
    exit
  9. Run the following command to import the .sql file into the empty database:

    mysql -u root -p <The name of the empty database> < /tmp/The name of the decompressed file

    Examples:

    mysql -u user -p testdb < /tmp/testdata_datafull_202012101615_160xxxxxx.sql
    Note
    • After the preceding command is successfully run, the system displays a message that prompts you to enter a password. Enter the password and press Enter.

    • If the "Can't find master key from keyring" error message is displayed, check whether the RDS instance meets all prerequisites.

  10. Log on to the empty database. Then, check whether data exists in the database. If data exists in the database, the data of the RDS instance is successfully restored to the self-managed instance.

FAQ

  • Why does my RDS instance not have logical backup files?

    By default, the system creates physical backups. You must manually create logical backups if required. For more information, see Use the automatic backup feature.

  • When I download a logical backup file, why is the value in the Backup Set Restore Point column displayed as 0 for the file?

    ApsaraDB RDS for MySQL allows you to restore data to a specific point in time by using a physical backup file and a log backup file. The ApsaraDB RDS console provides the Backup Set Restore Point column. In this column, you can view the timestamp of each physical backup file. Logical backup files cannot be used to restore data to a specific point in time. The value in the Backup Set Restore Point column is 0 for all logical backup files.

  • What do I do if the ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. error message is displayed?

    This issue occurs due to GTIDs. You can use the following methods to resolve the issue:

    • Enable the GTID feature. Then, repeat the steps in the "Procedure" section of this topic to restore the data.

    • Disable the GTID feature. Comment out the GTID_PURGED parameter in the .sql file that you want to import. Then, repeat the steps in the "Procedure" section of this topic to restore the data.

    • Confirm that the synchronous replication mode is disabled. Then, log on to the self-managed instance, run the reset master command, and then repeat the steps in the "Procedure" section of this topic to restore the data.

  • What do I do if the ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED error message is displayed?

    The imported .sql file contains specific GTIDs that can be found in the self-managed instance. In this case, log on to the self-managed instance, run the reset master command, and then repeat the steps in the "Procedure" section of this topic to restore the data.

    restmaster
  • After the data is restored to the self-managed instance, why is the data not automatically synchronized to the secondary instance of the self-managed instance?

    Check whether you can find the SESSION.SQL_LOG_BIN= 0 setting in the imported .sql file. If the setting is specified, the data restoration cannot be automatically synchronized to the secondary RDS instance.

    SQL_LOG_BIN