Create a MySQL RDS Database Instance with Terraform and connect with MySQL Workbench

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 on various topics being updated on constant basis.

Please visit my GitHub Repository for 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. Based on your OS, select the respective option under Generally Available (GA) Releases, Download and Install.

Resources Used:

• Terraform documentation use the navigation to the left to read about the available resources
• Resource: aws_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.

• 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

3. Generate the action plans

terraform plan

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.

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

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.

• Click on Test Connection to make sure that you are able to connect to the database properly.

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

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.

7. Delete AWS Resources

terraform destroy

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.

Comments

[🚩Ivan Amado🚩]

Very good!!!

[yahaya azeez]

Thank you for sharing.