> For the complete documentation index, see [llms.txt](https://isaac-exe.gitbook.io/various-tutorials/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://isaac-exe.gitbook.io/various-tutorials/tutorials/7.-mysql-oci-and-terraform/1.-deploy-mysql-oci-with-terraform.md). # 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) ](/various-tutorials/tutorials/untitled/vcn-basics.md) > For an introduction into MySQL DB System in OCI, check my tutorials "[The basics - OCI UI (MySQL DB System)](/various-tutorials/tutorials/3.-mysql/1.-the-basics-oci-ui.md)", "[The basics - OCI CLI (MySQL DB System)](/various-tutorials/tutorials/3.-mysql/3.2-the-basics-oci-cli.md)", respectively "[Access MySQL DB System](/various-tutorials/tutorials/3.-mysql/3.3-access-mysql-instance.md)" ### Getting familiar with Terraform for MySQL OCI #### Resources (for this example) `Resource` [`oci mysql mysql db system`](https://registry.terraform.io/providers/hashicorp/oci/latest/docs/resources/mysql_mysql_db_system)`- creates and launches the MySQL DB.` `The required fields of` [`oci mysql mysql db system`](https://registry.terraform.io/providers/hashicorp/oci/latest/docs/resources/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`](https://registry.terraform.io/providers/hashicorp/oci/latest/docs/data-sources/mysql_mysql_configurations)`- provides the available configuration for when creating a database system.` **`The required fields of`** [`oci mysql mysql configurations`](https://registry.terraform.io/providers/hashicorp/oci/latest/docs/data-sources/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" } ``` > **`type`**`refers 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`** ![](/files/-MVY3kiQO1z7r0tplGKE) `...and choose the compartment MySqlOciCompartment from`` `**`List Scope:`** ![](/files/-MVY3uTbNyRsDrH96FpC) `As soon as all resources are created, the MySQL DB will be in Active status:` ![](/files/-MVY4-8l1nklbVMtfw9C) ### 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`