Use mysqldump to import and export data - PolarDB

mysqldump is an official MySQL utility for exporting data and is supported by PolarDB-X 1.0. This topic describes how to use mysqldump to import data into and export data from PolarDB-X 1.0 across three common scenarios.

When to use mysqldump

mysqldump is suited for offline migrations with fewer than 10 million rows. Use it when you need to:

Export data from a MySQL database and import it into PolarDB-X 1.0
Copy data between two PolarDB-X 1.0 instances (for example, from a test environment to production)
Export data from a PolarDB-X 1.0 database back to a MySQL database

For large datasets or real-time migration, see Migrate data from a self-managed MySQL database to a PolarDB-X instance.

For complete mysqldump documentation, see the official MySQL documentation.

Scenario 1: Export from MySQL and import into PolarDB-X 1.0

Step 1: Export data from MySQL

Run the following command to export table schemas and data from a MySQL database to a file named dump.sql:

mysqldump -h <ip> -P <port> -u <user> -p<password> \
  --default-character-set=<char-set> \
  --net_buffer_length=10240 \
  --no-create-db \
  --skip-add-locks \
  --skip-lock-tables \
  --skip-tz-utc \
  --set-charset \
  [--hex-blob] \
  [--no-data] \
  <database> [<table1> <table2> ...] > dump.sql

Replace the placeholders with your actual values:

Placeholder	Description	Required
`<ip>`	IP address of the MySQL database	Yes
`<port>`	Port of the MySQL database	No
`<user>`	Username of the account used to connect to the MySQL database	Yes
`<password>`	Password of the account. No space is allowed between `-p` and the password.	Yes
`<char-set>`	Character set	Yes
`--hex-blob`	Exports BINARY, VARBINARY, and BLOB fields in hexadecimal format. Use this option when exporting binary string fields.	No
`--no-data`	Exports table schemas only, without data	No
`<database>`	Name of the database to export	Yes
`<table1> <table2> ...`	Specific tables to export. If omitted, all tables in the database are exported.	No

Step 2: Modify CREATE TABLE statements for sharding

The exported dump.sql file contains a CREATE TABLE statement for each table. Importing these statements into PolarDB-X 1.0 as-is creates non-partitioned tables. To implement sharding, modify the CREATE TABLE statements before importing.

For CREATE TABLE syntax in PolarDB-X 1.0, see CREATE TABLE.

Step 3: Import the data into PolarDB-X 1.0

Use one of the following methods to import dump.sql into PolarDB-X 1.0:

Method 1: Interactive import (recommended for monitoring)

mysql -h <ip> -P <port> -u <user> --default-character-set=<char-set>

After logging in:

source /yourpath/dump.sql

This method prints each operation to the screen as it runs, which lets you monitor progress. It takes longer than Method 2.

Method 2: Batch import

mysql -h <ip> -P <port> -u <user> --default-character-set=<char-set> < /yourpath/dump.sql

Set --default-character-set to the same character set used during export. On Microsoft Windows, escape the delimiters in the file path.

During import, you may see an error similar to

ERROR 1231 (HY000): [a29ef6461c00000][10.117.207.130:3306][****]Variable @saved_cs_client can't be set to the value of @@character_set_client

. This error is caused by implementation differences between PolarDB-X 1.0 and MySQL and does not affect the correctness of the imported data.

Scenario 2: Copy data between two PolarDB-X 1.0 instances

Use this scenario to migrate data between instances, such as from a test environment to a production environment.

Export data from the source PolarDB-X 1.0 instance to dump.sql. Use the same export command as in Scenario 1, Step 1, with the source instance's connection details.
Import dump.sql into the destination PolarDB-X 1.0 instance. Follow Scenario 1, Step 3.
Re-create sequences in the destination instance. mysqldump does not export PolarDB-X 1.0 sequences. If the source instance uses sequences and the destination instance must use the same ones, re-create them manually:
1. On the source instance, list all sequences:
```
SHOW SEQUENCES;
```
2. On the destination instance, create each sequence with the same name as in the source:
```
CREATE SEQUENCE <sequence_name>;
```

Scenario 3: Export from PolarDB-X 1.0 and import into MySQL

Step 1: Export data from PolarDB-X 1.0

Run the export command from Scenario 1, Step 1 using the PolarDB-X 1.0 instance's connection details.

Step 2: Remove PolarDB-X-specific DDL keywords

The CREATE TABLE statements exported from PolarDB-X 1.0 include partitioning keywords that MySQL does not support. Before importing, remove the following keywords from dump.sql:

DBPARTITION BY hash(<partition_key>)
TBPARTITION BY hash(<partition_key>)
TBPARTITIONS <N>
BROADCAST

Example: The following exported statement contains PolarDB-X 1.0 partitioning syntax:

CREATE TABLE multi_db_single_tbl
(id int,
name varchar(30),
primary key(id)) dbpartition by hash(id);

Modify it to a MySQL-compatible statement:

CREATE TABLE multi_db_single_tbl
(id int,
name varchar(30),
primary key(id));

Step 3: Import the modified file into MySQL

Follow Scenario 1, Step 3, replacing the PolarDB-X 1.0 connection details with your MySQL database connection details.

What's next

CREATE TABLE — PolarDB-X 1.0 DDL syntax reference for setting up sharding
Migrate data from a self-managed MySQL database to a PolarDB-X instance — for datasets larger than 10 million rows or real-time migration