How to Setup MySQL InnoDB Cluster on OCI Using Terraform

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 setting 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 set up the InnoDB Cluster

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

Step 1: Add your API key by using the steps below.

1.         Log in 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 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 were provisioned, execute the command below.

$ terraform destroy