Key Requirements:

High availability and automatic recovery.

Separate transactional and analytical queries with minimal performance impact.

Allow up to a 4-hour lag for analytical queries.

Analysis of Options:

Option A:

Multi-AZ deployments provide high availability but do not include read replicas for separating transactional and analytical queries.

Analytical queries on the secondary DB instance would impact the transactional workload.

Incorrect Approach:Does not meet the requirement of query separation.

Option B:

Multi-AZ DB clusters provide high availability and include a reader endpoint. However, these are better suited for Aurora and not RDS for MySQL.

Incorrect Approach:Not applicable to standard RDS for MySQL.

Option C:

Multiple read replicas allow separation of transactional and analytical workloads.

Queries can be pointed to a replica in a different AZ, ensuring no impact on transactional queries.

Correct Approach:Meets all requirements with high availability and query separation.

Option D:

Creating nightly snapshots and read-only databases adds significant operational overhead and does not support the 4-hour lag requirement.

Incorrect Approach:Not practical for dynamic query separation.

AWS Solution Architect References:

Amazon RDS Read Replicas

Multi-AZ Deployments