7.2 Deploy MySQL DB System with Terraform and access the system

This tutorial is a continuation of Deploy MySQL DB System with Terraform (basic tutorial).

I strongly advise you to read it before moving on to this one.

As we have mentioned in the previous tutorial, accessing the MySQL DB System from MySqlOciVCN has been proven to be troublesome:

root@deploymentmachine:/home/oci_mysql#  mysqlsh root@10.0.1.3
Please provide the password for 'root@10.0.1.3': ***********
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@10.0.1.3'
MySQL Error 2003 (HY000): Can't connect to MySQL server on '10.0.1.3' (110)


cannot reach mysql from deploymentmachine.

If we want to access the MySQL DB System, as a work around, we have to deploy an instance on which mysql-shell will be installed (see below diagram)

Adapting the Terraform code

From previous tutorial, we have 11 files that make possible the creation of a MySQL DB System within a VCN, in a different compartment.

root@deploymentmachine:/home/mysqlsh_mysqldb# tree .
.
├── compartment.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── int_gateway.tf
├── provider.tf
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf

0 directories, 11 files

We now need to provision an instance in same VCN and compartment as the MDS, to which we will assign a public IP address.

In order to proceed with the creation of a new instance, a few more variables must be added, such as the instance name, the instance shape, the image of the OS and the path to the ssh private keys.

Addition to file variables.tf

root@deploymentmachine:/home/mysqlsh_mysqldb# cat variables.tf | grep -A2 -E 'instance_*|private_key_path'
## for instance

variable "instance_name" {
  default = "MySqlShellInstance"
}
--
variable "instance_shape" {
  default = "VM.Standard.E2.1"
}
--
variable "instance_image" {
  default =   "ocid1.image.oc1.eu-frankfurt-1.aaaaaaaa2fbceq23oofnxf4v23urfnfzui6n6det6ianoyvtmsbo5nzv2efq"
}
--
variable "private_key_path" {
  default = "/root/.ssh/id_rsa"
}

The new fileinstance.tf will have the following content:

root@deploymentmachine:/home/mysqlsh_mysqldb#  more instance.tf
resource "oci_core_instance" "MySqlOciInstance" {

    availability_domain = var.mysql_db_system_availability_domain
    shape = var.instance_shape

    compartment_id = oci_identity_compartment.MySqlOciCompartment.id

    source_details {
        source_id = var.instance_image
        source_type = "image"
    }

    display_name = var.instance_name

    metadata = {
        ssh_authorized_keys = file("/root/.ssh/id_rsa.pub")
    }

    # for vnics & extracting the public IP

    create_vnic_details {
        assign_public_ip = true
        subnet_id = oci_core_subnet.MySqlOciSubnet.id
    }

}

We also need to change the data.tf file, by adding a couple of data sources, for extracting the primary VNIC, and obtaining the Public IP of the instance.

The file data.tf will now look like this:

root@deploymentmachine:/home/mysqlsh_mysqldb# more data.tf
data "oci_mysql_mysql_configurations" "mysql_shape" {

 compartment_id = oci_identity_compartment.MySqlOciCompartment.id
 display_name = "MySQL OCI Terraform"

 shape_name = var.mysql_shape_name
 type = ["DEFAULT"]

}

## for instance on which mysql-shell is installed

# get a list of vnic attachments

 data "oci_core_vnic_attachments" "MySqlOciVNICs" {

       compartment_id = oci_identity_compartment.MySqlOciCompartment.id
       availability_domain = var.mysql_db_system_availability_domain
       instance_id = oci_core_instance.MySqlOciInstance.id

  }

# get the primary VNIC ID

  data "oci_core_vnic" "MySqlOciVNICprimary" {
     vnic_id = lookup(data.oci_core_vnic_attachments.MySqlOciVNICs.vnic_attachments[0], "vnic_id")

  }

... and, as a last step, we need to install mysql-shell on that instance. We will go ahead and implement this by using "remote-exec" provisioner (since we are using this provisioner, we can also perform the necessary updates, and setup the firewall rules)

The new file remote.tf will have the following content:

root@deploymentmachine:/home/mysqlsh_mysqldb#  more remote.tf
resource "null_resource" "MySqlOciRemote" {

  depends_on = [oci_core_instance.MySqlOciInstance]

  provisioner "remote-exec" {
    inline = ["echo I am in ",
              "hostname",
              "python3 --version",
              "sleep 10",
              "sudo apt update",
              "sudo apt install -y firewalld",
              "sleep 5",
              "sudo firewall-cmd --add-port=3306/tcp --permanent",
              "sudo firewall-cmd --add-port=33060/tcp --permanent",
              " sudo firewall-cmd --reload",
              "sleep 5",
              "sudo apt install -y snapd",
              "sudo snap install mysql-shell"]

    connection {
      type = "ssh"
      user = "ubuntu"
      host = data.oci_core_vnic.MySqlOciVNICprimary.public_ip_address
      private_key = file(var.private_key_path)
   }

  }

}

Now, my folder has a total of 13 files:

root@deploymentmachine:/home/mysqlsh_mysqldb# tree .
.
├── compartment.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── instance.tf
├── int_gateway.tf
├── provider.tf
├── remote.tf
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf

0 directories, 13 files

Code example

The code example is at Github repository: https://github.com/MuchTest/MySqlDbSystemExample1

Comments are added for guidance - you will need to provide certain info if you want the environment to work (tenancy ocid, user ocid ... etc). Find a "how to" guide at this tutorial from Chapter 1.

Checking the deployed MySQL DB System in Oracle Cloud UI

Deploy the environment by using the main terraform commands:

• terraform init

• terraform plan

• terraform apply

When you run the command "terraform apply", the creation of instance "MySqlShellInstance", respectively MySQL DB System will start immediately:

Provisioning the instance

Creating the MySQL DB System

When the instance is launched, the provisioner "remote-exec" will perform the necessary steps for installing and configuring the mysql-shell.

Output from "terraform apply", when mysql-shell is installed:

As you are probably used to it, the creation of a MySQL DB System might take a while (~15min or more, depending on the resources you have chosen for its creation)

Access the MySQL DB System

As soon as both systems are provisioned, try to ssh into the new instance (I mentioned the public IP in these examples because everything will be deleted soon)

root@deploymentmachine:/home/mysqlsh_mysqldb# ssh ubuntu@158.101.170.206
The authenticity of host '158.101.170.206 (158.101.170.206)' can't be established.
ECDSA key fingerprint is SHA256:
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '158.101.170.206' (ECDSA) to the list of known hosts.
Welcome to Ubuntu 20.04.1 LTS (GNU/Linux 5.4.0-1035-oracle x86_64)

 [... snip ...]


ubuntu@mysqlshellinstance:~$ sudo -i
root@mysqlshellinstance:~#

... once you are in, from "mysqlshellinstance", try to reach the MySQL DB System via mysql-shell (in this example, the MySQL DB has 10.0.1.4 as IP address):

root@mysqlshellinstance:~# mysqlsh root@10.0.1.4
Cannot set LC_ALL to locale en_US.UTF-8: No such file or directory
Please provide the password for 'root@10.0.1.4': ***********
MySQL Shell 8.0.22

Copyright (c) 2016, 2020, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'root@10.0.1.4'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.23-u2-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
mysql-py []>
mysql-py []> \sql
Switching to SQL mode... Commands end with ;
mysql-sql []> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0012 sec)
mysql-sql []> \! hostname
mysqlshellinstance
mysql-sql []>
mysql-sql []> \q
Bye!
root@mysqlshellinstance:~# 

Destroy resources

To destroy resources, use the command "terraform destroy"