Configuring initial load advanced options for MySQL migrations

To further tune performance, change the export modes, or select compatibility options, there are several settings that you can configure in the Migration resource Advanced Settings, Initial Load tab for MySQL migrations.

• The following settings are available to fine-tune your MySQL migration:

  • Enable consistent data dumps: Enable consistent data dumps by locking the instance for backup during the dump.

    Note: This option is selected by default.

  • Set the dump time zone to UTC : Include a statement at the start of the dump to set the time zone to UTC.

    Note: This option is selected by default.

  • Handle grant or revoke errors : In the event of errors related to GRANT or REVOKE errors, select any of the following option:

    • abort: (default) stops the load process and displays an error.

    • drop_account: deletes the account and continues the load process.

    • ignore: ignores the error and continues the load process.

  • Ignore existing target objects: Import the dump even if it contains objects that already exist in the target schema in the MySQL instance.

• In Compatibility options, select the specified requirements for compatibility with OCI HeatWave Service for all tables in the dump output, altering the dump files as necessary:

  • Primary key compatibility: Select the primary key compatibility. The following options are available:

    • None: The default option.

    • Create invisible primary keys: Add a flag in the dump metadata to add primary keys in invisible columns, for each table that does not contain a primary key.

    • Ignore missing primary keys: 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.

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

  • Strip restricted grants: Remove specific privileges that are restricted by OCI HeatWave Service from GRANT statements, so users and their roles cannot be given these privileges (which would cause user creation to fail).

  • Strip tablespaces : Remove the TABLESPACE clause from CREATE TABLE statements, so all tables are created in their default tablespaces.

  • Ignore wildcard grants: If enabled, ignores errors from grants on schemas with wildcards, which are interpreted differently in systems where the partial_revokes system variable is enabled.

  • Strip invalid grants: If enabled, strips grant statements which would fail when users are loaded.