Chapter 11 MySQL HeatWave Migration Assistant

The MySQL HeatWave Migration Assistant allows you to migrate on-premises databases to MySQL HeatWave DB Systems in Oracle Cloud Infrastructure (OCI).

This is a separate feature that is unrelated to Chapter 10, Database Migration Wizard.

The migration assistant comes with MySQL Workbench. Download it from the MySQL Workbench Downloads page.

Overview

The MySQL HeatWave Migration Assistant performs the following tasks during the migration of the source database to the new DB System:

1. Automatically signs in to your OCI account, or directs you to the OCI sign-up page.

2. Completes compatibility checks between the source database and the new DB System to ensure that any issues are identified and addressed before the migration proceeds.

3. Creates and provisions the following OCI resources:

   • The jump host.

   • The new DB System along with the appropriate configurations, MySQL HeatWave Cluster (if selected), REST service, and High Availability (if selected).

   • The Object Storage bucket and appropriate pre-authenticated requests (PARs).

   • If required, the VCN and compartment.

4. Performs parallel data export and import from the Object Storage bucket.

5. If required, sets up an SSH tunnel.

6. If required, sets up and monitors inbound replication.

Requirements

• The migration assistant is supported on Microsoft Windows and macOS.

• The source database must be MySQL 5.6 or newer.

• To complete the migration, you need an OCI account. The migration assistant allows you to sign in to your existing account, or create a new OCI account.

Before You Begin

If the OCI account used for the migration is not an administrator account, it must have at least the following privileges. Replace GroupName and CompartmentName with the the appropriate group name and compartment name for the OCI account.

Allow group GroupName to inspect mysql-family in tenancy
Allow group GroupName to inspect compartments in tenancy
Allow group GroupName to inspect vcns in tenancy
Allow group GroupName to inspect subnets in tenancy
Allow group GroupName to use vnics in tenancy
Allow group GroupName to use subnets in tenancy
Allow group GroupName to use network-security-groups in tenancy
Allow group GroupName to inspect instance-family in tenancy

Allow group GroupName to manage mysql-configurations in compartment CompartmentName
Allow group GroupName to manage mysql-family in compartment CompartmentName
Allow group GroupName to manage instance-family in compartment CompartmentName
Allow group GroupName to manage buckets in compartment CompartmentName
Allow group GroupName to manage object-family in compartment CompartmentName

To learn more, see Managing Policies (IAM with Identity Domains) and Managing Policies (IAM without Identity Domains) in Oracle Cloud Infrastructure Documentation.

To start the migration assistant from the MySQL Workbench homepage, click the green migration icon for the appropriate MySQL Connection of the source database. You need to provide the password for the assistant to connect to the source database.

Migration Plan

Configure the settings to set up the migration. Click Next to move to the next section.

Set up the following settings for the new DB System in the Target Selection section.

• OCI Configuration Profile: Select the OCI configuration profile to use, which is configured in the OCI CLI config file. To learn more, see Setting up the Configuration File in Oracle Cloud Infrastructure Documentation.

• OCI Compartment: Select one of the following.

  • The compartment in your tenancy where the DB System and other required resources are created.

  • MySQL (create new) to automatically create a new compartment named MySQL.

  To learn more, see Working with Compartments in Oracle Cloud Infrastructure Documentation.

• OCI Networking: Select one of the following.

  • Create New to automatically create a suitable Virtual Cloud Network (VCN) named MySQLVCN in a compartment named Networks. The VCN will have a private subnet where the new DB System is placed, and a public subnet where a compute instance is placed to serve as a jump host.

    If you plan to host your applications in OCI, you can attach your application compute instances to the private subnet MySQLSubnet in this network. That allows the applications to connnect to the new DB System.

    If you plan to host applications outside OCI, configure a suitable network using Site-to-Site VPN or FastConnect from the OCI console. Then, select Use Existing and enter the appropriate networking details.

    If needed, you can specify the IP block allowed to connect to the jump host in the Source IP Allowed on Jump Host (CIDR) field. The default for this is the automatically detected source IP address for connections from the host where the migration assistant is running. You can change these settings for the VCN in the OCI console.

    See the following to learn more:

    • Site-to-Site VPN

    • FastConnect

    • VCN and Subnet Management

  • Use Existing and select the appropriate details to use a previously created VCN that has specific requirements. You can also create a custom VCN, restart the migration assistant, and then select it.

  To learn more, see VCNs and Subnets in Oracle Cloud Infrastructure Documentation.

• Configuration Template: Select a pre-defined configuration template for the new DB System based on the desired setup and required workload. By default, Production Workload templates enable backups, volume auto-expand, and High Availability, while Development Setup templates disable these options. There is also an Always Free template, which provisions a maximum of one free instance. To learn more, see Supported Shapes in the MySQL HeatWave on OCI Service Guide.

• Disk Size (GB): Enter the required size of the data volume for the new DB System. This amount must be large enough to contain the database tables, indexes, objects, redo log, binary logs, and other internal data. The minimum size is 50 GB. The volume will be configured to auto-expand for Production Workload templates, but not for Development Setup templates.

• Setup Type: Select one of the following.

  • Standalone to create a single instance of the new DB System.

  • High Availability to create three instances of the new DB System, which allows for automated failover and greater uptime.

  To learn more, see High Availability in the MySQL HeatWave on OCI Service Guide.

• HeatWave Cluster: Select one of the following.

  • The appropriate Cluster configuration to attach to the new DB System.

  • HeatWave OFF to not provision a Cluster to the new DB System.

  To learn more, see MySQL HeatWave Cluster in the MySQL HeatWave on OCI Service Guide.

• Display Name: Enter the name for the new DB System.

• Contact Emails (optional): Enter email addresses that will receive administrative emails related to the new DB System.

• MySQL Admin Username: Enter the username for the administrator account in the new DB System. By default, the username for the source database is used. Certain usernames are reserved, and you cannot use them for the administrator username. For the list of reserved usernames, see Reserved Usernames in the MySQL HeatWave on OCI Service Guide.

• Password: Enter the password for the administrator account in the new DB System. By default, the password for the source database is used.

Select the appropriate type of migration in the Migration Type section.

• Cold Migration: Create and load a snapshot of the source database into the new DB System. Updates to the source database during the migration are not loaded to the new DB System. To prevent missing updates, stop applications from writing during the migration process.

• Hot Migration: Create a snapshot of the source database, and then create an inbound replication channel for the new DB System. The replication channel continuously applies any updates received by the source database to the new DB System. The new DB System must be able to connect from the VCN to port 3306 of the source database host. The credentials for the source database are stored in OCI so that the inbound replication channel can connect to it.

The Schema Compatibility Checks section scans for compatibility issues in the schema of the source database. Select any issues, review the description of the issue, and then select the appropriate resolution option.

Review the Preview Migration Plan section to confirm the settings for the migration, and then click Start Migration Process.

Provisioning

Once the migration process starts, the following stages are completed.

• Compartment: Provision the new OCI compartment or verify the existing compartment. This is where the new DB System and other resources are stored.

• Object Storage: Create a bucket in OCI object storage for temporary storage of exported data for the migration. See Object Storage Buckets in Oracle Cloud Infrastructure Documentation.

• Virtual Cloud Network: Provision the new VCN or verify the existing VCN.

• Compute: Provision a compute instance and use it as a jump host.

• Prepare Jump Host: Install MySQL Shell and other components for importing the data from the source database.

• MySQL DB System: Launch the new DB System in OCI. This process may take about 15 minutes to complete.

• MySQL HeatWave Cluster: If configured, add the MySQL HeatWave Cluster to the new DB System and wait for it to become active.

Database Migration

The following steps are completed for the migration of the source database.

• Export: Export a snapshot of the schema and data from the source database to the object storage bucket.

• Connect to DB System: Verify if the new DB System can accept connections from the jump host.

• Import: Import the snapshot of the source database into the new DB System.

• Enable Crash Recovery: Re-enable crash recovery, which is temporarily disabled for the migration.

• Enable High Availability: If configured, enable high availability for the new DB System. This process may take 10 to 30 minutes to complete.

Data Synchronization

If you select Hot Migration, the following steps are completed for the synchronization of data.

• Setup SSH Tunnel: Launch an SSH tunnel to enable MySQL replication connections from the new DB System to the source database. Follow any on-screen instructions to set it up.

• Check Connectivity: Check network connectivity from OCI to the source database. This is required to keep the new DB System up-to-date using inbound replication.

• Start Replication: Create a MySQL replication channel from the new DB System to the source database instance, which replicates new transactions applied to the source database at the target.

Finalize

The following steps complete the migration process.

• Monitor Replication Progress: Monitor the MySQL replication channel until your applications are switched over.

• Database Ready: Confirmation that the migration process completed successfully and the new DB System is ready to use with information on accessing it. To learn more about MySQL HeatWave, see the MySQL HeatWave User Guide and the MySQL HeatWave on OCI Service Guide.