6.3 Exporting Data with MySQL Shell

This task describes how to export data from a source MySQL Server using MySQL Shell.

This task requires the following:

• The MySQL Shell command-line utility. The commands in this task use the JS (JavaScript) execution mode of MySQL Shell. For installation instructions, refer to Installing MySQL Shell.

  Note

  Exports created by MySQL Shell 8.0.27, or higher, cannot be imported by earlier versions of MySQL Shell. Using the latest version of MySQL Shell is always recommended.

• You have run the dumpSchema command with the dryRun and ocimds parameters set to true; for example:

  MySQL>JS> util.dumpSchemas(["tpch"], "tpchdump", {dryRun: true, ocimds: true})

  where tpch is the schema name and tpchdump is a local directory for the dump files.

  This performs a test run of the export, checking for compatibility issues. The compatibility issues and remediation steps are listed in the output. For more information about addressing compatibility issues, see MySQL Shell Instance and Schema Dump Utilities.

When exporting data from a source MySQL Server, you can either export an entire MySQL instance using dumpInstance() or specific schemas using dumpSchemas(). The syntax for each command is:

• util.dumpInstance(outputUrl[, options])

• util.dumpSchemas(schemas, outputUrl[, options])

The following task uses dumpSchemas() with the MySQL Shell compatibility option to export a schema named tpch.

To export a schema from a source MySQL Server:

1. Start a MySQL Shell session and connect to the source MySQL Server; for example:

   $> mysqlsh user@HostNameOfSourceMySQLServer

   For information about different MySQL Shell session and connection options, refer to the MySQL Shell User Guide.

2. Export the schema using the dumpSchemas() utility with any required MySQL Shell options and compatibility option modifiers. This command uses the ocimds option with several compatibility option modifiers.

   MySQL>JS> util.dumpSchemas(["tpch"], "/home/user1/tpchdump", {"ocimds": "true", 
             "compatibility": ["force_innodb", "strip_definers", "strip_tablespaces"]})

   where:

   • tpch is the name of the schema being exported.

   • /home/user1/tpchdump is the location (a local directory) where the schema dump files are written.

   • ocimds checks your data for compatibility with MySQL HeatWave on AWS. If set to true, it is not possible to export data that is incompatible with MySQL HeatWave on AWS.

   • compatibility specifies compatibility modifiers that modify the exported data for compatibility with MySQL HeatWave on AWS.

Note

For large datasets, it is recommended to use the MySQL Shell bytesPerChunk option to define larger chunks. The default chunk size is 32MB. To increase the size of the individual chunks, add the bytesPerChunk option to the command. For example: bytesPerChunk: 128M specifies a chunk size of 128MB.

For more information about dumpInstance and dumpSchemas options, see Instance Dump Utility, Schema Dump Utility, and Table Dump Utility.