All Products
Search
Document Center

Import and export data through mysqldump

Last Updated: Aug 15, 2020

Distributed Relational Database Service (DRDS) 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 DRDS 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 DRDS database from a MySQL database

Follow these steps to import data to a DRDS 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.

    1. 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 DRDS instance. ✔️
    port The port of the DRDS instance.
    user The username of the DRDS instance. ✔️
    password The password of the DRDS 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 DRDS database, you will create a single table in the DRDS 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 DRDS database in either of the following ways.

    In the following 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.

  • Execute mysql -h ip -P port -u user --default-character-set=char-set to log on to the target DRDS database. Run source /yourpath/dump.sql to import data to the target DRDS database. 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 DRDS database.

    During the import process, due to the implementation differences of some DRDS 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 DRDS database from another DRDS database

Assume that you have a DRDS 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 DRDS database in the production environment. Perform the following steps:

  1. Export data from the source DRDS database to a text file. For more information, see Step 1 of Use case 1.

  2. Import the data file to the target DRDS 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 DRDS databases. If the source DRDS database uses a Sequence object and you want to use the same Sequence object in the target DRDS database, you must create a Sequence object under the same name with the Sequence object in the target DRDS. Perform the following steps:

Execute the SHOW SEQUENCES command in the source DRDS database to obtain the status of the Sequence object in the current DRDS database.

Run the CREATE SEQUENCE command in the target DRDS database to create a Sequence object.

Use case 3: Export data from a DRDS database to a MySQL database

The procedure of exporting data from a DRDS database to a MySQL database is similar to the process of importing data between different DRDS databases.

  1. Export the table schema and data from the source DRDS 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 DRDS 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:

  1. CREATE TABLE multi_db_single_tbl
  2. (id int,
  3. name varchar(30),
  4. primary key(id)) dbpartition by hash(id);

Modify the statement as follows:

  1. CREATE TABLE multi_db_single_tbl
  2. (id int,
  3. name varchar(30),
  4. primary key(id));
  1. Import the modified file. For more information, see Step 3 of Use case 1.