Mysql – Unable to connect to an AWS RDS read replica of MySQL

amazon-rdsamazon-web-servicesMySQL

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?

Best Answer

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.

Related Question