# 7.2 Deploy MySQL DB System with Terraform and access the system > *This tutorial is a continuation of* [*Deploy MySQL DB System with Terraform* ](https://isaac-exe.gitbook.io/various-tutorials/tutorials/7.-mysql-oci-and-terraform/1.-deploy-mysql-oci-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)` ![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MX3LFZbjpZrPa5ZEFil%2F-MX3LPqAeDO6gr-4ceYb%2F5_changed.png?alt=media\&token=7446cc24-893c-46f9-b1f7-12c453da23ee) ### 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 file`***`instance.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: *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.*](https://isaac-exe.gitbook.io/various-tutorials/tutorials/1.-deployment-instance/install-and-configure-oci-cli) ### 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](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MVmLau35JoIEyeTpPIG%2F-MVmLlwJeHG_folOoz0u%2F1.png?alt=media\&token=28cf7a48-b5df-44dd-873c-685d61d8d35c) ![Creating the MySQL DB System](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MVmLau35JoIEyeTpPIG%2F-MVmLpLuN8TGjstpHXPG%2F2.png?alt=media\&token=5179b903-07c7-4c1f-9a70-014d91727527) `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:`* ![](https://3964595292-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MRFinYAjIA976nxMf0U%2F-MVmLau35JoIEyeTpPIG%2F-MVmLtX30NJlexr0UQEU%2F3.png?alt=media\&token=3a1a8054-8cf0-4a75-9f25-2594b8a02c21) `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 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"`