Using Oracle Cloud Infrastructure GoldenGate with MySQL Databases

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.

 
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.

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 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.

 
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.

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.

 
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.

 
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.

 
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.

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

 
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.

 
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.

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

 
Our Extract and Replicat processes are now up and running and show as Active in the OCI 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!