Use mysqldump to migrate data

This topic describes the procedures to use mysqldump to migrate data from or to PolarDB-X in common scenarios. mysqldump is an official MySQL utility that is used to export data and is supported by PolarDB-X. For information about the mysqldump command, see the MySQL official documentation.

Note: mysqldump is suitable for scenarios in which a small volume of data is migrated offline. A small volume of data is fewer than 10 million rows of data. If you want to migrate a larger volume of data or migrate data in real time, we recommend that you use Data Transmission Service (DTS).

Background information

You can use mysqldump to export table schemas and data and convert the schemas and data to SQL statements. This helps you import the schemas and data. The following code block describes the syntax of the corresponding SQL statements:

DROP TABLE IF EXISTS `table_name`;
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`);
INSERT INTO `table_name` VALUES (...),(...),...;
INSERT INTO `table_name` VALUES (...),(...),...;
...

The following sample code provides an example on how to run the mysqldump command to export data to an SQL file:

shell> 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

For information about the parameters of the mysqldump command, run the mysqldump --help command or see the MySQL official documentation. The following table describes the common parameters of the mysqldump command. You can configure the parameters based on your business requirements.

You can use one of the following methods to import the data in the SQL file into your destination database:

• Import the data by executing the SOURCE statement

  ## 1.Log on to the specified database.
  shell> mysql -h ip -P port -u user -pPassword --default-character-set=char-set 
  ## 2.Import the data by executing the statements in the source statement execution file.
  mysql> source dump.sql
  

• Import the data by running the MySQL command

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

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

We recommend that you do not export table schemas from a PolarDB-X database to a MySQL database because PolarDB-X supports sharding and the sharding functions that can be used in the CREATE TABLE ( DRDS mode ) statement and cannot be parsed by MySQL. MySQL does not support the following keywords in the sharding functions:

• DBPARTITION BY hash(partition_key)

• TBPARTITION BY hash(partition_key)

• TBPARTITIONS N

• BROADCAST

If you want to export table schemas, you must modify the statements for creating tables in the SQL file. We recommend that you export only data. After the data is exported, log on to the destination database, create a table, and then import the data to the table.

Scenario 1: Import data from a MySQL database into a PolarDB-X database

To import data from a MySQL database into a PolarDB-X database, perform the following steps:

1. Export data from the MySQL database to a file. Run the following command to export the data of a table from the MySQL database to a file. In this example, the file is named dump.sql. We recommend that you do not export the table schema.

   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
   

1. Log on to the PolarDB-X database and create a table. For information about the CREATE TABLE syntax in PolarDB-X, see CREATE TABLE ( DRDS mode ). If you do not set the --no-create-info parameter, dump.sql contains the statements to create tables in the MySQL database. You can modify the statements in dump.sql.

2. Import the file into the PolarDB-X database. You can use one of the following methods to import the file into the PolarDB-X database:

   • Run the mysql -h ip -P port -u user -pPassword --default-character-set=char-set command to log on to the PolarDB-X database. Then, run the source /yourpath/dump.sql command to import the file into the PolarDB-X database.
   • Run the mysql -h ip -P port -u user -pPassword --default-character-set=char-set< /yourpath/dump.sql command to import the file into the PolarDB-X database.

Note:

• 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.
• If you use the first method, the procedure to import the file is returned on your screen. You can monitor the procedures though this method is time-consuming.
• When the file is being imported, an error message may be returned because differences exist in the implementation of 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. 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 database into another PolarDB-X database

For example, you have a PolarDB-X database in your testing environment and want to import the table schemas and data of the database into a PolarDB-X database in your production environment. To migrate data between databases, perform the following steps:

1. Export data from your source PolarDB-X database to a text file. For more information, see Step 1 in scenario 1.

2. Import the text file into the destination PolarDB-X database. For more information, see Step 3 in scenario 1.

3. Create a sequence object. mysqldump does not export the sequence objects of PolarDB-X databases. If a sequence object is used in the source PolarDB-X database and you want to use the same sequence object in the destination PolarDB-X database, you must create the sequence object in the destination PolarDB-X 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 to register the media assets:

   1. Execute the SHOW SEQUENCES statement in the source PolarDB-X database to list sequence objects in the source PolarDB-X database.

   2. Execute the CREATE SEQUENCE statement to create the sequence objects in the destination PolarDB-X database.

For more information about sequence objects, see Sequence.

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

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

1. Export data from your source PolarDB-X database to a text file. For more information, see Step 1 in scenario 1.

2. Log on to your MySQL database and create a destination table. If the exported data includes statements to create tables, you must modify the statements to create tables in the source PolarDB-X database. For example, you must delete the keywords that MySQL does not support in the statements. The following statement provides an example on how a partitioned table in a PolarDB-X database is created:

   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 delete the function that is used to partition the table in the statement. The following code block provides the modified version of the statement that MySQL supports:

   CREATE TABLE `table_name` (
       `id` int(11) NOT NULL,
       `k` int(11) NOT NULL DEFAULT '0',
       ...
   ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

1. Import the text file into the destination PolarDB-X database. For more information, see Step 3 in scenario 1.