All Products
Search

This Product

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

    Document Center

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

    Last Updated:Apr 15, 2024

    You can use the backup download feature of ApsaraDB RDS for MySQL to export the snapshot backup file of your ApsaraDB RDS for MySQL instance that uses cloud disks as a CSV file or an SQL file. Then, you can use the CSV file or SQL file to restore the data of the RDS instance to a self-managed MySQL instance.

    Prerequisites

    • Your RDS instance meets the following requirements:

      • The RDS instance runs MySQL 8.0 or MySQL 5.7.

      • The RDS instance uses cloud disks.

      Note

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

    • Transparent Data Encryption (TDE) is disabled for the RDS instance. If tables are encrypted by using TDE, errors occur during the restoration. Before you restore the data of the RDS instance, you must decrypt the encrypted tables. For more information, see Configure TDE.

    • The local_infile parameter is enabled for the self-managed MySQL instance.

      Note

      • To check whether the local_infile parameter is enabled, you can execute the following statement: SHOW GLOBAL VARIABLES LIKE 'local_infile';. If the return value is ON, the parameter is enabled.

      • To enable the local_infile parameter, you can use the following setting: SET GLOBAL local_infile=1;.

    Limits

    When you restore the data to a self-managed MySQL instance by using the CSV file or the SQL file that you downloaded, take note of the following limits:

    • Fields of the following data types are not supported: BIT, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

      Note

      If the CSV file or the SQL file that you downloaded contains fields of the preceding data types, the fields are stored as hexadecimal representations. When you import the CSV file or the SQL file to the self-managed MySQL instance, the binary fields are processed as strings. In this case, you must call the UNHEX function in the LOAD DATA LOCAL INFILE command to convert the fields from hexadecimal representations to raw binary strings.

    • Fields of the following spatial data types are not supported: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION.

    Usage notes

    • We recommend that you use the same database engine version for your RDS instance and the self-managed MySQL instance. Different database engine versions may cause incompatibility issues and cause the restoration to fail.

    • Before you run the restoration command, make sure that the names of databases or tables in the self-managed MySQL instance are different from the names of databases or tables in the RDS instance. This prevents data conflicts or data loss. We recommend that you check and delete duplicate databases and tables that may cause data conflicts.

    • If you interrupt a restoration task, full data may not be restored or the restoration task may fail. Proceed with caution.

    Procedure

    This section describes how to use the SQL file to restore the data of an RDS instance that uses cloud disks to a self-managed MySQL instance on your Elastic Compute Service (ECS) instance running 64-bit CentOS 7.8. If you use other operating systems, you must use the corresponding commands.

    1. Log on to the ApsaraDB RDS console and convert the backup file of the RDS instance that uses cloud disks to a CSV file or an SQL file and then download the file to your computer or ECS instance. For more information, see Download backup files.

    2. Run the following command to decompress the downloaded package:

      tar -izxvf <Name of the package>.tar.gz -C <Directory to store the files that are obtained from the package>

      In this example, a package named backup.tar.gz is decompressed to the /home/mysql/data directory. You can replace the package name and directory name with actual names. 

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

    3. Optional. Check whether the backup file is decompressed to the /home/mysql/data directory. 

      ls -al /home/mysql/data

    4. Download the MySQL Python script to your computer or ECS instance.

    5. Run the following command to grant the required permissions on the restore_from_downloads.py file:

      chmod +x ./restore_from_downloads.py

    6. Run the following command to restore the data of the RDS instance to the self-managed MySQL instance by using the CSV file or SQL file:

      python ./restore_from_downloads.py <Directory of the CSV file or SQL file> <Database host> <Database port> <Username of the account> <Password of the account>

      Example:

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

      Command output:

      image.png

      Important

      • If the self-managed MySQL instance contains a database that has the same name as the database whose data you want to import, the import fails.

      • If the username or password of the account that you want to use contains special characters, such as number signs (#) and spaces, you must enclose the username or password in double quotation marks (""). For example, if the password is #1234, you must enter "#1234" in the preceding command.

      • If the Command 'python' not found error message is displayed, Python is not installed or the command that is used to run the Python script is not in the search path. In this case, you must check the version of Python that is installed in your system and the command that is used to run the Python script. For example, if Python 3 is installed, you can run the python3 ./restore_from_downloads.py /home/mysql/data/test1.sql 127.0.0.1 3306 zhtxxxxx "#txxxxx" command.

    References