AWS RDS Writer Endpoint vs Reader Endpoint

Question

I created an Amazon Aurora instance in my VPC. When the instance was created, it came with 2 endpoints, a writer and a reader endpoint.

The instance is using a security policy with an ingress rule (Type: All Traffic, Protocol: All, Port: All, Source: 0.0.0.0/0).

I tried both MySQL Workbench and MySQL monitor command interface to connect to the endpoints.

The connection to the Reader endpoint worked but that to the Writer endpoint didn't. The reader endpoint was readonly, so I was unable to build my DB using it.

Any idea?

Answer 1

An aurora cluster instance might be either a writer or a reader. Aurora clusters allow one writer and up to 15 readers. The instance role might change failover happens.

The writer DNS endpoint always resolves to the writer instance, Cluster writer endpoint

The reader endpoint DNS randomly resolves to one of the reader instances with TTL=1.

(Note: It might point to the writer instance only if they are one healthy instance is available in the cluster fleet) Cluster reader endpoint

Answer 2

In the comments, the author mentions it worked some times when they recreate, and sometimes it didn't. My suggestion was to review the network setup of the account.

The instances created share the same Security Group, so based on your scenario where one of them is functional, we can assume the SG is properly configured.

Each instance (reader/writer) is located in a different Availability Zone. That means each instance is in a different subnet. It's possible that one of the subnets is not configured properly (either with improper NACL rules, or incorrect Routing), and the non-functional instance is placed in that subnet. Since the allocation is dynamic everytime you create the cluster, this could create the on-and-off scenario.

Which subnets are used by an Aurora cluster depends on the RDS Subnet Group. This information is available in the cluster console > select each DB Identifier > Connectivity & Security > Subnet group, and use that value in the Subnet Group console (in the left menu). Ideally, all subnets should have the same NACL rules and be associated with the same Route Table (both in the VPC Console).

Side note: having your Security Group open to All Traffic from All Sources (0.0.0.0/0) is a security risk. Please evaluate narrowing down your ingress access.