Restore the data of an ApsaraDB RDS for MySQL instance to a self-managed MySQL instance by using snapshot backup files - ApsaraDB RDS

Use the Download Backup feature to convert a snapshot backup from an RDS for MySQL instance into a CSV or SQL file, then restore it to a self-managed MySQL database on a local machine or an ECS (Elastic Compute Service) instance.

Important

This topic covers disk-based RDS for MySQL instances only. For high-performance local disk instances, see Restore an RDS for MySQL physical backup file to a self-managed database or Restore an RDS for MySQL logical backup file to a self-managed database.

Prerequisites

Before you begin, make sure the following conditions are met:

The RDS for MySQL instance meets all of the following:
- Database version: 8.0 or 5.7 (Serverless instances are supported)
- Storage class: enterprise SSD (ESSD) or premium performance disk
- Status: Running
Find this information on the Basic Information page of the instance.

Important
The advanced download feature is not supported for premium performance disk instances that have the data archiving feature enabled. Download tasks will fail.
Transparent data encryption (TDE) is disabled on the RDS instance. If the instance has encrypted tables, decrypt them before proceeding. The restore will fail if encrypted tables exist.
The local_infile parameter is enabled on the self-managed MySQL database. Check the status (ON = enabled):
```
SHOW GLOBAL VARIABLES LIKE 'local_infile';
```
To enable:
```
SET GLOBAL local_infile=1;
```

Limitations

The following limitations apply when restoring data from a downloaded backup set (a CSV or SQL file converted from snapshot backup data).

Unsupported binary field types: BIT, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

These types are stored in hexadecimal format in the downloaded file. During import, MySQL treats them as strings. Use the UNHEX function in your LOAD DATA LOCAL INFILE command to convert them back to binary. For BLOB data specifically, use mysqldump to back up and restore instead:

Back up:

mysqldump -h 127.0.0.1 -u user -p --opt --default-character-set=utf8 --hex-blob <self-managed_database_name> --skip-triggers --skip-lock-tables > /tmp/<self-managed_database_name>.sql

Restore:

mysql -h 127.0.0.1 -u username -p database_name < backup_file.sql

Unsupported spatial data types: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION.

Before you start

Review the following before running the restore:

Script maintenance: The restore_from_downloads.py script in this topic is for reference only and is no longer maintained. Verify its compatibility with your environment before use.
Database version consistency: The RDS for MySQL version and your self-managed MySQL version must match. Version mismatches can cause restore failures.
No conflicting database or table names: The target database must not contain any databases or tables with the same names as those in the backup. The restore will fail if name conflicts exist.

Special characters in paths, accounts, or passwords: Enclose the value in double quotation marks if it contains &, #, or spaces.

# Path with special character (&)
python ./restore_from_downloads.py "/path/to/data&test" 127.0.0.1 3306 root zhtpasswordtest

# Password with special characters (#, @)
python ./restore_from_downloads.py /data 127.0.0.1 3306 root "#Test@20250821"

Job interruption: Do not interrupt the restore job once it starts. Interrupting the job can leave the restored data incomplete or cause the job to fail. Proceed with caution.

Restore a snapshot backup to a self-managed MySQL database

This topic uses an SQL file from an RDS for MySQL disk instance, restored to an ECS instance running Alibaba Cloud Linux 3.2104 LTS 64-bit. Adjust commands for your environment.

The restore involves three steps:

Download and decompress the backup file.
Prepare the restore script.
Run the restore command.

Step 1: Download and decompress the backup file

Log on to the RDS console. Use the Download Backup feature to convert the backup file to a CSV or SQL file and download it to your local machine or ECS instance.

Decompress the backup file.

For .tar.gz files:

tar -izxvf <compressed_package_name>.tar.gz -C <destination_directory>

Example — decompresses backup.tar.gz to /home/mysql/data:

tar -izxvf backup.tar.gz -C /home/mysql/data

For .tar.zst files:

zstd -d -c <compressed_package_name>.tar.zst | tar -xvf - -C <destination_directory>

Example — decompresses backup.tar.zst to /home/mysql/data:

zstd -d -c backup.tar.zst | tar -xvf - -C /home/mysql/data

(Optional) Verify that the files were decompressed to the correct location:
```
ls -al /home/mysql/data
```

Step 2: Prepare the restore script

Download the MySQL Python script file to your local machine or ECS instance.

Important
This script is for reference only. Adjust it for your environment before use.

Grant execute permissions to the script:

chmod +x ./<script_name>.py

Example:

chmod +x ./restore_from_downloads.py

Step 3: Run the restore command

Run the restore script with the following syntax:

python ./<script_name>.py <path_to_CSV_or_SQL_file_directory> <database_host> <database_port> <database_account> <database_password>

Replace the placeholders:

Placeholder	Description	Example
`<script_name>`	Name of the Python script	`restore_from_downloads`
`<path_to_CSV_or_SQL_file_directory>`	Directory containing the decompressed CSV or SQL files	`/home/mysql/data`
`<database_host>`	Host address of the self-managed MySQL database	`127.0.0.1`
`<database_port>`	Port of the self-managed MySQL database	`3306`
`<database_account>`	Database account	`root`
`<database_password>`	Password for the database account (enclose in quotes if it contains special characters)	`"#Tes********"`

Example:

python ./restore_from_downloads.py /home/mysql/data 127.0.0.1 3306 root "#Tes********"

Important

If you see Command 'python' not found, Python is not installed or not in the system PATH. Check which Python version is installed, then use the correct command. For Python 3:

python3 ./restore_from_downloads.py /home/mysql/data/test1.sql 127.0.0.1 3306 zhtxxxxx "#txxxxx"

What's next

Migrate data with DTS: Migrate data from an RDS for MySQL instance to a self-managed MySQL database.
Explore other restore options: Overview of data restoration solutions.
Restore from physical backup: Restore an RDS for MySQL physical backup file to a self-managed database.
Restore from logical backup: Restore an RDS for MySQL logical backup file to a self-managed database.