Optimize data import and export

Test environment

CREATE TABLE `sbtest1` (
    `id` int(11) NOT NULL,
    `k` int(11) NOT NULL DEFAULT '0',
    `c` char(120) NOT NULL DEFAULT '',
    `pad` char(60) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `k_1` (`k`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`id`);

Data import and export tools

Common data export methods for PolarDB-X include:

• Exporting data using the mysql -e command

• mysqldump

• Exporting data using the SELECT INTO OUTFILE statement (disabled by default)

• Exporting data using Batch Tool (the dedicated import and export tool for PolarDB-X)

Common data import methods for PolarDB-X include:

• Importing data using the SOURCE statement

• Importing data using the MySQL command

• Importing data using a program

• Importing data using the LOAD DATA statement

• Importing data using Batch Tool (the dedicated import and export tool for PolarDB-X)

Native MySQL commands

mysql -h ip  -P port -u usr -pPassword db_name -N -e "SELECT id,k,c,pad FROM sbtest1;" >/home/data_1000w.txt
## The raw data is tab-separated. Format: 188092293    27267211    59775766593-64673028018-...-09474402685    01705051424-...-54211554755

mysql -h ip  -P port -u usr -pPassword db_name -N -e "SELECT id,k,c,pad FROM sbtest1;" | sed 's/\t/,/g' >/home/data_1000w.csv
## The CSV file is comma-separated. Format: 188092293,27267211,59775766593-64673028018-...-09474402685,01705051424-...-54211554755

LOAD DATA LOCAL INFILE '/home/data_1000w.txt' INTO TABLE sbtest1;
## LOCAL specifies that the data is imported from a local file. The local_infile parameter must be enabled.

Data in CSV format can be imported by a program. For more information, see Use a program to import data.

mysqldump

The mysqldump tool connects to a local or remote server. For detailed instructions, see mysqldump.

• Export data example:

  mysqldump -h ip  -P port -u usr -pPassword --default-character-set=utf8mb4 --net_buffer_length=10240 --no-tablespaces --no-create-db --no-create-info --skip-add-locks --skip-lock-tables --skip-tz-utc --set-charset  --hex-blob db_name [table_name] > /home/dump_1000w.sql
                      

  The following are common issues and solutions for exporting data with mysqldump.

  1. Issue: mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode''

     Solution: Add --set-gtid-purged=OFF to disable gtid_mode.

  2. Issue: mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version''

     Solution: Check whether the versions of mysqldump and MySQL are consistent. Use a MySQL client that has the same version as the MySQL server.

  The exported data is in the format of SQL statements, which are primarily batch INSERT statements such as INSERT INTO `sbtest1` VALUES (...),(...), and the "net_buffer_length" parameter affects the batch size.

• Use the following methods to import data in SQL format:

  Method 1: Use the SOURCE statement to import data
  source /home/dump_1000w.sql
  
  Method 2: Use the mysql command to import data
  mysql -h ip  -P port -u usr -pPassword --default-character-set=utf8mb4 db_name < /home/dump_1000w.sql

Batch Tool

Batch Tool is a data import and export tool developed by Alibaba Cloud that supports multi-threaded operations.

• Export data:

  ## By default, the number of exported files is the same as the number of shards.
  java -jar batch-tool.jar -h ip  -P port -u usr -pPassword -D db_name -o export -t sbtest1 -s ,
  
  ## Export and merge data into a single file.
  java -jar batch-tool.jar -h ip  -P port -u usr -pPassword -D db_name -o export -t sbtest1 -s , -F 1

• Import data:

  ## Import 32 files.
  java -jar batch-tool.jar -hpxc-spryb387va****.polarx.singapore.rds.aliyuncs.com  -P3306 -uroot -pPassword -D sysbench_db -o import -t sbtest1 -s , -f "sbtest1_0;sbtest1_1;sbtest1_2;sbtest1_3;sbtest1_4;sbtest1_5;sbtest1_6;sbtest1_7;sbtest1_8;sbtest1_9;sbtest1_10;sbtest1_11;sbtest1_12;sbtest1_13;sbtest1_14;sbtest1_15;sbtest1_16;sbtest1_17;sbtest1_18;sbtest1_19;sbtest1_20;sbtest1_21;sbtest1_22;sbtest1_23;sbtest1_24;sbtest1_25;sbtest1_26;sbtest1_27;sbtest1_28;sbtest1_29;sbtest1_30;sbtest1_31" -np -pro 64 -con 32
  
  ## Import a single file.
  java -jar batch-tool.jar -h ip  -P port -u usr -p password -D db_name -o import -t sbtest1 -s , -f "sbtest1_0" -np

Export method comparison

The following benchmark exports 10 million rows of data, approximately 2 GB in size, from PolarDB-X.

Import method comparison

The following benchmark imports 10 million rows of data, approximately 2 GB in size, into PolarDB-X. The source data was exported in the previous test.

Conclusion

1. PolarDB-X is compatible with common data import and export methods used in MySQL operations. However, most of these methods are designed for single-node MySQL instances and only support single-threaded operations. They cannot fully utilize the distributed resources of PolarDB-X.

2. PolarDB-X provides Batch Tool, which is ideal for distributed environments. It supports multi-threaded operations and can import and export data extremely fast.