AWS RDS Setup Step-by-Step: MySQL for Production Workloads

AWS RDS Setup Without the 3 AM Failover Surprise

Managed MySQL sounds easy until someone provisions db.t3.micro in one AZ with no backups and wonders why production vanished during an AZ outage. RDS is reliable when you configure Multi-AZ, automated backups, and parameter groups intentionally.

Create a MySQL Instance via CLI

aws rds create-db-instance \
  --db-instance-identifier amsai-prod-mysql \
  --db-instance-class db.t3.medium \
  --engine mysql \
  --engine-version 8.0.36 \
  --master-username admin \
  --master-user-password "$(openssl rand -base64 24)" \
  --allocated-storage 50 \
  --storage-type gp3 \
  --vpc-security-group-ids sg-db123456 \
  --db-subnet-group-name private-db-subnets \
  --backup-retention-period 7 \
  --multi-az \
  --storage-encrypted \
  --deletion-protection

Parameter Groups and Monitoring

Clone default parameter group for app-specific tuning—innodb_buffer_pool_size around 70% of RAM on dedicated DB instances. Enable Performance Insights free tier for slow query visibility. Set max_connections above your app's pool size × instance count.

• Read replicas for reporting queries—never run BI tools on primary
• Secrets Manager rotation for master credentials
• CloudWatch alarms on CPU > 80%, free storage < 5GB, replica lag > 30s

Connection from Application Tier

Apps in private subnets connect via RDS endpoint DNS. Use connection pooling—PgBouncer for Postgres, ProxySQL or Laravel's built-in pool config for MySQL. Never expose RDS publicly unless you enjoy ransomware headlines.

Disaster Recovery Drills

Restore from automated backup to a staging instance quarterly. Measure RTO and RPO honestly—"we have backups" is not the same as "we restored in 45 minutes." Document runbook steps including DNS cutover and application credential rotation.

Enable deletion protection on production instances. One wrong CLI flag should not erase seven years of customer data. Separate IAM roles for read-only analysts versus migration runners with DDL permission.

Consider RDS Proxy when connection counts exceed 500 from horizontally scaled PHP-FPM pools. Proxy multiplexes connections and reduces "too many connections" incidents during traffic spikes.

Test failover manually in staging Multi-AZ at least once—DNS endpoint swap timing affects connection pool behavior in PHP apps holding long-lived connections. Configure app reconnect logic and pool validation queries so brief failovers do not require full app restart.

Cost and Sizing

Right-size instances using Performance Insights top waits—CPU idle with disk I/O saturated means storage or query tuning needed, not bigger instance class. gp3 lets you provision IOPS independently from storage size; OLTP workloads benefit from explicit IOPS allocation versus default gp2 burst credits exhausting under load.

Reserved instances for RDS save similarly to EC2—analyze three months on-demand before committing. Dev/staging instances stopped nights and weekends cut non-prod database spend 60% without affecting developer morning startup if automated start scheduler runs 7am local.

Document credential rotation order: update Secrets Manager, rolling-restart app workers, verify reconnect, then revoke old passwords. Half-updated pools cause 3 AM pages when stale FPM workers hold expired credentials until manual restart.

Monitoring Queries

Enable slow query log threshold aligned with SLA—often 500ms for OLTP. Feed slow logs to OpenSearch or CloudWatch Logs Insights with weekly review ritual. Top ten queries by total time consumed drive index strategy more reliably than guessing from ORM code alone during performance firefights.

Tag RDS instances with Environment and Application for cost allocation reports finance actually uses. Enable Enhanced Monitoring when diagnosing CPU vs IO wait confusion—without it, you guess whether to scale instance class or tune queries. Both matter; data separates the decisions.

Frequently Asked Questions

RDS vs Aurora?

Aurora scales reads and storage automatically—worth the premium above ~$5M ARR or heavy read load. RDS MySQL is fine for most SaaS until then.

What backup retention do we need?

7 days minimum production. 30 days if compliance requires point-in-time recovery windows.

How do we run migrations safely?

Blue/green deployments via RDS Blue/Green or maintenance window with app drain. Always test migrations on restored snapshot first.