MySQL Shell 8.0 (part of MySQL 8.0)
MySQL Shell's instance dump utility
util.dumpInstance()
and schema dump utility
util.dumpSchemas()
, introduced in MySQL Shell
8.0.21, support the export of all schemas or a selected schema
from an on-premise MySQL instance into an Oracle Cloud
Infrastructure Object Storage bucket or a set of local files. The
table dump utility util.dumpTables()
,
introduced in MySQL Shell 8.0.22, supports the same operations
for a selection of tables or views from a schema. The exported
items can then be imported into a MySQL Database Service DB System
(a MySQL DB System, for short) or a MySQL Server instance using
MySQL Shell's Section 8.6, “Dump Loading Utility”
util.loadDump()
.
MySQL Shell's instance dump utility, schema dump utility, and table dump utility provide Oracle Cloud Infrastructure Object Storage streaming, MySQL Database Service compatibility checks and modifications, parallel dumping with multiple threads, and file compression, which are not provided by mysqldump. Progress information is displayed during the dump. You can carry out a dry run with your chosen set of dump options to show information about what actions would be performed, what items would be dumped, and (for the instance dump utility and schema dump utility) what MySQL Database Service compatibility issues would need to be fixed, when you run the utility for real with those options.
When choosing a destination for the dump files, note that for import into a MySQL DB System, the MySQL Shell instance where you run the dump loading utility must be installed on an Oracle Cloud Infrastructure Compute instance that has access to the MySQL DB System. If you dump the instance, schema, or tables to an Object Storage bucket, you can access the Object Storage bucket from the Compute instance. If you create the dump files on your local system, you need to transfer them to the Oracle Cloud Infrastructure Compute instance using using the copy utility of your choice, depending on the operating system you chose for your Compute instance.
The dumps created by MySQL Shell's instance dump utility, schema
dump utility, and table dump utility comprise DDL files specifying
the schema structure, and tab-separated .tsv
files containing the data. You can also choose to produce the DDL
files only or the data files only, if you want to set up the
exported schema as a separate exercise from populating it with the
exported data. You can choose whether or not to lock the instance
for backup during the dump for data consistency. By default, the
dump utilities chunk table data into multiple data files and
compress the files.
If you need to dump the majority of the schemas in a MySQL
instance, as an alternative strategy, you can use the instance
dump utility rather than the schema dump utility, and specify the
excludeSchemas
option to list those schemas
that are not to be dumped. Similarly, if you need to dump the
majority of the tables in a schema, you can use the schema dump
utility with the excludeTables
option rather
than the table dump utility. The
information_schema
, mysql
,
ndbinfo
, performance_schema
,
and sys
schemas are always excluded from an
instance dump. The data for the
mysql.apply_status
,
mysql.general_log
,
mysql.schema
, and
mysql.slow_log
tables is always excluded from a
schema dump, although their DDL statements are included. You can
also choose to include or exclude users and their roles and
grants, events, routines, and triggers.
By default, the time zone is standardized to UTC in all the
timestamp data in the dump output, which facilitates moving data
between servers with different time zones and handling data that
has multiple time zones. You can use the tzUtc:
false
option to keep the original timestamps if
preferred.
From MySQL Shell 8.0.22, when you export instances or schemas to
an Oracle Cloud Infrastructure Object Storage bucket, during the
dump you can generate a pre-authenticated request URL for every
item. The user account that runs MySQL Shell's dump loading
utility util.loadDump()
uses these to load the
dump files without additional access permissions. By default, if
the ocimds
option is set to
true
and an Object Storage bucket name is
supplied using the osBucketName
option,
MySQL Shell's instance dump utility and schema dump utility
generate pre-authenticated request URLs for the dump files and
list them in a single manifest file. The dump loading utility
references the manifest file to obtain the URLs and load the dump
files. For instructions to generate or deactivate
pre-authenticated request URLs, see the description for the
ociParManifest
option.
The following requirements apply to dumps using the instance dump utility, schema dump utility, and table dump utility:
MySQL 5.7 or later is required for both the source MySQL instance and the destination MySQL instance.
Object names in the instance or schema must be in the
latin1
or utf8
characterset.
Data consistency is guaranteed only for tables that use the
InnoDB
storage engine.
The minimum required set of privileges that the user account
used to run the utility must have on all the schemas involved
is as follows: BACKUP_ADMIN
,
EVENT
,
RELOAD
,
SELECT
,
SHOW VIEW
, and
TRIGGER
. If the
consistent
option is set to
false
, the
BACKUP_ADMIN
and
RELOAD
privileges are not
required. If the consistent
option is set
to true
, which is the default, the
LOCK TABLES
privilege on all
dumped tables can substitute for the
RELOAD
privilege if the latter
is not available.
The upload method used to transfer files to an Oracle Cloud Infrastructure Object Storage bucket has a file size limit of 1.2 TiB. In MySQL Shell 8.0.21, the multipart size setting means that the numeric limit on multiple file parts applies first, creating a limit of approximately 640 GB. From MySQL Shell 8.0.22, the multipart size setting has been changed to allow the full file size limit.
The utilities convert columns with data types that are not
safe to be stored in text form (such as
BLOB
) to Base64. The size of these columns
therefore must not exceed approximately 0.74 times the value
of the max_allowed_packet
system variable (in bytes) that is configured on the target
MySQL instance.
For the table dump utility, exported views and triggers must not use qualified names to reference other views or tables.
For import into a MySQL DB System, set the
ocimds
option to true
,
to ensure compatibility with MySQL Database Service.
For compatibility with MySQL Database Service, all tables must
use the InnoDB
storage engine.
The ocimds
option checks for any exceptions
found in the dump, and the compatibility
option alters the dump files to replace other storage engines
with InnoDB
.
For the instance dump utility and schema dump utility, for
compatibility with MySQL Database Service, all tables in the
instance or schema must be located in the MySQL data directory
and must use the default schema encryption. The
ocimds
option alters the dump files to
apply these requirements.
A number of other security related restrictions and
requirements apply to items such as tablespaces and privileges
for compatibility with MySQL Database Service. The
ocimds
option checks for any exceptions
found during the dump, and the
compatibility
option automatically alters
the dump files to resolve some of the compatibility issues.
You might need (or prefer) to make some changes manually. For
more details, see the description for the
compatibility
option.
The instance dump utility, schema dump utility, and table dump utility use the MySQL Shell global session to obtain the connection details of the target MySQL server from which the export is carried out. You must open the global session (which can have an X Protocol connection or a classic MySQL protocol connection) before running one of the utilities. The utilities open their own sessions for each thread, copying options such as connection compression and SSL options from the global session, and do not make any further use of the global session.
In the MySQL Shell API, the instance dump utility, schema dump
utility, and table dump utility are functions of the
util
global object, and have the following
signatures:
util.dumpInstance(outputUrl[, options]) util.dumpSchemas(schemas, outputUrl[, options]) util.dumpTables(schema, tables, outputUrl[, options])
For the schema dump utility, schemas
specifies
a list of one or more schemas to be dumped from the MySQL
instance.
For the table dump utility, schema
specifies
the schema that contains the items to be dumped, and
tables
is an array of strings specifying the
tables or views to be dumped. From MySQL Shell 8.0.23, the table
dump includes the information required to set up the specified
schema in the target MySQL instance, although it can be loaded
into an alternative target schema by using the dump loading
utility's schema
option. In MySQL Shell
8.0.22, schema information is not included, so the dump files
produced by this utility must be loaded into an existing target
schema.
If you are dumping to the local filesystem,
outputUrl
is a string specifying the path to a
local directory where the dump files are to be placed. You can
specify an absolute path or a path relative to the current working
directory. You can prefix a local directory path with the
file://
schema. In this example, the connected
MySQL instance is dumped to a local directory, with some
modifications made in the dump files for compatibility with MySQL
Database Service. The user first carries out a dry run to inspect
the schemas and view the compatibility issues, then runs the dump
with the appropriate compatibility options applied to remove the
issues:
shell-js> util.dumpInstance("C:/Users/hanna/worlddump", {dryRun: true, ocimds: true}) Checking for compatibility with MySQL Database Service 8.0.21 ... Compatibility issues with MySQL Database Service 8.0.21 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL. Util.dumpInstance: Compatibility issues were found (RuntimeError) shell-js> util.dumpInstance("C:/Users/hanna/worlddump", { > ocimds: true, compatibility: ["strip_definers", "strip_restricted_grants"]})
The target directory must be empty before the export takes place.
If the directory does not yet exist in its parent directory, the
utility creates it. For an export to a local directory, the
directories created during the dump are created with the access
permissions rwxr-x---
, and the files are
created with the access permissions rw-r-----
(on operating systems where these are supported). The owner of the
files and directories is the user account that is running
MySQL Shell.
The table dump utility can be used to select individual tables
from a schema, for example if you want to transfer tables between
schemas. In this example in MySQL Shell's JavaScript mode, the
tables employees
and
salaries
from the hr
schema
are exported to the local directory emp
, which
the utility creates in the current working directory:
shell-js> util.dumpTables("hr", [ "employees", "salaries" ], "emp")
If you are dumping to an Oracle Cloud Infrastructure Object
Storage bucket, outputUrl
is a path that will
be used to prefix the dump files in the bucket, to simulate a
directory structure. Use the osBucketName
option to provide the name of the Object Storage bucket, and the
osNamespace
option to identify the namespace
for the bucket. In this example in MySQL Shell's Python mode, the
user dumps the world
schema from the connected
MySQL instance to an Object Storage bucket, with the same
compatibility modifications as in the previous example:
shell-py> util.dump_schemas(["world"], "worlddump", { > "osBucketName": "hanna-bucket", "osNamespace": "idx28w1ckztq", > "ocimds": "true", "compatibility": ["strip_definers", "strip_restricted_grants"]})
In the Object Storage bucket, the dump files all appear with the
prefix worlddump
, for example:
worlddump/@.done.json worlddump/@.json worlddump/@.post.sql worlddump/@.sql worlddump/world.json worlddump/world.sql worlddump/world@city.json worlddump/world@city.sql worlddump/world@city@@0.tsv.zst worlddump/world@city@@0.tsv.zst.idx ...
The namespace for an Object Storage bucket is displayed in the
Bucket Information tab of the bucket details
page in the Oracle Cloud Infrastructure console, or can be
obtained using the Oracle Cloud Infrastructure command line
interface. A connection is established to the Object Storage
bucket using the default profile in the default Oracle Cloud
Infrastructure CLI configuration file, or alternative details that
you specify using the ociConfigFile
and
ociProfile
options. For instructions to set up
a CLI configuration file, see
SDK
and CLI Configuration File
options
is a dictionary of options that can be
omitted if it is empty. The following options are available for
the instance dump utility, the schema dump utility, and the table
dump utility, unless otherwise indicated:
dryRun: [ true | false ]
Display information about what would be dumped with the
specified set of options, and about the results of MySQL
Database Service compatibility checks (if the
ocimds
option is specified), but do not
proceed with the dump. Setting this option enables you to
list out all of the compatibility issues before starting the
dump. The default is false
.
osBucketName:
"string
"
The name of the Oracle Cloud Infrastructure Object Storage
bucket to which the dump is to be written. By default, the
[DEFAULT]
profile in the Oracle Cloud
Infrastructure CLI configuration file located at
~/.oci/config
is used to establish a
connection to the bucket. You can substitute an alternative
profile to be used for the connection with the
ociConfigFile
and
ociProfile
options. For instructions to
set up a CLI configuration file, see
SDK
and CLI Configuration File.
osNamespace:
"string
"
The Oracle Cloud Infrastructure namespace where the Object
Storage bucket named by osBucketName
is
located. The namespace for an Object Storage bucket is
displayed in the Bucket Information tab of the bucket
details page in the Oracle Cloud Infrastructure console, or
can be obtained using the Oracle Cloud Infrastructure
command line interface.
ociConfigFile:
"string
"
An Oracle Cloud Infrastructure CLI configuration file that
contains the profile to use for the connection, instead of
the one in the default location
~/.oci/config
.
ociProfile:
"string
"
The profile name of the Oracle Cloud Infrastructure profile
to use for the connection, instead of the
[DEFAULT]
profile in the Oracle Cloud
Infrastructure CLI configuration file used for the
connection.
threads: int
The number of parallel threads to use to dump chunks of data from the MySQL instance. Each thread has its own connection to the MySQL instance. The default is 4.
maxRate:
"string
"
The maximum number of bytes per second per thread for data
read throughput during the dump. The unit suffixes
k
for kilobytes, M
for
megabytes, and G
for gigabytes can be
used (for example, setting 100M
limits
throughput to 100 megabytes per second per thread). Setting
0
(which is the default value), or
setting the option to an empty string, means no limit is
set.
showProgress: [ true | false ]
Display (true
) or hide
(false
) progress information for the
dump. The default is true
if
stdout
is a terminal
(tty
), such as when MySQL Shell is in
interactive mode, and false
otherwise.
The progress information includes the estimated total number
of rows to be dumped, the number of rows dumped so far, the
percentage complete, and the throughput in rows and bytes
per second.
compression:
"string
"
The compression type to use when writing data files for the
dump. The default is to use zstd compression
(zstd
). The alternatives are to use gzip
compression (gzip
) or no compression
(none
).
excludeSchemas: array of
strings
(Instance dump utility
only) Exclude the named schemas from the dump.
Note that the information_schema
,
mysql
, ndbinfo
,
performance_schema
, and
sys
schemas are always excluded from an
instance dump. If a named schema does not exist or is
excluded anyway, the utility ignores the item.
excludeTables: array of
strings
(Instance dump utility
and schema dump utility only) Exclude the named
tables from the dump. Table names must be qualified with a
valid schema name, and quoted with the backtick character if
needed. Note that the data for the
mysql.apply_status
,
mysql.general_log
,
mysql.schema
, and mysql.slow_log
tables
is always excluded from a schema dump,
although their DDL statements are included. Tables named by
the excludeTables
option do not have DDL
files or data files in the dump. If a named table does not
exist in the schema or the schema is not included in the
dump, the utility ignores the item.
all: [ true | false ]
(Table dump utility only) Setting this option to
true
includes all views and tables from
the specified schema in the dump. When you use this option,
set the tables
parameter to an empty
array. The default is false
.
users: [ true | false ]
(Instance dump utility
only) Include (true
) or
exclude (false
) users and their roles and
grants in the dump. The default is true
,
so users are included by default. The schema dump utility
and table dump utility do not include users, roles, and
grants in a dump. From MySQL Shell 8.0.22, you can use the
excludeUsers
or
includeUsers
option to specify individual
user accounts to be excluded or included in the dump files.
These options can also be used with MySQL Shell's dump
loading utility util.loadDump()
to
exclude or include individual user accounts at the point of
import, depending on the requirements of the target MySQL
instance.
In MySQL Shell 8.0.21, attempting to import users to a
MySQL DB System causes the import to fail if the
root
user account or another
restricted user account name is present in the dump
files, so the import of users to a MySQL DB System is
not supported in that release.
excludeUsers: array of
strings
(Instance dump
utility only) Exclude the named user accounts
from the dump files. This option is available from
MySQL Shell 8.0.22, and you can use it to exclude user
accounts that are not accepted for import to a MySQL DB
System, or that already exist or are not wanted on the
target MySQL instance. Specify each user account string in
the format
"'
for an account that is defined with a user name and host
name, or
user_name
'@'host_name
'""'
for an account that is defined with a user name only (which
is equivalent to
user_name
'""'
).
If a named user account does not exist, the utility ignores
the item.
user_name
'@'%
'"
includeUsers: array of
strings
(Instance dump utility
only) Include only the named user accounts in the
dump files. Specify each user account string as for the
excludeUsers
option. This option is
available from MySQL Shell 8.0.22, and you can use it as an
alternative to excludeUsers
if only a few
user accounts are required in the dump. You can also specify
both options, in which case a user account matched by both
an includeUsers
string and an
excludeUsers
string is excluded.
events: [ true | false ]
(Instance dump utility and schema dump
utility only) Include (true
)
or exclude (false
) events for each schema
in the dump. The default is true
.
routines: [ true | false ]
(Instance dump utility and schema dump
utility only) Include (true
)
or exclude (false
) functions and stored
procedures for each schema in the dump. The default is
true
. Note that user-defined functions
are not included, even when routines
is
set to true
.
triggers: [ true | false ]
Include (true
) or exclude
(false
) triggers for each table in the
dump. The default is true
.
defaultCharacterSet:
"string
"
The character set to be used during the session connections
that are opened by MySQL Shell to the server for the dump.
The default is utf8mb4
. The session value
of the system variables
character_set_client
,
character_set_connection
,
and character_set_results
are set to this value for each connection. The character set
must be permitted by the
character_set_client
system
variable and supported by the MySQL instance.
tzUtc: [ true | false ]
Include a statement at the start of the dump to set the time
zone to UTC. All timestamp data in the dump output is
converted to this time zone. The default is
true
, so timestamp data is converted by
default. Setting the time zone to UTC facilitates moving
data between servers with different time zones, or handling
a set of data that has multiple time zones. Set this option
to false
to keep the original timestamps
if preferred.
consistent: [ true | false ]
Enable (true
) or disable
(false
) consistent data dumps by locking
the instance for backup during the dump. The default is
true
. When true
is
set, the utility sets a global read lock using the
FLUSH TABLES WITH READ LOCK
statement.
The transaction for each thread is started using the
statements SET SESSION TRANSACTION ISOLATION LEVEL
REPEATABLE READ
and START TRANSACTION
WITH CONSISTENT SNAPSHOT
. When all threads have
started their transactions, the instance is locked for
backup and the global read lock is released.
ddlOnly: [ true | false ]
Setting this option to true
includes only
the DDL files for the dumped items in the dump, and does not
dump the data. The default is false
.
dataOnly: [ true | false ]
Setting this option to true
includes only
the data files for the dumped items in the dump, and does
not include DDL files. The default is
false
.
chunking: [ true | false ]
Enable (true
) or disable
(false
) chunking for table data, which
splits the data for each table into multiple files. The
default is true
, so chunking is enabled
by default. Use bytesPerChunk
to specify
the chunk size. In order to chunk table data into separate
files, a primary key or unique index must be defined for the
table, which the utility uses to select an index column to
order and chunk the data. If a table does not contain either
of these, a warning is displayed and the table data is
written to a single file. If you set the chunking option to
false
, chunking does not take place and
the utility creates one data file for each table.
bytesPerChunk:
"string
"
Sets the approximate number of bytes to be written to each
data file when chunking is enabled. The unit suffixes
k
for kilobytes, M
for
megabytes, and G
for gigabytes can be
used. The default is 64 MB (64M
) from
MySQL Shell 8.0.22 (32 MB in MySQL Shell 8.0.21), and the
minimum is 128 KB (128k
). Specifying this
option sets chunking
to
true
implicitly. The utility aims to
chunk the data for each table into files each containing
this amount of data before compression is applied. The chunk
size is an average and is calculated based on table
statistics and explain plan estimates.
ocimds: [ true | false ]
Setting this option to true
enables
checks and modifications for compatibility with MySQL
Database Service. The default is false
.
From MySQL Shell 8.0.23, this option is available for all
the utilities, and before that release, it is only available
for the instance dump utility and schema dump utility.
When this option is set to true
,
DATA DICTIONARY
, INDEX
DICTIONARY
, and ENCRYPTION
options in CREATE TABLE
statements are commented out in the DDL files, to ensure
that all tables are located in the MySQL data directory and
use the default schema encryption. Checks are carried out
for any storage engines in
CREATE TABLE
statements other than
InnoDB
, for grants
of unsuitable privileges to users or roles, and for other
compatibility issues. If any non-conforming SQL statement is
found, an exception is raised and the dump is halted. Use
the dryRun
option to list out all of the
issues with the items in the dump before the dumping process
is started. Use the compatibility
option
to automatically fix the issues in the dump output.
From MySQL Shell 8.0.22, when this option is set to
true
and an Object Storage bucket name is
supplied using the osBucketName
option,
the ociParManifest
option also defaults
to true
, meaning that pre-authenticated
requests are generated for every item in the dump, and the
dump files can only be accessed using these request URLs.
compatibility: array of
strings
Apply the specified requirements for compatibility with MySQL Database Service for all tables in the dump output, altering the dump files as necessary.From MySQL Shell 8.0.23, this option is available for all the utilities, and before that release, it is only available for the instance dump utility and schema dump utility.
The following modifications can be specified as a comma-separated list:
force_innodb
Change CREATE TABLE
statements to use the
InnoDB
storage engine for
any tables that do not already use it.
skip_invalid_accounts
Remove user accounts created with external authentication plugins that are not supported in MySQL Database Service.
strip_definers
Remove the DEFINER
clause from
views, routines, events, and triggers, so these
objects are created with the default definer (the user
invoking the schema), and change the SQL
SECURITY
clause for views and routines to
specify INVOKER
instead of
DEFINER
. MySQL Database Service
requires special privileges to create these objects
with a definer other than the user loading the schema.
If your security model requires that views and
routines have more privileges than the account
querying or calling them, you must manually modify the
schema before loading it.
strip_restricted_grants
Remove specific privileges that are restricted by
MySQL Database Service from
GRANT
statements, so
users and their roles cannot be given these privileges
(which would cause user creation to fail). From
MySQL Shell 8.0.22, this option also removes
REVOKE
statements for
system schemas (mysql
and
sys
) if the administrative user
account on an Oracle Cloud Infrastructure Compute
instance does not itself have the relevant privileges,
so cannot remove them.
strip_role_admin
Remove the ROLE_ADMIN
privilege from GRANT
statements. This privilege can be restricted by MySQL
Database Service.
strip_tablespaces
Remove the TABLESPACE
clause from
GRANT
statements, so
all tables are created in their default tablespaces.
MySQL Database Service has some restrictions on
tablespaces.
ociParManifest: [ true | false ]
Setting this option to true
generates a
pre-authenticated request for read access (an Object Read
PAR) for every item in the dump, and a manifest file listing
all the pre-authenticated request URLs. The
pre-authenticated requests expire after a week by default,
which you can change using the
ociParExpireTime
option.
This option is available from MySQL Shell 8.0.22, and can
only be used when exporting to an Object Storage bucket (so
with the osBucketName
option set). From
MySQL Shell 8.0.23, this option is available for all the
utilities, and in MySQL Shell 8.0.22, it is only available
for the instance dump utility and schema dump utility.
When the ocimds
option is set to
true
and an Object Storage bucket name is
supplied using the osBucketName
option,
ociParManifest
is set to
true
by default, otherwise it is set to
false
by default.
The user named in the Oracle Cloud Infrastructure profile
that is used for the connection to the Object Storage bucket
(the DEFAULT
user or another user as
named by the ociProfile
option) is the
creator for the pre-authenticated requests. This user must
have PAR_MANAGE
permissions and
appropriate permissions for interacting with the objects in
the bucket, as described in
Using
Pre-Authenticated Requests. If there is an issue
with creating the pre-authenticated request URL for any
object, the associated file is deleted and the dump is
stopped.
To enable the resulting dump files to be loaded, create a
pre-authenticated read request for the manifest file object
(@.manifest.json
) following the
instructions in
Using
Pre-Authenticated Requests. You can do this while
the dump is still in progress if you want to start loading
the dump before it completes. You can create this
pre-authenticated read request using any user account that
has the required permissions. The pre-authenticated request
URL must then be used by the dump loading utility to access
the dump files through the manifest file. The URL is only
displayed at the time of creation, so copy it to durable
storage.
Before using this access method, assess the business requirement for and the security ramifications of pre-authenticated access to a bucket or objects.
A pre-authenticated request URL gives anyone who has the URL access to the targets identified in the request. Carefully manage the distribution of the pre-authenticated URL you create for the manifest file, and of the pre-authenticated URLs for exported items in the manifest file.
ociParExpireTime:
"string
"
The expiry time for the pre-authenticated request URLs that
are generated when the ociParManifest
option is set to true. The default is the current time plus
one week, in UTC format.
This option is available from MySQL Shell 8.0.22. From MySQL Shell 8.0.23, this option is available for all the utilities, and in MySQL Shell 8.0.22, it is only available for the instance dump utility and schema dump utility.
The expiry time must be formatted as an RFC 3339 timestamp,
as required by Oracle Cloud Infrastructure when creating a
pre-authenticated request. The format is
YYYY-MM-DDTHH-MM-SS
immediately followed
by either the letter Z (for UTC time), or the UTC offset for
the local time expressed as [+|-]hh:mm
,
for example
2020-10-01T00:09:51.000+02:00
.
MySQL Shell does not validate the expiry time, but any
formatting error causes the pre-authenticated request
creation to fail for the first file in the dump, which stops
the dump.