Good news everyone, if you don’t have the ability to spin up databases, or just don’t want to deal with it, Amazon has created some wonderful tools to help you out. Wait, Amazon? Yes, Amazon! Many think Amazon as being a shopping mall of everything, a media store, and shipping giant. But wait, there’s more! Amazon has a whole line of services, which we will cover, that include Windows Server 2012 virtual machines, databases like MySQL, virtual networking, and yes even directory and email services. However, this is just the tip of the iceberg, there are tons of other services available as well. If you can’t wait for me to talk about them, visit aws.amazon.com for more information.
Now back to our regular scheduled programming….
Okay, you need a database, something simple, MySQL will work. You could download the install file, install the database and all its tools and get going. That could take a long time if you are not sure what you are doing and then risk screwing something up. Head over to aws.amazon.com and sigup for their Amazon Web Services. It is free to sign up, but requires a credit card, you know just in case you do something that requires money. Once you sign up, you have a list of options, I mean just look at it all!!
I have been working with AWS for a few months now, but only recently started diving into it. I wanted to start making tutorials on these services because some of the services can get tricky to setup, trust me I know!
So, if you look on the list, the dashboard, you will see several groups, one group is Database. This is where you will be able to create a number of different database types. The type of database depends on what you going to be using it for. For example, if you want a database to store a bunch of data and you are going to be writing data to it, then you will be fine with a standard database. But if you want to store A LOT of data in a database, you are looking at the fourth option (Redshift), where you can store petabytes of data in a data warehouse. But, for users like us and about 99% of everyone else, RDS option will work just fine. I mean once you choose that you can configure your instance to your likings, as long as you pay for it!
Let’s begin, go ahead a navigate to the Database group and click on RDS, you should get a screen like this:
Choosing a Database
This shows me that I have one DB Instance running and a bunch of things that don’t really matter right now. What we are looking for right now is that Create Instance section, which is located towards the middle of the page. You can see this by looking for the big blue “Launch a DB Instance” button. Go ahead a click on the big blue button. Now you should get a few options on which database you want to use, let’s choose MySQL for this tutorial. You will click on the database and click the Select button. Next, AWS will ask you if this is for production use or not production, for this tutorial, guess what, it’s not a production database, so choose no and proceed to the next step.
Specify the Details
More options, well, at least AWS is taking the whole installation thing away! This section you will leave the DB Engine, License Model, and DB Engine Version to their defaults. The DB Instance Class is where you can spec the instance out. Keep in mind, what you choose, is what you pay for, and this stuff isn’t cheap! For the sake of this tutorial, choose db.t2.micro, which should be the smallest and least expensive. Multi-AZ Deployment is No. Storage type can be left at default, General Purpose (SSD). Allocated Storage, this is where it depends on what you are going to be using it for, for testing, keep it at 5 GB.
The last section is how you are going to identify your instance. This is a unique name for you instance, be creative! Once you have that figured out, set a master user name, master is a good one! Then set a password, DON’T FORGET THIS! Then confirm it, because, you know, once is never enough!
Everything here can be left at default, except that you have create a Database Name, if you don’t, no database will be created. Go ahead and specify a name for it, make it meaningful, all the other settings are fine. Then click Launch DB Instance. You will get a confirmation screen, telling you that it was created. Click on View Your DB Instances. AWS will bring you to the RDS Dashboard where you will see your newly created database instance. It will not be ready yet, it takes a few minutes to set everything up. You can stay on this page and it will refresh and you can check the status.
Connecting to the Instance
Once you have everything complete and the status is available, you can connect to it. Since we are using MySQL, I suggest using MySQL Workbench, but feel free to use other tools. In order to find out what URL to use to connect to the database, you will look for the Endpoint and Port on the dashboard for your database. You will use this information to create a new connection in the workbench. Use the master user name and password to log in for the first time. Once logged in, you can create different users.
Some issues you may run into when creating the database instance. If you did not change any of the security settings, which is fine, because I said leave them defaults. But anyway, you will find that you can only connect from your computer only, or at least any computer originating from your public ISP IP. In order to fix this, you need to edit the security assigned or create a new one.
Look for the Security Groups under Security and Network of your database instance. Click on it and it will bring up a new tab or window. You probably only have one listed for now, but if you have more, make sure you are on the correct one. At the bottom of the page, you find a tab for Inbound, which is traffic going to the database. Most likely this is set for Protocol TCP and Port 3306, which is what MySQL uses to communicate on. This is all fine and dandy for testing at one location where the IP doesn’t change. But this won’t work if there are multiple offices or your ISP changes your IP. Basically, what this means is that the database instance will only accept connections on port 3306 from what ever the IP is, using TCP, very specific rule. We want to change this to allow more than one client connection from different IP’s. At the top of the page click on Actions, then Edit inbound rules. You can now add rules or modify the current one. Adding a rule gives you different protocol options or you can specify a custom one, specify a port, and the source, which is an IP. If you know the IP address of your other source, then you can specify it, it is a public IP. If not then you will probably allow from anywhere and make any connection allowed. For testing, this is fine, but for production, you will want to narrow this down. Go ahead and save the changes and test the connection. It should work now, the rules are applied immediately, if you have a lot of instances that use that same rule, it might take a minute to propagate.
That’s It, you now have a new database to play with. Please check back for other tutorials and other database related articles.