7.4 Instance Dump Utility and Schema Dump Utility

MySQL Shell's instance dump utility and schema dump utility, 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 schemas 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 7.5, “Dump Loading Utility”.

MySQL Shelll's instance dump utility and schema 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 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 or schema 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 and schema 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.

You can specify schemas or individual tables to exclude from a dump. 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.

The following requirements apply to dumps using the instance dump utility and schema 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 upload method used to transfer files to an Oracle Cloud Infrastructure Object Storage bucket has a file size limit of 1.2 TiB.

• 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 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.

• 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.

• 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 and schema 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 and schema dump utility are functions of the util global object, and have the following signatures:

util.dumpInstance(outputUrl[, options]) 
util.dumpSchemas(schemas, outputUrl[, options])

For the schema dump utility, schemas specifies a list of one or more schemas to be dumped from the MySQL instance. If most of the schemas are to be dumped, an alternative strategy is to use the instance dump utility and specify the excludeSchemas option to list those schemas that are not to be dumped.

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.

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, 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 both the instance dump utility and the schema 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 (tenancy name) 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

Exclude the named schemas from the dump. This option is for the instance dump utility only. 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

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.

users: [ true | false ]

Include (true) or exclude (false) users and their roles and grants in the dump. The default is true. This option is for the instance dump utility only. The schema dump utility does not include users, roles, and grants in a dump.

events: [ true | false ]

Include (true) or exclude (false) events for each schema in the dump. The default is true.

routines: [ true | false ]

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 32 MB (32M), 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. 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.

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. 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.

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).

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.