While connecting to Aurora RDS locally from machine using any IDE like MySQL Workbench or SQL Developer mostly 2 types of error are encountered:
1) ERROR 2003 (HY000)
2) ERROR 2005 (HY000)
ERROR 2003 (HY000): Generally this occurs because of security access reason. This means that the database instance is correctly set up and host is live but there could be combination of any below three misconfiguration:
1) Database Cluster Public accessibility set as No
If the Public accessibility is No, then it cannot be accessed directly and can be only accessed within other AWS services. One way to connect is by setting bastion (EC2 server) and then connect to EC2 from your local machine via ssh set up. And allow port 3306 inbound security to bastion. This is the most secured way of connecting RDS as it does allow its port access to the world. Otherwise the ports will be opened to whole world making it vulnerable to cyber attacks.
2) Second reason could be that even if Public accessibility is set to Yes and Security group Inbound rules are also defined to allow access directly but you may find the error because of Subnet group set up. Only if RDS is attached to public subnet (internet gateway attached to this subnet) it can be accessed directly. If Private subnet are attached to the the database cluster then again it needs to be accessed via Bastion (EC2 server) and allowing inbound rules for port 3306 for bastion.
3) Last misconfiguration could be VPC Security groups inbound rules attached to the Cluster. Check if port 3306 is allowed for your IP’s from which you are accessing. Try selecting Anywhere option and then connect. Though this is not a recommended approach to allow access from anywhere but could be good way to test your connectivity.
As you can see above that 0.0.0.0/0 and ::/0 means allow inbound connection from where. This is not preferred in prod environment. So give only port 3306 access to specific IP addresses of you network from where you will access RDS. If you don’t have any specific list of IP’s then set up bastion for connectivity.
ERROR 2005 (HY000): This is mostly due to incorrect host address while connecting to RDS. So check correct host and re-try.
Command to connect Aurora RDS
mysql –user=username –password=”password” -h host.rds.amazonaws.com -P 3306 dbname
mysql -h host.rds.amazonaws.com -P 3306 -u username -p
Note: Preferred approach to connect locally from your machine:
1) Create database cluster in private subnet. Give internet access to this private subnet (NAT gateway attached) through public subnet(Internet Gateway and NAT gateway attached)
2) Set Public accessibility to No.
3) Launch EC2 instance (bastion) in same VPC and public subnet. Allow ssh access only through private keys.
4) Create Security Group for RDS cluster and allow access to PORT 3306 to EC2 instance
5) Now connect EC2 instance locally via ssh and run above mentioned mysql command or establish connection from MySQL workbench or any other IDE.
Refer AWS doc for more detail: https://aws.amazon.com/premiumsupport/knowledge-center/rds-connect-using-bastion-host-linux/
I will create another post for detail step to Create AWS RDS Cluster and connect RDS using EC2 server (bastion) SSH connection from local machine.