Create a MySQL RDS Database Instance with Terraform and connect with MySQL Workbench
Tags: mysql, rds, terraform, database
Published: 2023-03-14 23:03:41

# What is Terraform?

- It is an open-source IaaC (Infrastructure as a code) software tool where you define and create resources using providers in the declarative configuration language example JSON.
- With Terraform, You can package and reuse the code in the form of modules.
- It supports a number of cloud infrastructure providers such as AWS, Azure, GCP, IBM Cloud, OCI, etc. 

Please visit my **[GitHub Repository for Terraform projects](https://github.com/awsmine/Terraform_projects)** on various topics being updated on constant basis.

Please visit my **[GitHub Repository for RDS_projects](https://github.com/awsmine/RDS_projects)** on various topics being updated on constant basis.




_**Let’s get started!**_


# Objectives:

**1.** Create infrastructure for this project

**2.** Change to the directory - ***`terraform-project`*** and run ***`terraform init`***

**3.** Generate the action plans

**4.** Create all the resources declared in ***`main.tf`*** configuration file.

**5.** Check the infra structures created, from AWS Console

**6.** Testing RDS Connection using the MySQL Workbench 

**7.** Delete AWS Resources


# Pre-requisites:
- AWS user account with admin access, not a root account.
- Cloud9 IDE with AWS CLI and Terraform.
- [Download the MySql GUI Tool](https://dev.mysql.com/downloads/workbench/). Based on your OS, select the respective option under Generally Available (GA) Releases, Download and Install.


# Resources Used:

- [Terraform documentation](https://registry.terraform.io/providers/hashicorp/aws/latest/docs) use the navigation to the left to read about the available resources
- [Resource: aws_db_instance](https://registry.terraform.io/providers/hashicorp/aws/latest/docs/resources/db_instance)

# Steps for implementation to this project:

## 1. Create infrastructure for this project

- Let’s create the following organizational structure as shown below.

- Create a directory - ***`terraform-project`***

- Create 4 files - ***`variables.tf`***, ***`terraform.tfvars`***, ***`main.tf`***, and ***`outputs.tf`***. 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3xv8anhlndo00r2zv0t5.png)


- Create a ***`variables.tf`*** file. 

```
#variables.tf
variable "access_key" {
    description = "Access key to AWS console"
}
variable "secret_key" {
    description = "Secret key to AWS console"
}
variable "region" {
    description = "AWS region"
}
```

- Create a ***`terraform.tfvars`*** file. 

- You are defining the dynamic values for the variables declared in variables.tf file.

```
#terraform.tfvars
region = "us-east-1"
access_key = "<YOUR AWS CONSOLE ACCESS ID>"
secret_key = "<YOUR AWS CONSOLE SECRET KEY>"
```

- Create a ***`main.tf`*** file. 

```
#main.tf
#defining the provider as aws
provider "aws" {
    region     = "${var.region}"
    access_key = "${var.access_key}"
    secret_key = "${var.secret_key}"
}

#create a security group for RDS Database Instance
resource "aws_security_group" "rds_sg" {
  name = "rds_sg"
  ingress {
    from_port       = 3306
    to_port         = 3306
    protocol        = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

#create a RDS Database Instance
resource "aws_db_instance" "myinstance" {
  engine               = "mysql"
  identifier           = "myrdsinstance"
  allocated_storage    =  20
  engine_version       = "5.7"
  instance_class       = "db.t2.micro"
  username             = "myrdsuser"
  password             = "myrdspassword"
  parameter_group_name = "default.mysql5.7"
  vpc_security_group_ids = ["${aws_security_group.rds_sg.id}"]
  skip_final_snapshot  = true
  publicly_accessible =  true
}
```

- Create an ***`outputs.tf`*** file. 
- Outputs the security group id and RDS Database Instance endpoint to confirm that they are created.

```
#outputs.tf
output "security_group_id" {
  value       = aws_security_group.rds_sg.id
}
output "db_instance_endpoint" {
  value       = aws_db_instance.myinstance.endpoint
}
```

## 2. Change to the ***`terraform-project`*** and run ***`terraform init`*** to initialize Terraform. 


```
cd ../terraform-project

terraform init
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ozn6mhysdc8uskpsk5ir.png)



## 3. Generate the action plans

```
terraform plan
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tvdxttojz5i54on8otd9.png)



## 4. Create all the resources declared in ***`main.tf`*** configuration file.

```
terraform apply
```

- Wait for 5-6 minutes till all the resources have been created.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zh2rnvhbal69qm5ga4xm.png)


## 5. Check the infra structures created, from AWS Console

- Go to RDS Console, Click on the Databases on the left navigation panel, You can see RDS Database Instance created successfully


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3ui33q5ws6f4a3h4w8kp.png)



## 6. Testing RDS Connection using the MySQL Workbench   

**1.** To connect to a database on a DB instance using MySQL monitor, find the endpoint (DNS name) and port number for your DB Instance.
 - Go to databases and click on ***`myrdsinstance`***.

 - Under the Connectivity & security section, copy and note the endpoint and port.

 - **Endpoint:** ***`myrdsinstance.cgizjtuyxkda.us-east-1.rds.amazonaws.com`***

 - **Port:** ***`3306`***


**2.** Open MySQL Workbench. Click on the plus icon.
 
 - **Connection Name:** ***`MyDatabseConnection`***

 - **Host Name:** Enter the endpoint  ***`myrdsinstance.cgizjtuyxkda.us-east-1.rds.amazonaws.com`***

 - **Port:** ***`3306`***

 - **Username:** ***`myrdsuser`***

 - **Password:** Click on Store in Vault and enter password ***`myrdspassword`***. Click on ok.     

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6n8im6zaeksrrcnp9bmj.png)


 - Click on Test Connection to make sure that you are able to connect to the database properly.
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ep46klz1k6c7b944zydi.png)


 - Click on ok and ok again to save the connection.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r63516334c3ed7jm9sc0.png)


**3.** Click on it to open the database. Enter the database password if prompted.

 - After successfully connecting and opening the database, you can create tables and perform various queries over the connected database.

 - Navigate to the Schemas tab to see databases available to start doing database operations. More details on database operations are available [here](https://dev.mysql.com/doc/).



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eyamh59e6xd4bmirsxg7.png)



## 7. Delete AWS Resources

```
terraform destroy
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1b9bzos7k64hx0okz64d.png)




# What we have done so far

- We have successfully created a MySQL RDS Database Instance with Terraform and destroyed it later not to incur charges.