Migrate AWS RDS for MySQL to HeatWave MySQL using OCI Database Migration

July 31, 2024 | 9 minute read
Jorge Martinez
Senior Product Manager
Text Size 100%:

Introduction:

OCI Database Migration service supports MySQL migrations.  Use this guide for step-by-step instructions on how to migrate an AWS RDS for MySQL database to HeatWave MySQL database with minimal downtime.

The service provides validated, cross-version, fault-tolerant, and incremental homogeneous Oracle and MySQL migrations. It simplifies database migration workflows with advanced orchestration automation, source and target compatibility diagnostics and remediation, and a unified user experience. Migration scenarios can be short or long-lived and be performed with or without database downtime, eliminating operational disruption.

For more information about the service visit its home page.
 

high level mysql flow

 

Scenario Assumptions for this exercise:

AWS RDS for MySQL instance:

  • Engine MySQL Community version 8. 
  • Non-multitenant architecture
  • Enable binary logging. Set the backup retention period to a positive nonzero value.
  • Set the instance as Publicly accessible.
  • The DB instance parameter group should include the following values:
    • binlog_row_metadata=FULL
    • binlog_row_image= FULL
    • binlog_format= ROW
    • log_bin=1

Follow the next link to learn more about Parameter group functionality. The following link contains general information about AWS RDS for MySQL.

Oracle Cloud Infrastructure:

  • Use the following instructions to create the resources that OCI Database Migration operations depend on.
  • An existing OCI HeatWave MySQL database to be used as a target, version 8.4.0 was used for this blog. The next link will guide you to create a Heatwave DB system. 

 

Step 1:  Identify the AWS RDS instance connection details

First find the endpoint (DNS name) and port number for the RDS DB instance.

Navigation: Amazon RDS homepage>Databases>Your DB> Connectivity & security tab

 

where to get RDS private endpoint


OCI Database Migration needs an IP address, the following command nslookup + RDS Private Endpoint should show a similar response as the following, take note of the IP address:

 

nslookup and private endpoint

 

In the Configuration tab, locate the following information:

  • DB name (not the DB instance ID)
  • Master username

db name and master username

 

Getting Started with OCI Database Migration

Step 2:  Create a Database connection for the source RDS database in OCI Database Migration

Database connection resources enable networking and connectivity for the source and target databases.

Navigation: Go to Migration & Disaster Recovery > Database Migration > Database connections: Press Create connection.
In the page General Information, fill in the following entries, otherwise leave defaults:

  • Name: Provide a name for the database connection.
  • Compartment: Select your compartment
  • Type: Amazon RDS for MySQL
  • Vault: Select your previously created vault
  • Encryption key: Select your previously created key

Press Next
 

source db connection general information

 

In the page Connection details, fill in the following entries, otherwise leave defaults:

  • Database name: The name of your database
  • Host: The public ip from your source database
  • Port: 3306
  • Initial load database username: Enter the master user name (admin in this exercise)
  • Initial load database password: Enter the password accordingly
  • Select Create private endpoint to access this database. 
    • Select the correct private subnet. **Observe that there is an issue being fixed where for Public IPs this is required.

Press Create
 

source db connection creation connection details

 

Once the connection is Active, click on the Test connection button to validate the connectivity works, if you don’t receive a successful result then review the connection message and correct the connection details.

 

test connection

 

Step 3:  Create a Database connection for the HeatWave MySQL database system (target)

In the page General Information, fill in the following entries, otherwise leave defaults:

  • Name: Provide a name for the database connection.
  • Compartment: Select your compartment
  • Type: OCI MySQL Heatwave
  • Vault: Select your previously created vault
  • Encryption key: Select your previously created key

Press Next

 

create target db connection general information

 

In the page Connection details, fill in the following entries, otherwise leave defaults:

  • Database details, Select MySQL database system
  • Database system: Select your Heatwave instance from the list of values
  • Database name: Enter the same database name from RDS for MySQL
  • Initial load database username: Enter the master user name (admin in this exercise)
  • Initial load database password: Enter the password accordingly
  • Select Create private endpoint to access this database
  • Select the correct private subnet.

Press Create

 

target db connection creation connection details

 

Use Plain as your security protocol during the connections creation, the other options are being revised at the moment and should not be used.

Repeat the steps to test the new connection, once the connection is Active, click on the Test connection button to validate the connectivity works, proceed once the test succeeds.

 

Step 4:  Create a Migration

When a migration is created with OCI Database Migration, it is specified how the migration should run, source and target database connections  are selected, and then the data transport settings are configured . Optionally, advanced GoldenGate and MySQL Shell settings can be configured.

Navigation: Go to Migration & Disaster Recovery > Database Migration >Migrations: Press Create Migration

On the page General information, fill in the following entries, otherwise leave defaults:

  • Name: Provide a name for the migration
  • Compartment: Select your compartment

Press Next
 

migration creation general information

 

On the page Select databases, fill in the following entries, otherwise leave defaults:

  • Source database, Database connection: Select the source database connection (the database connection for the RDS database).
  • Target database, Database connection: Select the target database connection for the HeatWave MySQL database system.

Press Next

 

create migration select databases

 

On the page Migration options, fill in the following entries, otherwise leave defaults:

  • Object Storage bucket: Use the list of values to select the storage bucket previously created.
  • Check the Use online replication option
    • This tells the service to provision an OCI GoldenGate instance, this is transparent to the user and no management or monitoring is needed. 

Press Create
 

migration creation migration options

 

Step 5:  Validating a Migration

Before a Migration job can be executed for a migration resource in OCI Database Migration, the migration resource must be validated.

Navigation: Go to Migration & Disaster Recovery > Database Migration >Migrations> Select your migration> Migration details

Click Validate

This job will validate the source, target, and migration settings, if all is fine it should complete successfully.

If there are any issues the user interface will display the error and provide a potential solution. Once the error is fixed click Validate again for a new job to be created.
 

validation job

 

After a migration resource is validated then migration jobs can be executed.

 

Step 6:  Start the Migration job

Navigation: Go to Migration & Disaster Recovery > Database Migration >Migrations> Select your migration> Migration details

Click Start

When a migration job starts, it can be configured to pause at a specified phase, and then it can be resumed when ready.

A confirmation dialog opens, and there the job can be configured to pause at any point by selecting a phase in Require User Input After, the pre-selected value is Monitor replication lag. This phase monitors Oracle GoldenGate Extract and Replicat operations until Replicat has caught up on the target database; end-to-end (E2E) replication lag should be less than 30 seconds.

 

start migration

 

When the selected phase to pause after completes, the job will enter in a Waiting state until it is resumed (or terminated). If it was selected to pause after the phase Monitor Replication Lag, the transaction replication continues during the Waiting state. It will stop upon resume.

Click on Resume.

 

resume job

 

This phase does the following:

  • Ensures replication E2E lag is still less than 30 seconds
  • Ensures that Extract has captured outstanding transactions on the source database
  • Stops Extract
  • Ensures Replicat has applied all remaining trail file data
  • Stops Replicat

After phase Switchover has completed, the workload on the target database (end of downtime) can start.

The last phase is Cleanup. This phase performs cleanup operations such as deleting GoldenGate Extract and GoldenGate Replicat processes and connection details on source and target database respectively. Learn more of the different phases at the following link.


 

successful migration job

 

Conclusion:

OCI Database Migration (DMS) migrates Oracle databases from on-premises or cloud deployments to OCI. The easy-to-use graphical user experience validates and manages migration workflows. DMS for MySQL migrations transparently uses MySQL Shell as an engine for the initial load, Premigration Advisor Tool, and Oracle GoldenGate service for secure, fault-tolerant and incremental migrations.

 

Additional OCI Database Migration resources:

Jorge Martinez

Senior Product Manager

Jorge Martinez is a Product Manager in the GoldenGate organization focusing on OCI Database Migration service. 


Previous Post

Handle Deleted Source Rows in OCI Data Integration

Carla Romano | 2 min read

Next Post


Securing GoldenGate using Certificates

Volker Kuhr | 13 min read