Which AWS solution can achieve this?
Amazon Aurora Global Database
Amazon DynamoDB global tables
Amazon RDS for MySQL with Multi-AZ enabled
Amazon RDS for MySQL with a cross-Region snapshot copy
Explanations:
Amazon Aurora Global Database provides low-latency global reads and supports cross-Region replication with a typical RPO of less than a second and an RTO of under a minute, making it suitable for the requirements.
Amazon DynamoDB global tables are designed for high availability and automatic multi-Region replication, but they do not offer a traditional relational database structure, which is required here. Additionally, the RPO is typically less than a second, but the structure does not match the requirements for a relational database.
Amazon RDS for MySQL with Multi-AZ provides high availability and failover support but does not support cross-Region replication, which is necessary for multi-Region disaster recovery. The RPO for this setup is not suitable for the 1-second requirement.
Amazon RDS for MySQL with cross-Region snapshot copy allows for disaster recovery but does not provide the RPO of 1 second, as snapshots are not continuous and can take time to create and restore, making it unsuitable for the specified RPO.