MNM MNM - 5 months ago 31
Java Question

Use MySQL Workbench to connect to Amazon AWS db

I have been trying to connect to my AWS database using MySql workbench with no luck.
I have the database running but cannot connect to it.
I have a jdbc url to connect to the aws server. Like this
jdbc:mysql://xxxxxxxxx2.c6bdhftggj7t.somestuff.amazonaws.com:3306/mypartofserver
I have a username and password so I know where to put them but I don't know how to connect. Should I try Oracle SQL one I think that that one has a JDBC connection.

I don't see where to try to connect to the server to add tables and edit them. I am a bit lost here
Thank you very much

Answer

The first step is to determine if exposing the db to the internet is truly in your best interest. Many would say that you should not. For the following step it assumes you want to, and for the first cut it will be over the standard port 3306. I will strive to improve this answer with a secure channel later. Also, a few screenshots will follow when I get back to my office.

Steps

  1. For the Security Group in force for your running ami instance, ensure that port 3306 is open. This is after you have first determined if this is appropriate for you organization. Note that there are CIDR settings for which IP addresses are allowed to get through this firewall.

  2. Determine if you are fine connecting with the (a) Public IP Address that is assigned to your server, (b) the long Public DNS Entry such as:

    xxxxxxx-west-2.compute.amazonaws.com

Note the above was redacted. Or (c) if you want to Allocate an Elastic IP Address to your server (which will automatically remove the auto assigned Public IP Address at the same time).

Elastic IP Addresses have the added benefit of your being able to point them to any running instance in a very flexible manner. Either thru the web interface or through various API's. If you aren't using one that is Allocated, then consider de-allocating pronto and free it (but you lose it as a hard-coded known one that you are used to). But charges will stop. They are free as long as they are Associated with a Running Instance. When they are not, charges start hourly. Like a cent an hour.

  1. On the mysql daemon side of things, make sure you have the database(s) created, and users set up with the appropriate Grants to databases. Do not over-grant rights with wildcards so as to do an easy connect test. Rather, seek grants that have minimal rights and only add privileges as needed. Grant privileges to databases and or tables explicitly as opposed to wildcards as a best practice.

The host to use in the Grant call is often the '%' character for any host with that username. Use so cautiously, and see a more locked-down alternative in my "other answer" link below that uses a comcast hostname.

It is never a bad idea to issue the following as a sanity check to see users in your system:

mysql> select user,host,password from mysql.user where user like '%joe%';
+--------+------+-------------------------------------------+
| user   | host | password                                  |
+--------+------+-------------------------------------------+
| joe7   | %    | *7F748B224136BAF824D3FF63F0C5B18BB93CFA8B |
| joe8   | %    | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| joe123 | %    | *0913BF2E2CE20CE21BFB1961AF124D4920458E5F |
+--------+------+-------------------------------------------+

The above mysql database is a special one that houses, among other things, the users authorized to use the server. That is at the server-level. The grants for privileges to certain databases are another thing entirely.

Here the Joe users are shown to have a wildcard host, and a hashed password. For further info, check one of my other answers here.

  1. On the jdbc-side, you are now able to connect in a normal fashion using your choice from (2) above, including the dbname.

As mentioned, I will improve this when I have more time.

Comments