7.1 Deploy MySQL DB System with Terraform (basic tutorial)

This tutorial covers:

• Terraform documentation for MySQL OCI (explained for this example)

• Example of provisioning a MySQL OCI service (new compartment, with a VCN and public subnet)

Prerequisites:

For networking documentation (along with Terraform examples), check my tutorials on VCN (basics)

For an introduction into MySQL DB System in OCI, check my tutorials "The basics - OCI UI (MySQL DB System)", "The basics - OCI CLI (MySQL DB System)", respectively "Access MySQL DB System"

Getting familiar with Terraform for MySQL OCI

Resources (for this example)

Resource oci mysql mysql db system- creates and launches the MySQL DB.

The required fields of oci mysql mysql db system - provide the admin user, the admin's password, the shape, the storage capacity (GB), the compartment, the AD and subnet OCID

admin's password password must be between 8 and 32 characters long, and must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character.

If you do not offer a proper password, the creation of the instance will fail

shape_name refers to the type of instance on which you want to deploy the MySQL service.

data_storage_in_gb refers to data volume in giga that must be created and attached to the instance

These requirements can be set-up as variables:

root@deploymentmachine:/home/oci_mysql# more variables.tf | grep -A2 -E 'mysql_db_system_admin_password|mysql_db_system_admin_username|mysql_shape_name|mysql_storage'
variable "mysql_db_system_admin_password" {
  default="Str0nkPa$$wd"
}
--
variable "mysql_db_system_admin_username" {
  default = "root"
}
--
variable "mysql_shape_name" {
  default = "VM.Standard.E2.1"
}
--
variable "mysql_storage" {
  default = 50
}

The resource oci_mysql_mysql_db_system will be applied in the create_mysql.tf file:

root@deploymentmachine:/home/oci_mysql# more create_mysql.tf
resource "oci_mysql_mysql_db_system" "mysql_create" {

    admin_password = var.mysql_db_system_admin_password

    admin_username = var.mysql_db_system_admin_username

    availability_domain = var.mysql_db_system_availability_domain

    compartment_id = oci_identity_compartment.MySqlOciCompartment.id

    shape_name = var.mysql_shape_name

    subnet_id = oci_core_subnet.MySqlOciSubnet.id

    ## this appear as optional in documentation
    ## but it is a must to add it

    data_storage_size_in_gb = var.mysql_storage
}

Data sources (for this example)

Data source oci mysql mysql configurations- provides the available configuration for when creating a database system.

The required fields of oci mysql mysql configurations: one needs to provide the compartment OCID, the shape_name and the type.

shape_name refers to the type of instance on which you want to deploy the MySQL service.

root@deploymentmachine:/home/oci_mysql# more variables.tf | grep -A2 'shape'
variable "mysql_shape_name" {
  default = "VM.Standard.E2.1"
}

typerefers to either a "DEFAULT" configuration or a "CUSTOM" configuration.

We will provision the MySQL service on a VM.Standard.E2.1 instance, with DEFAULT configuration, in data.tf file:

root@deploymentmachine:/home/oci_mysql# 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"]

}

Code example

The files under oci_mysql folder:

root@deploymentmachine:/home/oci_mysql# tree -I '*.backup|*.tfstate'
.
├── 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

The content of variables.tf

root@deploymentmachine:/home/oci_mysql#  more variables.tf

variable "compartment_ocid" {
  default = "ocid1.tenancy.oc1..aaaaaaaa5u7673g6im7w7533shadcgbqxuzpcv6toqmb5yxzxigmeirt6hdq"
}

# for mysql
variable "mysql_db_system_admin_password" {
  default="ABCabc123$%"
}

variable "mysql_db_system_admin_username" {
  default = "root"
}

variable "mysql_shape_name" {
  default = "VM.Standard.E2.1"
}

variable "mysql_db_system_availability_domain" {
  default = "Aodz:EU-FRANKFURT-1-AD-1"
}

variable "mysql_storage" {
  default = 50
}


#for vcn block

variable "cidrblockz" {
  type = list(string)
  default = ["10.0.0.0/16"]
}

#for subnet

variable "cidrsubnet" {
  default = "10.0.1.0/24"
}

# for ingress


variable "cidr_ingress" {
  default = "10.0.0.0/16"
}

# for security list

variable "portz" {
 default = [22,3306,33060]
}

The provider.tf (just in case)

root@deploymentmachine:/home/oci_mysql#  more provider.tf

provider "oci" {
  tenancy_ocid = "ocid1.tenancy.oc1..aaaaaaaafaketenancyocidhahahah123234234"
  user_ocid = "ocid1.user.oc1..aaaaaaafakeuserocidhahahah12312434"
  private_key_path = "/root/.oci/oci_api_key.pem"
  fingerprint = "xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
  region = "eu-frankfurt-1"
}

1. For compartment & networking configuration

The content of compartment.tf

resource "oci_identity_compartment" "MySqlOciCompartment" {
    compartment_id = var.compartment_ocid
    description = "Compartment test for VCN"
    name = "MySqlOciCompartment"
}

The content of internet gateway, int_gateway.tf

root@deploymentmachine:/home/tests/terra/test12# more int_gateway.tf
resource "oci_core_internet_gateway" "MySqlOciInternetGateway" {
  compartment_id = oci_identity_compartment.MySqlOciCompartment.id
  display_name = "MySqlOciInternetGateway"
  vcn_id = oci_core_virtual_network.MySqlOciVCN.id
}

The content of vcn.tf:

root@deploymentmachine:/home/oci_mysql#  more vcn.tf
resource "oci_core_virtual_network" "MySqlOciVCN" {
  cidr_blocks = var.cidrblockz
  compartment_id = oci_identity_compartment.MySqlOciCompartment.id
  display_name = "MySqlOciVCN"

  # for dns

  dns_label = "MySqlOciVCN"
}

Content of dhcp_opt.tf:

root@deploymentmachine:/home/oci_mysql# more dhcp_opt.tf
resource "oci_core_dhcp_options" "MySqlOciDHCPOptions" {

  compartment_id = oci_identity_compartment.MySqlOciCompartment.id
  vcn_id = oci_core_virtual_network.MySqlOciVCN.id
  display_name = "MySqlDHCPOptions"

  options {
    type = "DomainNameServer"
    server_type = "VcnLocalPlusInternet"
  }

  options {
    type = "SearchDomain"
    search_domain_names = ["mysqldboci.com"]
  }

 }

The content of route.tf

root@deploymentmachine:/home/oci_mysql# more route.tf
resource "oci_core_route_table" "MySqlOciRouteTable" {
  compartment_id = oci_identity_compartment.MySqlOciCompartment.id
  vcn_id = oci_core_virtual_network.MySqlOciVCN.id
  display_name = "MySqlOciRouteTable"

  route_rules {
    destination = "0.0.0.0/0"
    network_entity_id = oci_core_internet_gateway.MySqlOciInternetGateway.id
  }
}

The content of subnet.tf

resource "oci_core_subnet" "MySqlOciSubnet"{

  cidr_block = var.cidrsubnet
  compartment_id = oci_identity_compartment.MySqlOciCompartment.id
  vcn_id = oci_core_virtual_network.MySqlOciVCN.id

  display_name = "MySqlOciSubnet"

  # security list

  security_list_ids = [oci_core_security_list.MySqlOciSecurityList.id]

  # route table

  route_table_id = oci_core_route_table.MySqlOciRouteTable.id

  # dhcp
  dhcp_options_id = oci_core_dhcp_options.MySqlOciDHCPOptions.id

  # dns
  dns_label = "MySqlOci"

}

The security lists, security_list.tf:

root@deploymentmachine:/home/oci_mysql# more security_list.tf
resource "oci_core_security_list" "MySqlOciSecurityList" {

  compartment_id = oci_identity_compartment.MySqlOciCompartment.id

  display_name = "MySqlOciSecurityList"

  vcn_id = oci_core_virtual_network.MySqlOciVCN.id

  egress_security_rules {
    stateless = false
    protocol = "6"
    destination = "0.0.0.0/0"
 }

   # apply ingress tcp rules for each port
   # of variable portz

   dynamic "ingress_security_rules" {
    for_each = toset(var.portz)
      content {
        protocol = "6"
        source = "0.0.0.0/0"
        tcp_options {
           max = ingress_security_rules.value
           min = ingress_security_rules.value
       }
    }
  }



  ingress_security_rules {
    stateless = false
    protocol = "6"
    source = var.cidr_ingress
  }
}

2. For MySQL OCI configuration

The content of data.tf:

root@deploymentmachine:/home/oci_mysql#  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"]

}

The content of create_mysql.tf

root@deploymentmachine:/home/oci_mysql#  more create_mysql.tf
resource "oci_mysql_mysql_db_system" "mysql_create" {

    admin_password = var.mysql_db_system_admin_password

    admin_username = var.mysql_db_system_admin_username

    availability_domain = var.mysql_db_system_availability_domain

    compartment_id = oci_identity_compartment.MySqlOciCompartment.id

    shape_name = var.mysql_shape_name

    subnet_id = oci_core_subnet.MySqlOciSubnet.id

    data_storage_size_in_gb = var.mysql_storage
}

Checking the deployed MySQL OCI in Oracle Cloud UI

Deploy the environment by using the main terraform commands:

• terraform init

• terraform plan

• terraform apply

 root@deploymentmachine:/home/oci_mysql# terraform apply
 [ ... snip ... ] 
 Plan: 8 to add, 0 to change, 0 to destroy.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

oci_identity_compartment.MySqlOciCompartment: Creating...
oci_identity_compartment.MySqlOciCompartment: Creation complete after 1s [id=ocid1.compartment.oc1..a]
data.oci_mysql_mysql_configurations.mysql_shape: Reading...
oci_core_virtual_network.MySqlOciVCN: Creating...
data.oci_mysql_mysql_configurations.mysql_shape: Read complete after 0s [id=MysqlMysqlConfigurationsDataSource-3606668890]
oci_core_virtual_network.MySqlOciVCN: Creation complete after 1s [id=ocid1.vcn.oc1.eu-frankfurt-1.aq]
oci_core_dhcp_options.MySqlOciDHCPOptions: Creating...
oci_core_internet_gateway.MySqlOciInternetGateway: Creating...
oci_core_security_list.MySqlOciSecurityList: Creating...
oci_core_internet_gateway.MySqlOciInternetGateway: Creation complete after 0s [id=ocid1.internetgateway.oc1.eu-frankfurt-1.aa]
oci_core_route_table.MySqlOciRouteTable: Creating...
oci_core_security_list.MySqlOciSecurityList: Creation complete after 0s [id=ocid1.securitylist.oc1.eu-frankfurt-1.aq]
oci_core_dhcp_options.MySqlOciDHCPOptions: Creation complete after 0s [id=ocid1.dhcpoptions.oc1.eu-frankfurt-1.aq]
oci_core_route_table.MySqlOciRouteTable: Creation complete after 0s [id=ocid1.routetable.oc1.eu-frankfurt-1.aa]
oci_core_subnet.MySqlOciSubnet: Creating...
oci_core_subnet.MySqlOciSubnet: Creation complete after 4s [id=ocid1.subnet.oc1.eu-frankfurt-1.aa]
oci_mysql_mysql_db_system.mysql_create: Creating...
oci_mysql_mysql_db_system.mysql_create: Still creating... [10s elapsed]
oci_mysql_mysql_db_system.mysql_create: Still creating... [20s elapsed]
oci_mysql_mysql_db_system.mysql_create: Still creating... [30s elapsed]

[ ....  this will take a while ... ]

oci_mysql_mysql_db_system.mysql_create: Still creating... [11m40s elapsed]
oci_mysql_mysql_db_system.mysql_create: Creation complete after 11m42s [id=ocid1.mysqldbsystem.oc1.eu-frankfurt-1.aq]

Apply complete! Resources: 8 added, 0 changed, 0 destroyed.
root@deploymentmachine:/home/oci_mysql#  

The deployment of the MySQL OCI service may take a while (around 15min in this case).

In the meantime, you can check in the OCI UI if the compartment has been created, along with the configuration in progress for MySQL OCI:

Go to Menu > MySQL > DB Systems

...and choose the compartment MySqlOciCompartment from List Scope:

As soon as all resources are created, the MySQL DB will be in Active status:

Destroy resources

To destroy resources, use the command "terraform destroy"

Discussion

This tutorial was meant to be a brief introduction in Terraform and MySQL DB System.

If you try to reach the MySQL DB from your machine of deployment, it will obviously fail:

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.

Work around on this subject in next tutorial