# 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` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://isaac-exe.gitbook.io/various-tutorials/tutorials/7.-mysql-oci-and-terraform/1.-deploy-mysql-oci-with-terraform.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.