Import and export data by using mysqldump

Usage notes

• For more information about the mysqldump command, see the official MySQL documentation.
• mysqldump is suitable for scenarios in which a small amount of data is imported or exported offline. A small amount of data is fewer than 10 million rows of data. For more information about how to migrate large amounts of data or migrate data in real time, see Migrate data from a self-managed MySQL database to a PolarDB-X instance.

Scenario 1: Export data from a MySQL database to a text file

1. Export data from a MySQL database to a text file.
   Run the following command to export table schemas and data from a MySQL database. In this example, the exported file is 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 table3...] > dump.sql

   Option	Description	Required

   Table 1. Options

   Option	Description	Required
   ip	The IP address of the MySQL database.	✔️
   port	The port of the MySQL database.	❌
   user	The username of the account that is used to connect to the MySQL database.	✔️
   password	The password of the account that is used to connect to the MySQL database. Take note that the specified password must follow -p and that no spaces are allowed between -p and the specified password.	✔️
   char-set	The character set.	✔️
   --hex-blob	Exports binary string fields in the hexadecimal format. If you want to export binary string fields, you must use this option. The binary string fields can be of the BINARY, VARBINARY, or BLOB data type.	❌
   --no-data	Does not export data.	❌
   table	Specifies the tables that you want to export. You can specify one or more tables. If you do not use this option, the command exports all tables from the specified database.	❌

2. Modify the CREATE TABLE statements.

   The text file exported from the MySQL database contains a CREATE TABLE statement for each table. If you execute these CREATE TABLE statements in PolarDB-X 1.0, non-partitioned tables are created in PolarDB-X 1.0. If you want to implement sharding, you must manually modify the CREATE TABLE statement. For more information about the syntax of the CREATE TABLE statement in PolarDB-X 1.0, see CREATE TABLE.

3. Use a text file to import data into a PolarDB-X 1.0 database. You can use one of the following methods to import data from a file into a PolarDB-X 1.0 database:
   • Run the mysql -h ip -P port -u user --default-character-set=char-set command to log on to the PolarDB-X 1.0 database. Then, run the source /yourpath/dump.sql command to import data from the file into the PolarDB-X 1.0 database.
     Note

     If you use the first method, the procedure to import data from the file is returned on your screen. This method consumes more time, but allows you to monitor the procedure.
   • Run the mysql -h ip -P port -u user --default-character-set=char-set< /yourpath/dump.sql command to import data from the file into the PolarDB-X 1.0 database.

   Note

   • In the preceding commands, you must set default-character-set to the character set that you use. If you run one of the preceding commands in Microsoft Windows, you must escape the delimiters of the file path.
   • When the file is imported, an error message may be returned because differences exist in the implementation of PolarDB-X 1.0 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. The correctness of the imported data is not affected if an error message of this type is returned.

Scenario 2: Import data from one PolarDB-X 1.0 database into another PolarDB-X 1.0 database

For example, you have a PolarDB-X 1.0 database in your test environment and want to import the table schemas and data of the database into a PolarDB-X 1.0database in your production environment. You can perform the following steps:

1. Export data from the source PolarDB-X 1.0 database to a text file.
2. Import data from the text file into the destination PolarDB-X 1.0 database. See Step 3 in Scenario 1.
3. Create sequence objects.
   mysqldump does not export the sequence objects of PolarDB-X 1.0 databases. If a sequence object is used in the source PolarDB-X 1.0 database and you want to use the same sequence object in the destination PolarDB-X 1.0 database, you must create the sequence object in the destination PolarDB-X 1.0 database. The sequence object in the destination database must be named the same as the sequence object in the source database. Perform the following steps:

   1. Execute the SHOW SEQUENCES statement on the source PolarDB-X 1.0 database to list the sequence objects in the source PolarDB-X 1.0 database.
   2. Execute the CREATE SEQUENCE statement to create the sequence objects in the destination PolarDB-X 1.0 database.

Scenario 3: Export data from a PolarDB-X 1.0 database to a MySQL database

The procedure to export data from a PolarDB-X 1.0 database to a MySQL database is similar to the procedure to migrate data between PolarDB-X 1.0 databases. To export data from a PolarDB-X database to a MySQL database, perform the following steps:

1. Export the table schemas and data from the PolarDB-X 1.0 database.
   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 table3...] > dump.sql

   Note

   Specify the options of the PolarDB-X 1.0 database and run the preceding command.
2. Modify the data definition language (DDL) statement of partitioned tables.
   The CREATE TABLE statement of partitioned tables in PolarDB-X 1.0 is not compatible with MySQL. To import the data into the MySQL database, you must modify the exported SQL file and delete the following keywords:

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

   The following code is the exported CREATE TABLE statement for a partitioned table:

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

   Modify the code to the following statement:

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

3. Import the modified text file. See Step 3 in Scenario 1.