Easily Connect to an Amazon RDS MySQL Database Using MySQL Workbench

Question

I'm just trying to connect to an Amazon MySQL RDS instance from MySQL Workbench on my local machine. At the moment I am not trying to connect to an EC2 instance or to any other server or to do something special. I just want to connect in the easiest way, so I can add a database, tables and some data from MySQL Workbench.

I have a Workbench MySQL application downloaded and installed on my Mac running OSX 10.9.4.

I installed an instance of RDS MySQL on Amazon AWS.

I have an EC2 security group that is authorized for this RDS instance. (Should I use the CIDR / IP security group? If so, can you stop in more detail)

I opened MySQL Workbench and in the "Configure a new connection" window:

• The connection method is set to standard (TCP / IP).
• The hostname is the endpoint that I find under my RDS panel for this instance
• Username is my username that I made when I created this RDS instance.
• The password asks "Save in the keychain ..." I put the password that I made when I created this instance of RDS.

When I test the connection in WorkBench, I get an error message:

Can't connect to MySQL server on 'SERVER_IP_ADDRESS' (60) 

What am I doing wrong? Go step by step from the very beginning for beginners.

Answer 1

I have an EC2 security group that is authorized for this RDS instance. (Should I use the CIDR / IP security group? If so, can you stop in more detail)

This is the part that seems to be missing. You need to change the settings of your security group:

• You need to resolve the IP address of the computer that is trying to connect to the RDS instance using CIDR / IP. If you use the same computer to edit the security group as you try to connect via MySQL Workbench, then when you select "Connection Type: CIDR / IP" (at the bottom of the security group settings), the dialog will automatically Fill in your own IP block (your IP address added using / 32).

• Click "Authorize" and it should do.

I tested this resolution, in seconds to a minute, to visually populate the AWS console, but as soon as it says "Authorized", the connection should work.

This, of course, assumes your connection string is correct .;)

Answer 2

A few things you want to make sure:

• The VPC security group allows connections to the port used (default is 3306).
• Make sure you have an Internet gateway connected to the VPC.
• Your subnet route table should contain a route that directs Internet traffic to the Internet gateway. ( source )

For me, the latter slipped. In particular, I added a destination to the route table leading to 0.0.0.0/0 (ALL). I do not know how this is related to security, but after that it worked. This is a dropped db for me, and it is isolated from other instances / VPC.

Answer 3

This link helped me:

http://domino.symetrikdesign.com/2010/10/07/how-to-create-a-mysql-workbench-connection-to-amazon-ec2-server/

The SSH hostname must be the public DNS of your ec2 instance.

The SSH username must be the username for your ec2 instance (for example: ec2-user).

The SSH password must be your password for the ec2 instance.

The MySQL hostname should be "the endpoint that you find on your RDS control panel"

The username and password must be the username and password for the rds instance.

Answer 4

• In the AWS security group, open 3306 for incoming machine connections
• Grant privileges for remote access to MySQL - grant all privileges. to '@' identified using the grant option; flush privileges;
• Local MySQL Workbench setup - MySQLWorkbench installation screenshot

Good luck

Answer 5

You have several options:

• Open RDS direct access to the IP address of your computer. This should be easy enough, but it may not be so safe if your IP address is not static or provided through NAT, which will allow other computers to use the same IP address.
• Access RDS through an SSH tunnel on your EC2 instance. You would SSH into your EC2 instance using the appropriate key, and then connect to RDS. This is probably the best configuration to create a security point, since you do not need to open any additional holes in the RDS firewall, and you usually need to have SSH to access your EC2 instance.

Answer 6

Amazon instructions for the task seem decent, so I won’t try to duplicate all of this here; just the relevant parts. I use the command line, but the same settings apply for MySQL Workbench :

 mysql -h hostname 

If this does not help, feel free to leave a comment.

Answer 7

Answer

e.thompsy helped me, but since then everything has changed in the AWS interface. So that your computer can access the MySQL instance:

• Go to your RDS control panel and click on Jobs. This will display your RDS instances.
• Deploy the RDS instance that you want to access.
• In the "Security and Network" section, a security group is displayed and will have a security group name identifier (sg-xxxxxx)
• On a separate tab, open the EC2 control panel.
• In the left menu, in the "Network and Security" section, click "Security Groups"
• Click on the security group with the corresponding group ID from your RDS instance.
• The options are listed below for this group. Click inbox.
• Click "Edit." A dialog box will appear.
• Click Add Rule
• Select MYSQL / Aurora and it will populate TCP for the protocol and 3306 for Port Range, which is the default MySQL port. If you want to change this, select "Custom TCP" and enter in the range that you want to open.
• In the "Source" drop-down list, select "My IP" if you are on a computer that you must have access to, otherwise select "Custom" and enter the IP address of the device to provide access to the next value "/ 32" ,
• Click Save, then connect to MySql Workbench using Standard TCP / IP to your RDS endpoint using the port you specified. In this case, you definitely need to log in to Username / Password.

I do not know about the impact of security on opening this port to your IP address. If you are worried, I suggest you cancel the inbound rule when you need to access the RDS instance.

Answer 8

To connect Mysql RDS through the MySQL Workbench, follow these steps.

Type: mysql -h -P 3306 -u -p in cmd windows [you will be prompted for a password] If the security group and username pwd are correct, you will get the mysql dialog

ERROR 2003 (HY000): Cannot connect to MySQL server on '' (10060) - an error occurs when there are no incoming rules.

Always try to maintain a new database security group.

If the default security group is selected, default access is denied.

Security group. Incoming rule must be changed to MYSQL / Aurora TCP 3306 MyIP

he is connecting.