How to Setup MySQL InnoDB Cluster on OCI Using Terraform

 

Introduction

Setting up a MySQL InnoDB Cluster on Oracle Cloud Infrastructure (OCI) using Terraform allows you to automate the deployment of a highly available, self-healing MySQL environment. The InnoDB Cluster offers features such as native high availability, automatic failover, and built-in group replication, making it ideal for production-grade database deployments.

Using Terraform, Oracle's recommended Infrastructure as Code (IaC) tool, simplifies and standardizes the provisioning of cloud resources. It ensures repeatable, version-controlled, and scalable deployments.

In this blog post, I will provide a step-by-step guide on how to set up a three-node MySQL InnoDB Cluster on Oracle Cloud Infrastructure (OCI). This setup includes a Bastion host where the MySQL Router is deployed to load balance application connections to the clustered database.

Pre-requisites

The following requirements are needed to setup the InnoDB Cluster

1. Create your API private and public keys
2. Create your SSH keys.
3. Access to Oracle Cloud Infrastructure resource.

 

Step 1 Add your API key to using the steps below.

1.           Login to OCi
2.        From your user profile -> Click on API Keys -> Add Public Key

Step 2 Terraform Installation

   1.     Download the software using the command below.

          $ wget  https://releases.hashicorp.com/terraform/0.13.4/terraform_0.13.4_linux_amd64.zip

        2. Unzip the file and validate the terraform version

              $ unzip terraform_0.13.4_linux_amd64.zip

              $ terraform version

 3.     Get the Terraform code

$ git clone https://github.com/lefred/oci-mysql-idc.git

4. Create the terraform.tfvars from the template.

   $ cd oci-mysql-idc

   $ cp terraform.tfvars.template terraform.tfvars

   Replace the following based on your OCI environment and requirement:

-- tenancy_ocid, user_ocid, fingerprint, private_key_path, region

--compartment_ocid,ssh_authorized_keys_path, ssh_private_key_path, 

clusteradmin_password,, cluster_name, number_of_nodes & node_shape

Below is my terraform.tfvars file after updating it with the details of my OCI environment. 

 

5.     Terraform Deployment

-                  Initialize the environment with the command below from the oci-mysql-idc directory.

     $ terraform init  

          6.     Create a terraform plan with the command below 

                $ terraform plan 

   7. Execute the terraform apply command to create the InnoDB cluster. 

       $ terraform apply 

         Confirm by responding with “yes” to provision the cluster

    After successful provisioning of the MySQL InnoDB cluster, you will see the

     IP addresses of the of the cluster nodes and the Bastion/MySQL router server.

Test connectivity to the Bastion and confirm that the router is running. 

    Connect to the MySQL InnoDB Cluster 

   Switching from the JavaScript/Pyton interface to the SQL interface

    Check InnoDB Cluster Status to confirm the Primary and Secondary Database Instances

8. To delete all the resources that where provisioned, execute the command below.

$ terraform destroy  

A Day in the Life of a Managed Services Administrator: Navigating Databases and Cloud Environments

Working as a Managed Services Administrator responsible for databases and cloud infrastructure is both challenging and rewarding. Each day presents a unique set of tasks—from ensuring database performance to managing cloud resources. Here’s a glimpse into my typical day.

---

Monitoring and Incident Management

My day begins with a review of database tickets raised by clients, as well as those automatically generated from Oracle Enterprise Manager (OEM) alerts. These tickets help track and manage database issues across client environments. Each ticket is prioritized based on its urgency, and I ensure timely updates are provided in accordance with the Service Level Agreements (SLAs) established with the clients. I also make it a priority to resolve completed tickets promptly, aiming to maintain a clean and organized ticket queue.

In addition to ticket management, I check for anomalies in database performance, storage utilization, and cloud resource consumption. Any identified issues are addressed based on severity and potential client impact.

---

Maintenance and Optimization

After addressing immediate concerns, I shift my focus to routine maintenance activities. This includes applying database patches (when patch windows are scheduled), optimizing SQL queries, performing database and Data Guard health checks, and ensuring cloud infrastructure is right-sized for current workloads. I also collaborate with teammates, discussing ongoing support activities and sharing lessons learned or insights.

---

Learning and Documentation

I dedicate time each day to learning—whether by testing new concepts or exploring emerging technologies. Staying up to date is essential in this rapidly evolving field. Equally important is documentation: I ensure all actions taken are thoroughly recorded for future reference, accountability, and compliance.

---

Monthly Reports

At the beginning of each month, I collaborate with other team members to prepare monthly reports for our clients. These reports highlight database availability metrics, resource utilization trends, and performance insights—enabling clients to forecast capacity needs and plan ahead.

---

What Difficulties Do We Face in Managed Services?

The demands of Managed Services go far beyond system setup or periodic updates. We remain actively engaged until issues are fully resolved. This requires continuous server maintenance and 24/7 monitoring to ensure optimal database and cloud performance.

We also rotate weekly on-call (pager) duty, meaning we must be ready to respond to alerts or emergencies at any time—day or night. This level of commitment is key to maintaining the high reliability our clients expect.

---

Why Managed Services Matter

By providing clients with 24x7 managed services for their database and cloud infrastructure, we deliver consistent monitoring, maintenance, and expert support for their mission-critical systems. This allows them to focus on their core business operations while trusting us to ensure stability, performance, and resilience.

---

How to enable SSL on MySQL Source and Replica

 The procedure below outlines the steps to configure SSL on both the MySQL source and replica.

Before generating a Certificate Authority (CA) certificate, confirm whether an existing CA certificate is already present on the primary server. If a certificate exists, you can use the command below to verify its details:

$ cd /mysql/data

$ openssl x509 -in ca.pem -text -noout

 Where ca.pem is the CA certificate.

Use the details below to create a certificate on the source and replica

 Step 1: Generate the CA Certificate

$ openssl genpkey -algorithm RSA -out ca-key.pem

$ openssl req -new -key ca-key.pem -out ca-csr.pem

$ openssl x509 -req -in ca-csr.pem -signkey ca-key.pem -out ca.pem

 

Step 2 Generate the Server Certificate and Key

$ openssl genpkey -algorithm RSA -out server-key.pem

$ openssl req -new -key server-key.pem -out server-csr.pem

$ openssl x509 -req -in server-csr.pem -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem

 

Step 3: Generate the Client Certificate and Key

$ openssl genpkey -algorithm RSA -out client-key.pem

$ openssl req -new -key client-key.pem -out client-csr.pem

$ openssl x509 -req -in client-csr.pem -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem

 

Step 4 : Edit MySQL Configuration

 Add the following entries to the my.cnf file on the source and replica

On the source

 vi /etc/mysql/my.cnf

ssl-ca = /mysql/data/ca.pem

ssl-cert = /mysql/data/server-cert.pem

ssl-key = /mysql/data/server-key.pem

 On the replica

 vi /etc/mysql/my.cnf

ssl-ca = /mysql/data/ca.pem

ssl-cert = /mysql/data/client-cert.pem

ssl-key = /mysql/data/client-key.pem

Restart the replica MySQL instance

Step 5: Restart MySQL server for changes to take effect

  Verify SSL Configuration on the Server

  SHOW VARIABLES LIKE '%ssl%';

 

Step 6. Configure MySQL Client to Use SSL

 $ mysql -u username -p --ssl-ca=/mysql/data/ca.pem --ssl-cert=/mysql/data/client-cert.pem --ssl-key=/mysql/data//client-key.pem -h mysql-server-hostname

 

 mysql> SHOW STATUS LIKE 'Ssl_cipher';

 From the output ensure that SSL is enabled

Step 7. Enable replication

mysql> CHANGE REPLICATION SOURCE TO
              SOURCE_HOST = 'source IP address',
              SOURCE_PORT = 3306,
              SOURCE_USER = 'repltest',
               SOURCE_PASSWORD = 'xxxxxxxxx',
          SOURCE_AUTO_POSITION = 1;

How to Identify Root Cause of OCI DCBS Patch Operation Failure

 

Patching of Oracle database is now easy with the use of the OCI console.

In the OCI Console, you can identify a failed update operation by checking the update history of a DB system or individual database. If an update fails, its status will be marked as 'Failed,' along with a brief description of the error. If the error message doesn't provide sufficient information to resolve the issue, you can use the database CLI and log files to gather additional details.

$ cd /opt/oracle/dcs/bin

Use the below command to list the patching jobs on the DBCS VM. 

$ dbcli list-jobs

With the unique ID from the list of patching jobs, use the describe-job command to view more details of the job ID.

$ dbcli describe-job -i 890f9340-b6da-4b72-9c29-bd3a99bcc379  -j 

The output will show details of the job ID including error message is available. 

The logs are found on /opt/oracle/dcs/log location. 

$ cd /opt/oracle/dcs/log

How to Configure a File System using OCI File Storage Service

 

Introduction

Oracle Cloud Infrastructure (OCI) File Storage Service provides a fully managed, scalable file system that enables you to store and access data on-demand in the cloud. It is ideal for applications that require file-based storage with shared access across multiple compute instances. With OCI File Storage, you can manage your data in the cloud like a traditional file system, using protocols like NFS (Network File System), making it simple to integrate into existing workflows and applications.  In this blog I have highlighted the procedure to do the following to create and mount a file storage system to a compute instance and verify the availability of the file storage system.

 Prerequisites

• Access to the OCI console.
• A VCN with internet connectivity.
• A compute instance. 

   For this procedure, I created a VCN fs-vcn-01 with two ingress rules TCP traffic for ports 2048-2050 and 111. 

1.      In this section, we will create file system storage. Click navigation button to open OCI Services menu and click on Storage. Under File Storage, click File Systems.

Click create filesystem to create a new filesystem and under export Information, click edit details and change the export path to an easy-to-remember name.

Under mount target Information, click edit details and click create new mount target, select the VCN you created for Virtual Cloud Network. Choose Public Subnet-YOUR_VCN_NAME (Regional) for Subnet.

The OCI console will show your File System details. Under Exports, click your mount target name under Mount Target. In Mount Target Details page, note down the IP Address.

We now have a file system storage created. Next, use your SSH key pair to connect to your compute instance and mount the file system.  I created a new VM demo-fs-01 for practice.

Using the navigation button, click Storage -> File Storage -> File Systems. Click on the newly created file system. Under Exports, you'll see the mount target name under Mount Target. Click on the mount target name and then on the Action icon on the right and select View Details.

Execute the mount the commands to mount the new filesystem.

Verify the newly created file system.

Before mounting the new NFS filesystem

After mounting the new filesystem

How to use Table Blockchain in Oracle 23ai

 

Oracle 23ai introduces the concept of Table Blockchain, which allows you to create and manage immutable tables. These tables are particularly useful in scenarios requiring a high level of data integrity and security, such as financial transactions, audits, and compliance logging. A blockchain table in Oracle 23ai ensures immutability once data is inserted, it cannot be deleted or modified without detection. The Oracle 23ai database has the capability to detect any attempt to tamper with the data in the table. Each row is hashed and the hash is stored in the row itself. Rows are cryptographically linked, forming a chain of data, similar to a blockchain.

In this blog I will show you how to use Table Blockchain in Oracle 23ai.

Prerequisites

1    You must have a 23ai pluggable database.

2.  You must have a user with access to the pluggable database.

Steps

1.       Connect to the OE schema and create a blockchain table using the CREATE BLOCKCHAIN TABLE command.

SQL> create blockchain table financial sales (order_num number, day date, cust_id number, movie_id number, title varchar(25))
NO DROP UNTIL 15 days IDLE
NO DELETE UNTIL 16 days AFTER INSERT
LOCKED HASHING USING "SHA2_512" version "v2";
 

2.       As the same user, insert records into the newly created financial_sales table and commit.

SQL>  insert into financial_sales values (00359, systimestamp, 5000, 9875, 'Avatar');

commit;

 

3.       Open a new terminal as sysdba user and insert a row into the financial_sales table.

SQL>  INSERT INTO oe.financial_sales VALUES (00123, systimestamp, 5000, 9875,'Top Gun2');

 

4.       As sysdba user from the new terminal, try and update the order_id that the sysdba user has just inserted. You will get an ORA-05715 error.

 

SQL>  UPDATE oe.financial_sales SET cust_id=5001 WHERE order_num=00123;

 

5.       From the first session, as the OE user try and drop the table, and you will get an ORA-05723 which does not allow the user to drop the table based on the NO DROP UNTIL 15 days clause when the table was created.

 

SQL> DROP TABLE oe.financial_sales;