I can never remember how to connect to a MYSQL Database deployed with RDS and Aurora in a VPC. As my setup inevitably gets more complex, I can't seem figure out the right combination of security groups, VPC settings, and SSH tunneling flags, that lets me magically connect. So my goal for this blog is to deploy the simplest setup of: Terraform + RDS + Aurora + MYSQL and be able to connect to the database.


I will assume familiarity with Aurora and MYSQL. but before we get started, lets go over RDS and Aurora:

RDS:

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

Aurora:

Amazon Aurora is a MySQL and PostgreSQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. It provides up to five times better performance than MySQL with the security, availability, and reliability of a commercial database at one tenth the cost.

So that sounds great! Let's build it!

Build our Database Cluster and Instance

Note: These are instructions for OSX
Note: This assumes you have terraform installed


main.tf

resource "aws_rds_cluster_instance" "cluster_instances" {
  identifier         = "${var.cluster_name}-instance"
  cluster_identifier = "${aws_rds_cluster.cluster.id}"
  instance_class     = "${var.instance_class}"
}

resource "aws_rds_cluster" "cluster" {
  cluster_identifier     = "${var.cluster_name}"
  database_name          = "sample_rds"
  master_username        = "${var.username}"
  master_password        = "${var.password}"
  vpc_security_group_ids = ["${aws_security_group.aurora-sg.id}"]
  skip_final_snapshot    = true
}

resource "aws_security_group" "aurora-sg" {
  name   = "aurora-security-group"
  vpc_id = "${aws_default_vpc.default.id}"

  ingress {
    protocol    = "tcp"
    from_port   = 3306
    to_port     = 3306
    cidr_blocks = ["0.0.0.0/0"]
  }

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

provider "aws" {
  region = "us-east-1"
}

variable "cluster_name" {
  default = "rds-sample-cluster"
} 
  
variable "instance_class" {
  default = "db.t2.small"
}

variable "username" {
  default = "master"
}

variable "password" {
  default = "password"
}

This creates an RDS Cluster, RDS Instance and Security Group:

RDS Clusters:

A DB cluster consists of one or more instances, and a cluster volume that manages the data for those instances. An Aurora cluster volume is a virtual database storage volume that spans multiple Availability Zones, with each Availability Zone having a copy of the cluster data.

RDS Instance:

A DB instance is an isolated database environment running in the cloud. It is the basic building block of Amazon RDS. A DB instance can contain multiple user-created databases, and can be accessed using the same client tools and applications you might use to access a stand-alone database instance.

Security Group:

A security group acts as a virtual firewall that controls the traffic for one or more instances.


Create our Bastion Host

More detailed instructions for building a Bastion host here

bastion.tf

resource "aws_default_vpc" "default" {}

resource "aws_instance" "bastion" {
  ami                         = "ami-1d4e7a66"
  key_name                    = "${aws_key_pair.bastion_key.key_name}"
  instance_type               = "t2.micro"
  security_groups             = ["${aws_security_group.bastion-sg.name}"]
  associate_public_ip_address = true
}

resource "aws_security_group" "bastion-sg" {
  name   = "bastion-security-group"
  vpc_id = "${aws_default_vpc.default.id}"

  ingress {
    protocol    = "tcp"
    from_port   = 22
    to_port     = 22
    cidr_blocks = ["0.0.0.0/0"]
  }

  egress {
    protocol    = "tcp"
    from_port   = 3306
    to_port     = 3306
    cidr_blocks = ["0.0.0.0/0"]
  }

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

resource "aws_key_pair" "bastion_key" {
  key_name   = "your_key_name"
  public_key = "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDWbz6ur89BKQ+am87EovJsv6g9QpbOiw13lTF7Kw1StbQAmkcGGrNTK2LIWsP3cQf+P+gptRAJbuqB1jQKZ283TwwREIv+l5AMKrbEkanOF4zsc8a9zitejlOLvVUxtVoMi5ROVYD2dLKjqAbDtqIC9LmMD+hcpqcXLhS6t+HVSVI862dTNVFY1EGukLGQ3IEJfw5v7FDzLn72NsuUiXEeCZu8DtlXLCTYRnqv+XkJQWVocPdFDUWISSIQ0CTFu+GJvJjdqDyAhYo3it7Eybj6XuSgLDwkQcNU45Ewz4Nn7LwV+f4Av8D25m4FZOfpWaj5+q9Fc9nRdIsB7P0oFgj5YoaTngQKy27MJ5UppMO7OOhriurJ/PBOrGpeqPcftWKLpcHLIGrm3ndoDKQx12R1s0gyYpA4JuNUWHYcxNrFa2rs/6AoFuS7wNUmM+DYB8iTjOl6dT8dS5AgMxGoZ3NepMPYilw1gf+gw9Ft3pHs2IMfDfqwZpXga8KdYwxBmRakpHdA7Nzje8ufvP/TBawsqVcW7z5gG9uPhYtfnYYezSIxv56PMSWEfqchkz+raPsElzIGtPcC1snncQlau95utV25r88BzXhCMJwNy9aDNEfSrm5SORlA97xicroCOuRjw2PnQyIXKvWDZtyqX5799x37K/HDYpJnvcgwpTlDZQ== your_email@example.com"
}

output "bastion_public_dns" {
  value = "${aws_instance.bastion.public_dns}"
}

Deploy our Database and VPC

terraform apply

Output from terraform apply:

Outputs:

bastion_public_dns = ec2-52-91-54-64.compute-1.amazonaws.com
rds_instance_endpoint = rds-sample-cluster-instance.cn03auqgmvh3.us-east-1.rds.amazonaws.com

Create an SSH tunnel to your RDS instance


ssh -L 3307:rds-sample-cluster-instance.cn03ausgmvh3.us-east-1.rds.amazonaws.com:3306 ubuntu@ec2-52-91-54-64.compute-1.amazonaws.com -N

Lets break this down


-L local_socket:host:hostport

Specifies that connections to the given TCP port or Unix socket on the local (client) host are to be forwarded to the given host and port, or Unix socket, on the remote side. This works by allocating a socket to listen to either a TCP port on the local side, optionally bound to the specified bind_address, or to a Unix socket. Whenever a connection is made to the local port or socket, the connection is forwarded over the secure channel, and a connection is made to either host port hostport, or the Unix socket remote_socket, from the remote machine.

We want to route traffic on port 3306 from the host rds-sample-cluster-instance.cn03auqgmvh3.us-east-1.rds.amazonaws.com to port 3307,
and we can connect through the Bastion Host: ubuntu@ec2-52.91.54.64.us-east-1.compute.amazonaws.com.

and finally:

-N  Do not execute a remote command. This is useful for just forwarding ports.

Now we can connect to our remote database locally:

mysql -u master -p -P 3307 -h 127.0.0.1  

Success!


Photo by Ambreen Hasan / Unsplash


TLDR

main.tf

provider "aws" {
  region = "us-east-1"
}

variable "cluster_name" {
  default = "rds-sample-cluster"
} 
  
variable "instance_class" {
  default = "db.t2.small"
}

variable "username" {
  default = "master"
}

variable "password" {
  default = "password"
}

# =============
# = RDS Setup =
# =============

resource "aws_rds_cluster_instance" "cluster_instances" {
  identifier         = "${var.cluster_name}-instance"
  cluster_identifier = "${aws_rds_cluster.cluster.id}"
  instance_class     = "${var.instance_class}"
}

resource "aws_rds_cluster" "cluster" {
  cluster_identifier     = "${var.cluster_name}"
  database_name          = "sample_rds"
  master_username        = "${var.username}"
  master_password        = "${var.password}"
  vpc_security_group_ids = ["${aws_security_group.aurora-sg.id}"]
  skip_final_snapshot    = true
}

resource "aws_security_group" "aurora-sg" {
  name   = "aurora-security-group"
  vpc_id = "${aws_default_vpc.default.id}"

  ingress {
    protocol    = "tcp"
    from_port   = 3306
    to_port     = 3306
    cidr_blocks = ["0.0.0.0/0"]
  }

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

# =================
# = Bastion Setup =
# =================

resource "aws_default_vpc" "default" {}

resource "aws_instance" "bastion" {
  ami                         = "ami-1d4e7a66"
  key_name                    = "${aws_key_pair.bastion_key.key_name}"
  instance_type               = "t2.micro"
  security_groups             = ["${aws_security_group.bastion-sg.name}"]
  associate_public_ip_address = true
}

resource "aws_security_group" "bastion-sg" {
  name   = "bastion-security-group"
  vpc_id = "${aws_default_vpc.default.id}"

  ingress {
    protocol    = "tcp"
    from_port   = 22
    to_port     = 22
    cidr_blocks = ["0.0.0.0/0"]
  }

  egress {
    protocol    = "tcp"
    from_port   = 3306
    to_port     = 3306
    cidr_blocks = ["0.0.0.0/0"]
  }

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

resource "aws_key_pair" "bastion_key" {
  key_name   = "your_key_name"
  public_key = "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDWbz6ur89BKQ+am87EovJsv6g9QpbOiw13lTF7Kw1StbQAmkcGGrNTK2LIWsP3cQf+P+gptRAJbuqB1jQKZ283TwwREIv+l5AMKrbEkanOF4zsc8a9zitejlOLvVUxtVoMi5ROVYD2dLKjqAbDtqIC9LmMD+hcpqcXLhS6t+HVSVI862dTNVFY1EGukLGQ3IEJfw5v7FDzLn72NsuUiXEeCZu8DtlXLCTYRnqv+XkJQWVocPdFDUWISSIQ0CTFu+GJvJjdqDyAhYo3it7Eybj6XuSgLDwkQcNU45Ewz4Nn7LwV+f4Av8D25m4FZOfpWaj5+q9Fc9nRdIsB7P0oFgj5YoaTngQKy27MJ5UppMO7OOhriurJ/PBOrGpeqPcftWKLpcHLIGrm3ndoDKQx12R1s0gyYpA4JuNUWHYcxNrFa2rs/6AoFuS7wNUmM+DYB8iTjOl6dT8dS5AgMxGoZ3NepMPYilw1gf+gw9Ft3pHs2IMfDfqwZpXga8KdYwxBmRakpHdA7Nzje8ufvP/TBawsqVcW7z5gG9uPhYtfnYYezSIxv56PMSWEfqchkz+raPsElzIGtPcC1snncQlau95utV25r88BzXhCMJwNy9aDNEfSrm5SORlA97xicroCOuRjw2PnQyIXKvWDZtyqX5799x37K/HDYpJnvcgwpTlDZQ== your_email@example.com"
}

# ===========
# = Outputs =
# ===========

output "bastion_public_ip" {
  value = "${aws_instance.bastion.public_ip}"
}

Make sure you replace the public_key in your aws_key_pair

Run:

terraform apply
Outputs:

bastion_public_dns = ec2-52-91-54-64.compute-1.amazonaws.com
rds_instance_endpoint = rds-sample-cluster-instance.cn03auqgmvh3.us-east-1.rds.amazonaws.com

Export the variables to use for your SSH tunnel:

export RDS_INSTANCE_ENDPOINT="ec2-52-91-54-64.compute-1.amazonaws.com"
export BASTION_PUBLIC_DNS="rds-sample-cluster-instance.cn03auqgmvh3.us-east-1.rds.amazonaws.com"

Fancier export with terraform output -json:
Note: you need jq to be installed brew install jq

export BASTION_PUBLIC_DNS=$(terraform output -json | jq -r '.bastion_public_dns.value')
export RDS_INSTANCE_ENDPOINT=$(terraform output -json | jq -r '.rds_instance_endpoint.value')

-r is short for --raw-input, which for our case means not to surround the value in quotes.

Open SSH tunnel to your database:

ssh -L 3307:$RDS_INSTANCE_ENDPOINT:3306 ubuntu@$BASTION_PUBLIC_DNS -N

Connect to your database:

mysql -u master -p -P 3307 -h 127.0.0.1

type in your password and.....