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

Runtime environment

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

Procedure

1. Visit the RDS instance list, select a region above, and click the target instance ID.
2. In the left-side navigation pane, click Backup and Restoration.
3. On the Data Backup tab of the Backup and Restoration page, select a time range and click OK
4. Find the logical backup file that you want to download. Then, click Download in the Actions column.
   Note

   • Logical backups must be manually created. For more information, see Automatic and manual backups.
   • If you cannot find Download in the Actions column, you must check whether the MySQL version of your RDS instance supports the download of logical backup files. For more information, see Download the data and log backup files of an ApsaraDB RDS for MySQL instance.
5. In the Download Instance Backup Set dialog box, click the icon to the right of Copy Public URL.
   
6. Log on to the Linux operating system in which the self-managed MySQL 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 name that you want to use for the downloaded logical backup file>.tar

   Note

   • The -c parameter specifies to enable resumable download.
   • The -O parameter specifies to save the downloaded logical backup file based on the specified file name.
7. Run the following command to decompress the downloaded logical backup file, which includes the compressed files of the default system databases and those of the databases that you have created:

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

   Example:

   tar xvf hins123456.tar -C /tmp

   
8. Run the following command to decompress the compressed file of the database that you want to restore (the name of the compressed file is suffixed by the .sql.gz extension):

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

   Example:

   gzip -d /tmp/testdata_datafull_202012101615_160xxxxxx.sql.gz

   Note The .sql file that is generated from the decompression will be imported in Step 10.
9. Run the following commands to log on to the self-managed MySQL instance and create an empty database:

   mysql -u root -p<The password that is used to log on to the self-managed MySQL instance>
   create database <The name of the empty database>;
   exit

10. 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 that is generated in Step 10

    Example:

    mysql -u root -p testdb < /tmp/testdata_datafull_202012101615_160xxxxxx.sql

    Note After the preceding command is successfully run, you are prompted for a password. You need only to enter the required password and press Enter.
11. Log on to the empty database. Then, check for data in the database. If you can find data in the database, the restoration is successful.
    

FAQ

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

  By default, ApsaraDB RDS creates physical backups. You must manually create logical backups if required. For more information, see Automatic and manual backups.

• 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. Therefore, the ApsaraDB RDS console provides the Backup Set Restore Point column, where you can view the timestamp of each physical backup file. Logical backup files are not used to restore data to a specific point in time. Therefore, the value in the Backup Set Restore Point column is 0 for each logical backup file.

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

  This issue is caused by the GTID feature. You can consider the following solutions:

  • Enable the GTID feature. Then, perform the restoration again.
  • Do not enable the GTID feature. Comment out the GTID_PURGED parameter in the .sql file that you want to import. Then, perform the restoration again.
  • Check that the synchronous replication mode is disabled. Log on to the self-managed MySQL instance, run the reset master command, and then perform the restoration again.
• What do I do if "ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED" is reported?

  The imported .sql file contains some GTIDs that can be found on the self-managed MySQL instance. In this case, log on to the self-managed MySQL instance, run the reset master command, and then perform the restoration again.

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

  Check whether you can find the "SESSION.SQL_LOG_BIN= 0" setting at the end of the imported .sql file. If the setting is specified, the data that is restored to the self-managed MySQL instance cannot be automatically synchronized to the secondary instance.