MySQL Shell 8.0 (part of MySQL 8.0)
MySQL Shell's dump loading utility
util.loadDump()
, introduced in MySQL Shell
8.0.21, supports the import into a MySQL Database Service DB
System (a MySQL DB System, for short) or a MySQL Server instance
of schemas or tables dumped using MySQL Shell's
Section 8.5, “Instance Dump Utility, Schema Dump Utility, and Table Dump Utility”.
The dump loading utility provides data streaming from remote
storage, parallel loading of tables or table chunks, progress
state tracking, resume and reset capability, and the option of
concurrent loading while the dump is still taking place.
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 the dump files are in an Oracle Cloud Infrastructure
Object Storage bucket, you can access the Object Storage bucket
from the Compute instance. If the dump files are on your local
system, you need to transfer them to the Oracle Cloud
Infrastructure Compute instance using the copy utility of your
choice, depending on the operating system you chose for your
Compute instance. Ensure the dump was created with the
ocimds
option set to true
in
MySQL Shell's instance dump utility or schema dump utility, for
compatibility with MySQL Database Service. MySQL Shell's table
dump utility does not use this option.
The dump loading utility uses the LOAD DATA LOCAL
INFILE
statement, so the global setting of the
local_infile
system
variable on the target MySQL instance must be
ON
for the duration of the import. By
default, this system variable is set to
ON
in a standard MySQL DB System
configuration.
On the target MySQL instance, the dump loading utility
checks whether the
sql_require_primary_key
system variable is set to ON
, and if it
is, returns an error if there is a table in the dump files
with no primary key. By default, this system variable is set
to OFF
in a standard MySQL DB System
configuration.
The dump loading utility does not automatically apply the
gtid_executed
GTID set from the source MySQL instance on the target MySQL
instance. The GTID set is included in the dump metadata from
MySQL Shell's instance dump utility, schema dump utility,
or table dump utility, as the
gtidExecuted
field in the
@.json
dump file. To apply these GTIDs
on the target MySQL instance for use with replication, from
MySQL Shell 8.0.22, use the
updateGtidSet
option to either append
them to or replace the
gtid_purged
GTID set, depending on the release of the target MySQL
instance. This is not currently supported on MySQL DB System
due to a permissions restriction. In MySQL Shell 8.0.21,
the GTID set can be imported manually, though this is not
supported on MySQL DB System.
For output produced by the instance dump utility or schema dump
utility, MySQL Shell's dump loading utility uses the DDL files
and tab-separated .tsv
data files to set up the
server instance or schema in the target MySQL instance, then load
the data. Dumps containing only the DDL files or only the data
files can be used to perform these tasks separately. The dump
loading utility also lets you separately apply the DDL files and
data files from a regular dump that contains both sorts of files.
For output produced by MySQL Shell's table dump utility, from
MySQL Shell 8.0.23, the dump contains the information required to
set up the schema that originally contained the table. By default,
from that release, the schema is recreated in the target MySQL
instance if it does not already exist. Alternatively, you can
specify the schema
option in the dump loading
utility to load the table into an alternative schema in the target
MySQL instance, which must exist there. In MySQL Shell 8.0.22,
the table dump utility's files do not contain the schema
information, so the target schema must exist in the target MySQL
instance. In that release, by default, the current schema of the
global shell session is used as the target schema, or the
schema
option can be used to name the schema.
You can customize the import with further options in the dump loading utility:
You can select individual tables or schemas to import or to exclude from the import.
Users and their roles and grants are excluded by default, but you can choose to import them.
You can specify a different character set for the data in the target MySQL instance to that used in the dump files.
You can update the ANALYZE TABLE
histograms, even after the data has already been loaded.
You can choose to skip binary logging on the target MySQL
instance during the course of the import using a SET
sql_log_bin=0
statement.
You can carry out a dry run with your chosen set of dump loading options to show what actions would be performed when you run the utility for real with those options.
The waitDumpTimeout
option lets you apply a
dump that is still in the process of being created. Tables are
loaded as they become available, and the utility waits for the
specified number of seconds after new data stops arriving in the
dump location. When the timeout elapses, the utility assumes the
dump is complete and stops importing.
Progress state for an import is stored in a persistent progress
state file, which records steps successfully completed and steps
that were interrupted or failed. By default, the progress state
file is named
load-progress.
and created in the dump directory, but you can choose a different
name and location. The dump loading utility references the
progress state file when you resume or retry the import for a
dump, and skips completed steps. De-duplication is automatically
managed for tables that were partially loaded. If you interrupt a
dump in progress by using Ctrl + C, on the first
use of that key combination, no new tasks are started by the
utility but existing tasks continue. Pressing Ctrl +
C again stops existing tasks, resulting in error
messages. In either case, the utility can still resume the import
from where it stopped.
server_uuid
.json
You can choose to reset the progress state and start the import
for a dump again from the beginning, but in this case the utility
does not skip objects that were already created and does not
manage de-duplication. If you do this, to ensure a correct import,
you must manually remove from the target MySQL instance all
previously loaded objects from that dump, including schemas,
tables, users, views, triggers, routines, and events. Otherwise,
the import stops with an error if an object in the dump files
already exists in the target MySQL instance. With appropriate
caution, you may use the ignoreExistingObjects
option to make the utility report duplicate objects but skip them
and continue with the import. Note that the utility does not check
whether the contents of the object in the target MySQL instance
and in the dump files are different, so it is possible for the
resulting import to contain incorrect or invalid data.
Do not change the data in the dump files between a dump stopping
and a dump resuming. Resuming a dump after changing the data has
undefined behavior and can lead to data inconsistency and data
loss. If you need to change the data after partially loading a
dump, manually drop all objects that were created during the
partial import (as listed in the progress state file), then run
the dump loading utility with the
resetProgress
option to start again from the
beginning.
If you need to modify any data in the dump’s data files before
importing it to the target MySQL instance, you can do this by
combining MySQL Shell’s parallel table import utility
util.importTable()
with the dump loading
utility. To do this, first use the dump loading utility to load
only the DDL for the selected table, to create the table on the
target server. Then use the parallel table import utility to
capture and transform data from the output files for the table,
and import it to the target table. Repeat that process as
necessary for any other tables where you want to modify the data.
Finally, use the dump loading utility to load the DDL and data for
any remaining tables that you do not want to modify, excluding the
tables that you did modify. For a description of the procedure,
see Modifying Dumped Data.
To load dump files with pre-authenticated requests from an Object
Storage bucket into a MySQL DB System, you need the
pre-authenticated read request URL that has been created for the
manifest file object (@.manifest.json
). Also
create a pre-authenticated read-write request for a text file in
the same prefixed location as the dump files in the Object Storage
bucket. This will be the dump loading utility's progress state
file, which is required when you are loading dump files with
pre-authenticated requests. You can use any user account with the
required permissions to create the request. The text file can have
any name, and you can create the file or let the utility create
it. The content of the file will be in JSON format, so a
.json
file extension is appropriate if you are
using one (for example, progress.json
).
As an alternative to storing the progress state file with the dump files, which is the default, you can use a local file in the location where you run the dump loading utility. If you do not have permissions to create a pre-authenticated read-write request for the progress state file, this method enables you to store progress. When you use a local file, note that the dump cannot be resumed by running the dump loading utility from an alternative location.
With pre-authenticated requests, when you run the dump loading
utility, specify the dump's URL as the pre-authenticated request
URL for the @.manifest.json
file. Also specify
the progress state file (progressFile
option)
as the pre-authenticated request URL for the file in the Object
Storage bucket, or as the file on the local system if you chose
that option. The user account that runs the dump loading utility
can then load the dump files using the URLs in the manifest file
without additional access permissions. If the dump is still in
progress, the dump loading utility monitors and waits for new
additions to the manifest file, rather than to the Object Storage
bucket.
The DDL files for a dump are loaded by a single thread, but the data is loaded in parallel by the number of threads you select, which defaults to 4. If table data was chunked when the dump was created, multiple threads can be used for a table, otherwise each thread loads one table at a time. The dump loading utility schedules data imports across threads to maximize parallelism. If the dump files were compressed by MySQL Shell's dump utilities, the dump loading utility handles decompression for them.
By default, fulltext indexes for a table are created only after the table is completely loaded, which speeds up the import. You can choose to defer all index creation (except the primary index) until each table is completely loaded. You can also opt to create all indexes during the table import. You can also choose to disable index creation during the import, and create the indexes afterwards, for example if you want to make changes to the table structure after loading.
For an additional improvement to data loading performance, you can
disable the InnoDB
redo log on the
target MySQL instance during the import. Note that this should
only be done on a new MySQL Server instance (not a production
system), and this feature is not available on MySQL DB System. For
more information, see
Disabling Redo Logging.
The dump loading utility uses the MySQL Shell global session to obtain the connection details of the target MySQL instance to which the dump is to be imported. You must open the global session (which can have an X Protocol connection or a classic MySQL protocol connection) before running the utility. The utility opens its own sessions for each thread, copying options such as connection compression and SSL options from the global session, and does not make any further use of the global session.
In the MySQL Shell API, the dump loading utility is a function of
the util
global object, and has the following
signature:
util.loadDump(url[, options])
If you are importing a dump that is located in the Oracle Cloud
Infrastructure Compute instance's filesystem where you are running
the utility, url
is a string specifying the
path to a local directory containing the dump files. You can
prefix a local directory path with the file://
schema. In this example in MySQL Shell's JavaScript mode, a dry
run is carried out to check that there will be no issues when the
dump files are loaded from a local directory into the connected
MySQL instance:
shell-js> util.loadDump("/mnt/data/worlddump", {dryRun: true})
If you are importing a dump from an Oracle Cloud Infrastructure
Object Storage bucket, url
is the path prefix
that the dump files have in the bucket, which was assigned using
the outputUrl
parameter when the dump was
created. 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 JavaScript mode,
the dump prefixed worlddump
is loaded from an
Object Storage bucket into the connected MySQL DB System using 8
threads:
shell-js> util.loadDump("worlddump", { > threads: 8, osBucketName: "hanna-bucket", osNamespace: "idx28w1ckztq"})
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:
dryRun: [ true | false ]
Display information about what actions would be performed
given the specified options and dump files, including any
errors that would be returned based on the dump contents,
but do not proceed with the import. The default is
false
.
osBucketName:
"string
"
The name of the Oracle Cloud Infrastructure Object Storage
bucket where the dump files are located. 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 upload chunks of data to the target MySQL instance. Each thread has its own connection to the MySQL instance. The default is 4. if the dump was created with chunking enabled (which is the default), the utility can use multiple threads to load data for a table; otherwise a thread is only used for one table.
progressFile:
"string
"
A local file location for the dump loading utility's
progress state file, which persists progress state for an
import. By default, the progress state file is named
load-progress.
and created in the dump directory, but you can change that
using this option. Setting server_uuid
.jsonprogressFile
to an empty string disables progress state tracking, which
means that the dump loading utility cannot resume a
partially completed import.
showProgress: [ true | false ]
Display (true
) or hide
(false
) progress information for the
import. 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 number of active
threads and their actions, the amount of data loaded so far,
the percentage complete and the rate of throughput. When the
progress information is not displayed, progress state is
still recorded in the dump loading utility's progress state
file.
resetProgress: [ true | false ]
Setting this option to true
resets the
progress state and starts the import again from the
beginning. The default is false
. Note
that with this option, the dump loading utility does not
skip objects that were already created and does not manage
de-duplication. If you want to use this option, to ensure a
correct import, you must first manually remove from the
target MySQL instance all previously loaded objects,
including schemas, tables, users, views, triggers, routines,
and events from that dump. Otherwise, the import stops with
an error if an object in the dump files already exists in
the target MySQL instance. With appropriate caution, you may
use the ignoreExistingObjects
option to
make the utility report duplicate objects but skip them and
continue with the import.
waitDumpTimeout:
int
Setting this option activates concurrent loading by
specifying a timeout (in seconds) for which the utility
waits for further data after all uploaded data chunks in the
dump location have been processed. This allows the utility
to import the dump while it is still in the process of being
created. Data is processed as it becomes available, and the
import stops when the timeout is exceeded with no further
data appearing in the dump location. The default setting,
0
, means that the utility marks the dump
as complete when all uploaded data chunks have been
processed and does not wait for more data.
ignoreExistingObjects: [ true | false ]
Import the dump even if it contains objects that already
exist in the target schema in the MySQL instance. The
default is false
, meaning that an error
is issued and the import stops when a duplicate object is
found, unless the import is being resumed from a previous
attempt using a progress state file, in which case the check
is skipped. When this option is set to
true
, duplicate objects are reported but
no error is generated and the import proceeds. This option
should be used with caution, because the utility does not
check whether the contents of the object in the target MySQL
instance and in the dump files are different, so it is
possible for the resulting import to contain incorrect or
invalid data. An alternative strategy is to use the
excludeTables
option to exclude tables
that you have already loaded where you have verified the
object in the dump files is identical with the imported
object in the target MySQL instance. The safest choice is to
remove duplicate objects from the target MySQL instance
before restarting the dump.
ignoreVersion: [ true | false ]
Import the dump even if the major version number of the
MySQL instance from which the data was dumped is different
to the major version number of the MySQL instance to which
the data will be uploaded. The default is
false
, meaning that an error is issued
and the import does not proceed if the major version number
is different. When this option is set to
true
, a warning is issued and the import
proceeds. Note that the import will only be successful if
the schemas in the dump files have no compatibility issues
with the new major version.
From MySQL Shell 8.0.23, this option also permits the
import of a dump created without the use of the
ocimds
option into a MySQL Database
Service instance.
Before attempting an import using the
ignoreVersion
option, use MySQL Shell's
upgrade checker utility
checkForServerUpgrade()
to check the
schemas on the source MySQL instance. Fix any compatibility
issues identified by the utility before dumping the schemas
and importing them to the target MySQL instance.
updateGtidSet: [ off | append | replace ]
Apply the gtid_executed
GTID set from the source MySQL instance, as recorded in the
dump metadata, to the
gtid_purged
GTID set on the
target MySQL instance. The
gtid_purged
GTID set holds the GTIDs of all transactions that have been
applied on the server, but do not exist on any binary log
file on the server. This option is available from
MySQL Shell 8.0.22, but in that release it is not supported
on MySQL DB System due to a permissions restriction. From
MySQL 8.0.23, the option can also be used for a MySQL DB
System instance. The default is off
,
meaning that the GTID set is not applied.
Do not use this option for a dump produced by MySQL Shell's table dump utility, only for dumps produced by MySQL Shell's instance dump utility or schema dump utility. Also, do not use this option when Group Replication is running on the target MySQL instance.
For MySQL instances that are not MySQL DB System instances,
when you set append
or
replace
to update the GTID set, also set
the skipBinlog
option to
true
. This ensures the GTIDs on the
source server match the GTIDs on the target server. For
MySQL DB System instances, this option is not used.
For a target MySQL instance from MySQL 8.0, you can set the
option to append
, which appends the
gtid_executed
GTID set from
the source MySQL instance to the
gtid_purged
GTID set on the
target MySQL instance. The
gtid_executed
GTID set to be applied, which is shown in the
gtidExecuted
field in the
@.json
dump file, must not intersect
with the gtid_executed
set
already on the target MySQL instance. For example, you can
use this option when importing a schema from a different
source MySQL instance to a target MySQL instance that
already has schemas from other source servers.
You can also use replace
for a target
MySQL instance from MySQL 8.0, to replace the
gtid_purged
GTID set on the
target MySQL instance with the
gtid_executed
GTID set from
the source MySQL instance. To do this, the
gtid_executed
GTID set from
the source MySQL instance must be a superset of the
gtid_purged
GTID set on the target MySQL instance, and must not
intersect with the set of transactions in the target's
gtid_executed
GTID set that
are not in its gtid_purged
GTID set.
For a target MySQL instance at MySQL 5.7, set the option to
replace
, which replaces the
gtid_purged
GTID set on the
target MySQL instance with the
gtid_executed
GTID set from
the source MySQL instance. In MySQL 5.7, to do this the
gtid_executed
and
gtid_purged
GTID sets on the target MySQL instance must be empty, so the
instance must be unused with no previously imported GTID
sets.
In MySQL Shell 8.0.21, where this option is not available,
you can apply the GTID set manually on a MySQL Server
instance (except where Group Replication is in use). For
MySQL DB System, this method is not supported. To apply the
GTID set, after the import, use MySQL Shell's
\sql
command (or enter SQL mode) to issue
the following statement on the connected MySQL instance,
copying the gtid_executed
GTID set from
the gtidExecuted
field in the
@.json
dump file in the dump metadata:
shell-js> \sql SET @@GLOBAL.gtid_purged= "+gtidExecuted_set
";
This statement, which works from MySQL 8.0, adds the source
MySQL Server instance's
gtid_executed
GTID set to
the target MySQL instance's
gtid_purged
GTID set. For
MySQL 5.7, the plus sign (+
) must be
omitted, and the
gtid_executed
and
gtid_purged
GTID sets on the target MySQL instance must be empty. For
more details, see the description of the
gtid_purged
system variable
in the release of the target MySQL instance.
skipBinlog: [ true | false ]
Skips binary logging on the target MySQL instance for the
sessions used by the utility during the course of the
import, by issuing a SET sql_log_bin=0
statement. The default is false
, so
binary logging is active by default. For MySQL DB System,
this option is not used, and the import stops with an error
if you attempt to set it to true
. For
other MySQL instances, always set
skipBinlog
to true
if
you are applying the
gtid_executed
GTID set from
the source MySQL instance on the target MySQL instance,
either using the updateGtidSet
option or
manually. When GTIDs are in use on the target MySQL instance
(gtid_mode=ON
), setting this option to
true
prevents new GTIDs from being
generated and assigned as the import is being carried out,
so that the original GTID set from the source server can be
used. The user account must have the required permissions to
set the sql_log_bin
system variable.
loadIndexes: [ true | false ]
Create (true
) or do not create
(false
) secondary indexes for tables. The
default is true
. When this option is set
to false
, secondary indexes are not
created during the import, and you must create them
afterwards. This can be useful if you are loading the DDL
files and data files separately, and if you want to make
changes to the table structure after loading the DDL files.
Afterwards, you can create the secondary indexes by running
the dump loading utility again with
loadIndexes
set to
true
and
deferTableIndexes
set to
all
.
deferTableIndexes: [ off | fulltext | all ]
Defer the creation of secondary indexes until after the
table data is loaded. This can reduce loading times.
off
means all indexes are created during
the table load. The default setting
fulltext
defers full-text indexes only.
all
defers all secondary indexes and only
creates primary indexes during the table load, and also
(from MySQL Shell 8.0.22) indexes defined on columns
containing auto-increment values. In MySQL Shell 8.0.21, do
not set all
if you have any unique key
columns containing auto-increment values.
analyzeTables: [ off | on | histogram ]
Execute ANALYZE TABLE
for tables when
they have been loaded. on
analyzes all
tables, and histogram
analyzes only
tables that have histogram information stored in the dump.
The default is off
. You can run the dump
loading utility with this option to analyze the tables even
if the data has already been loaded.
characterSet:
"string
"
The character set to be used for the import to the target
MySQL instance, for example in the CHARACTER
SET
option of the LOAD
DATA
statement. The default is the character set
given in the dump metadata that was used when the dump was
created by MySQL Shell's instance dump utility, schema dump
utility, or table dump utility, which default to using
utf8mb4
. The character set must be
permitted by the character_set_client
system variable and supported by the MySQL instance.
schema: "string
"
The existing target schema into which a dump produced by MySQL Shell's table dump utility must be loaded.
From MySQL Shell 8.0.23, this option is not required,
because the dump files from the table dump utility contain
the information required to set up the schema that
originally contained the table. By default, from that
release, the schema is recreated in the target MySQL
instance if it does not already exist. Alternatively, you
can specify the schema
option to load the
table into an alternative schema in the target MySQL
instance, which must exist there.
In MySQL Shell 8.0.22, the dump files from the table dump
utility do not contain the schema information, so the target
schema must exist in the target MySQL instance. In that
release, by default, the current schema of the global shell
session is used as the target schema, or the
schema
option can be used to name the
target schema.
excludeSchemas: array of
strings
Exclude the named schemas from the import. Note that the
information_schema
,
mysql
, ndbinfo
,
performance_schema
, and
sys
schemas are always excluded from a
dump that is created by MySQL Shell's instance dump
utility. If a named schema does not exist in the dump files,
the utility ignores the item.
includeSchemas: array of
strings
Load only the named schemas from the dump files. You can
specify both options, in which case a schema name matched by
both an includeSchemas
string and an
excludeSchemas
string is excluded.
excludeTables: array of
strings
Exclude the named tables from the import. 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 dump created by
MySQL Shell's schema dump utility, although their DDL
statements are included. Tables named by the
excludeTables
option are not uploaded to
the target MySQL instance. If a named table does not exist
in the schema or the schema does not exist in the dump
files, the dump loading utility ignores the item.
includeTables: array of
strings
Load only the named tables from the dump files. Table names
must be qualified with a valid schema name, and quoted with
the backtick character if needed. You can specify both
options, in which case a table name matched by both an
includeTables
string and an
excludeTables
string is excluded.
loadDdl: [ true | false ]
Setting this option to true
imports only
the DDL files from the dump, and does not import the data.
The default is false
.
loadData: [ true | false ]
Setting this option to true
imports only
the data files from the dump, and does not import the DDL
files. The default is false
.
loadUsers: [ true | false ]
Import (true
) or do not import
(false
) users and their roles and grants
into the target MySQL instance. The default is
false
, so users are not imported by
default. Statements for the current user are skipped. From
MySQL Shell 8.0.22, if a user already exists in the target
MySQL instance, an error is returned and the user's grants
from the dump files are not applied. From MySQL Shell
8.0.22, you can use the excludeUsers
or
includeUsers
option in the dump loading
utility to specify user accounts to be excluded or included
in the import.
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.
MySQL Shell's schema dump utility and table dump utility do
not include users, roles, and grants in a dump, but the
instance dump utility can, and does by default. From
MySQL Shell 8.0.22, the excludeUsers
and
includeUsers
options can also be used in
the instance dump utility to exclude or include named user
accounts from the dump files.
If you specify true
but the supplied dump
files do not contain user accounts, before MySQL Shell
8.0.23, the utility returns an error and stops the import.
From MySQL Shell 8.0.23, the utility instead returns a
warning and continues.
excludeUsers: array of
strings
Exclude the named user accounts from the import. 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 in the dump files,
the utility ignores the item.
user_name
'@'%
'"
includeUsers: array of
strings
Include only the named user accounts in the import. 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 target MySQL instance. You
can also specify both options, in which case a user account
matched by both an includeUsers
string
and an excludeUsers
string is excluded.
MySQL Shell’s parallel table import utility
util.importTable()
can be used in combination
with the dump loading utility util.loadDump()
to modify data in the chunked output files before uploading it
to the target MySQL instance. You can modify the data for one
table at a time by this method. Follow this procedure, which
works from MySQL Shell 8.0.23:
Use the dump loading utility with the
loadDdl
option, to load the DDL file and
create the selected table on the target MySQL instance with
no data.
shell-js> util.loadDump("/mnt/data/proddump", { > includeTables: ["product.pricing"], > loadDdl: true, > loadData: false});
Use the parallel table import utility to capture and
transform the data for the table, and import it to the empty
table on the target MySQL instance. In this example, the
data for the pricing
table is in multiple
compressed files, which are specified using wildcard pattern
matching. The values from the id
and
prodname
columns in the dump files are
assigned unchanged to the same columns in the target table.
The values from the price
column in the
dump files are captured and assigned to the variable
@1
. The decodeColumns
option is then used to reduce the prices by a standard
amount, and the reduced prices are placed in the
price
column of the target table.
shell-js> util.importTable ("/mnt/data/proddump/product@pricing@*.zst", { > schema: "product", > table: "pricing", > columns: ["id", "prodname", 1], > decodeColumns: { "price": "0.8 * @1"}});
Repeat Steps 1 and 2 as needed for any other tables in the dump files where you need to modify the data.
When you have finished uploading all the tables and data that needed to be modified, use the dump loading utility to load both the DDL and the data for any remaining tables that you do not need to modify. Be sure to exclude the tables that you did modify in the previous steps.
shell-js> util.loadDump("/mnt/data/proddump", {excludeTables: ["product.pricing"]});