Chapter 46: Terraform 3-Tier Architecture Multi-AZ with ALBs Auto Scaling Bastion NAT and RDS

46.7 DB subnet group, Multi-AZ RDS standby and read replica

By Ravindra Bagale · Last updated: 25 October 2026

Story. Suppose we are protecting the shop's orders database. With multi_az = true, RDS keeps a standby copy in the other zone, updated synchronously; if the primary's zone fails, RDS switches the DNS name to the standby in about a minute or two, and the app reconnects to the same endpoint. The standby takes no traffic. For heavy reports we add a read replica: it is copied asynchronously, has its own endpoint, and serves only reads, so reports never slow down checkout.

Steps

1. Write db.tf.
2. Run terraform validate.
3. After apply (about 15 to 25 minutes for RDS), check MultiAZ and the replica source.
4. Optional: force a failover and watch the zone change.

# ---- db.tf ----
resource "aws_db_subnet_group" "db" {
  name       = "${var.project}-db"
  subnet_ids = [for s in aws_subnet.db : s.id]
  tags = {
    Name = "${var.project}-db-subnets"
  }
}

resource "aws_db_instance" "primary" {
  identifier                  = "${var.project}-mysql"
  engine                      = "mysql"
  engine_version              = "8.0"
  instance_class              = "db.t3.micro"
  allocated_storage           = 20
  max_allocated_storage       = 100
  storage_type                = "gp3"
  storage_encrypted           = true
  db_name                     = "shopdb"
  username                    = var.db_username
  manage_master_user_password = true
  db_subnet_group_name        = aws_db_subnet_group.db.name
  vpc_security_group_ids      = [aws_security_group.db.id]
  multi_az                    = true
  publicly_accessible         = false
  backup_retention_period     = 7
  backup_window               = "18:30-19:30"
  maintenance_window          = "sun:20:00-sun:21:00"
  skip_final_snapshot         = true
  deletion_protection         = false
}

resource "aws_db_instance" "replica" {
  identifier             = "${var.project}-mysql-replica"
  replicate_source_db    = aws_db_instance.primary.identifier
  instance_class         = "db.t3.micro"
  storage_encrypted      = true
  vpc_security_group_ids = [aws_security_group.db.id]
  availability_zone      = local.azs["b"]
  publicly_accessible    = false
  skip_final_snapshot    = true
}

What each line does

• subnet_ids = [for s in aws_subnet.db : s.id] — DB subnets in both zones.
• max_allocated_storage = 100 — Storage grows automatically up to 100 GB.
• manage_master_user_password = true — Password in Secrets Manager.
• multi_az = true — Creates the standby in the other zone.
• backup_retention_period = 7 — Backups are required for a read replica and give point-in-time restore.
• backup_window = "18:30-19:30" — UTC, which is midnight to 1 a.m. India time.
• maintenance_window = "sun:20:00-sun:21:00" — UTC, which is Monday 1:30 to 2:30 a.m. India time. Must not overlap the backup window.
• replicate_source_db = aws_db_instance.primary.identifier — Makes this instance a read replica of the primary.
• (no username, password or db_name on the replica) — A replica copies them from the primary.
• availability_zone = local.azs["b"] — Places the replica in AZ-b.
• vpc_security_group_ids = [db] — Same rule: only the app tier connects.

To test failover after apply, run aws rds reboot-db-instance --db-instance-identifier shop3t-mysql --force-failover, then describe the instance again and see its AvailabilityZone change.

When it is green.

$ aws rds describe-db-instances --query "DBInstances[].[DBInstanceIdentifier,MultiAZ,ReadReplicaSourceDBInstanceIdentifier]"
[["shop3t-mysql", true, null],
 ["shop3t-mysql-replica", false, "shop3t-mysql"]]

When it is red.

Error: creating RDS DB Instance (read replica) (shop3t-mysql-replica): InvalidDBInstanceState:
Automated backups are not enabled for this database instance.

Fix. Set backup_retention_period to 1 or more on the primary, apply, then create the replica.

What: every term in plain English

• DB subnet group — The subnets RDS may use. Here both DB subnets, one per zone.
• Multi-AZ — RDS keeps a synchronous standby copy in another zone. If the primary fails, AWS flips the endpoint to the standby in one to two minutes.
• standby — The hidden copy in the other zone. It serves no reads; it waits for failover.
• read replica — An asynchronous read-only copy with its own endpoint, used for reports and read-heavy pages.
• replicate_source_db — The Terraform argument that makes an aws_db_instance a replica of another.
• manage_master_user_password — RDS creates the admin password and keeps it in Secrets Manager. It never appears in code.
• backup_retention_period — Days of automatic backups. Must be above 0 to create a read replica.
• failover — Switching the primary role from one zone to the standby in the other.

Example 1: Create the DB subnet group over both zones

RDS places the primary in one subnet and the standby in the other. Without two zones in the group, Multi-AZ is impossible.

1. Collect both DB subnet ids.
2. Create the subnet group.
3. Check it in the CLI.

resource "aws_db_subnet_group" "db" {
  name       = "${var.project}-db"
  subnet_ids = [for s in aws_subnet.db : s.id]
}

aws rds describe-db-subnet-groups --db-subnet-group-name shop3t-db \
  --query "DBSubnetGroups[0].Subnets[].SubnetAvailabilityZone.Name"

Output

[
    "ap-south-1a",
    "ap-south-1b"
]

What it shows: Two zones listed means the group is ready for Multi-AZ.

Example 2: Primary with Multi-AZ and a managed password

HDFC Bank policy: no password in code, encrypted storage, standby in another zone. These four lines meet all three.

1. Set multi_az = true.
2. Set manage_master_user_password = true.
3. Encrypt storage and keep backups.

resource "aws_db_instance" "primary" {
  identifier                  = "${var.project}-mysql"
  engine                      = "mysql"
  engine_version              = "8.0"
  instance_class              = "db.t3.micro"
  allocated_storage           = 20
  db_subnet_group_name        = aws_db_subnet_group.db.name
  vpc_security_group_ids      = [aws_security_group.db.id]
  username                    = var.db_username
  manage_master_user_password = true
  multi_az                    = true
  storage_encrypted           = true
  backup_retention_period     = 1
  skip_final_snapshot         = true
}

Output

aws_db_instance.primary: Still creating... [12m0s elapsed]
aws_db_instance.primary: Creation complete after 13m4s [id=db-ABCDEFGHIJKLMNOP]

What it shows: Multi-AZ RDS takes 10 to 15 minutes. skip_final_snapshot is only for labs.

Example 3: Add a read replica for reports

At Swiggy, finance reports run on a replica so they never slow down order writes on the primary.

1. Create a second aws_db_instance.
2. Point replicate_source_db at the primary.
3. Do not set username or password; they come from the source.

resource "aws_db_instance" "replica" {
  identifier             = "${var.project}-mysql-ro"
  replicate_source_db    = aws_db_instance.primary.identifier
  instance_class         = "db.t3.micro"
  vpc_security_group_ids = [aws_security_group.db.id]
  storage_encrypted      = true
  skip_final_snapshot    = true
}

aws rds describe-db-instances --db-instance-identifier shop3t-mysql-ro \
  --query "DBInstances[0].[ReadReplicaSourceDBInstanceIdentifier,Endpoint.Address]"

Output

[
    "shop3t-mysql",
    "shop3t-mysql-ro.abc123xyz.ap-south-1.rds.amazonaws.com"
]

What it shows: The replica has its own endpoint. Point report queries at it; writes always go to the primary endpoint.

Example 4: Read the generated password safely

The password lives in Secrets Manager. The app reads it at runtime with its IAM role; humans fetch it only when needed, never paste it in code.

1. Get the secret ARN from Terraform.
2. Fetch the value with the CLI.
3. Use it once, then clear your shell.

output "db_secret_arn" {
  value = aws_db_instance.primary.master_user_secret[0].secret_arn
}

aws secretsmanager get-secret-value --secret-id $(terraform output -raw db_secret_arn) \
  --query SecretString --output text | python3 -c "import sys,json; print(list(json.load(sys.stdin)))"

Output

['username', 'password']

What it shows: We print only the keys, not the value, so the password never lands in your terminal history or a screenshot.

Example 5: Connect from an app server

Only the app group may reach 3306. Testing from an app server proves the chain and the endpoint together.

1. SSH to an app server through the bastion.
2. Install the MySQL client.
3. Connect with the primary endpoint.

sudo dnf install -y mariadb105
mysql -h $DB_ENDPOINT -u admin -p -e "SELECT @@hostname, @@read_only;"
mysql -h $DB_RO_ENDPOINT -u admin -p -e "SELECT @@read_only;"

Output

+-----------------+-------------+
| @@hostname      | @@read_only |
+-----------------+-------------+
| ip-10-6-1-23    |           0 |
+-----------------+-------------+
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+

What it shows: read_only 0 on the primary, 1 on the replica. -p asks for the password interactively, so it never appears on the command line.

Example 6: Force a failover and watch the zone change

Interviewers at Amazon ask how you know Multi-AZ works. You prove it with a forced reboot with failover and time it.

1. Note the current zone.
2. Reboot with failover.
3. Check the zone again.

aws rds describe-db-instances --db-instance-identifier shop3t-mysql --query "DBInstances[0].AvailabilityZone"
aws rds reboot-db-instance --db-instance-identifier shop3t-mysql --force-failover > /dev/null
aws rds wait db-instance-available --db-instance-identifier shop3t-mysql
aws rds describe-db-instances --db-instance-identifier shop3t-mysql --query "DBInstances[0].AvailabilityZone"

Output

"ap-south-1a"
"ap-south-1b"

What it shows: The endpoint name stayed the same; only the zone changed. Apps reconnect without any config change.

Command cheat sheet

Verify: did it work?

1. Multi-AZ on

   aws rds describe-db-instances --db-instance-identifier shop3t-mysql --query "DBInstances[0].MultiAZ"
   

   Output

   true
   

2. Replica linked

   aws rds describe-db-instances --db-instance-identifier shop3t-mysql --query "DBInstances[0].ReadReplicaDBInstanceIdentifiers"
   

   Output

   ["shop3t-mysql-ro"]
   

3. Not public

   aws rds describe-db-instances --db-instance-identifier shop3t-mysql --query "DBInstances[0].PubliclyAccessible"
   

   Output

   false
   

When it is red 1

Error: creating RDS DB Instance (read replica): InvalidDBInstanceState: Automated backups are not enabled for this database instance

Cause. backup_retention_period is 0 on the primary.

Fix, step by step

1. Set backup_retention_period = 1 or more.
2. Apply the primary first.

When it is red 2

Error: DBSubnetGroupDoesNotCoverEnoughAZs

Cause. The subnet group has subnets in only one zone.

Fix, step by step

1. Add the DB subnet from the second zone.
2. Check for_each over local.azs.

When it is red 3

ERROR 2003 (HY000): Can't connect to MySQL server on 'shop3t-mysql...' (110)

Cause. The client is not in the app group, or db_from_app is missing.

Fix, step by step

1. Connect from an app server, not web or bastion.
2. Check the 3306 rule.

Cleanup

terraform destroy -auto-approve

Practice tasks

Homework: try at home

Recap

• DB subnet group spans both zones.
• multi_az = true gives a standby.
• A replica has its own read-only endpoint.
• RDS manages the password in Secrets Manager.
• Force failover to prove it works.

FAQ

Can I read from the standby?

No. Only a read replica serves reads (with classic Multi-AZ).

Is replica data instant?

Almost; it is asynchronous, so there can be a small lag.

Does failover change the endpoint?

No, the DNS name stays; it points to the new primary.

Why db.t3.micro?

Cheapest class that supports Multi-AZ for a lab.

Interview questions

1. Difference between Multi-AZ and a read replica?

   Multi-AZ is a synchronous standby for failover; a replica is an asynchronous copy for reads.

2. How do you keep the DB password out of Terraform?

   manage_master_user_password = true stores it in Secrets Manager.

3. How do you test Multi-AZ?

   Reboot with force failover and check the zone changed.

4. Why does a replica need backups on the source?

   RDS uses the backup and binary logs to build and feed the replica.

Samajla ka?

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards