Use mysqldump to import and export PolarDB-X data - PolarDB

This topic describes how to use the mysqldump tool to export data from PolarDB-X in different scenarios.

Introduction

PolarDB-X supports mysqldump, an official MySQL data export tool. mysqldump exports table schemas and data and converts them into SQL statements that can be directly imported.

Note

mysqldump is suitable for offline exports of small data volumes, up to 10 million rows. To migrate larger data volumes or perform real-time data migration tasks, use Data Transmission Service (DTS).

Syntax

Run the following command to use the mysqldump tool to export data:

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

To view the descriptions of mysqldump parameters, run the mysqldump --help command or see the MySQL official documentation. The following table describes common parameters.

Parameter	Description
ip	The IP address of the PolarDB-X instance.
port	The port of the PolarDB-X instance.
user	The username for the PolarDB-X database.
password	The password for the PolarDB-X database. Note that the password must immediately follow -p with no space in between.
char-set	The specified encoding.
--hex-blob	Exports binary string fields in hexadecimal format. This option is required if you have binary data. The affected field types include BINARY, VARBINARY, and BLOB.
--no-data	Does not export data.
table	Specifies a table to export. By default, all tables in the database are exported.
--no-create-info	Does not export table creation information.
--net_buffer_length	The size of the transmission buffer. This affects the length of the INSERT statement. The default value is 1046528.

Examples

The following sections describe how to use the mysqldump tool in different scenarios.

Do not export table schemas when you transfer data between PolarDB-X and MySQL. PolarDB-X supports sharding, and its sharding functions in the CREATE TABLE (DRDS mode) statement are not compatible with MySQL. Incompatible keywords include the following:

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

If you export table schemas, you must modify the table creation statements in the exported SQL file to ensure a successful import. We recommend that you export only the data. Then, you can log on to the database, manually create the tables, and import the data.

Scenario 1: Import data from MySQL to PolarDB-X

To import data from MySQL to PolarDB-X, follow these steps.

Export data from MySQL to a file.

Run the following command to export table data from MySQL. We recommend that you do not export the table schema. This example assumes the output file is named dump.sql.

mysqldump -h ip -P port -u user -pPassword --default-character-set=char-set --net_buffer_length=204800 --no-create-db --no-create-info --skip-add-locks --skip-lock-tables --skip-tz-utc --set-charset --hex-blob database [table1 table2 table3...] > dump.sql

Log on to PolarDB-X and manually create the destination table. For more information about the syntax for creating tables in PolarDB-X, see CREATE TABLE (DRDS mode). If you did not add the --no-create-info parameter, the exported dump.sql file contains the table creation statements from MySQL. You must modify the table creation statements in the file to ensure compatibility.
Import the data file into PolarDB-X. You can use one of the following two methods to import the data file into PolarDB-X:
- Log on to the destination PolarDB-X instance by running the mysql -h ip -P port -u user -pPassword --default-character-set=char-set command. Then, run the source /yourpath/dump.sql command to import the data into the destination PolarDB-X instance.
- Directly import the data into the destination PolarDB-X instance by running the mysql -h ip -P port -u user -pPassword --default-character-set=char-set< /yourpath/dump.sql command.
Note
- In both commands, set default-character-set to the actual data encoding. If you are using a Windows platform, you must escape the separators in the file path for the source command.
- The first method displays all steps on the screen. This method is slightly slower but lets you observe the import procedure.
- During the import, an error may occur because of implementation differences between PolarDB-X and MySQL. The error message is 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 type of error does not affect the correctness of the imported data.

Scenario 2: Import from one PolarDB-X to another PolarDB-X

For example, you have a PolarDB-X instance in a test environment. After testing, you need to import some table schemas and data into a production PolarDB-X instance. To do this, follow these steps.

Export data from the source PolarDB-X instance to a text file. For more information, see Step 1 in Scenario 1.
Import the data file into the destination PolarDB-X instance. For more information, see Step 3 in Scenario 1.
Manually create Sequence objects.
mysqldump does not export Sequence objects from PolarDB-X. If you used Sequence objects in the source PolarDB-X instance and need to use them in the destination PolarDB-X instance, you must manually create Sequence objects with the same names in the destination PolarDB-X instance. The steps are as follows:
1. On the source PolarDB-X instance, execute SHOW SEQUENCES to get the status of the Sequence objects in the current PolarDB-X instance.
2. On the destination PolarDB-X database, create new Sequence objects using the CREATE SEQUENCE command.
For more information about Sequence commands, see Sequence.

Scenario 3: Export data from PolarDB-X to MySQL

The procedure to export data from PolarDB-X to MySQL is similar to importing data between PolarDB-X instances and includes the following steps.

Export data from the source PolarDB-X instance to a text file. For more information, see Step 1 in Scenario 1.
Log on to MySQL and manually create the destination table. If the exported data includes table creation statements, you must modify them in the exported file. Delete keywords and other information from the PolarDB-X statements that are incompatible with MySQL.
For example, consider a sharded table in PolarDB-X:
```
CREATE TABLE `table_name` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    ...
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`id`);
```
You must remove the sharding function statement, which is incompatible with MySQL, and change it to:
```
CREATE TABLE `table_name` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    ...
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
```
Import the data file into PolarDB-X. For instructions, see Step 3 in Scenario 1.

FAQ

Problem: mysqldump reports the when trying to dump tablespace error.
Cause: PolarDB-X does not support tablespaces.
Solution: Add the --no-tablespaces parameter to the mysqldump command.
Problem: mysqldump reports the SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-bucket-specified"') FROM information_schema.COLUMN_STATISTICS ... error.
Cause: An error occurs when querying column statistics information because of an incompatible JSON format.
Solution: Add the --column-statistics=0 parameter to the mysqldump command to skip exporting statistics information.
Problem: mysqldump reports the Couldn't execute 'SHOW TRIGGERS ...' error.
Cause: PolarDB-X does not support triggers.
Solution: Add the --skip-triggers parameter to the mysqldump command.
Problem: When you import a table that does not have a primary key, the Duplicate entry '0' for key 'PRIMARY' error is reported.
Cause: In The dump file sets the NO_AUTO_VALUE_ON_ZERO sql_mode, which causes a primary key conflict.
Solution: the NO_AUTO_VALUE_ON_ZERO field from the sql_mode setting in the dump file.
Problem: How can I prevent garbled Chinese characters when exporting data with mysqldump?
Solution: If your instance version is 5.4.19 or later, you can use a privileged account to set COMPATIBLE_CHARSET_VARIABLES to true. Then, run the mysqldump export.
```
SET GLOBAL COMPATIBLE_CHARSET_VARIABLES=true;
```