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

Prerequisites

• Your RDS instance meets the following requirements:

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

  • Series: High Availability (HA) Series

  • The RDS instance uses local SSDs.

  Note

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

• A logical backup file is created for the RDS instance. For more information, see Configure an automatic backup policy for an ApsaraDB RDS for MySQL instance.

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, click Backup and Restoration.

3. In the Base Backups > Data Backup list, find the logical backup file that you want to download and click Download Instance Backup File in the Actions column.

   Note

   • You must manually create logical backups. For more information, see Manually back up an ApsaraDB RDS for MySQL instance.

   • If the Download Instance Backup File button is not displayed, check whether your RDS instance supports backup downloads.

4. In the Download Instance Backup File dialog box, click Copy on the right side of the external download URL to copy the 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 Billing.

   • 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 where the self-managed database is deployed and run the following command to download the logical backup file:

   wget -c '<External download URL of the data backup file>' -O <Custom file name>.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. Decompress the logical backup file, including the compressed files of system databases and user-created databases.

   1. Run the following command to decompress the backup file:

      tar xvf <Custom file name>.tar -C /tmp

      Note

      • If an error message such as This does not look like a tar archive is displayed during the decompression, check whether the file that you downloaded is a logical backup file of the RDS instance.

      • If an error message such as Wrote only 512 of 10240 bytes. Exiting with failure status due to previous errors is displayed during the decompression, check whether your disk space is full. You can change the configurations to expand the disk space and try again.

   2. Run the following command to view the directory structure after decompression:

      tree /tmp/backup_root/  # Replace with the actual root directory after decompression

      Example of a multi-level backup file structure:

      /tmp/backup_root/
      ├── database1/   # Directory of target database 1
      │ ├── schema.sql # Database structure file
      │ └── data.sql   # Data file
      ├── database2/   # Directory of target database 2
      │ ├── schema.sql
      │ └── data.sql
      └── config.txt    # Backup metadata (not required)

7. Run the following command to navigate to the target database directory:

   cd /tmp/backup_root/database_name  # Replace with the actual database name

8. Decompress the compressed file of the destination database that you want to restore (with the .sql.gz extension) by running the following commands:

   gzip -d schema.sql.gz  # Decompress the structure file
   gzip -d data.sql.gz    # Decompress the data file

   Note

   The .sql file that is obtained from the decompression will be imported in Step 10.

9. Log on to the database and create an empty database by running the following commands:

   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<Database password>
   create database <The name of the empty database>;
   exit

10. Run the following commands to import the .sql file into the database:

    # Import the table schema
    mysql -u user -p <The name of the empty database> < schema.sql
    
    # Import the data
    mysql -u user -p <The name of the empty database> < data.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.

11. Verify the restoration result. If data is displayed in the database, the migration is successful.

    mysql -u user -p
    mysql> USE <The name of the empty database>;
    mysql> SHOW TABLES;       # Check whether tables exist
    mysql> SELECT COUNT(*) FROM <Table name>;  # Verify the data volume

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 Configure an automatic backup policy for an ApsaraDB RDS for MySQL instance.

• Why is the Restore Point in Time value 0 when I download a logical backup file?

  The physical backup files and log backup files of an ApsaraDB RDS for MySQL instance can be used to restore data to a specific point in time. Therefore, a specific Restore Point in Time (timestamp) is displayed. Logical backup files cannot be used to restore data to a specific point in time. Therefore, the Consistent Time value is 0.

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

  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.

  • If you do not want to enable the GTID feature, you can comment out all GTID_PURGED content in the import file (with the .sql extension). Then, repeat the steps in the "Procedure" section of this topic to restore the data.

  • If you do not need to replicate data between a primary instance and secondary instances, you can log on to the database and run the RESET MASTER command. Then, repeat the steps in the "Procedure" section of this topic to restore the data.

• How do I resolve 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?

  If an import file (with the .sql extension) contains GTID information, the database must not contain other GTID information. Log on to the database and run the RESET MASTER command to reset the database. Then, repeat the steps in the "Procedure" section of this topic to restore the data.

  

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

  You can check whether the import file (with the .sql extension) contains SESSION.SQL_LOG_BIN= 0. This setting prevents the operations on the primary instance from being synchronized to the secondary instance.