PolarDB-X 1.0 allows you to export data through mysqldump of MySQL. This topic describes the common use cases and related precautions regarding the typical scenarios of importing data to and exporting data from PolarDB-X 1.0 databases.
- For more information about the mysqldump command, see the MySQL official documentation.
- Mysqldump is suitable for importing and exporting data in small volumes (less than 10 million data records) offline.
Use case 1: Import data to a PolarDB-X 1.0 database from a MySQL database
- Export data from a MySQL database to a text file. Enter the following command to export
the table schema and data from the MySQL database. Assume that data is exported to
the file dump.sql.
mysqldump -h ip -P port -u user -ppassword --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 table3...] > dump.sql
The parameters are listed in the following table. Set the parameters according to the actual situation.
Parameter Description Required
The IP address of the PolarDB-X 1.0 instance. ✔️
The port of the PolarDB-X 1.0 instance. ❌
The username of the PolarDB-X 1.0 instance. ✔️
The password of the PolarDB-X 1.0 instance. Note that the password is preceded by
-pand does not contain a space after
The specified encoding type. ✔️
Exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB. ❌
Do not export data. ❌
Specifies a table to export. By default, all the tables in the database are exported. ❌
- Modify the table creation statement. The data file exported from the MySQL database contains the table creation statement for each table. If you execute these table creation statements directly in the PolarDB-X 1.0 database, you will create a single table in the PolarDB-X 1.0 database. If you want to partition a table to database shards and table shards, you must modify the table creation statement.
- Import data file to the PolarDB-X 1.0 database in either of the following ways.
mysql -h ip -P port -u user --default-character-set=char-setto log on to the target PolarDB-X 1.0 database. Run
source /yourpath/dump.sqlto import data to the target PolarDB-X 1.0 database.Note If you use the first method, all the steps appear on the screen, and the process is relatively slow, but you can view the import progress.
mysql -h ip -P port -u user --default-character-set=char-set< /yourpath/dump.sqlto import data to the target PolarDB-X 1.0 database.Note
- In the above two commands,
default-character-setmust be set to the actual data encoding format. In Windows, the separators in the file path specified by the SOURCE command must be converted to escape characters.
- During the import process, due to the implementation differences of some PolarDB-X 1.0 and MySQL databases, you might receive errors such as
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 imported data.
- In the above two commands,
Use case 2: Import data to a PolarDB-X 1.0 database from another PolarDB-X 1.0 database
Assume that you have a PolarDB-X 1.0 database in the test environment. After you complete the testing, you want to import the table schema and data generated during the testing process to a PolarDB-X 1.0 database in the production environment. Perform the following steps:
- Export data from the source PolarDB-X 1.0 database to a text file. For more information, see Step 1 of Use case 1.
- Import the data file to the target PolarDB-X 1.0 database. For more information, see Step 3 of Use case 1.
- Create a Sequence object manually.
mysqldump does not support exporting Sequence objects from PolarDB-X 1.0 databases. If the source PolarDB-X 1.0 database uses a Sequence object and you want to use the same Sequence object in the target PolarDB-X 1.0 database, you must create a Sequence object under the same name with the Sequence object in the target PolarDB-X 1.0. Perform the following steps:
- Execute the SHOW SEQUENCES command in the source PolarDB-X 1.0 database to obtain the status of the Sequence object in the current PolarDB-X 1.0 database.
- Run the CREATE SEQUENCE command in the target PolarDB-X 1.0 database to create a Sequence object.
Use case 3: Export data from a PolarDB-X 1.0 database to a MySQL database
The procedure of exporting data from a PolarDB-X 1.0 database to a MySQL database is similar to the process of importing data between different PolarDB-X 1.0 databases.
- Export the table schema and data from the source PolarDB-X 1.0 database. For more information, see Step 1 of Use case 1.
- Modify the DDL statement for shard table manually.
The Table creation statement of the shard table in PolarDB-X 1.0 is not compatible with MySQL. To import data to MySQL, you must manually modify the exported SQL file and delete the following keywords:
- DBPARTITION BY hash(partition_key):
- TBPARTITION BY hash(partition_key):
- TBPARTITIONS N
See the following example for the statement of exporting a shard table:
CREATE TABLE multi_db_single_tbl (id int, name varchar(30), primary key(id)) dbpartition by hash(id);
Modify the statement as follows:
CREATE TABLE multi_db_single_tbl (id int, name varchar(30), primary key(id));
- Import the modified file. For more information, see Step 3 of Use case 1.