Restore the data of a PolarDB cluster to a self-managed MySQL database by using snapshot backup files - PolarDB

You can use the backup download feature of PolarDB to export the snapshot backup file of your PolarDB cluster 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 PolarDB cluster to a self-managed MySQL database.

Prerequisites

PolarDB for MySQL cluster requirements

Version requirements:
- The cluster is a PolarDB for MySQL Enterprise Edition cluster.
- The cluster runs PolarDB for MySQL 5.6, 5.7, 8.0.1, or 8.0.2.
Region requirements:
China (Chengdu), China (Guangzhou), China (Qingdao), China (Beijing), China (Shanghai), China (Zhangjiakou), China (Hangzhou), China (Shenzhen), China (Hong Kong), Malaysia (Kuala Lumpur), Indonesia (Jakarta), Japan (Tokyo), Singapore, US (Silicon Valley), and US (Virginia).
Note
The feature will be available in other regions soon.
The disk encryption feature is disabled for your PolarDB cluster. The backup download feature is not supported in a PolarDB cluster for which the disk encryption feature is enabled.

Self-managed MySQL database requirements

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

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 execute the following statement: SET GLOBAL local_infile=1;.

Limits

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

Fields of the following binary 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 database, 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.

Precautions

We recommend that you use the same database engine version for your PolarDB for MySQL cluster and the self-managed MySQL database. 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 database are different from the names of databases or tables in the PolarDB cluster. 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 a PolarDB for MySQL cluster that uses cloud disks to a self-managed MySQL database 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.

Log on to the PolarDB console and convert the backup file of the PolarDB cluster 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.
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
```
Optional. Check whether the backup file is decompressed to the /home/mysql/data directory.
```
ls -al /home/mysql/data
```
Download the Python script.py to your computer or ECS instance.
Run the following command to grant the required permissions on the restore_from_downloads.py file:
```
chmod +x ./restore_from_downloads.py
```
Run the following command to restore the data of the PolarDB cluster to the self-managed MySQL database 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:
Important
- If the self-managed MySQL database 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.