PolarDB:Use mysqldump to migrate data

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:

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.

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

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

2. 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.
3. 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

FAQ

1. Problem: No data is exported when I use the --all-databases parameter to export all databases, but a specified database can be exported.

   Cause: When you use mysqldump to export all databases, mysqldump first tries to find the system database named mysql. The export fails because the system database named mysql does not exist in PolarDB-X.

   Solution: Create an empty database named mysql in PolarDB-X. The empty database mysql is also imported. Avoid using the --all-databases parameter to export all databases at a time. You can specify a database to export.

2. Problem: mysqldump returns the when trying to dump tablespace error.

   Cause: No tablespace is involved in PolarDB-X.

   Solution: Add the --no-tablespaces parameter in mysqldump.

3. Problem: mysqldump returns the SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-bucket-specified"') FROM information_schema.COLUMN_STATISTICS ... error.

   Cause: This error is reported due to the JSON format is incompatible when you query the column statistics.

   Solution: Add the --column-statistics=0 parameter in mysqldump to skip exporting statistics.

4. Problem: mysqldump returns the Couldn't execute 'SHOW TRIGGERS ...' error.

   Cause: Triggers are not supported in PolarDB-X.

   Solution: Add the --skip-triggers parameter in mysqldump.

5. Problem: When you import a table without a primary key, the Duplicate entry '0' for key 'PRIMARY' error is reported.

   Cause: The NO_AUTO_VALUE_ON_ZERO field is set in sql_mode for the dumped file. This causes a primary key conflict.

   Solution: Delete the NO_AUTO_VALUE_ON_ZERO field in sql_mode from the dumped file.