MySQL Shell 8.0.22: Data Export/Import Utilities Tested with MySQL 8/5.7/5.6

MySQL Shell 8.0.22 data exportMySQL Shell is an advanced client tool that has many features and is easy to use. The recent MySQL release (MySQL 8.0.22) has the utility “exportTable()”, which supports exporting the data into a file from a table using MySQL shell. The exported data file can be imported using the utility “importTable()”, which was released in MySQL 8.0.17.

With “exportTable()”, you can export the data into a local server or in any S3-compliant object storage provider. In this blog, I am going to explain how those exportTable() & importTable() utilities are working in local servers and I also did some tests with MySQL 5.7 and MySQL 5.6.

Overview of exportTable() & importTable()

exportTable():

  • Introduced in MySQL 8.0.22. 
  • The utility is used to export the data from the MySQL table into a data file. 
  • It can be used to export the table to a local server or any S3-compliant object storage provider. 
  • By default, the data will be exported in the default format for MySQL Shell’s table import utility. 
  • It supports CSV and TSV files.
  • Compression is supported with “gzip” or “zstd”.
  • JSON is not supported.

Below is the format of the query that will be used to export the data.

importTable():

  • Introduced in MySQL 8.0.17. 
  • The utility is used to import the data into a MySQL table from a data file.
  • It supports parallelism and a very fast method to import a large data file into the table. 
  • It is similar to LOAD DATA INFILE. 
  • By default, the data will be imported in the default format for the MySQL shell table export utility.
  • The data file will be chunked and processed with different threads. 
  • We can control the number of threads for parallel execution.
  • “local_infile” is the mandatory variable that should be ON on the server. 

 

Below is the format of the query that will be used to import the data.

How to Export the Table Into a File Using util.exportTable()

For testing, I have installed the latest Percona Server for MySQL 8.0.22 and created the table “percona.herc7”. The size of the table is around 2.3G. 

In this section, I am going to address three topics:

  • Default output file structure using util.exportTable()
  • Modified output file structure using util.exportTable() 
  • Compressed output using util.exportTable() 

Default Output File Structure Using util.exportTable()

I am going to export the table “percona.herc7” with the default option so that the utility will create the output file with default structure.

The table export is completed and the output seems pretty good. During the execution, the utility prints the progress, data size, execution time, rows written, throughput, etc.

The output from the data file looks like tab-separated:

Query from processlist: 

Note: Make sure to create the needed directories before executing the command. Otherwise, the command will fail. 

Modified Output File Structure Using util.exportTable()

In this section, I am going to play with the options and create the output file with a comma separated CSV file. I am going to modify the following options to create the CSV file.

  • fieldsOptionallyEnclosed: true, 
  • fieldsTerminatedBy: “,”
  • linesTerminatedBy: “\n”
  • fieldsEnclosedBy: ‘”‘
  • defaultCharacterSet: “utf8”
  • showProgress: true
  • dialect: “csv”