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.
Follow these steps to import data to a DRDS database from a MySQL database:
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
The IP address of the DRDS instance. ✔️
The port of the DRDS instance. ❌
The username of the DRDS instance. ✔️
The password of the DRDS instance. Note that the password is preceded by
-pand does not contain a space after
The specified encoding type. ✔️
Exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB. ❌
Do not export data. ❌
Specifies a table to export. By default, all the tables in the database are exported. ❌
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.
Import data file to the DRDS database in either of the following ways.
In the following two commands,
default-character-setmust 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.
mysql -h ip -P port -u user --default-character-set=char-setto log on to the target DRDS database. Run
source /yourpath/dump.sqlto 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.
mysql -h ip -P port -u user --default-character-set=char-set< /yourpath/dump.sqlto 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.
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:
Export data from the source DRDS database to a text file. For more information, see Step 1 of Use case 1.
Import the data file to the target DRDS database. For more information, see Step 3 of Use case 1.
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.
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.
Export the table schema and data from the source DRDS database. For more information, see Step 1 of Use case 1.
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
See the following example for the statement of exporting a shard table:
CREATE TABLE multi_db_single_tbl
primary key(id)) dbpartition by hash(id);
Modify the statement as follows:
CREATE TABLE multi_db_single_tbl
- Import the modified file. For more information, see Step 3 of Use case 1.