PolarDB:Restore to a self-managed MySQL database

Prerequisites

Limitations

The following limitations apply when restoring data from a downloaded backup to a self-managed MySQL database:

• The following binary field types are not supported: BIT, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

  Note

  If a backup set contains these field types, they are stored in hexadecimal format. You must then manually use the UNHEX function within the load data local infile command to convert the hexadecimal values back to their original binary format.

• The following spatial field types are not supported: GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION.

Usage notes

• Use the same database version for the PolarDB for MySQL cluster and the self-managed MySQL database. Incompatible features between different versions can cause the restore operation to fail.

• Before running the restore command, ensure the destination database does not contain any databases or tables with the same names as those in the backup data. This prevents data conflicts and potential data loss. Remove any conflicting databases or tables beforehand.

• Interrupting a restore task can result in incomplete data or task failure.

Procedure

This example demonstrates restoring data from a PolarDB for MySQL cluster's SQL backup file to a self-managed MySQL database on an ECS instance (CentOS 7.8 64-bit). Adapt the commands for your specific environment.

1. In the PolarDB console, use the Download backup files feature to convert the cluster's backup file to CSV or SQL format. Then, download the file to your local machine or an ECS instance.

2. • 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) Verify that the backup file is decompressed to the specified directory (/home/mysql/data).

   ls -al /home/mysql/data

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

   Important

   This script is for reference only and may require modification for your environment. Use it with caution.

5. Make the restore_from_downloads.py script executable by running the following command:

   chmod +x ./restore_from_downloads.py

6. Restore the data from the CSV or SQL file to your self-managed database:

   python ./restore_from_downloads.py <path_to_csv_or_sql_directory> <database_endpoint> <database_port> <database_username> <database_password>

   Example:

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

   Note

   If you close the terminal window, the script stops running. 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 &

   Output:

   [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

   Important

   • Ensure your self-managed database does not contain a database with the same name as one in the backup. Otherwise, the restore operation will fail.

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

   • If you receive an error like Command 'python' not found, it means Python is not installed or is not in your system's PATH. Verify your Python installation and the command used to run it. For example, if Python 3 is installed, you can use python3 ./restore_from_downloads.py /home/mysql/data/test1.sql 127.0.0.1 3306 zhtxxxxx "#txxxxx".

   • If the warning message [Warning] Using a password on the command line interface can be insecure appears when you run a Python script, it is because the script uses the mysql -h<database endpoint> -P<database port> -u<database username> -p<database password> -e<SQL> command. This method is insecure because other users on the system can view the password by running commands such as ps. This warning does not affect your recovery operation. After the recovery is complete, you can log on to the self-managed MySQL database and change the account password.

   • An error like Access denied for user 'xxx'@'xxx' (using password: YES) indicates an incorrect database username or password. Verify that you have entered the credentials correctly.