• Arpit Narula

Leverage Oracle MySQL Database Service (MDS) with Oracle Integration Cloud

Updated: Aug 2

A brief history of MySQL

MySQL is one of the most widely used open-source RDBMS. It was owned and sponsored by the Swedish company MySQL AB, which was bought by Sun Microsystems in 2008. In 2010, Oracle acquired Sun Microsystems and since then it is 100% developed, managed, and supported by the Oracle's MySQL team and MySQL Database Service (aka MDS) is a fully managed database service available in Oracle Cloud as PaaS.


Objective

The primary objective of this blog is to detail out how Oracle's MySQL DB can be provisioned on OCI Gen -2 and integrate it with Oracle Integration Cloud. MySQL Database Service on Oracle Cloud might prove out to be a very cost effective and performance enhancing solution for many use cases. A few such use cases are

  • Custom Logging and Error Handling Frameworks

  • Implementing Parking Lot Patterns for Integrations

  • Building Data Stage for VBCS applications


So let's jump into the fun sections where we will cover the following topics in detail.

a) Create an MDS Cloud Instance

b) Setup compute instance (that will connect to MDS)

c) Create an OIC Instance and Agent Setup

d) Build a sample integration


a) Create an MDS Cloud Instance

Before creating the MySQL instance, we need to setup several other objects in Oracle Cloud such as:

i) Compartment

ii) Policy

iii) VCN


i) Compartment

Once in Oracle Cloud console, from the Navigation Menu, under Governance and Administration, select Identity -> Compartments





ii) Policy

On the Navigation Menu, under Governance and Administration, select Identity -> Policies

Note: The policy needs to be created in root compartment


Add the following 3 statements in the Policy:

▪ Allow group Administrators to {COMPARTMENT_INSPECT} in tenancy o Policy Statement : ▪ Allow group Administrators to {VCN_READ, SUBNET_READ, SUBNET_ATTACH, SUBNET_DETACH} in tenancy o Policy Statement

▪ Allow group Administrators to manage mysql-family in tenancy


iii) VCN

On the Navigation Menu, under Core Infrastructure, select Networking -> Virtual Cloud Networks. Select Start VCN Wizard, select VCN with Internet Connectivity and click on Button Start VCN Wizard


Provide a VCN name and Under Configure VCN and Subnets,

add 10.0.0.0/16 on VCN CIDR Block,

add 10.0.0.0/24 on Public Subnet CIDR Block, and

10.0.1.0/24 on Private Subnet CIDR Block and click Next


Once the VCN is created, you will see 2 subnets under this VCN (public and private)






iv) MySQL DB Instance

On the Navigation Menu, under Database, select MySQL -> DB Systems

Click on Create MySQL DB System

Provide the necessary details on the page such as:

  • Compartment: The one we created in the beginning

  • Instance Type: Standalone or High Availability or Heatwave

  • Credentials: These will be MySQL admin username and password

  • VCN and Subnet: Choose the Private subnet that was created as part of VCN setup

You can keep default values for rest of the parameters such as Backup/Retention Period and Backup Window etc. and click "Create". Within few mins, the MDS instance will be provisioned.



Note: Please note that the MDS instance has been provisioned in a Private Subnet and has been given a Private IP. Hence you will not be able to access it from your local system. We will have to create a separate compute instance and add ingress rules to access it.


b) Create an MDS Cloud Instance

In this section we will create a compute instance that will act as a MDS client and we will use this machine to interact with MDS Instance. This is a 3 step approach:

  • Create a compute instance

  • Install MySQL client libraries

  • Add ingress rule to Private Subnet

  • Install Sakila DB

i) Compute Instance

On the navigation Menu, under Core Infrastructure, click on Compute -> Instances

On Instances in MDS_OIC_POC Compartment, click on Create Instance

You will need to provide very basic information such as size, OS version etc. to create this instance.



Let me highlight few key points for the configuration:

  • Make sure you assign a Public IP to this machine

  • Generate ssh key pair locally and upload public key

ssh-keygen -t rsa -N "" -b 2048 -C "mds.client.keys"

Once the Linux machine is ready, you can login using the Public IP, Private key and opc username. Here is a screenshot of the same.


ii) Install MySQL Libraries

Now we have to install MySQL Libraries on this machine so that it can connect the MDS instance.

Download MySQL Yum Repository

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm



Install MySQL

sudo yum install –y mysql80-community-release-el7-3.noarch.rpm


Install MySQL Shell

sudo yum install –y mysql-shell

iii) Add Ingress Rule

Now we have to configure the Private Subnet to accept traffic on MySQL port.

In the Console, on the Navigation Menu, under Core Infrastructure, click on Networking -> Virtual Cloud Networks. Click MDS-VCN and select "Security Lists" from left bottom links


Select the Security List for Private Subnet and click on "Add Ingress Rules". Add the detail as shown in below screenshot.

The new Ingress Rules will be shown under the Ingress Rules List.

After this you should be able to access MDS instance from the compute using the following command.

mysqlsh -u<MDS Admin Username> -p -h<Private Ip of MDS Instance>

iv) Install Sakila DB

Now for our POC purpose we can install a sample Database in MDS instance called "Sakila".

Download the Sakila DB on compute

wget https://downloads.mysql.com/docs/sakila-db.tar.gz


Change to SQL Prompt and run scripts

This marks the completion of Compute instance setup.


c) Build an OIC instance and Setup Agents

In this section we will provision an OIC instance and setup connectivity agents that will connect OIC to MDS instance via the compute instance we created in above section.

OIC Instance

Provisioning an OIC instance is very simple in OIC. We just need to set the message pack parameter. We have taken 1 message pack for this POC.

Agent Setup

For setting up the agents we need to download the following 3 components on Compute instance.

a) OIC Connectivity Agent jar

For OIC agent, follow the below:

Click Home > Integrations, then click Agents.

Click Download > Connectivity Agent.

b) JDK

https://www.oracle.com/java/technologies/javase/javase8u211-later-archive-downloads.html#license-lightbox


c) MySQL agent connector

https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.25.zip

Once we have these components downloaded, create 2 folders on compute instance oicagent and java.

  • Move the OIC agent zip to oicagent folder and unzip it

  • Move jdk tar to java folder and untar it

  • Move MySQL agent connector zip in ~/oicagent/agenthome/thirdparty/lib and unzip

  • Move mysql-connector-java-8.0.25.jar from unzipped location to lib folder

Now we need to create an Agent Group in OIC instance. Go to OIC service console.

Click on Integrations and select Agents.

Click "Create Agent Group" and add the details as shown below.

Update the InstallerProfile.cfg file at /home/opc/oicagent location. Add OIC URL, Agent Group name and OIC credentials.

Now we need to start the agent, post which we can verify its status in Agents section on OIC


Now we are good to create connection to MDS instance and use that in OIC integrations!


d) OIC integration with MDS Instance

Open OIC Integration console and create 2 connections.

i) Connections

Invoke: We will configure MySQL Adapter connection as Trigger type.

  • Add host, port and DB name details in MySQL Adapter connection as shown below

  • Provide credentials

  • Add the agent configured in prior sections


Test Connection Successful!


Trigger: And a REST adapter as an Trigger Type. Create a simple REST API connection with Basic Authentication Security

ii) Basic Routing

Create a basic routing integration and drag REST connection on Trigger side and MySQL connection on Invoke side.

MySQL Connection configuration

REST Connection configuration


This is the sample response payload you can upload in sample response format section of the configuration.

<ActorList>

<Actor>

<id>1</id>

<name>Cuba</name>

<surname>Gooding</surname>

</Actor>

<Actor>

<id>2</id>

<name>Hal</name>

<surname>Jordan</surname>

</Actor>

</ActorList>


Transformation

There is no request transformation in this integration as it is a simple get call without any parameters. I have pasted the response transformation that was applied between MySQL getActorList Response and REST getActorListAPI Response.

That is it. Post deployment we can test this integration from OIC Test console or Postman.

Here is the screenshot from Postman where we received the rows from actors table from MDS instance.

Thanks for going through this Article. If any doubts, you can ask me in the below section, I will try to answer those.

111 views