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()
. To get the best functionality,
always use the most recent version available of MySQL Shell's
dump and dump loading utilities.
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 the destination MySQL instance. The source MySQL instance must also be MySQL 5.7 or later to use the full functionality of the utilities. From MySQL Shell 8.0.22, it is possible to dump an instance, schema, or table from a MySQL 5.6 instance and load it into a MySQL 5.7 or later destination, but dumping user accounts from MySQL 5.6 is not supported. The utilities only support General Availability (GA) releases of MySQL Server versions.
Object names in the instance or schema must be in the
latin1
orutf8
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
, andTRIGGER
. If theconsistent
option is set tofalse
, theBACKUP_ADMIN
andRELOAD
privileges are not required. If theconsistent
option is set totrue
, which is the default, theLOCK TABLES
privilege on all dumped tables can substitute for theRELOAD
privilege if the latter is not available.From MySQL Shell 8.0.24, the user account used to run the utility needs the
REPLICATION CLIENT
privilege in order for the utility to be able to include the binary log file name and position in the dump metadata. If the user ID does not have that privilege, the dump continues but does not include the binary log information. The binary log information can be used after loading the dumped data into the replica server to set up replication with a non-GTID source server, using theASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
option of theCHANGE REPLICATION SOURCE TO
statement (which is available from MySQL Server 8.0.23).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 themax_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 totrue
, to ensure compatibility with MySQL Database Service.For compatibility with MySQL Database Service, all tables must use the
InnoDB
storage engine. Theocimds
option checks for any exceptions found in the dump, and thecompatibility
option alters the dump files to replace other storage engines withInnoDB
.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 thecompatibility
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 thecompatibility
option.For MySQL Database Service High Availability, which uses Group Replication, primary keys are required on every table. From MySQL Shell 8.0.24, the
ocimds
option checks and reports an error for any tables in the dump that are missing primary keys. Thecompatibility
option can be set to ignore missing primary keys if you do not need them, or to notify MySQL Shell’s dump loading utility to add primary keys in invisible columns where they are not present. For details, see the description for thecompatibility
option. If possible, instead of managing this in the utility, consider creating primary keys in the tables on the source server before dumping them again.
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 isfalse
.-
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 theociConfigFile
andociProfile
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, andG
for gigabytes can be used (for example, setting100M
limits throughput to 100 megabytes per second per thread). Setting0
(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 istrue
ifstdout
is a terminal (tty
), such as when MySQL Shell is in interactive mode, andfalse
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
, andsys
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
, andmysql.slow_log tables
is always excluded from a schema dump, although their DDL statements are included. Tables named by theexcludeTables
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 thetables
parameter to an empty array. The default isfalse
.-
users: [ true | false ]
-
(Instance dump utility only) Include (
true
) or exclude (false
) users and their roles and grants in the dump. The default istrue
, 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
orincludeUsers
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 utilityutil.loadDump()
to exclude or include individual user accounts at the point of import, depending on the requirements of the target MySQL instance.NoteDumping user accounts from a MySQL 5.6 instance is not supported. If you are dumping from this version, set
users: false
.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, oruser_name
'@'host_name
'""'
for an account that is defined with a user name only (which is equivalent touser_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 toexcludeUsers
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 anincludeUsers
string and anexcludeUsers
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 istrue
.-
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 istrue
. Note that user-defined functions are not included, even whenroutines
is set totrue
.-
triggers: [ true | false ]
Include (
true
) or exclude (false
) triggers for each table in the dump. The default istrue
.-
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 variablescharacter_set_client
,character_set_connection
, andcharacter_set_results
are set to this value for each connection. The character set must be permitted by thecharacter_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 tofalse
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 istrue
. Whentrue
is set, the utility sets a global read lock using theFLUSH TABLES WITH READ LOCK
statement. The transaction for each thread is started using the statementsSET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
andSTART TRANSACTION WITH CONSISTENT SNAPSHOT
. When all threads have started their transactions, the instance is locked for backup (as described in LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements) 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 isfalse
.-
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 isfalse
.-
chunking: [ true | false ]
Enable (
true
) or disable (false
) chunking for table data, which splits the data for each table into multiple files. The default istrue
, so chunking is enabled by default. UsebytesPerChunk
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 tofalse
, 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, andG
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 setschunking
totrue
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 isfalse
. 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
, andENCRYPTION
options inCREATE 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 inCREATE TABLE
statements other thanInnoDB
, 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 thedryRun
option to list out all of the issues with the items in the dump before the dumping process is started. Use thecompatibility
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 theosBucketName
option, theociParManifest
option also defaults totrue
, 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 an array of strings:
-
force_innodb
Change
CREATE TABLE
statements to use theInnoDB
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 theSQL SECURITY
clause for views and routines to specifyINVOKER
instead ofDEFINER
. 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 removesREVOKE
statements for system schemas (mysql
andsys
) 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 fromGRANT
statements. This privilege can be restricted by MySQL Database Service.-
strip_tablespaces
Remove the
TABLESPACE
clause fromGRANT
statements, so all tables are created in their default tablespaces. MySQL Database Service has some restrictions on tablespaces.-
ignore_missing_pks
Make the instance, schema, or table dump utility ignore any missing primary keys when the dump is carried out, so that the
ocimds
option can still be used without the dump stopping due to this check. Dumps created with this modification cannot be loaded into a MySQL Database Service High Availability instance, because primary keys are required for MySQL Database Service High Availability, which uses Group Replication. To add the missing primary keys instead, use thecreate_invisible_pks
modification, or consider creating primary keys in the tables on the source server.-
create_invisible_pks
-
Add a flag in the dump metadata to notify MySQL Shell’s dump loading utility to add primary keys in invisible columns, for each table that does not contain a primary key. This modification enables a dump where some tables lack primary keys to be loaded into a MySQL Database Service High Availability instance. Primary keys are required for MySQL Database Service High Availability, which uses Group Replication.
The dump data is unchanged by this modification, as the tables do not contain the invisible columns until they have been processed by the dump loading utility. The invisible columns (which are named "
my_row_id
") have no impact on applications that use the uploaded tables.Adding primary keys in this way does not yet enable inbound replication of the modified tables to a High Availability instance, as that feature currently requires the primary keys to exist in both the source server and the replica server. If possible, instead of using this modification, consider creating primary keys in the tables on the source server, before dumping them again. From MySQL 8.0.23, you can do this with no impact to applications by using invisible columns to hold the primary keys. This is a best practice for performance and usability, and helps the dumped database to work seamlessly with MySQL Database Service.
NoteMySQL Shell’s dump loading utility can only be used to load dumps created with the
create_invisible_pks
modification onto a target MySQL instance at MySQL Server 8.0.24 or later, due to a limitation on hidden columns in MySQL 8.0.23. The dump loading utility in versions of MySQL Shell before MySQL Shell 8.0.24 silently ignores the dump metadata flag and does not add the primary keys, so ensure that you use the latest version of the utility.
-
-
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 theociParExpireTime
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 totrue
and an Object Storage bucket name is supplied using theosBucketName
option,ociParManifest
is set totrue
by default, otherwise it is set tofalse
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 theociProfile
option) is the creator for the pre-authenticated requests. This user must havePAR_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.ImportantBefore 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 example2020-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.