ApsaraDB RDS for MySQL allows you to export the backup data of an ApsaraDB RDS for MySQL instance that uses standard SSDs or enhanced SSDs (ESSDs) as a CSV file. You can use the CSV file to restore the data of the RDS instance to a self-managed MySQL instance.

Prerequisites

  • The local_infile parameter is enabled for the self-managed MySQL instance.
    Note You can execute the following SQL statements to check the status of local_infile or enable local_infile:
    • Check whether local_infile is enabled: SHOW GLOBAL VARIABLES LIKE 'local_infile';
    • Enable local_infile: SET GLOBAL local_infile=1;
  • Transparent Data Encryption (TDE) is disabled for the RDS instance. If tables are encrypted by using TDE, errors occur when you restore the data of the RDS instance. Before you restore the data of the RDS instance by using a CSV file, you must decrypt the encrypted tables. For more information, see Decrypt a table.

Limits

When you restore the data to a self-managed MySQL instance by using the CSV 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 that you downloaded contains fields of the BINARY data type, the fields are stored as hexadecimal representations. When you import the CSV file to the self-managed MySQL instance, the fields that are stored as hexadecimal representations 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 strings to raw binary strings.
  • Fields of the following spatial data types are not supported: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION.
  • We recommend that you restore the data of an RDS instance to a self-managed MySQL instance that runs the same database engine version as the RDS instance. If the database engine versions of the self-managed MySQL instance and the RDS instance are different, incompatibility issues may occur and cause the restoration to fail.

Procedure

In this example, an Ubuntu 20.04 LTS operating system is used. If you use other operating systems, you must use the corresponding commands.

  1. Use the advanced download feature to convert the backup file into a CSV file and download the CSV file to your computer as a package. For more information, see Advanced download.
  2. Decompress the downloaded package.
    The decompression command is in the following format: tar -izxvf <Name of the downloaded package>.tar.gz -C <Path to store the file that is obtained from the downloaded package>.

    Example:

    tar -izxvf test1.tar.gz -C /home/mysql/data
  3. Download the Python script.
  4. Run the following command to grant permissions on the Python script restore_from_downloads.py:
    chmod +x ./restore_from_downloads.py
  5. Restore data to the destination database by using the CSV file.
    The restoration command is in the following format: python ./restore_from_downloads.py <Path of the CSV file> <Host on which the self-managed MySQL instance is deployed> <Port that is used to connect to the self-managed MySQL instance> <Username of the account that is used to connect to the self-managed MySQL instance> <Password of the account that is used to connect to the self-managed MySQL instance>.

    Example:

    python ./restore_from_downloads.py /home/mysql/data/ 127.0.0.1 3306 zhtxxxxx "#txxxxx"
    Note
    • If the Command 'python' not found error message is displayed, 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, the command may be python3 ./restore_from_downloads.py ~/mysql/data/ 127.0.0.1 3306 zhtxxxxx "#txxxxx".
    • 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.
    1