*Originally published on 07/30/2024
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.
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
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:
In the Configuration tab, locate the following information:
- DB name (not the DB instance ID)
- 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
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
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.
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
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
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
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
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
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.
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.
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.
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.
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.