Use the MySQL Shell dumpInstance utility to export a MySQL instance to an Object Storage bucket.

1. Run the following command in the JS execution mode. The command exports an entire MySQL instance to an Object Storage bucket, stripping any grants that cannot be used in a MySQL HeatWave Service DB system:

   util.dumpInstance("<BucketPrefix>", {osBucketName: "<MDSBucket>", threads: <ThreadSize>, ocimds: true, 
       compatibility: ["strip_restricted_grants", "strip_definers", "ignore_missing_pks", 
       "skip_invalid_accounts"], bytesPerChunk: "<ChunkSize>"})

   • util.dumpInstance: Exports an entire MySQL instance.
   • <BucketPrefix>: (Optional) Adds a prefix to the files uploaded to the bucket. If this is specified, the files are uploaded to the defined bucket with the prefix in the following format: <BucketPrefix>/filename, similarly to a file path. For example, if <BucketPrefix> is set to test, every file uploaded to the defined bucket, <MDSBucket>, is done so as test/filename. If you download the file, the prefix is treated as a folder in the download. For local exports, this parameter is the path to the local directory you want to export to.

     Although the contents of this parameter are optional, the quotation marks are not. Even if you do not intend to use a prefix, you must include the quotation marks in the syntax of the command. For example:

     util.dumpInstance("", {osBucketName: "<MDSBucket>", threads: <ThreadSize>, ocimds: true, 
         compatibility: ["strip_restricted_grants", "strip_definers", "ignore_missing_pks", 
         "skip_invalid_accounts"], bytesPerChunk: "<ChunkSize>"})

   • osBucketName: Specifies the case-sensitive name of the Object Storage bucket to export to. MySQL Shell uses the tenancy and user information defined in the config file.
   • threads: (Optional) Specifies the number of processing threads to use for this task. The default value is 4. For best performance, it is recommended to set this parameter to the number of CPU cores available on the database server.
   • ocimds: Checks the data for compatibility with MySQL HeatWave Service. When this is set to true, you cannot export an instance if it is incompatible with MySQL HeatWave Service.
     Note
     
     The loadDump utility allows import of dumps that are created with the ocimds option enabled only.
   • compatibility: List the parameters that specify which modifications are performed on the exported data. The compatibility options used here assume that the exported data is in a standalone DB system, not a highly available DB system. See Compatibility Checks.
   • bytesPerChunk: (Optional) For large datasets, it is recommended to use this parameter to define larger chunks. The default chunk size is 64 MB. For example, bytesPerChunk: 128M, specifies a chunk size of 128MB.

MySQL HeatWave Service has several security-related restrictions that are not present in a MySQL instance. Use the ocimds option of the dump utility to perform compatibility checks on the schemas, and if there are any issues, the utility aborts the dump and produces a detailed list of issues and suggests steps to correct them.

The following command shows how to perform compatibility checks using the ocimds option. Some issues found by the ocimds option may require you to manually edit the schema before it can be loaded into MySQL HeatWave Service.

util.dumpInstance("<BucketPrefix>", {osBucketName: "<MDSBucket>", threads: <ThreadSize>, ocimds: true, 
    compatibility: ["force_innodb", "strip_definers", "strip_restricted_grants", "skip_invalid_accounts", "strip_tablespaces", "ignore_missing_pks" 
    ], bytesPerChunk: "<ChunkSize>"})

You can use the following comma-separated compatibility options to automatically modify the dumped schemas, which resolves some of these compatibility issues:

• force_innodb: MySQL HeatWave Service supports the InnoDB storage engine, only. This option modifies the ENGINE= clause of CREATE TABLE statements that use incompatible storage engines and replaces them with InnoDB.
• strip_definers: Strips the "DEFINER=account" clause from views, routines, events, and triggers. MySQL HeatWave Service requires special privileges to create these objects with a definer other than the user loading the schema. By stripping the DEFINER clause, these objects will be created with that default definer. Views and Routines have their SQL SECURITY clause changed from DEFINER to INVOKER. This ensures that the access permissions of the account querying or calling these are applied, instead of the user that created them. If your database security model requires views and routines have more privileges than their invoker, manually modify the schema before loading it. See DEFINER and SQL Security.
• strip_restricted_grants: Certain privileges are restricted in the MySQL HeatWave Service. Privileges such as RELOAD, FILE, SUPER, BINLOG_ADMIN, and SET_USER_ID. You cannot create users granting these privileges. This option strips these privileges from dumped GRANT statements.
• skip_invalid_accounts: You cannot export a user that has no password defined. This option skips any such users.
• strip_tablespaces: Tablespaces have some restrictions in the MySQL HeatWave Service. If you need tables created in their default tablespaces, this option strips the TABLESPACE= option from CREATE TABLE statements.
• Primary key flags:
  • create_invisible_pks: Primary keys are required by high availability. If you intend to export data for use in a highly available DB system, add primary keys as they are not defined on the tables. This compatibility flag adds invisible primary keys to each table which requires them. See Prerequisites.
  • ignore_missing_pks: If you do not intend to use high availability on your DB system, this compatibility flag ignores missing primary keys in your dump.

Additionally, DATA DIRECTORY, INDEX DIRECTORY, and ENCRYPTION options in CREATE TABLE statements are always commented out in DDL scripts if the ocimds option is enabled.