Screenshot from the article

Introduction

Amazon Web Services (AWS) offers a wide range of database solutions catering to different workloads, from traditional relational databases to NoSQL and in-memory databases. Understanding the types of databases, their use cases, and management commands is essential for AWS Cloud Engineers, DevOps professionals, and Database Administrators.

What This Guide Covers:

  • Types of databases in AWS and their use cases
  • Basic and advanced commands with examples
  • Best practices for database management in AWS
  • Connecting to databases from a local machine and from AWS instances (EC2/ECS)
  • FAQs and troubleshooting common issues

1. Types of Databases in AWS

AWS provides both managed and self-managed database options. Below are the primary database types:

A. Relational Databases (SQL-based)

Used for structured data with ACID compliance.

  • Amazon RDS (Relational Database Service) — Managed SQL databases like MySQL, PostgreSQL, MariaDB, SQL Server, and Oracle.
  • Amazon Aurora — High-performance, cloud-native relational database compatible with MySQL and PostgreSQL.

B. NoSQL Databases

Designed for scalability and flexibility.

  • Amazon DynamoDB — Key-Value and Document NoSQL database with single-digit millisecond performance.
  • Amazon ElastiCache — Managed in-memory database service for caching (Redis/Memcached).
  • Amazon Neptune — Graph database for applications needing relationship-based queries.
  • Amazon Timestream — Serverless database for time-series data.

C. Data Warehousing & Analytics

For high-performance analytics and data processing.

  • Amazon Redshift — Fully managed petabyte-scale data warehouse.
  • AWS Glue — ETL (Extract, Transform, Load) service for processing large-scale data.
  • Amazon Athena — Query data stored in S3 using SQL.

D. Ledger & Blockchain Databases

For immutable and transparent records.

  • Amazon QLDB (Quantum Ledger Database) — Fully managed ledger database.
  • Amazon Managed Blockchain — Blockchain service for decentralized applications.

2. Common Database Commands and Examples

A. Amazon RDS — MySQL Commands

1. Connecting to an RDS MySQL instance from a laptop

mysql -h mydb-instance.cxyz123.us-east-1.rds.amazonaws.com -u admin -p

2. Creating a Database and Table

CREATE DATABASE myapp;
USE myapp;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

3. Running Queries

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM users;

4. Creating a Read Replica for RDS

aws rds create-db-instance-read-replica --db-instance-identifier mydb-replica \
--source-db-instance-identifier mydb-instance --region us-east-1

B. Amazon DynamoDB — NoSQL Commands

1. Creating a Table

aws dynamodb create-table --table-name Users \
--attribute-definitions AttributeName=UserId,AttributeType=S \
--key-schema AttributeName=UserId,KeyType=HASH \
--billing-mode PAY_PER_REQUEST

2. Inserting an Item

aws dynamodb put-item --table-name Users --item '{"UserId": {"S": "123"}, "Name": {"S": "Alice"}}'

3. Querying Data

aws dynamodb get-item --table-name Users --key '{"UserId": {"S": "123"}}'

C. Amazon Redshift — Data Warehouse Commands

1. Connecting to a Redshift Cluster

psql -h my-redshift-cluster.endpoint.amazonaws.com -U admin -d mydatabase

2. Creating a Table and Loading Data

CREATE TABLE sales (id INT, amount DECIMAL(10,2), date DATE);
COPY sales FROM 's3://mybucket/sales.csv'
CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/MyRedshiftRole'
CSV;

D. Amazon ElastiCache (Redis) Commands

1. Connecting to Redis from an EC2 instance

redis-cli -h mycache-cluster.abcdef.ng.0001.use1.cache.amazonaws.com -p 6379

2. Setting and Getting a Key-Value Pair

SET user:1001 "Alice"
GET user:1001

3. Best Practices for AWS Databases

✅ Security Best Practices

  • Enable IAM authentication where supported.
  • Use AWS Secrets Manager for credentials management.
  • Enable TLS/SSL for encrypted connections.
  • Implement VPC security groups to control database access.

✅ Performance Optimization

  • Use Read Replicas for better read performance.
  • Enable Multi-AZ deployment for high availability.
  • Use ElastiCache to offload database reads.

✅ Backup & Disaster Recovery

  • Enable automated backups in RDS.
  • Use Point-in-Time Recovery in DynamoDB.
  • Regularly snapshot databases before updates.

4. Connecting from Laptop and EC2/ECS to AWS Databases

A. Connecting from Laptop

  1. Install MySQL client: sudo apt install mysql-client
  2. Connect to RDS:
mysql -h my-rds-instance.cxyz123.us-east-1.rds.amazonaws.com -u admin -p

3. Ensure Security Group allows your IP (modify inbound rules in AWS Console).

B. Connecting from EC2

  1. SSH into EC2:
ssh -i my-key.pem ec2-user@ec2-54-123-45-67.compute-1.amazonaws.com

2. Connect to RDS or DynamoDB:

mysql -h my-rds-instance.cxyz123.us-east-1.rds.amazonaws.com -u admin -p aws dynamodb scan --table-name Users

3. Ensure Security Group allows EC2 private IP.

5. FAQs & Troubleshooting

❓ Why can’t I connect to my RDS database from my laptop?

✅ Ensure RDS Security Group allows your IP.

✅ Check if Public Access is enabled in RDS settings.

✅ Verify credentials and database endpoint.

❓ How do I enable automatic backups for RDS?

aws rds modify-db-instance --db-instance-identifier mydb-instance --backup-retention-period 7

❓ What’s the difference between Amazon RDS and Aurora?

✅ Aurora offers better performance (5x faster than MySQL, 3x faster than PostgreSQL).

✅ Aurora supports auto-scaling, while RDS does not.

Conclusion

AWS provides a diverse set of database solutions for different use cases. By understanding their capabilities, best practices, and troubleshooting techniques, you can ensure optimal performance, security, and availability in your AWS cloud environment.

📢 Have questions or feedback? Drop a comment below or connect with me on Twitter/X@spysood!

Originally published on Medium.