Multi-Cloud Data Replication from Amazon RDS MariaDB/MySQL to Oracle Autonomous Database using OCI Goldengate

Published in

Oracle Developers

7 min readDec 4, 2022

Oracle Cloud Infrastructure (OCI) provides Goldengate as a fully managed service. Goldengate provides a comprehensive Change Data Capture (CDC) replication platform, you can use it to build complex data replication topologies using on-premise to cloud, cloud to cloud or any other hybrid model.

OCI Goldengate supports Oracle, MySQL, MariaDB, Apache Kafka, OCI Object Store and OCI Streaming. For both MySQL and MariaDB it supports remote capture and apply of trandactions.

Your source can be running on Amazon RDS MySQL or MariaDB and you can capture transactions from OCI. This is useful in building multi-cloud architecture For eg: Your transactional applications can be running on one cloud and you can build your analytics capability on OCI

For this blog post we will be capturing transactions from Amazon RDS MariaDB 10.5 instance and writing it to Oracle Autonomous Data Warehouse

Architecture

Requirements

MariaDB 10.4 or 10.5 or MySQL 5.7, 8.0 instance running on Amazon RDS
Oracle Autonomous Data Warehouse running on OCI
OCI Goldengate Deployment for Oracle
OCI Goldengate Deployment for MySQL
Low-latency connection between AWS and OCI (private connectivity preferred)

Important Link : Database Versions supported for MySQL on OCI Goldengate

AWS Configuration Before Getting Started

All the steps performed here are using Amazon RDS MariaDB 10.5 but you can also use RDS MySQL 8.0 with little to no change in these steps.

Create a new parameter group for MariaDB version you are using and set below parameters.

2. Apply the new parameter group to the RDS MariaDB instance and reboot the instance.

3. Create a sample table called orders.

CREATE TABLE demo.orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint,
  product_id bigint,
  product_description VARCHAR(500),
  order_delivery_address VARCHAR(500),
  order_date_taken date,
  order_misc_notes VARCHAR(500)
);

Enable automated backups on the RDS instance and make sure the retention of the binlog is long enough to endure any network related outages.

call mysql.rds_set_configuration('binlog retention hours', 24);

OCI Configuration Before Getting Started

1. Deploy a OCI Goldengate deployment for MySQL

Using this OCI GG deployment you can use it both for MySQL and MariaDB for capture and apply.

OCI Goldengate for MySQL Source and Targets

2. Deploy Another OCI Goldengate deployment for Oracle

3. Have an Oracle Autonomous Data Warehouse instance ready and create the same table structure in Oracle

CREATE TABLE admin.orders (
 order_id number,
 customer_id number,
 product_id number,
 product_description VARCHAR2(500),
 order_delivery_address VARCHAR2(500),
 order_date_taken date,
 order_misc_notes VARCHAR2(500),
 CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

If you need to translate MariaDB schema to Oracle you can use this free online schema converter tool.

Steps to Create CDC Replication from Amazon RDS MariaDB to OCI Autonomous Database

1. Add the AWS RDS MariaDB instance as a connection and assign the connection to the MySQL deployment

2. On AWS RDS Security Group make sure ingress is allowed over tcp port 3306 for incoming connections form OCI Goldengate.

You can use the private IP of the Goldengate instance if you have a private virtual circuit setup between AWS VPC and OCI VCN.

3. Launch the OCI Goldengate MySQL console

4. Connect to MariaDB connection to check the connectivity and add the checkpoint table

5. Create an Extract (Capture) process to capture transactions from RDS

If all steps were executed correctly then you will see the Extract process as running.

6. Create a Distribution path to send Goldengate trail files from MySQL to Oracle deployment

a. Create a local credential called ‘distusr’ on the OCI MySQL deployment.

b. Launch OCI Goldengate console for Oracle and add a similar named new user ‘distusr’ as a local user.

c. Create a distribution path on OCI MySQL deployment using target hostname of the OCI Oracle deployment.

If all steps were executed correctly you should see the distribution service running and the trail files ‘MD’ being send to the Oracle deployment as trail files ‘ME’.

7. Create Replicat (Apply) on OCI Goldengate for Oracle Deployment to apply transactions to ADW

Create a connection to Autonomous Database on OCI Goldengate and assign to the OCI Goldengate Oracle deployment.

Add a Replicat on OCI Goldengate for Oracle to apply the trails coming from the OCI MySQL deployment.

If all the steps were executed successfully you can see the replicat running on the OCI Oracle Goldengate deployment.

Testing the Replication from Amazon RDS MariaDB to Oracle Autonomous Datawarehouse

Insert transactions on the MariaDB table created earlier ‘orders’.

INSERT into demo.orders  values (1,1000,2048,"Chair","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into demo.orders  values (2,1000,2049,"Table","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into demo.orders  values (3,1000,2050,"Lounge","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into demo.orders  values (4,1000,2051,"TV","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into demo.orders  values (5,1000,2052,"Fridge","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into demo.orders  values (6,1000,2053,"Mattress","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-31","Deliver to Doorstep");

Check on Autonomous Database from sqlcl tool.

Confirm from the statistics of the extract.

Confirm from the statistics of the replicat.

Summary of Steps

Configure Amazon RDS instance to enable binlog on the MariaDB instance
Create OCI Autonomous DB, OCI Goldengate deployment for Oracle and MySQL
Assign connections to both source and target on both the OCI Goldengate deployments
Create Extract to capture transactions from Amazon RDS MariaDB
Create distribution path on OCI Goldengate for MySQL to send captured trail files to OCI Goldengate Oracle
Create Replicat to apply transactions coming from the remote trails to the Autonomous Database

Conclusion

Using a combination of OCI Goldengate deployments we can easily create replication from MariaDB/MySQL to Oracle Autonomous Database. You can even create MariaDB/MySQL to MariaDB/MySQL replication using the same MySQL deployment acting as a hub for replication.

Oracle Cloud Infrastructure Goldengate provides a very powerful data integration platform which can be used for a variety of use cases with multiple source and target databases.

References:

OCI GoldenGate now supports MySQL, Apache Kafka, OCI Object Store, and OCI Streaming

OCI GG Documentation

Replicate data from OCI MySQL Database to Autonomous Data Warehouse

Understanding What’s Supported for MySQL

Database Versions supported for MySQL and Oracle on OCI Goldengate

Oracle Developers and Oracle OCI Free Tier

Join our Oracle Developers channel on Slack to discuss this and other topics!

Build, test, and deploy your applications on Oracle Cloud — for free! Get access to OCI Cloud Free Tier!