Unable to connect to an AWS RDS read replica of MySQL

Question

I'm not able to connect to an AWS RDS MySQL read replica.

$ mysql -h my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com -umyname
ERROR 2003 (HY000): Can't connect to MySQL server on 'my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com' (60)

Here're the configurations I've checked:

1. The security group allows me to connect from my local computer to the remote RDS.

2. The Public accessibility of the read replica is Yes

3. I can use nmap to detect that port 3306 on the RDS is open.

$ nmap -Pn -p 3306 my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com
Host discovery disabled (-Pn). All addresses will be marked 'up' and scan times will be slower.
Starting Nmap 7.91 ( https://nmap.org ) at 2021-05-18 17:12 CST
Nmap scan report for my-db-read-replica.qprdbd0pavr5.us-east-1.rds.amazonaws.com (3.208.xxx.xxx)
Host is up.
rDNS record for 3.208.xxx.xxx: ec2-3-208-xxx-xxx.compute-1.amazonaws.com

PORT     STATE    SERVICE
3306/tcp filtered mysql

Nmap done: 1 IP address (1 host up) scanned in 6.46 seconds

I've also rebooted the read replica, but I still can't connect to it.

The connection worked normally yesterday.

What could be the reason for failure of the connection?

Answer 1

The root cause of the issue is that the subnet group of the read replica contains both public and private subnets.

A public subnet has a route table that has 0.0.0.0/0 routed to an internet gateway.

A private subnet has a route table that has 0.0.0.0/0 routed to a NAT instance.

When the instance type of the read replica is changed, AWS might create a new read replica in a different subnet.

If the new read replica is located in a private subnet, I cannot connect to it from my local computer.