All Products
Search

This Product

    ApsaraDB RDS:Restore an ApsaraDB RDS for MySQL snapshot backup file to a self-managed database

    Document Center

    ApsaraDB RDS:Restore an ApsaraDB RDS for MySQL snapshot backup file to a self-managed database

    Last Updated:Jun 21, 2026

    Use the Download Backup feature to convert a snapshot backup from a disk-based instance of ApsaraDB RDS for MySQL into a CSV or SQL file. You can then restore this file to a self-managed MySQL database on a local machine or an ECS instance.

    Prerequisites

    • The ApsaraDB RDS for MySQL instance meets the following requirements:

      • Database version: 8.0 or 5.7 (Serverless instances are supported)

      • Storage type: ESSD or premium performance disk

      • Status: running

      Note

      • You can view this information on the instance's Basic Information page.

      • The advanced download feature is not supported for instances that use premium performance disks and have the data archiving feature enabled. Download tasks for these instances fail.

    • TDE must be disabled on the ApsaraDB RDS for MySQL instance. If the instance contains encrypted tables, the restore process will fail. You must decrypt them first.

    • The local_infile parameter must be enabled on your self-managed MySQL database.

      Note

      • To check the status of the local_infile parameter (ON indicates that it is enabled), run the following command: SHOW GLOBAL VARIABLES LIKE 'local_infile';

      • To enable the local_infile parameter, run the following command: SET GLOBAL local_infile=1;

    Limitations

    The following limitations apply to data types when you restore a downloaded backup set to a self-managed MySQL database.

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

      Note

      • If a backup set contains these data types, they are stored in hexadecimal format. During the import, MySQL treats them as strings. You must manually use the UNHEX() function in the load data local infile command to convert them back to their binary format.

      • Alternatively, for BLOB data, you can use mysqldump to back up and restore data to your local MySQL database.

        Back up data: 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 data: 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 begin

    • Script maintenance: The restore_from_downloads.py script provided in this topic is for reference only and is no longer maintained. Use it with caution and verify its compatibility with your environment.

    • Database version consistency: Ensure the major version of your self-managed MySQL database matches that of the ApsaraDB RDS for MySQL instance. Version mismatches can cause restore failures due to feature incompatibilities.

    • Naming conflicts: Ensure the target self-managed database has no naming conflicts with the databases or tables in the backup set. Name conflicts will cause the restore to fail.

    • Task interruption: Interrupting the restore task can result in incomplete data or a failed restore. Do not interrupt the process after it starts.

    • Handling special characters: If a file path, database account, or password contains special characters, such as &, #, or a space, you must enclose the value in double quotation marks (""). Otherwise, the command will fail. For example:

      # If a file path contains special characters, such as &, enclose the path in double quotation marks.
python ./restore_from_downloads.py "/path/to/data&test" 127.0.0.1 3306 root zhtpasswordtest

# If a password contains special characters, such as # or @, enclose the password in double quotation marks.
python ./restore_from_downloads.py /data 127.0.0.1 3306 root "#Test@20250821"

    Procedure

    This example demonstrates how to restore an SQL file from a disk-based ApsaraDB RDS for MySQL instance to a self-managed MySQL database. The self-managed database in this example runs on an ECS instance with Alibaba Cloud Linux 3.2104 LTS 64-bit. Adapt the commands for your environment.

    1. Download and decompress the backup file

      1. Log on to the RDS console and use the Download Backup feature to convert the backup file from your ApsaraDB RDS for MySQL disk-based instance into a CSV or SQL file. Then, download the file to your local machine or ECS instance.

      2. Decompress the downloaded backup file on your local machine or ECS instance.

        • If the backup file is in .tar.gz format, run the following command:

          tar -izxvf <compressed_file_name>.tar.gz -C <destination_directory>
# Example: Decompress the backup.tar.gz file to the /home/mysql/data directory. The file name and directory in the command are for reference only.
tar -izxvf backup.tar.gz -C /home/mysql/data

        • If the backup file is in .tar.zst format, run the following command:

          zstd -d -c <compressed_file_name>.tar.zst | tar -xvf - -C <destination_directory>
# Example: Decompress the backup.zst file to the /home/mysql/data directory. The file name and directory in the command are for reference only.
zstd -d -c backup.tar.zst | tar -xvf - -C /home/mysql/data

      3. (Optional) Run the following command to verify that the backup file was decompressed to the specified location (/home/mysql/data):

        ls -al /home/mysql/data

    2. Prepare the restore script

      1. Prepare a restore script.

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

        Important

        This script is provided for reference only. Modify the script to fit your environment. Use the script with caution.

      2. Run the following command to grant the execute permission on the script:

        chmod +x ./<script_name>.py
# Example:
chmod +x ./restore_from_downloads.py

    3. Run the restore command

      Important

      If an error message such as Command 'python' not found appears, Python is not installed or the python command is not in the system's PATH. In this case, check your installed Python version and use the correct command to run the script. For example, if you have Python 3 installed, run the python3 ./restore_from_downloads.py /home/mysql/data/test1.sql 127.0.0.1 3306 zhtxxxxx "#txxxxx" command.

      python ./<script_name>.py <path_to_CSV_or_SQL_directory> <database_host> <database_port> <database_account> <database_password>
# Example:
python ./restore_from_downloads.py /home/mysql/data 127.0.0.1 3306 root "#Tes********"
      [root@ixxx ~]# python ./restore_from_downloads.py /home/mysql/data 127.0.0.1 3306 root "#Tesxxx"
[INFO]: restore data from /home/mysql/data to 127.xxx:3306
mysql: [Warning] Using a password on the command line interface can be insecure.
[INFO]: restore structure database: zhxxx ends
mysql: [Warning] Using a password on the command line interface can be insecure.
[INFO]: restore structure table: zhxxx ends
[INFO]: trying to exec: mysql -h127.xxx -P3306 -uroot -p#Tesxxx </home/mysql/data/zxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
[INFO]: restore data [1/1] of table zhxxx.zhxxx

    References