PolarDB:Restore data to a self-managed MySQL database from a backup file

Prerequisites

Limits

Note the following limits when you restore data to a self-managed MySQL database from a downloaded backup set:

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

  Note

  If the backup set contains fields of these types, the fields are stored in hexadecimal format. When you import the data, MySQL processes these binary fields as strings. In this case, you must manually use the UNHEX function in the load data local infile command to convert the hexadecimal values to the original binary strings.

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

Usage notes

• Ensure that your PolarDB for MySQL cluster and self-managed MySQL database use the same version. Different database versions may have incompatible features that can cause the restoration to fail.

• Before you run the restore command, ensure that no databases or tables in the target database have the same names as those in the backup data to prevent data conflicts or loss. Carefully check for and delete any conflicting databases and tables.

• If you interrupt a restore job for any reason, the data may be incomplete or the job may fail. Proceed with caution.

Procedure

This example shows how to restore data from an SQL file from a PolarDB for MySQL cluster to a self-managed MySQL database. In this example, the self-managed database runs on an ECS instance that uses 64-bit CentOS 7.8. The commands may vary depending on your environment.

1. Log on to the PolarDB console. Use the Download backup files feature to convert a cluster's backup file into a CSV or SQL file and download the file to your computer or an ECS instance.

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

   • If the backup file is in .tar.gz format:

     tar -izxvf <Name of the compressed file>.tar.gz -C <Path to store the decompressed file> # In this example, a compressed file named backup.tar.gz is decompressed to the /home/mysql/data directory. Replace the file name and directory as needed. tar -izxvf backup.tar.gz -C /home/mysql/data

   • If the backup file is in .tar.zst format:

     zstd -d -c <Name of the compressed file>.tar.zst | tar -xvf - -C <Path to store the decompressed file> # In this example, a compressed file named backup.zst is decompressed to the /home/mysql/data directory. Replace the file name and directory as needed. zstd -d -c backup.tar.zst | tar -xvf - -C /home/mysql/data

3. (Optional) Verify that the backup file was decompressed to the specified location (/home/mysql/data).

   ls -al /home/mysql/data

4. Download python_script.py to your computer or an ECS instance.

   Important

   This script is for reference only. You may need to adjust the script based on your environment and requirements. Use with caution.

5. Run the following command to grant permissions to the restore_from_downloads.py Python script file:

   chmod +x ./restore_from_downloads.py

6. Restore the data from the CSV or SQL file to the self-managed database. The restore command is as follows:

   python ./restore_from_downloads.py <Path of the CSV or SQL file directory> <Database endpoint> <Database port> <Database account> <Database password>

   Example:

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

   Note

   If you close the window where the Python script is running, the script stops. To run the script in the background, use the following command:

    nohup python ./restore_from_downloads.py /home/mysql/data 127.0.0.1 3306 root "#Tes********" > app.log 2>&1 &

   Execution result:

   

   Important

   • Ensure that the self-managed database does not contain a database with the same name as a database in the backup data. Otherwise, the restoration fails.

   • If the database account name or password contains special characters, such as the number sign (#) or spaces, enclose the password in double quotation marks ("") when you pass it as a command-line parameter. For example, if the password is #1234, enter "#1234" in the command.

   • If an error message such as Command 'python' not found appears, it indicates that Python is not installed or the Python command is not in the system's search path. In this case, confirm the Python version that is installed on your system and verify that the command to run Python is correct. For example, if Python 3 is installed, you can use the python3 ./restore_from_downloads.py /home/mysql/data/test1.sql 127.0.0.1 3306 zhtxxxxx "#txxxxx" command.

   • The warning message [Warning] Using a password on the command line interface can be insecure may appear when you run the Python script. This is because the script uses the mysql -h<Database endpoint> -P<Database port> -u<Database account> -p<Database password> -e<SQL> command. The warning indicates that other users on the system might be able to see the password by running commands such as ps. This warning does not affect the restore operation. After the restore operation is complete, log on to the self-managed MySQL database to change the password.

   • If an error message such as Access denied for user 'xxx'@'xxx' (using password: YES) appears, it indicates that the database account or password is incorrect. Verify that you entered them correctly.