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 thedryRun
andocimds
parameters set to true; for example:MySQL>JS> util.dumpSchemas(["tpch"], "tpchdump", {dryRun: true, ocimds: true})
where
tpch
is the schema name andtpchdump
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:
-
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.
-
Export the schema using the
dumpSchemas()
utility with any required MySQL Shell options andcompatibility
option modifiers. This command uses theocimds
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
-
/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 totrue
, 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.