8.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

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/[e-mail]	
worlddump/[e-mail]	
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.

Note

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 "'user_name'@'host_name'" for an account that is defined with a user name and host name, or "'user_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.

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.

Important

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.