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.

Note

  • 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

  1. 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
    IP The IP address of the PolarDB-X 1.0 instance. ✔️
    port The port of the PolarDB-X 1.0 instance.
    user The username of the PolarDB-X 1.0 instance. ✔️
    password The password of the PolarDB-X 1.0 instance. Note that the password is preceded by -p and does not contain a space after -p. ✔️
    char-set The specified encoding type. ✔️
    --hex-blob Exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.
    --no-data Do not export data.
    table Specifies a table to export. By default, all the tables in the database are exported.
  2. 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.
  3. Import data file to the PolarDB-X 1.0 database in either of the following ways.
    • Execute mysql -h ip -P port -u user --default-character-set=char-set to log on to the target PolarDB-X 1.0 database. Run source /yourpath/dump.sql to 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.
    • Run mysql -h ip -P port -u user --default-character-set=char-set< /yourpath/dump.sql to import data to the target PolarDB-X 1.0 database.
      Note
      • In the above two commands, default-character-set must 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.

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:

  1. Export data from the source PolarDB-X 1.0 database to a text file. For more information, see Step 1 of Use case 1.
  2. Import the data file to the target PolarDB-X 1.0 database. For more information, see Step 3 of Use case 1.
  3. 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:

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

  1. Export the table schema and data from the source PolarDB-X 1.0 database. For more information, see Step 1 of Use case 1.
  2. 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
    • BROADCAST

    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));
  3. Import the modified file. For more information, see Step 3 of Use case 1.