ApsaraDB RDS:Restore an RDS MySQL logical backup to a self-managed database

How it works

1. Download the logical backup file from the RDS console.

2. Decompress the .tar archive and then the per-database .sql.gz files.

3. Create an empty target database and import the schema and data files with mysql.

Prerequisites

Before you begin, make sure you have:

• An ApsaraDB RDS for MySQL instance with all of the following (check the Basic Information page):

  • Major version: 8.0, 5.7, 5.6, or 5.5

  • Edition: High-availability Edition

  • Storage type: Local SSD

• A logical backup file already created. Logical backups must be created manually — the system creates physical backups by default. See Manual backup.

• A Linux host running the same MySQL major version as the RDS instance, with enough free disk space to hold the decompressed backup files.

Download the backup file

1. Go to the Instances page. In the top navigation bar, select the region where your RDS instance resides, then click the instance ID.

2. In the navigation pane on the left, click Backup and Restoration.

3. On the Base Backups > Data Backup tab, find the logical backup file you want to restore, then click Download Instance Backup File in the Actions column.

   Note

   If Download Instance Backup File is unavailable, check whether your instance edition supports backup downloads.

4. In the Download Instance Backup File dialog box, copy the download URL:

   • If your ECS instance and RDS instance are in the same VPC (recommended): copy the internal network URL. This is faster and more stable, especially for large backup files.

   • Otherwise: copy the external download URL. A free quota applies for internet backup downloads; traffic beyond the quota is charged. See Billing.

5. On the Linux host, run the following command to download the backup file. Replace <download_url> with the URL you copied and <custom_file_name> with a name of your choice.

   Flag	Description
   -c	Enables resumable downloads, so the download resumes from where it stopped if interrupted
   -O	Saves the file with the specified name

   wget -c '<download_url>' -O <custom_file_name>.tar

Decompress the backup file

1. Extract the .tar archive:

   Note

   If you see This does not look like a tar archive, confirm that you downloaded an RDS logical backup file and not a physical backup. If you see Wrote only 512 of 10240 bytes, your disk is full — change the instance configuration to increase disk space, then try again.

   tar xvf <custom_file_name>.tar -C /tmp

2. Check the directory structure after extraction. The archive contains one subdirectory per database, each with a schema file and a data file:

   tree /tmp/backup_root/   # Replace with the actual root directory created by tar.

   Expected output:

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

3. Navigate to the directory of the database you want to restore:

   cd /tmp/backup_root/<database_name>

4. Decompress the .sql.gz files:

   gzip -d schema.sql.gz
   gzip -d data.sql.gz

   This produces schema.sql and data.sql, which you will import in the next step.

Import the data

1. Log in to MySQL and create an empty target database. The user must have permission to execute all SQL statements in the .sql files.

   Placeholder	Description	Example
   <user>	MySQL username	root
   <password>	MySQL password (no space after -p)	mypassword
   <target_database_name>	Name for the empty database to create	restored_db

    mysql -u <user> -p<password>

    CREATE DATABASE <target_database_name>;
    EXIT;

   Replace the placeholders as follows:

2. Import the schema and then the data:

   Note

   If you see Can't find master key from keyring, your instance may not meet the prerequisites listed in this guide. Verify the RDS instance edition and storage type.

    # Import the table schema
    mysql -u <user> -p <target_database_name> < schema.sql
   
    # Import the data
    mysql -u <user> -p <target_database_name> < data.sql

   Enter your password when prompted after each command.

Verify the restoration

1. Log in to MySQL and confirm that the tables and data are present:

    mysql -u <user> -p

    USE <target_database_name>;
    SHOW TABLES;                            -- Check that tables exist
    SELECT COUNT(*) FROM <table_name>;      -- Verify the row count

   If the tables and data appear, the restoration is complete.

FAQ

Why doesn't my instance have any logical backups?

RDS creates physical backups by default. To get a logical backup, create one manually. See Manual backup.

Why is the Restore Point in Time value 0 for logical backups?

Point-in-time restore requires a physical backup combined with a log backup. Logical backups do not support point-in-time restore, so the Restore Point in Time field shows 0.

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

This error means the target database already contains global transaction identifier (GTID) history. Use one of these approaches:

• Enable GTID on the target database and re-run the import.

• Comment out all GTID_PURGED lines in the .sql file and re-run the import.

• If primary/secondary replication is not in use, run RESET MASTER on the target database to clear GTID history, then re-run the import.

How do I fix `ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED`?

The .sql file contains GTID information that conflicts with existing GTID history in the target database. Run RESET MASTER to clear the GTID history, then re-run the import.

Why is the restored data only in the primary database and not synced to the secondary database?

The .sql file contains SESSION.SQL_LOG_BIN= 0, which disables binary logging during the import and prevents changes from replicating to the secondary database. Check the import file for this setting.

What's next

• Back up an ApsaraDB RDS instance — set up automatic backups to make future restores easier.

• Use mysqldump to migrate MySQL data — migrate data from a self-managed database into an RDS instance.

• RDS MySQL mysqldump options — reference for all mysqldump flags supported by ApsaraDB RDS for MySQL.