Using Oracle Cloud Infrastructure GoldenGate with MySQL Databases

December 9, 2022 | 6 minute read
Text Size 100%:

OCI GoldenGate is a fully managed service providing a real-time data mesh platform, which uses replication to keep data highly available and enable real-time analysis. OCI GoldenGate supports several sources and targets, including MySQL and OCI MySQL HeatWave Database Service. In this article, we will find out how the MySQL deployment and connection types make it easier than ever to integrate with MySQL databases.

OCI GoldenGate with MySQL databases
 
The OCI GoldenGate MySQL deployment type includes support for OCI MySQL HeatWave Database Service and MySQL and MariaDB databases running on-premises or in 3rd party clouds:

  • MySQL Database Service (OCI MDS)
  • MySQL 5.7 and 8.0 (on-premises & Cloud)
  • MariaDB 10.4 and 10.5 (on-premises & Cloud)
  • Azure Database for MySQL 5.7 and 8.0
  • Amazon Aurora 5.7
  • Amazon RDS for MySQL 5.7 and 8.0
  • Amazon RDS for MariaDB 10.4 and 10.5

Creating a MySQL Deployment

You can create a MySQL deployment using the OCI Console, CLI, or APIs. From the OCI Console menu, go to Oracle Database, click GoldenGate and click Create deployment.
OCI GoldenGate Overview

Go through the deployment creation screens and select MySQL when prompted for a technology. Finally, click Create to start the creation of the deployment.

Create an OCI GoldenGate deployment for MySQL

Create a Connection to OCI MySQL HeatWave Database Service

To create a Connection, go to the Overview or Connections page and click Create connection. Pick OCI MySQL Database Service as the Type and click Next.

Create a connection to a MySQL database in OCI GoldenGate - 1/2
 
Select a database hosted on OCI or enter your database information manually. In this article, we will use Select MySQL database system. Pick your database from the list, enter a username and password, and specify the SSL details. Click Create to proceed with the Connection creation.

Note: The connection automatically creates a private endpoint to access an OCI MySQL HeatWave Database Service instance securely. You can also create a private endpoint when entering the MySQL database details manually, and enabling the Network connectivity via private endpoint option.

Create a connection to a MySQL database in OCI GoldenGate - 2/2

Assign a Connection to a Deployment

A Connection must be assigned to a Deployment before it can be used. The assignment can be done from the Deployment or the Connection. You don’t need to wait for the Connection to be Active to assign it to a Deployment.

Open your Deployment, click Assigned connections, then click Assign connection.

Select your Connection in the list and click Assign connection to create the assignment.

Assign connection to deployment
 
Note: Connection types are compatible with specific deployment types. MySQL Connections can only be assigned to MySQL Deployments, for example. In heterogeneous use cases, you must use different Deployments and connect them using Distribution Paths.
Suppose you plan to send data from an OCI MySQL HeatWave Database Service into OCI Streaming. In that case, you will need two OCI GoldenGate Deployments: MySQL (deployment type: MySQL) and OCI Streaming (deployment type: Big Data).

Create data replication processes using MySQL databases

You can start designing your Extracts and Replicats once you have assigned the required Connections to your Deployment(s).

Go to your Deployment details page, and click Launch console to start the OCI GoldenGate Console. Open the menu, click Configuration, review your Credentials and, when possible, click Connect to test the connectivity to your sources and/or targets.

Test connectivity in GoldenGate
 
Click Overview and click Add Extract (plus icon) in the Extracts panel to create either an Initial Load Extract or a Change Data Capture Extract.

Create an Extract
 
We will create a CDC Extract, select Change Data Capture Extract, and click Next. Enter a Name, enable Remote only if your MySQL database is not using global transaction identifiers (GTIDs), select the Credential Domain and Alias, and enter a Trail Name. Click Next.

Extract Options in GoldenGate Console
 
Specify which tables and schemas GoldenGate will capture data from in the Parameter Files page, then click Create and Run.

Extract parameters in GoldenGate Console
 
Creating a Replicat follows a similar process: click Overview and click Add Replicat (plus icon) in the Replicats panel. Select a Replicat type from Classic, Coordinated, or Parallel. You can find more information about the different Replicat types here. We will select Parallel in this example and click Next.

Replicat creation in GoldenGate Console
 
Enter a Process Name, select a Domain and Alias, specify a Trail Name and enter a Checkpoint Table name (previously created at the Configuration > Credentials level). Click Next.

Replicat options in GoldenGate Console
 
Specify how the source and target tables will be mapped by the Replicat and click Create and Run.

Replicat parameters in GoldenGate Console
 
Our Extract and Replicat processes are now up and running and show as Active in the OCI GoldenGate Console.

End to end replication process in GoldenGate Console
 
We have seen how easy it is to replicate data from and into MySQL databases with OCI GoldenGate. You can follow similar steps to integrate MySQL with any source and target supported by GoldenGate on-premises and OCI GoldenGate. As a next step, I recommend going through the following LiveLab to get familiar with OCI GoldenGate and how it integrates with MySQL: Replicate data from MySQL to Autonomous Database using Oracle Cloud Infrastructure GoldenGate. Stay tuned for new blogs coming up soon!

 

Julien TESTUT

Julien Testut is a Senior Principal Product Manager in the Oracle GoldenGate group focusing on OCI GoldenGate and GoldenGate for Big Data. He previously led product management activities for OCI Data Integration, Data Integration Platform Cloud (DIPC), Oracle Data Integrator (ODI), and ODI Cloud Service. Julien has an extensive background in Cloud, Big Data, Data Integration, Data Quality, and Data Governance solutions. He is also a co-author of the 'Getting Started with Oracle Data Integrator: A Hands-on Tutorial' and 'Oracle Data Integrator Cookbook' books. Before joining Oracle, he was an Applications Engineer at Sunopsis, which Oracle then acquired.


Previous Post

Parallel Replicat – Managing large Transactions

Volker Kuhr | 5 min read

Next Post


OCI GoldenGate Real-Time Ingestion for Azure Data Lake Storage

Deniz Sendil | 8 min read