R E V D B

Loading

terraform mysql rds

Goal of this guide

In this how to guide we will go through the basics of setting up MySQL RDS with Terraform. To do this, the minimum we have to do is:

  • Create a network (VPC)
  • Create a security group to allow incoming MySQL traffic
  • Create the RDS instance
  • Launch an EC2 instance to test the database server

Creating your network

This is the most complicated part of this guide, to be able to create an RDS instance, you will need a VPC with at least 2 subnets. We will also add an Internet Gateway and a default route table to be able to connect to this VPC from the outside. If you have these setup already, you can skip to the next part. If you don’t, setting up networking on your account, deserves its own post, so we will do the minimum to get going here.

First lets create the VPC and its subnets:

resource "aws_vpc" "main" {
  cidr_block = "10.10.0.0/16"
}
// first subnet
resource "aws_subnet" "main-1" {
  vpc_id     = aws_vpc.main.id
  cidr_block = "10.10.1.0/24"
}
// second subnet
resource "aws_subnet" "main-2" {
  vpc_id     = aws_vpc.main.id
  cidr_block = "10.10.2.0/24"
}

Now that we have a VPC we might want to add an Internet Gateway and a route table associating the gateway with the VPC. This will enable networking access to and from the outside world, just like your router does in your home.

// adding internet gateway to the vpc
resource "aws_internet_gateway" "gw" {
  vpc_id = aws_vpc.main.id
}
// adding the route table entry 
// to use the internet gateway 
resource "aws_default_route_table" "main" {
  route {
    cidr_block = "0.0.0.0/0"
    gateway_id = aws_internet_gateway.gw.id
  }
  default_route_table_id = aws_vpc.main.default_route_table_id
}

Security group configuration

In Amazon AWS Security groups and Network ACLs control network traffic in and out from a given AWS resource and network. For this tutorial we will only have to worry about adding the right security group to allow MySQL traffic to the newly created database resource.

resource "aws_security_group" "db" {
  name        = "allow_mysql"
  description = "Allow MySQL inbound traffic"
  vpc_id      = aws_vpc.main.id

  ingress {
    description = "MySQL from VPC"
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = [aws_vpc.main.cidr_block]
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

Starting MySQL RDS with Terraform

The last step is to configure RDS before applying changes via
Terraform, in this how to guide MySQL 8.0 will be used on a db.t3.micro
instance.

// The two subnets from above
resource "aws_db_subnet_group" "db" {
  subnet_ids = [aws_subnet.main-1.id, aws_subnet.main-2.id]
}
resource "aws_db_instance" "db" {
  allocated_storage    = 5
  storage_type         = "gp2"
  engine               = "mysql"
  engine_version       = "8.0"
  instance_class       = "db.t3.micro"
  name                 = "db1"
  username             = "user"
  password             = "hello_mysql"

  vpc_security_group_ids = [aws_security_group.db.id]
  db_subnet_group_name = aws_db_subnet_group.db.id
}
// output the database endpoint URL
output "database_endpoint" {
  value = aws_db_instance.db.endpoint
}

Running Terraform

Initialize Terraform by running terraform init in the terminal.

$ terraform init
Initializing the backend...

Initializing provider plugins...
- Checking for available provider plugins...
- Downloading plugin for provider "aws" (hashicorp/aws) 2.70.0...

Terraform has been successfully initialized!

You may now begin working with Terraform. Try running "terraform plan" to see
any changes that are required for your infrastructure. All Terraform commands
should now work.

If you ever set or change modules or backend configuration for Terraform,
rerun this command to reinitialize your working directory. If you forget, other
commands will detect it and remind you to do so if necessary.

Run terraform apply. If all is good you should see the following.

$ terraform apply
aws_vpc.main: Refreshing state... [id=vpc-08ebcd516167e27aa]
aws_internet_gateway.gw: Refreshing state... [id=igw-066623252db85c6c7]
aws_subnet.main-2: Refreshing state... [id=subnet-01f9e8109e8c473b5]
aws_subnet.main-1: Refreshing state... [id=subnet-0ce3dd25734df1c13]
aws_security_group.db: Refreshing state... [id=sg-09380fc86ad52009b]
aws_default_route_table.main: Refreshing state... [id=rtb-0706b99e61b9f9fa0]
aws_db_subnet_group.db: Refreshing state... [id=terraform-20200920100104441600000001]
aws_db_instance.db: Refreshing state... [id=terraform-20200920101212267400000001]

An execution plan has been generated and is shown below.
Resource actions are indicated with the following symbols:
+ create

Terraform will perform the following actions:

# aws_db_instance.db will be created
+ resource "aws_db_instance" "db" {
+ address = (known after apply)
+ allocated_storage = 5
+ apply_immediately = (known after apply)
+ arn = (known after apply)
+ auto_minor_version_upgrade = true
+ availability_zone = (known after apply)
+ backup_retention_period = (known after apply)
+ backup_window = (known after apply)
+ ca_cert_identifier = (known after apply)
+ character_set_name = (known after apply)
+ copy_tags_to_snapshot = false
+ db_subnet_group_name = (known after apply)
+ delete_automated_backups = true
+ endpoint = (known after apply)
+ engine = "mysql"
+ engine_version = "8.0"
+ hosted_zone_id = (known after apply)
+ id = (known after apply)
+ identifier = (known after apply)
+ identifier_prefix = (known after apply)
+ instance_class = "db.t3.micro"
+ kms_key_id = (known after apply)
+ license_model = (known after apply)
+ maintenance_window = (known after apply)
+ monitoring_interval = 0
+ monitoring_role_arn = (known after apply)
+ multi_az = (known after apply)
+ name = "db1"
+ option_group_name = (known after apply)
+ parameter_group_name = (known after apply)
+ password = (sensitive value)
+ performance_insights_enabled = false
+ performance_insights_kms_key_id = (known after apply)
+ performance_insights_retention_period = (known after apply)
+ port = (known after apply)
+ publicly_accessible = false
+ replicas = (known after apply)
+ resource_id = (known after apply)
+ skip_final_snapshot = false
+ status = (known after apply)
+ storage_type = "gp2"
+ timezone = (known after apply)
+ username = "user"
+ vpc_security_group_ids = (known after apply)
}

# aws_db_subnet_group.db will be created
+ resource "aws_db_subnet_group" "db" {
+ arn = (known after apply)
+ description = "Managed by Terraform"
+ id = (known after apply)
+ name = (known after apply)
+ name_prefix = (known after apply)
+ subnet_ids = (known after apply)
}

# aws_default_route_table.main will be created
+ resource "aws_default_route_table" "main" {
+ default_route_table_id = (known after apply)
+ id = (known after apply)
+ owner_id = (known after apply)
+ route = [
+ {
+ cidr_block = "0.0.0.0/0"
+ egress_only_gateway_id = ""
+ gateway_id = (known after apply)
+ instance_id = ""
+ ipv6_cidr_block = ""
+ nat_gateway_id = ""
+ network_interface_id = ""
+ transit_gateway_id = ""
+ vpc_peering_connection_id = ""
},
]
+ vpc_id = (known after apply)
}

# aws_internet_gateway.gw will be created
+ resource "aws_internet_gateway" "gw" {
+ arn = (known after apply)
+ id = (known after apply)
+ owner_id = (known after apply)
+ vpc_id = (known after apply)
}

# aws_security_group.db will be created
+ resource "aws_security_group" "db" {
+ arn = (known after apply)
+ description = "Allow MySQL inbound traffic"
+ egress = [
+ {
+ cidr_blocks = [
+ "0.0.0.0/0",
]
+ description = ""
+ from_port = 0
+ ipv6_cidr_blocks = []
+ prefix_list_ids = []
+ protocol = "-1"
+ security_groups = []
+ self = false
+ to_port = 0
},
]
+ id = (known after apply)
+ ingress = [
+ {
+ cidr_blocks = [
+ "10.10.0.0/16",
]
+ description = "MySQL from VPC"
+ from_port = 3306
+ ipv6_cidr_blocks = []
+ prefix_list_ids = []
+ protocol = "tcp"
+ security_groups = []
+ self = false
+ to_port = 3306
},
]
+ name = "allow_mysql"
+ owner_id = (known after apply)
+ revoke_rules_on_delete = false
+ vpc_id = (known after apply)
}

# aws_subnet.main-1 will be created
+ resource "aws_subnet" "main-1" {
+ arn = (known after apply)
+ assign_ipv6_address_on_creation = false
+ availability_zone = (known after apply)
+ availability_zone_id = (known after apply)
+ cidr_block = "10.10.1.0/24"
+ id = (known after apply)
+ ipv6_cidr_block = (known after apply)
+ ipv6_cidr_block_association_id = (known after apply)
+ map_public_ip_on_launch = false
+ owner_id = (known after apply)
+ vpc_id = (known after apply)
}

# aws_subnet.main-2 will be created
+ resource "aws_subnet" "main-2" {
+ arn = (known after apply)
+ assign_ipv6_address_on_creation = false
+ availability_zone = (known after apply)
+ availability_zone_id = (known after apply)
+ cidr_block = "10.10.2.0/24"
+ id = (known after apply)
+ ipv6_cidr_block = (known after apply)
+ ipv6_cidr_block_association_id = (known after apply)
+ map_public_ip_on_launch = false
+ owner_id = (known after apply)
+ vpc_id = (known after apply)
}

# aws_vpc.main will be created
+ resource "aws_vpc" "main" {
+ arn = (known after apply)
+ assign_generated_ipv6_cidr_block = false
+ cidr_block = "10.10.0.0/16"
+ default_network_acl_id = (known after apply)
+ default_route_table_id = (known after apply)
+ default_security_group_id = (known after apply)
+ dhcp_options_id = (known after apply)
+ enable_classiclink = (known after apply)
+ enable_classiclink_dns_support = (known after apply)
+ enable_dns_hostnames = (known after apply)
+ enable_dns_support = true
+ id = (known after apply)
+ instance_tenancy = "default"
+ ipv6_association_id = (known after apply)
+ ipv6_cidr_block = (known after apply)
+ main_route_table_id = (known after apply)
+ owner_id = (known after apply)
}

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:

As you can see Terraform is going to create 8 resources and 0 resources are going to be changed or destroyed as we expect. Entering “yes” will apply the changes on your AWS account and after all the resources are created Terraform will output the MySQL server’s endpoint URL.

Launch an EC2 instance in the same VPC as our MySQL server to test the database server:

$ mysql -h terraform-20200920101212267400000001.cqhu6rnhwf6h.us-east-1.rds.amazonaws.com -u user -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.20 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> Bye

Conclusion

As you have seen the most work for standalone MySQL installations is with setting up networking and security groups. Secure password management was out of scope of this introductory tutorial, but it is a must for every production database installation so we will cover that in an upcoming blog post with other tips to keep your MySQL server installation secure.

Leave a Comment

revDB_Light

© 2020 Revenants CIE LLC.

US toll-free: +1-877-REVDB4U

International: +1-669-777-6044

Redwood City, CA 94061
PO Box 610126