How to Run MySQL on Amazon RDS Securely (AWS CLI Step-by-Step)

What this guide helps you do

By the end of this guide you will be able to:

• Create a DB subnet group spanning two Availability Zones
• Launch a MySQL 8.0 RDS instance in a private subnet with secure defaults
• Lock down network access with security group rules
• Connect to the instance over SSL from within your VPC
• Verify the database is responding and create an application user with least privilege
• Enable slow query logging, configure backups, and tune parameter groups
• Understand when MySQL on RDS is the right choice and when to consider alternatives

Simple explanation

Key terms explained:

• Amazon RDS: a managed service that runs MySQL (and other engines) on infrastructure AWS owns. You never SSH into the underlying machine. See Amazon RDS Overview for the full picture.
• DB subnet group: a list of private subnets in your VPC, spanning at least two Availability Zones, that RDS is allowed to use when placing instances.
• Security group: a stateful firewall on the RDS instance that controls which sources can connect on port 3306. See Security Groups in AWS Explained.
• Endpoint: the DNS hostname RDS gives you to connect to, such as myapp-mysql.xxxx.eu-west-2.rds.amazonaws.com. The IP behind it can change; the hostname does not.
• Private subnet: a subnet with no route to an internet gateway. Resources inside it are not reachable from the internet. See Subnets in AWS: Public, Private, and Isolated.
• Multi-AZ: RDS runs a synchronous standby replica in a second Availability Zone. If the primary fails, RDS promotes it automatically. You keep connecting to the same endpoint. See RDS Backups and High Availability.

When to use MySQL on RDS

MySQL on RDS is a strong fit when:

• Your application uses SQL: tables, joins, foreign keys, transactions
• You are migrating an existing MySQL database from on-premises or from EC2
• You want managed backups, patching, and failover without a dedicated DBA
• You need point-in-time recovery included with minimal configuration
• Your team is already comfortable with MySQL and wants to ship, not maintain infrastructure

When not to use MySQL on RDS

Consider an alternative when:

• You need higher availability or throughput at scale. Amazon Aurora MySQL is wire-compatible with MySQL but uses a different storage architecture that replicates across six copies in three AZs by default. The RDS vs Aurora comparison is worth reading before you commit at higher traffic levels.
• You need complex queries, JSON columns, or PostGIS. PostgreSQL on Amazon RDS has a richer feature set for analytical queries and advanced data types.
• You need OS-level control or unsupported extensions. If your workload requires a MySQL plugin or kernel setting that RDS does not expose, a self-managed MySQL on EC2 is the alternative, though the operational overhead is significant.
• Your access patterns are key-value or document-oriented. DynamoDB will outperform a relational database and cost less at very high scale for simple lookups.

How MySQL on RDS works

RDS splits responsibility into two layers:

AWS (control plane) handles everything below your SQL prompt: provisioning EC2 hardware, installing and patching the OS and MySQL engine, taking daily snapshots, storing transaction logs, detecting instance failures, and promoting the standby in a Multi-AZ setup.

You (data plane) handle everything above the SQL prompt: creating databases, designing schemas, managing users and permissions, optimising queries, running migrations, and deciding when to upgrade MySQL major versions.

When you create an RDS instance, AWS provisions a virtual machine, attaches EBS storage, starts MySQL, and exposes a DNS endpoint. You connect via that endpoint using the same MySQL client and connection strings you already know. You never log into the underlying server.

Prerequisites

Before running the commands in this guide, you need:

• AWS CLI configured with credentials that have RDS, EC2, and VPC permissions
• An existing VPC with private subnets in at least two Availability Zones. See VPC Networks Explained if you need to create one.
• Two private subnet IDs. These are subnets that route to a NAT gateway, not an internet gateway. Run aws ec2 describe-subnets to find them.
• A security group for RDS, created in the VPC with no inbound rules initially. You add the rule in Step 3.
• MySQL client installed. Confirm with mysql --version.
• A way to connect from inside the VPC: a bastion host, an EC2 instance in the same VPC, or an SSM Session Manager tunnel. A private RDS instance is not reachable directly from your laptop. See Connecting to Amazon RDS Securely for all connection options.

Architecture and networking checklist

Confirm you have the following before running any commands:

• VPC ID (e.g. vpc-0abc12345)
• At least two private subnet IDs in different AZs (subnet-0aaa, subnet-0bbb)
• Security group ID for RDS with no inbound rules yet (sg-rds-xxxxx)
• Security group ID for your application tier (sg-app-xxxxx)
• MySQL client installed
• A connection method planned: bastion, EC2 in the same VPC, or SSM tunnel

If any of these are missing, address them first. The subnet group cannot be changed after instance creation.

Step 1: Create a DB subnet group

A DB subnet group registers the private subnets that RDS can use when placing your instance. It must include subnets in at least two Availability Zones, otherwise Multi-AZ failover has nowhere to put the standby replica.

aws rds create-db-subnet-group \
  --db-subnet-group-name myapp-db-subnet-group \
  --db-subnet-group-description "Private subnets for RDS instances" \
  --subnet-ids '["subnet-0abc12345", "subnet-0def67890"]'

Replace the subnet IDs with your private subnet IDs. Use subnets that route to a NAT gateway for outbound traffic, not subnets with a direct internet gateway route.

Step 2: Create the MySQL RDS instance

Recommended: use Secrets Manager for the master password

The --manage-master-user-password flag tells RDS to generate a strong master password and store it in AWS Secrets Manager automatically. The password never appears in your shell history, CLI output, or CI logs.

aws rds create-db-instance \
  --db-instance-identifier myapp-mysql-prod \
  --db-instance-class db.t3.micro \
  --engine mysql \
  --engine-version 8.0 \
  --master-username admin \
  --manage-master-user-password \
  --allocated-storage 20 \
  --storage-type gp3 \
  --db-subnet-group-name myapp-db-subnet-group \
  --vpc-security-group-ids sg-0abc12345 \
  --backup-retention-period 7 \
  --multi-az \
  --no-publicly-accessible \
  --deletion-protection \
  --tags Key=Environment,Value=production Key=Project,Value=myapp

To retrieve the master password from Secrets Manager after the instance is created:

# Find the secret ARN
aws rds describe-db-instances \
  --db-instance-identifier myapp-mysql-prod \
  --query 'DBInstances[0].MasterUserSecret.SecretArn' \
  --output text

# Retrieve the secret value
aws secretsmanager get-secret-value \
  --secret-id <secret-arn> \
  --query SecretString \
  --output text

Alternative: self-managed password

If you cannot use Secrets Manager, pass the password directly. The password will appear in your shell history. Clear it afterwards.

aws rds create-db-instance \
  --db-instance-identifier myapp-mysql-prod \
  --db-instance-class db.t3.micro \
  --engine mysql \
  --engine-version 8.0 \
  --master-username admin \
  --master-user-password 'Replace-With-Strong-Password-1!' \
  --allocated-storage 20 \
  --storage-type gp3 \
  --db-subnet-group-name myapp-db-subnet-group \
  --vpc-security-group-ids sg-0abc12345 \
  --backup-retention-period 7 \
  --multi-az \
  --no-publicly-accessible \
  --deletion-protection \
  --tags Key=Environment,Value=production Key=Project,Value=myapp

Key flags explained:

• --db-instance-class db.t3.micro: smallest burstable instance, suitable for development and low-traffic workloads. Use db.m6i.large or larger for production with consistent load.
• --engine-version 8.0: MySQL 8.0 is the current supported version. MySQL 5.7 reached end of life in October 2023.
• --storage-type gp3: General Purpose SSD with consistent baseline throughput. More cost-effective than gp2 for most workloads.
• --backup-retention-period 7: keep 7 days of automated backups for point-in-time recovery. Increase to 14-35 days for production.
• --multi-az: run a synchronous standby in a second AZ for automatic failover.
• --no-publicly-accessible: the instance gets no public IP and is not reachable from the internet. Always set this in production.
• --deletion-protection: prevents accidental deletion. You must explicitly disable this before the instance can be deleted.

Check the status while the instance provisions (it takes 5-10 minutes):

aws rds describe-db-instances \
  --db-instance-identifier myapp-mysql-prod \
  --query 'DBInstances[0].DBInstanceStatus' \
  --output text

When the output shows available, the instance is ready.

Step 3: Lock down the security group

The security group on your RDS instance should allow inbound traffic on port 3306 only from your application tier’s security group. Never open port 3306 to 0.0.0.0/0.

# Allow the app server security group to connect to RDS on port 3306
aws ec2 authorize-security-group-ingress \
  --group-id sg-rds-security-group-id \
  --protocol tcp \
  --port 3306 \
  --source-group sg-app-server-security-group-id

Why reference a security group instead of an IP address?

When you use a security group as the source, any instance belonging to sg-app-server-security-group-id can connect automatically. New app servers get access when they join the group; terminated servers lose it immediately. IP addresses in AWS can change, so group-based rules are far more maintainable than IP-based rules. For background, see Private vs Public IP Addresses in AWS.

Step 4: Find the endpoint and connect securely

Get the endpoint:

aws rds describe-db-instances \
  --db-instance-identifier myapp-mysql-prod \
  --query 'DBInstances[0].Endpoint.Address' \
  --output text

The output will be something like myapp-mysql-prod.xxxx.eu-west-2.rds.amazonaws.com. Always use this hostname. Never use a raw IP address.

Download the RDS CA certificate bundle:

curl -o global-bundle.pem \
  https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

Connect with SSL (from an EC2 instance or via a bastion/SSM tunnel):

mysql -h myapp-mysql-prod.xxxx.eu-west-2.rds.amazonaws.com \
      -u admin \
      -p \
      --ssl-ca=/path/to/global-bundle.pem \
      --ssl-verify-server-cert

Enforce SSL at the database level:

Relying on applications to request SSL is not sufficient. A misconfigured app could connect in plaintext and you would never know. Use a parameter group to enforce require_secure_transport so the database rejects non-SSL connections entirely:

# Create a custom parameter group (required; default groups cannot be modified)
aws rds create-db-parameter-group \
  --db-parameter-group-name myapp-mysql80-params \
  --db-parameter-group-family mysql8.0 \
  --description "Custom MySQL 8.0 parameters"

# Require SSL on all connections
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-mysql80-params \
  --parameters ParameterName=require_secure_transport,ParameterValue=ON,ApplyMethod=immediate

# Apply the parameter group to your instance
aws rds modify-db-instance \
  --db-instance-identifier myapp-mysql-prod \
  --db-parameter-group-name myapp-mysql80-params \
  --apply-immediately

For all four connection patterns (direct VPC, bastion host, RDS Proxy, and SSM Session Manager) see Connecting to Amazon RDS Securely.

Step 5: Verify the database is working

Once connected, run a quick sanity check before writing any application code:

-- Check the MySQL version
SELECT VERSION();

-- List existing databases
SHOW DATABASES;

-- Create and verify a test database
CREATE DATABASE myapp_test;
USE myapp_test;

-- Create a minimal test table
CREATE TABLE connectivity_check (
  id INT PRIMARY KEY AUTO_INCREMENT,
  checked_at DATETIME DEFAULT NOW()
);

INSERT INTO connectivity_check () VALUES ();
SELECT * FROM connectivity_check;

-- Clean up
DROP DATABASE myapp_test;

If all of these succeed, your network path, security group, credentials, and SSL connection are all working correctly.

Step 6: Create an application user with least privilege

Connect as the master user and create a dedicated application user:

-- Create the application user
CREATE USER 'myapp'@'%' IDENTIFIED BY 'StrongRandomPassword1!';

-- Grant only what the application needs on its own database
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_production.* TO 'myapp'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'myapp'@'%';

Use % for the host if your application servers have varying private IP addresses. If you know the private CIDR range, restrict the host to that range for an additional layer of defence.

Store this password in AWS Secrets Manager and rotate it regularly. Do not put it in source code or in environment variable files that get committed to git.

Step 7: Configure backups, logs, and parameter groups

Backups

Automated backups are enabled by --backup-retention-period. For production, increase the retention window and set a low-traffic backup window:

aws rds modify-db-instance \
  --db-instance-identifier myapp-mysql-prod \
  --backup-retention-period 14 \
  --preferred-backup-window "03:00-04:00" \
  --apply-immediately

For the full picture of point-in-time recovery and Multi-AZ failover, see RDS Backups and High Availability.

Parameter group tuning

RDS uses parameter groups to configure MySQL settings that would normally live in my.cnf. The default parameter group cannot be modified, so all tuning requires a custom group. Add slow query logging and connection limits alongside the SSL setting from Step 4:

aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-mysql80-params \
  --parameters \
    ParameterName=max_connections,ParameterValue=500,ApplyMethod=pending-reboot \
    ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate \
    ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate \
    ParameterName=require_secure_transport,ParameterValue=ON,ApplyMethod=immediate

MySQL on RDS vs Aurora vs PostgreSQL on RDS

Start with MySQL on RDS for existing MySQL applications or standard web workloads. Move to Aurora MySQL when you hit performance or availability limits. Move to PostgreSQL on RDS when your workload needs features MySQL does not provide.

See RDS vs Aurora for a full decision-oriented comparison.

Troubleshooting connection issues

Connection refused or timeout on port 3306

The most common cause is a missing or incorrect security group rule. Check that your RDS security group has an inbound rule on port 3306 from the security group attached to your EC2 instance or bastion.

# Inspect the inbound rules on your RDS security group
aws ec2 describe-security-groups \
  --group-ids sg-rds-security-group-id \
  --query 'SecurityGroups[0].IpPermissions'

Cannot connect from your laptop

Your RDS instance is in a private subnet with no public IP. You cannot connect directly from the internet. Use SSM Session Manager port forwarding or an SSH tunnel through a bastion host. See Connecting to Amazon RDS Securely.

Host not found or DNS resolution failure

Check that you copied the endpoint correctly from the CLI output. Endpoints look like identifier.xxxxxxxx.region.rds.amazonaws.com. If the instance status is not available yet, the endpoint will not resolve. Confirm the status:

aws rds describe-db-instances \
  --db-instance-identifier myapp-mysql-prod \
  --query 'DBInstances[0].DBInstanceStatus' \
  --output text

SSL certificate errors

Confirm you downloaded the global bundle from AWS (global-bundle.pem) and that the path passed to --ssl-ca is correct. Also check that you are connecting using the RDS endpoint hostname, not a raw IP address. The certificate is issued to the hostname, not the IP.

Wrong endpoint after a Multi-AZ failover

After failover, the DNS record for your endpoint updates to point at the new primary. If you hardcoded an IP address in your connection string, you end up connecting to the failed instance. Always use the endpoint hostname and let DNS resolve it.

For general AWS network troubleshooting, see Troubleshooting Network Issues in AWS.