Running MySQL yourself is easy at the beginning. Running it reliably in production is a completely different challenge. You must plan for backups, failover, monitoring, upgrades, connection spikes, security, scaling, and disaster recovery—all while keeping the application available.

Amazon Relational Database Service (Amazon RDS) removes much of this operational burden. AWS manages infrastructure provisioning, database software installation, automated backups, patching, and many common recovery operations, while you remain responsible for database design, access control, performance tuning, cost, and application behaviour.

The commands and naming are adapted into one independent, production-aware tutorial that you can follow in your own AWS account.

What You Will Learn

By the end of this guide, you will understand how to:

  • Create an encrypted RDS for MySQL database in private subnets
  • Retrieve credentials safely from AWS Secrets Manager
  • Connect to and verify the database without exposing it publicly
  • Configure Multi-AZ high availability and test failover
  • Monitor database load with CloudWatch Database Insights
  • Generate load and identify expensive SQL statements
  • Scale compute and storage safely
  • perform minor and major MySQL upgrades using safer deployment patterns
  • Create snapshots and perform point-in-time recovery
  • Manage database configuration with parameter groups
  • Export and analyse snapshot data with AWS Glue and Amazon Athena
  • Protect applications from connection storms with RDS Proxy
  • Apply production security controls
  • Design cross-Region disaster recovery
  • Understand when active-active replication is appropriate
  • Clean up every chargeable resource created during the tutorial

Prerequisites

You will need:

  • An AWS account with permission to manage RDS, EC2, VPC, IAM, KMS, Secrets Manager, CloudWatch, S3, Glue, and Athena
  • AWS CLI v2 configured with an IAM identity—not long-lived root credentials
  • A VPC spanning at least two Availability Zones
  • Two private database subnets
  • A private EC2 administration host, CloudShell VPC environment, or another secure host that can reach the database
  • The MySQL 8 client
  • Basic knowledge of SQL and AWS networking

This guide uses eu-west-2. Replace the Region, subnet IDs, security-group IDs, account IDs, passwords, and resource names with your own values.

Cost warning: RDS instances, Multi-AZ deployments, RDS Proxy, NAT gateways, cross-Region resources, Advanced Database Insights, snapshots, Glue, Athena, and data transfer can incur charges. Complete the cleanup section when you finish.

Architecture Overview

The main production architecture contains:

  1. An application or administration host in private application subnets
  2. An RDS Proxy endpoint for connection pooling
  3. An encrypted RDS for MySQL primary database in private database subnets
  4. A synchronous standby in a second Availability Zone when Multi-AZ is enabled
  5. Secrets Manager for database credentials
  6. CloudWatch for metrics, logs, alarms, and Database Insights
  7. S3, Glue, and Athena for offline analytics
  8. A cross-Region replica or copied snapshot for disaster recovery

The database should not have a public IP address. Network access should flow from a narrowly scoped application security group to the database or proxy security group on TCP port 3306.

Configure the Working Environment

Set reusable environment variables:

1
2
3
4
5
6
7
8
export AWS_REGION="eu-west-2"
export DB_ID="codingtricks-mysql"
export DB_NAME="appdb"
export DB_USER="adminuser"
export DB_CLASS="db.t4g.medium"

aws sts get-caller-identity
aws configure get region

Create a DB subnet group using at least two private subnets in different Availability Zones:

1
2
3
4
5
aws rds create-db-subnet-group \
  --region "$AWS_REGION" \
  --db-subnet-group-name codingtricks-db-subnets \
  --db-subnet-group-description "Private subnets for RDS MySQL" \
  --subnet-ids subnet-aaaaaaaa subnet-bbbbbbbb

For production, keep the following network rules:

  • RDS and the proxy must be deployed in private subnets.
  • Set Publicly accessible to No.
  • The DB security group should accept 3306 only from the proxy security group, or directly from the application security group when no proxy is used.
  • Do not allow 0.0.0.0/0 or ::/0 to the MySQL port.
  • Use Systems Manager Session Manager instead of opening SSH to the internet.

Create an Encrypted RDS for MySQL Instance

The simplest production starting point is a single RDS DB instance with encryption, backups, deletion protection, log exports, and Secrets Manager-managed credentials. Multi-AZ will be enabled in the next section.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
aws rds create-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --engine mysql \
  --db-instance-class "$DB_CLASS" \
  --allocated-storage 100 \
  --max-allocated-storage 500 \
  --storage-type gp3 \
  --storage-encrypted \
  --db-name "$DB_NAME" \
  --master-username "$DB_USER" \
  --manage-master-user-password \
  --db-subnet-group-name codingtricks-db-subnets \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --backup-retention-period 7 \
  --preferred-backup-window "02:00-03:00" \
  --preferred-maintenance-window "sun:03:30-sun:04:30" \
  --enable-cloudwatch-logs-exports '["error","general","slowquery"]' \
  --enable-performance-insights \
  --deletion-protection \
  --no-publicly-accessible \
  --copy-tags-to-snapshot \
  --tags Key=Environment,Value=lab Key=Project,Value=codingtricks-rds

AWS can manage and rotate the master password through Secrets Manager. This prevents the password from appearing in shell history, CloudFormation parameters, CI/CD logs, or source code. RDS supports this integration for DB instances and Multi-AZ DB clusters, although some features have compatibility limitations.

Wait until the instance becomes available:

1
2
3
aws rds wait db-instance-available \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID"

Retrieve the endpoint and secret ARN:

1
2
3
4
5
aws rds describe-db-instances \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].{Endpoint:Endpoint.Address,Port:Endpoint.Port,AZ:AvailabilityZone,Secret:MasterUserSecret.SecretArn}' \
  --output table

RDS endpoints are DNS names. Applications must resolve the endpoint on every new connection and must not pin its current IP address.

Connect Securely and Verify MySQL

From a host that can reach the private DB subnets, retrieve the secret without printing it into application logs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
export SECRET_ARN=$(aws rds describe-db-instances \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].MasterUserSecret.SecretArn' \
  --output text)

export DB_HOST=$(aws rds describe-db-instances \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].Endpoint.Address' \
  --output text)

export DB_PASSWORD=$(aws secretsmanager get-secret-value \
  --region "$AWS_REGION" \
  --secret-id "$SECRET_ARN" \
  --query SecretString \
  --output text | jq -r '.password')

mysql --host="$DB_HOST" --port=3306 \
  --user="$DB_USER" --password="$DB_PASSWORD" \
  --ssl-mode=REQUIRED

Run a small verification inside MySQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT VERSION(), @@hostname, @@port, NOW();

CREATE DATABASE IF NOT EXISTS appdb;
USE appdb;

CREATE TABLE healthcheck (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO healthcheck (message) VALUES ('RDS MySQL is ready');
SELECT * FROM healthcheck;

If the connection fails, verify DNS resolution, route tables, network ACLs, security-group references, the DB status, port 3306, and whether your client requires the current AWS RDS certificate bundle.

Configure Multi-AZ High Availability

A standard Multi-AZ DB instance keeps a synchronous standby in another Availability Zone. The standby is maintained for failover; it is not a read-scaling endpoint. For read scaling, create read replicas or consider a Multi-AZ DB cluster where supported.

Enable Multi-AZ:

1
2
3
4
5
6
7
8
9
aws rds modify-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --multi-az \
  --apply-immediately

aws rds wait db-instance-available \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID"

Verify it:

1
2
3
4
5
aws rds describe-db-instances \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].{MultiAZ:MultiAZ,PrimaryAZ:AvailabilityZone,SecondaryAZ:SecondaryAvailabilityZone,Endpoint:Endpoint.Address}' \
  --output table

Test a controlled failover during a maintenance window:

1
2
3
4
aws rds reboot-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --force-failover

Your application must use short connection timeouts, bounded retries with exponential backoff and jitter, and a pool that discards broken connections. The RDS DNS endpoint remains the logical connection target, but existing TCP sessions do not survive failover.

Monitor with CloudWatch Database Insights

CPU alone does not explain database performance. A database can be slow because of locks, I/O waits, too many connections, inefficient indexes, temporary tables, or a few expensive statements.

CloudWatch Database Insights gives a fleet-level view of database health and uses Performance Insights data to help analyse database load. In the console, open:

CloudWatch → Insights → Database Insights → select the RDS instance

Pay attention to:

  • DB load / Average Active Sessions (AAS) compared with available vCPUs
  • Top SQL to identify expensive statement fingerprints
  • Top waits to distinguish CPU, I/O, locks, and concurrency pressure
  • CPUUtilization
  • DatabaseConnections
  • FreeableMemory
  • FreeStorageSpace
  • ReadLatency and WriteLatency
  • ReadIOPS and WriteIOPS
  • DiskQueueDepth
  • ReplicaLag for read replicas

Create a basic CPU alarm:

1
2
3
4
5
6
7
8
9
10
11
12
13
aws cloudwatch put-metric-alarm \
  --region "$AWS_REGION" \
  --alarm-name "${DB_ID}-high-cpu" \
  --namespace AWS/RDS \
  --metric-name CPUUtilization \
  --dimensions Name=DBInstanceIdentifier,Value="$DB_ID" \
  --statistic Average \
  --period 300 \
  --evaluation-periods 3 \
  --datapoints-to-alarm 2 \
  --threshold 80 \
  --comparison-operator GreaterThanThreshold \
  --treat-missing-data missing

In production, send alarms to an SNS topic or incident-management integration and create separate warning and critical thresholds. Also alarm on storage, memory, connections, replica lag, and failover events.

Generate Test Load

sysbench is useful for learning how workload changes appear in Database Insights:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
sysbench oltp_read_write \
  --mysql-host="$DB_HOST" \
  --mysql-user="$DB_USER" \
  --mysql-password="$DB_PASSWORD" \
  --mysql-db="$DB_NAME" \
  --tables=8 \
  --table-size=100000 prepare

sysbench oltp_read_write \
  --mysql-host="$DB_HOST" \
  --mysql-user="$DB_USER" \
  --mysql-password="$DB_PASSWORD" \
  --mysql-db="$DB_NAME" \
  --tables=8 \
  --table-size=100000 \
  --threads=32 \
  --time=300 \
  --report-interval=10 run

Use Top SQL and wait events to find the bottleneck before resizing the instance. Scaling an inefficient query only makes an expensive problem larger.

Scale and Optimise the Database

Vertical Compute Scaling

Change the DB instance class when CPU, memory, or network capacity is consistently exhausted after query tuning:

1
2
3
4
5
aws rds modify-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --db-instance-class db.r7g.large \
  --apply-immediately

Applying immediately can cause an outage. For normal production changes, omit --apply-immediately and let RDS apply the modification during the preferred maintenance window.

Storage Autoscaling

MaxAllocatedStorage allows RDS to increase storage automatically as space is consumed:

1
2
3
4
5
aws rds modify-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --max-allocated-storage 1000 \
  --apply-immediately

Storage autoscaling does not scale storage back down. Set cost alarms and choose a maximum that provides operational headroom without hiding uncontrolled data growth.

Read Scaling

Create a read replica for read-heavy workloads:

1
2
3
4
5
6
aws rds create-db-instance-read-replica \
  --region "$AWS_REGION" \
  --db-instance-identifier "${DB_ID}-reader-1" \
  --source-db-instance-identifier "$DB_ID" \
  --db-instance-class "$DB_CLASS" \
  --no-publicly-accessible

Read replicas use asynchronous replication. Applications must tolerate replica lag and must not send read-after-write operations to a replica when immediate consistency is required.

Handle Minor and Major Upgrades Safely

First discover valid upgrade targets:

1
2
3
4
5
6
7
8
9
10
11
12
CURRENT_VERSION=$(aws rds describe-db-instances \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].EngineVersion' \
  --output text)

aws rds describe-db-engine-versions \
  --region "$AWS_REGION" \
  --engine mysql \
  --engine-version "$CURRENT_VERSION" \
  --query 'DBEngineVersions[0].ValidUpgradeTarget[].{Version:EngineVersion,Major:IsMajorVersionUpgrade}' \
  --output table

Before any upgrade:

  1. Read the MySQL and RDS release notes.
  2. Run compatibility checks against a restored snapshot.
  3. Test the application, drivers, stored procedures, events, and replication.
  4. Verify parameter and option-group compatibility.
  5. Take a manual snapshot.
  6. Define rollback criteria and an owner for the decision.
  7. Schedule a change window and monitor application errors and replication lag.

For a minor upgrade:

1
2
3
4
5
aws rds modify-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --engine-version TARGET_MINOR_VERSION \
  --apply-immediately

For major changes, Amazon RDS Blue/Green Deployments can create a synchronized green environment where you test the new engine or parameter configuration before switchover. The green database should normally remain read-only until switchover.

Important: At the time of writing, RDS Blue/Green Deployments do not support a source database whose master password is managed by the RDS integration with Secrets Manager. Always check the latest AWS limitations before choosing the upgrade method.

Back Up and Restore

RDS supports two complementary backup types:

  • Automated backups provide point-in-time recovery within the configured retention window.
  • Manual snapshots remain until you explicitly delete them and are useful before major changes or for longer retention.

Create a manual snapshot:

1
2
3
4
5
6
7
8
9
10
export SNAPSHOT_ID="${DB_ID}-$(date +%Y%m%d%H%M)"

aws rds create-db-snapshot \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --db-snapshot-identifier "$SNAPSHOT_ID"

aws rds wait db-snapshot-completed \
  --region "$AWS_REGION" \
  --db-snapshot-identifier "$SNAPSHOT_ID"

Restore the snapshot into a new DB instance:

1
2
3
4
5
6
7
8
aws rds restore-db-instance-from-db-snapshot \
  --region "$AWS_REGION" \
  --db-instance-identifier "${DB_ID}-restore-test" \
  --db-snapshot-identifier "$SNAPSHOT_ID" \
  --db-instance-class "$DB_CLASS" \
  --db-subnet-group-name codingtricks-db-subnets \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --no-publicly-accessible

For point-in-time recovery, inspect the restorable range:

1
2
3
4
5
aws rds describe-db-instances \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --query 'DBInstances[0].{Earliest:EarliestRestorableTime,Latest:LatestRestorableTime}' \
  --output table

Then restore:

1
2
3
4
5
6
7
8
aws rds restore-db-instance-to-point-in-time \
  --region "$AWS_REGION" \
  --source-db-instance-identifier "$DB_ID" \
  --target-db-instance-identifier "${DB_ID}-pitr" \
  --use-latest-restorable-time \
  --db-subnet-group-name codingtricks-db-subnets \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --no-publicly-accessible

A backup is not proven until you restore it, validate row counts and integrity, and measure the recovery time. Automate restore tests in a non-production account.

Manage Configuration with Parameter Groups

RDS parameter groups are managed collections of MySQL settings. Default groups cannot be edited, so create a custom group that matches your engine family:

1
2
3
4
5
6
7
8
9
10
11
12
aws rds create-db-parameter-group \
  --region "$AWS_REGION" \
  --db-parameter-group-name codingtricks-mysql8-prod \
  --db-parameter-group-family mysql8.0 \
  --description "Production MySQL 8 parameters"

aws rds modify-db-parameter-group \
  --region "$AWS_REGION" \
  --db-parameter-group-name codingtricks-mysql8-prod \
  --parameters \
    "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate"

Attach it:

1
2
3
4
5
aws rds modify-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --db-parameter-group-name codingtricks-mysql8-prod \
  --apply-immediately

Some parameters are dynamic; static parameters require a reboot. Check the ApplyType, IsModifiable, and allowed values before modifying anything:

1
2
3
4
aws rds describe-db-parameters \
  --region "$AWS_REGION" \
  --db-parameter-group-name codingtricks-mysql8-prod \
  --query 'Parameters[?ParameterName==`slow_query_log` || ParameterName==`long_query_time`]'

Treat parameter groups as code. Keep the desired values in Terraform, CloudFormation, or CDK; review changes; and test them against a production-like workload.

Analyse Snapshot Data with S3, Glue, and Athena

RDS can export supported snapshot data to Amazon S3 in Apache Parquet format. This lets analytical tools query a consistent snapshot without sending heavy reporting queries to the production database.

The workflow is:

  1. Create or reuse an encrypted RDS snapshot.
  2. Create an S3 bucket and prefix for the export.
  3. Create an IAM role that RDS can assume to write the export.
  4. Grant the role access to the destination bucket and KMS key.
  5. Start the snapshot export task.
  6. Create a Glue Data Catalog database.
  7. Run a Glue crawler against the exported Parquet files.
  8. Query the resulting tables in Athena.

Example export command:

1
2
3
4
5
6
7
8
aws rds start-export-task \
  --region "$AWS_REGION" \
  --export-task-identifier "${DB_ID}-export-001" \
  --source-arn "arn:aws:rds:${AWS_REGION}:123456789012:snapshot:${SNAPSHOT_ID}" \
  --s3-bucket-name codingtricks-rds-analytics-123456789012 \
  --s3-prefix rds-exports \
  --iam-role-arn arn:aws:iam::123456789012:role/RDSSnapshotExportRole \
  --kms-key-id arn:aws:kms:${AWS_REGION}:123456789012:key/KEY_ID

Monitor it:

1
2
3
aws rds describe-export-tasks \
  --region "$AWS_REGION" \
  --export-task-identifier "${DB_ID}-export-001"

After the Glue crawler creates tables, use Athena for queries such as:

1
2
3
4
5
6
7
SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_revenue
FROM rds_analytics.orders
GROUP BY customer_id
ORDER BY total_revenue DESC
LIMIT 20;

This is a batch analytics pattern, not real-time replication. Partition data where possible, compress results, restrict S3 and KMS permissions, and configure an Athena workgroup with query-result encryption and cost controls.

Protect Applications with RDS Proxy

Serverless functions and horizontally scaled services can open thousands of short-lived database connections. MySQL spends memory and CPU managing those sessions. RDS Proxy pools and reuses connections, helps absorb traffic bursts, and can improve application resilience during database failover.

The proxy requires:

  • Network access to the DB instance
  • A secret for each database user, or supported end-to-end IAM authentication
  • An IAM role that allows the proxy to read the required secret and use its KMS key
  • TLS enforcement for production clients

Create the proxy:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
aws rds create-db-proxy \
  --region "$AWS_REGION" \
  --db-proxy-name codingtricks-mysql-proxy \
  --engine-family MYSQL \
  --auth "SecretArn=$SECRET_ARN,IAMAuth=DISABLED" \
  --role-arn arn:aws:iam::123456789012:role/RDSProxySecretRole \
  --vpc-subnet-ids subnet-aaaaaaaa subnet-bbbbbbbb \
  --vpc-security-group-ids sg-0proxysecuritygroup \
  --require-tls

aws rds register-db-proxy-targets \
  --region "$AWS_REGION" \
  --db-proxy-name codingtricks-mysql-proxy \
  --db-instance-identifiers "$DB_ID"

Retrieve the proxy endpoint:

1
2
3
4
5
aws rds describe-db-proxies \
  --region "$AWS_REGION" \
  --db-proxy-name codingtricks-mysql-proxy \
  --query 'DBProxies[0].Endpoint' \
  --output text

Change only the application’s database host to the proxy endpoint. Test transactions carefully: session state, temporary tables, user variables, and some prepared statements can pin a client to one underlying connection and reduce pooling efficiency.

RDS Proxy is useful for connection management; it does not optimise SQL or increase the database’s compute capacity.

Production Security Checklist

Network Security

  • Keep RDS private and disable public access.
  • Use security-group-to-security-group rules.
  • Separate application, proxy, and database security groups.
  • Use multiple Availability Zones without opening broad CIDR ranges.
  • Record and review VPC Flow Logs where appropriate.

Identity and Secrets

  • Never use the AWS root user for administration.
  • Grant least-privilege IAM permissions.
  • Store database passwords in Secrets Manager and rotate them.
  • Use separate MySQL users for applications, migrations, monitoring, and administration.
  • Consider IAM DB authentication for suitable short-lived connections.
  • Never embed secrets in AMIs, container images, repositories, or user data.

Encryption

  • Enable KMS encryption when creating the DB instance.
  • Require TLS for client connections.
  • Encrypt snapshots, S3 exports, logs, and cross-Region copies.
  • Use customer-managed KMS keys when your compliance or cross-account design requires control over key policy and rotation.

Auditing and Resilience

  • Export supported MySQL logs to CloudWatch Logs.
  • Enable CloudTrail for RDS, IAM, Secrets Manager, and KMS API activity.
  • Enable deletion protection for production instances.
  • Restrict snapshot sharing and regularly audit it.
  • Use AWS Config and Security Hub controls where appropriate.
  • Tag resources with owner, application, environment, cost centre, and data classification.

Design Disaster Recovery

Multi-AZ provides high availability inside one Region; it is not a complete regional disaster-recovery strategy.

Choose a DR pattern based on your recovery point objective (RPO), recovery time objective (RTO), data classification, and budget:

Pattern Typical recovery Cost Main consideration
Cross-Region snapshot copy Longer RTO and RPO Low Simple, but restoration takes time
Cross-Region automated backup replication PITR in another Region Low to medium Validate engine and Region support
Cross-Region read replica Lower RPO and RTO Medium to high Asynchronous lag and manual promotion
Application-level multi-Region design Lowest targets Highest Conflict handling and operational complexity

Create a cross-Region read replica from the destination Region:

1
2
3
4
5
6
7
aws rds create-db-instance-read-replica \
  --region eu-west-1 \
  --db-instance-identifier codingtricks-mysql-dr \
  --source-db-instance-identifier "arn:aws:rds:eu-west-2:123456789012:db:${DB_ID}" \
  --db-instance-class db.t4g.medium \
  --kms-key-id arn:aws:kms:eu-west-1:123456789012:key/DESTINATION_KEY_ID \
  --no-publicly-accessible

A failover runbook should include:

  1. Confirm the primary Region is unavailable or the database is unrecoverable.
  2. Freeze or isolate remaining writers when possible.
  3. Measure replication lag and record the expected data-loss window.
  4. Promote the replica.
  5. Update application configuration or DNS through an approved mechanism.
  6. Validate writes, reads, background jobs, and integrations.
  7. Communicate recovery status.
  8. Define how data will be reconciled and how the original Region will be rebuilt.

Test the runbook regularly. A diagram without a tested promotion, credential, networking, DNS, and application procedure is not a DR plan.

Understand Active-Active Replication

Active-active replication allows more than one database to accept writes. It can support specialised availability or geographic use cases, but it is much harder than a primary-and-replica design.

Before adopting it, answer:

  • Can the same row be updated in two locations?
  • How will primary keys remain globally unique?
  • What happens when inserts, updates, or deletes conflict?
  • Can every client tolerate eventual consistency?
  • How will schema changes be coordinated?
  • How will a failed node rejoin safely?
  • How will replication loops be prevented?

RDS for MySQL supports managed active-active cluster capabilities for specific versions and Regions. Check current AWS engine and Region support before designing around the feature. For most applications, a single writer with Multi-AZ protection, read replicas, and a documented DR promotion process is simpler and safer.

Use active-active only when the business requirement justifies conflict management, operational complexity, higher cost, and more demanding testing.

Production Best Practices Summary

  • Deploy databases only in private subnets.
  • Use Multi-AZ for production availability.
  • Encrypt storage and require TLS.
  • Store and rotate credentials in Secrets Manager.
  • Enable automated backups and test restores.
  • Monitor AAS, waits, Top SQL, memory, storage, latency, and connections.
  • Tune queries and indexes before scaling hardware.
  • Use read replicas for read scaling, not as a substitute for Multi-AZ.
  • Use RDS Proxy for bursty connection-heavy workloads.
  • Test upgrades on restored or green environments.
  • Manage parameter groups and infrastructure as code.
  • Define measurable RPO and RTO targets.
  • Test failover and regional recovery regularly.
  • Create budgets, cost-allocation tags, and alarms.

Troubleshooting Guide

Cannot Connect to the RDS Endpoint

Check:

1
2
nslookup "$DB_HOST"
nc -vz "$DB_HOST" 3306

Then verify the client is inside a routable network, security groups reference the correct source, network ACLs allow return traffic, the database is available, and the hostname is the RDS endpoint rather than an old IP.

Access denied for user

Confirm the username, secret, MySQL host permissions, and authentication plugin. If the secret was rotated, ensure the application or connection pool has fetched the new value.

Too Many Connections

Inspect:

1
2
3
4
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
SHOW FULL PROCESSLIST;

Fix leaked connections, reduce pool sizes across service replicas, add timeouts, and consider RDS Proxy. Increasing max_connections without confirming available memory can make an outage worse.

High CPU

Use Top SQL, wait events, and execution plans. Check indexes, rows examined, query frequency, lock contention, temporary tables, and application retry loops before resizing the DB instance.

Storage Is Filling Up

Review binary log retention, general and slow logs, temporary tables, table growth, and unused data. Enable storage autoscaling before the database becomes critically low, but also fix the cause of unbounded growth.

Replica Lag Is Increasing

Check long-running transactions, write bursts, large DDL operations, destination instance capacity, network conditions, and replication errors. Do not route consistency-sensitive reads to a lagging replica.

Environment Cleanup

The following operations delete lab resources. Confirm every identifier before running them.

Remove the proxy:

1
2
3
aws rds delete-db-proxy \
  --region "$AWS_REGION" \
  --db-proxy-name codingtricks-mysql-proxy

Delete replicas and restored test instances:

1
2
3
4
5
aws rds delete-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "${DB_ID}-reader-1" \
  --skip-final-snapshot \
  --delete-automated-backups

Disable deletion protection on the main lab instance and delete it:

1
2
3
4
5
6
7
8
9
10
11
aws rds modify-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --no-deletion-protection \
  --apply-immediately

aws rds delete-db-instance \
  --region "$AWS_REGION" \
  --db-instance-identifier "$DB_ID" \
  --skip-final-snapshot \
  --delete-automated-backups

Also remove resources that apply to your run:

  • Manual snapshots and cross-Region snapshot copies
  • PITR and restore-test instances
  • Cross-Region replicas
  • Snapshot export tasks and exported S3 objects
  • Glue crawlers, Data Catalog tables, databases, and Athena query results
  • CloudWatch alarms and log groups
  • Secrets that are no longer required
  • Custom IAM roles and policies
  • Custom parameter groups after all attached instances are gone
  • The DB subnet group and temporary security groups
  • Temporary EC2 or CloudShell VPC environments

Never delete shared VPC, KMS, IAM, logging, or security resources simply because the lab used them.

Conclusion

Amazon RDS makes MySQL easier to operate, but a managed service does not automatically make a workload production-ready. Reliability comes from combining private networking, Multi-AZ, tested backups, useful monitoring, controlled upgrades, connection management, least-privilege access, and a rehearsed disaster-recovery plan.

Happy Coding