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

Important

This tutorial is for reference only. The Python restoration script is no longer maintained. Evaluate the script before you use it.

PolarDB for MySQL lets you export a snapshot backup from a disk-based instance as a CSV or SQL file using the Download backup files feature. You can then import the file into a self-managed MySQL database.

Use this approach when you need to extract data from a PolarDB for MySQL cluster and load it into a self-managed MySQL instance — for example, for migration, compliance archiving, or offline analysis.

Prerequisites

Before you begin, make sure you have:

A PolarDB for MySQL Enterprise Edition cluster of the Cluster Edition series
A self-managed MySQL database running the same version as your PolarDB cluster
The local_infile parameter enabled on the self-managed MySQL database
A RAM user with permission to download backup files — see RAM user permissions

Supported regions:

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), US (Virginia), and Germany (Frankfurt).

Support for additional regions will be available soon.

Unsupported configurations:

The backup download feature is not supported for encrypted PolarDB clusters.

Enable `local_infile` on the self-managed MySQL database:

To check whether local_infile is enabled, run:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

ON indicates the parameter is enabled. To enable it, run:

SET GLOBAL local_infile=1;

Limitations

The following data types are not fully supported when restoring from a downloaded backup set:

Category	Unsupported types
Binary	BIT, BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
Spatial	GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION

Important

If your backup contains binary-type fields, they are stored in hexadecimal format in the exported file. MySQL treats them as strings during import. To recover the original binary values, use the UNHEX function in LOAD DATA LOCAL INFILE commands to convert the hexadecimal values back to binary strings.

Usage notes

The self-managed MySQL database and the PolarDB for MySQL cluster must run the same version. Version mismatches can cause incompatibilities that make the restoration fail.
Before running the restore command, make sure the target database has no databases or tables with the same names as those in the backup. Duplicate names cause data conflicts. Delete any conflicting databases and tables before proceeding.
If you interrupt a restore job, the restored data may be incomplete. Avoid interrupting the operation.

Restore data from a backup file

This example restores data from a SQL file to a self-managed MySQL database running on an ECS instance with 64-bit CentOS 7.8. Commands may vary depending on your environment.

Step 1: Download the backup file

Log on to the PolarDB console and use the Download backup files feature to export your cluster's backup as a CSV or SQL file. Download the file to your local machine or an ECS instance.

Step 2: Decompress the backup file

Run the appropriate command based on the file format:

`.tar.gz` format:

tar -izxvf <compressed-file>.tar.gz -C <output-directory>

Example — decompress backup.tar.gz to /home/mysql/data:

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

`.tar.zst` format:

zstd -d -c <compressed-file>.tar.zst | tar -xvf - -C <output-directory>

Example — decompress backup.tar.zst to /home/mysql/data:

zstd -d -c backup.tar.zst | tar -xvf - -C /home/mysql/data

(Optional) Verify the files were decompressed to the correct location:

ls -al /home/mysql/data

Step 3: Download the restore script

Download restore_from_downloads.py to the same machine where you decompressed the backup.

Important

This script is for reference only. Adjust it based on your environment and requirements before use.

Step 4: Grant execute permissions

chmod +x ./restore_from_downloads.py

Step 5: Run the restore

python ./restore_from_downloads.py <csv-or-sql-directory> <db-endpoint> <port> <db-account> <db-password>

Example:

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

If Python 3 is installed on your system, use python3 instead of python: If the password contains special characters such as # or spaces, enclose it in double quotation marks. For example, if the password is #1234, use "#1234".

python3 ./restore_from_downloads.py /home/mysql/data/test1.sql 127.0.0.1 3306 zhtxxxxx "#txxxxx"

Expected output:

To keep the restore running after you close the terminal:

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

Troubleshooting

Error	Cause	Solution
Restore fails with a database name conflict	The target database already has a database with the same name as one in the backup	Drop the conflicting database before re-running the script
`Command 'python' not found`	Python is not installed or the `python` command is not in your system path	Check which Python version is installed and use the matching command (`python3` for Python 3)
`Access denied for user 'xxx'@'xxx' (using password: YES)`	The database account or password is incorrect	Verify both and re-run the command
`[Warning] Using a password on the command line interface can be insecure`	The script passes the password via the `-p` flag in the `mysql` command, making the password visible to other users running `ps`	The warning does not affect the restore operation. After the restore is complete, log on to the self-managed MySQL database and change the password