LAB05_AWS_RDS MySQL Instance with Parameter Group and Random Password

terraform {
  required_version = ">= 1.5.0"
  required_providers {
    aws    = { source = "hashicorp/aws",    version = "~> 5.0" }
    random = { source = "hashicorp/random", version = "~> 3.5" }
  }
}

variable "aws_region"   { type = string; default = "us-east-1" }
variable "vpc_id"       { type = string; description = "VPC to deploy the DB into." }
variable "subnet_ids"   { type = list(string); description = "List of at least 2 private subnet IDs in different AZs." }
variable "db_name"      { type = string; default = "appdb" }
variable "db_username"  { type = string; default = "dbadmin" }
variable "environment"  { type = string; default = "dev" }

# main.tf

provider "aws" {
  region = var.aws_region
}

# Generate a strong random password — never hardcode DB passwords.
# The result is stored in Terraform state. Use AWS Secrets Manager in production
# if you need automatic rotation of database credentials.
resource "random_password" "db" {
  length           = 20
  special          = true
  override_special = "!#$%^&*()-_=+[]{}"  # exclude characters that confuse some MySQL drivers
}

# DB Subnet Group — tells RDS which subnets it can use.
# Requires subnets in at least 2 different AZs (AWS requirement).
resource "aws_db_subnet_group" "main" {
  name       = "app-db-subnet-group-${var.environment}"
  subnet_ids = var.subnet_ids
  description = "Private subnets for the application database"

  tags = { Environment = var.environment, ManagedBy = "terraform" }
}

# Custom Parameter Group — tune MySQL settings.
# Parameter groups are versioned by engine family, e.g., mysql8.0.
resource "aws_db_parameter_group" "mysql8" {
  name   = "app-mysql8-params-${var.environment}"
  family = "mysql8.0"
  description = "Custom MySQL 8.0 parameters for the application DB"

  # Enable slow query log to identify queries taking longer than 1 second.
  parameter {
    name  = "slow_query_log"
    value = "1"
  }

  parameter {
    name  = "long_query_time"
    value = "1"         # log queries slower than 1 second
  }

  tags = { Environment = var.environment, ManagedBy = "terraform" }
}

# Security group — only allow MySQL traffic from within the VPC.
resource "aws_security_group" "rds" {
  name        = "rds-sg-${var.environment}"
  description = "Allow MySQL from within the VPC"
  vpc_id      = var.vpc_id

  ingress {
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["10.0.0.0/16"]  # VPC CIDR — only internal traffic
    description = "MySQL from VPC"
  }

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

  tags = { Name = "rds-sg", Environment = var.environment }
}

# RDS MySQL instance.
resource "aws_db_instance" "app" {
  identifier        = "app-db-${var.environment}"
  engine            = "mysql"
  engine_version    = "8.0"
  instance_class    = "db.t3.micro"

  db_name  = var.db_name
  username = var.db_username
  password = random_password.db.result  # from the random_password resource

  # Storage configuration
  allocated_storage     = 20
  storage_type          = "gp3"
  storage_encrypted     = true          # always encrypt at rest

  # Network configuration
  db_subnet_group_name   = aws_db_subnet_group.main.name
  vpc_security_group_ids = [aws_security_group.rds.id]
  publicly_accessible    = false        # private — only reachable within VPC

  # Tuning and compliance
  parameter_group_name = aws_db_parameter_group.mysql8.name
  multi_az             = false          # Single-AZ for dev; set true for prod

  # Maintenance and lifecycle
  backup_retention_period = 7           # keep 7 days of automated backups
  skip_final_snapshot     = true        # dev only — set false in production
  deletion_protection     = false       # set true in production

  tags = { Environment = var.environment, ManagedBy = "terraform" }
}

output "db_endpoint" {
  description = "Connection endpoint (host:port) for the database."
  value       = aws_db_instance.app.endpoint
}

output "db_password" {
  description = "Generated database password. Store this in a password manager."
  value       = random_password.db.result
  sensitive   = true   # hides the value in plan/apply output and logs
}

terraform init
terraform plan -var="vpc_id=vpc-xxxx" -var='subnet_ids=["subnet-aaa","subnet-bbb"]'
terraform apply -var-file="dev.tfvars"

# Read the sensitive password output (won't show in apply but works here)
terraform output -raw db_password

# Test connectivity from an EC2 instance in the same VPC
mysql -h $(terraform output -raw db_endpoint | cut -d: -f1) \
      -u dbadmin -p appdb

terraform destroy -var-file="dev.tfvars"