Displays information about the copy with the specified set of options, and about the results of HeatWave Service compatibility checks, but does not proceed with the copy. Setting this option enables you to list out all of the compatibility issues before starting the copy. The default is false.

Display (true) or hide (false) progress information for the copy. 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 copied, the number of rows copied so far, the percentage complete, and the throughput in rows and bytes per second.

The number of parallel threads to use to copy chunks of data from the MySQL instance. Each thread has its own connection to the MySQL instance. The default is 4.

The copy utilities require twice the number of threads, one thread to copy and one thread to write. If threads is set to N, 2N threads are used.

The maximum number of bytes per second per thread for data read throughput during the copy. 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.

The character set to be used during the session connections that are opened by MySQL Shell to the target server. 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.

If enabled, on dump, a metadata file, @.checksums.json is generated with the copy. This file contains the checksum data for the copy, enabling data verification.

The following conditions apply if checksum: true during the copy process:

If enabled, the utility checks the generated checksum data after the corresponding data is loaded. The verification is limited to data which was dumped, ignoring generated data such as invisible primary keys.

Errors are returned if a checksum does not match or if a table is missing and cannot be verified.

If checksum: true but no data was loaded, either due to loadData: false or no data being dumped, the utility verifies the dump's checksum information against the current contents of the affected tables.

Enable (true) or disable (false) consistent data copies by locking the instance for backup during the copy. The default is true.

When true is set, the utility sets a global read lock using the FLUSH TABLES WITH READ LOCK statement (if the user ID used to run the utility has the RELOAD privilege), or a series of table locks using LOCK TABLES statements (if the user ID does not have the RELOAD privilege but does have LOCK TABLES). 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 (as described in LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements) and the global read lock is released.

If the user account does not have the BACKUP_ADMIN privilege and LOCK INSTANCE FOR BACKUP cannot be executed, the utilities make an extra consistency check during the copy. If this check fails, an instance copy is stopped, but a schema or table copy continues and returns an error message to alert the user that the consistency check failed.

Enable (true) or disable (false) the extra consistency check performed when consistent: true. Default is false.

This option is ignored if consistent: false.

The target schema into which the contents of the copied schema must be loaded.

If the schema does not exist, it is created, and the copied schema is loaded to that new schema. If the new schema name differs from the schema name in the copy, the copy is loaded to the new schema, but no changes are made to the loaded data. That is, any reference to the old schema name remains in the data. All stored procedures, views, and so on, refer to the original schema, not the new one.

This load option is supported for single schema copies, or for filtering options which result in a single schema. That is, if you are using copyInstance to copy data to a new instance, you can copy all the data to a single schema if the source contains only one schema, or the defined filters result in a single schema being copied to the destination.

Skips binary logging on the target MySQL instance for the sessions used by the utility during the course of the copy, by issuing a SET sql_log_bin=0 statement. The default is false, so binary logging is active by default. For HeatWave Service DB Systems, 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.

Copy even if the major version number of the source from which the data was copied is non-consecutive to the major version number of the destination, such as 5.6 to 8.1. The default is false, meaning that an error is issued and the copy stops if the major version number is different. When this option is set to true, a warning is issued and the copy proceeds. Note that the copy will only be successful if the copied schemas have no compatibility issues with the new major version.

Before attempting a copy using the ignoreVersion option, use MySQL Shell's upgrade checker utility checkForServerUpgrade() to check the source instance and fix any compatibility issues identified by the utility before attempting to copy.

The default value is false.

Copy the instance even if it contains user accounts or DDL objects that already exist in the target instance. If this option is set to false, any existing object results in an error. Setting it to true drops existing user accounts and objects before creating them.

Copy even if the copy contains objects that already exist in the target instance. The default is false, meaning that an error is issued and the copy stops when a duplicate object is found. When this option is set to true, duplicate objects are reported but no error is generated and the copy 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 copy to contain incorrect or invalid data. An alternative strategy is to use the excludeTables option to exclude tables that you have already copied 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 copy.

The action taken in the event of errors related to GRANT or REVOKE errors.

The maximum number of bytes that can be copied from a data chunk in a single LOAD DATA statement. If a data file exceeds the maxBytesPerTransaction value, multiple LOAD DATA statements load data from the file in chunks less than or equal to the maxBytesPerTransaction value.

The unit suffixes k for kilobytes, M for megabytes, and G for gigabytes can be used. The minimum value is 4096 bytes. If a lesser value is specified, an exception is thrown. If the maxBytesPerTransaction option is unset, the bytesPerChunk value is used instead.

If a data file contains a row that is larger than the maxBytesPerTransaction setting, the row's data is requested in a single LOAD DATA statement. A warning is emitted for the first row encountered that exceeds the maxBytesPerTransaction setting.

An intended use for this option is to load data in smaller chunks when a data file is too large for the target server's limits, such as the limits defined by the server's group_replication_transaction_size_limit or max_binlog_cache_size settings. For example, If you receive the error "MySQL Error 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage" when loading data, set maxBytesPerTransaction to a value less than or equal to the server instance’s max_binlog_cache_size setting.

A list of SQL statements to run at the start of each client session used for copying data into the target MySQL instance. You can use this option to change session variables. For example, the following statements skip binary logging on the target MySQL instance for the sessions used by the utility during the course of the import, and increase the number of threads available for index creation:

sessionInitSQL: ["SET SESSION sql_log_bin=0;", "SET SESSION innodb_ddl_threads=8,"]

If an error occurs while running the SQL statements, the copy stops and returns an error message.

Include a statement at the start of the copy to set the time zone to UTC. All timestamp data in the output is converted to this time zone. The default is true. 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.

Enable (true) or disable (false) chunking for table data, which splits the data for each table into multiple files. The default is true. Use bytesPerChunk to specify the chunk size. If you set the chunking option to false, chunking does not take place and the utility creates one data file for each table.

If a table has no primary key or unique index, chunking is done based on the number of rows in the table, the average row length, and the bytesPerChunk value.

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), and the minimum is 128 KB (128k). Specifying this option sets chunking to true implicitly.

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.

MySQL Shell utilizes MySQL Server's parallel index creation. All indexes in a table are added simultaneously.

See Configuring Parallel Threads for Online DDL Operations for restrictions and configuration.

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 indexes defined on columns containing auto-increment values.

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.

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. The default is off, meaning that the GTID set is not applied.

Do not use this option when Group Replication is running on the target MySQL instance.

For MySQL instances that are not HeatWave Service 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 HeatWave Service 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.

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.

Apply the specified requirements for compatibility with HeatWave Service for all tables in the copy, altering the dump files as necessary.

The following modifications can be specified as an array of strings:

A key-value pair comprising of a valid table identifier, of the form schemaName.tableName, and a valid SQL condition expression used to filter the data being copied.

A key-value pair comprising of a valid table identifier, of the form schemaName.tableName, and a list of valid partitions.

For example, to copy only the partitions named p1 and p2 from the table schema.table: partitions: {'schema.table':["p1", "p2"]}

Setting this option to true includes only the DDL files for the items in the copy, and does not copy the data. The default is false.

Setting this option to true includes only the data files for the items in the copy, and does not include DDL files. The default is false.

(Instance copy utility only) Include (true) or exclude (false) users and their roles and grants in the copy. The default is true. The schema and table copy utilities do not include users, roles, or grants in a copy.

You can use the excludeUsers or includeUsers option to specify individual user accounts to be excluded from or included in the copy.

(Instance copy utility only) Exclude the named user accounts from the copy. Use to exclude user accounts that are not accepted for import to a HeatWave Service 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. If you do not supply a host name, all accounts with that user name are excluded.

(Instance copy utility only) Include only the named user accounts in the copy. Specify each user account string as for the excludeUsers option. Use as an alternative to excludeUsers if only a few user accounts are required in the copy. You can also specify both options to include some accounts and exclude others.

(Instance copy utility only) Exclude the named schemas from the copy. Note that the information_schema, mysql, ndbinfo, performance_schema, and sys schemas are always excluded from an instance copy.

(Instance copy utility only) Include only the named schemas in the copy. You cannot include the information_schema, mysql, ndbinfo, performance_schema, or sys schemas by naming them on this option. If you want to copy one or more of these schemas, you can do this using the schema copy utility util.copySchemas().

(Instance and schema copy utilities only) Exclude the named tables (DDL and data) from the copy. 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 copy, although their DDL statements are included, and you cannot include that data by naming the table in another option or utility.

(Instance and schema copy utilities only) Include only the named tables in the copy. Table names must be qualified with a valid schema name, and quoted with the backtick character if needed.

(Instance and schema copy utilities only) Include (true) or exclude (false) events for each schema in the copy. The default is true.

(Instance and schema copy utilities only) Exclude the named events from the copy. Names of events must be qualified with a valid schema name, and quoted with the backtick character if needed.

(Instance and schema copy utilities only) Include only the named events in the copy. Event names must be qualified with a valid schema name, and quoted with the backtick character if needed.

(Instance and schema copy utilities only) Include (true) or exclude (false) functions and stored procedures for each schema in the copy. The default is true. Note that user-defined functions are not included, even when routines is set to true.

(Instance and schema copy utilities only) Exclude the named functions and stored procedures from the copy. Names of routines must be qualified with a valid schema name, and quoted with the backtick character if needed.

(Instance and schema copy utilities only) Include only the named functions and stored procedures in the copy. Names of routines must be qualified with a valid schema name, and quoted with the backtick character if needed.

(Table copy utility only) Setting this option to true includes all views and tables from the specified schema in the copy. The default is false. When you use this option, set the tables parameter to an empty array.

(All copy utilities) Include (true) or exclude (false) triggers for each table in the copy. The default is true.

(All copy utilities) Exclude the named triggers from the copy. Names of triggers must be qualified with a valid schema name and table name (schema.table.trigger), and quoted with the backtick character if needed. You can exclude all triggers for a specific table by specifying a schema name and table name with this option (schema.table).

(All copy utilities) Include only the named triggers in the copy. Names of triggers must be qualified with a valid schema name and table name (schema.table.trigger), and quoted with the backtick character if needed. You can include all triggers for a specific table by specifying a schema name and table name with this option (schema.table).

(Instance copy utility and schema copy utilities only)

Include (true, default) or exclude (false) libraries from the dump. If set to true, copies all libraries in the copy. If the libraries option is not provided, it is assumed to be true and all libraries are copied.

If the target version does not support libraries, a warning is displayed.

See Using JavaScript Libraries for information on libraries.

(Instance copy utility and schema copy utilities only)

List of library objects to be included in the dump in the format schema.library.

For example:

              "includeLibraries": [ "`sakila`.`library1`",  "`sakila`.`library2`" ] 
            

(Instance copy utility and schema copy utilities only)

List of library objects to be excluded from the dump in the format schema.library.

For example:

              "excludeLibraries": [ "`sakila`.`library1`",  "`sakila`.`library2`" ]