How To Set Up and Use a Database in the Cloud
Tell Majstorich
Putting your data in the cloud is super common nowadays, but what is it? “In the cloud” essentially means that your data is not saved on your personal device, but you have access to your data as long as you have internet service. The benefits of this setup are:
Your data is backed up multiple times, so the risk of losing your data is so much lower (close to impossible, in my opinion). You are no longer dependent on your computer.
You have so much more power and storage capacity in the cloud, so you don’t need to shell out a pretty penny for a super powerful personal computer; you just have to pay for it on the cloud on an as needed basis, which is very affordable.
You can access your data anywhere and anytime you have internet access.
How-To Set Up AWS
AWS (Amazon Web Services) is Amazon’s digital infrastructure service. Basically, they have tons of computers so that you can use them over the internet for so many purposes: websites, databases, virtual computers, storage, and more.
I’m going to go over a relatively simple way to set up a database in the cloud, using Amazon’s Lightsail and its managed database feature. But they are actually several ways to accomplish this goal, like using Amazon RDS or setting up a virtual computer using Lightsail and then installing and managing the database yourself. Cost and experience are limiting factors for all options. Using Lightsail’s managed databases, the cost is $15 per month but the experience needed is small. I like Lightsail, because you have several other services you can use in addition to a managed database. So, let’s get started.
Setting up a managed database
2. Once setup, you search for the Lightsail service in the top search bar and navigate to the Lightsail home page (while signed in).
3. On the Lightsail homepage, there are a variety of services listed that you can choose. Click on “Databases.” When that loads, click the “Create Database” button.
4. Choose your options for your database. I chose a location near-ish my location, MySQL database (since I figured it had a lot answers of StackOverflow), Standard availability, and the lowest tier service.
5. When you click the “Create Database” button at the bottom, it can take a few minutes for the database to be set up and it will be grayed out until the process is done. Think it took about 15 minutes for me.
6. Once set up, click on the database and you’ll get the information on how to connect to your database. By default, the database has “Public Mode” disabled, but you’ll have to have to enable it to access the database from your personal computer.
Accessing your database
I tried two database access programs: Sequel Ace and MySQLWorkbench. I ended up liking Sequel Ace more, because I’m on a Mac and the interface was just a bit easier to use for me. I’m going to do the walk through for Sequel Ace first, but the process is remarkably similar.
Connecting to database using Sequel Ace
1. Download and install Sequel Ace. I did this from the Mac App Store, which is super simple
2. Once you’re in the program, the connection entry area just shows up.
3. “TCP/IP” should be selected
4. Set “Name” to whatever you want to call this connection.
5. Enter the Host from Lightsail (also called endpoint).
6. Change the port to 3306 or whatever the port is in Lightsail.
7. Enter the user as whatever it is set to in Lightsail. Mine was “dbmasteruser”
8. Enter the password from Lightsail.
9. Now click “Test Connection.” If you have an error, you either still have your Lightsail database “Public Mode” disabled, or you have something incorrectly entered.
10. Click “Connect” and you’re in!
Connecting to database using MySQLWorkbench
1. Download and install MySQLWorkbench from the MySQL website (https://www.mysql.com/products/workbench/).
2. Once you’re in the program, click the little plus sign next to “MySQL Connections” to create a new connection.
3. Set “Connection Name” to whatever you want to call this connection.
4. Choose “Standard (TCP/IP)” for the connection method.
5. Enter the Hostname from Lightsail (also called endpoint).
6. Change the port to 3306 or whatever the port is in Lightsail.
7. Enter the user as whatever it is set to in Lightsail. Mine was “dbmasteruser”
8. Enter the password from Lightsail.
9. Now click “Test Connection.” If you have an error, you either still have your Lightsail database “Public Mode” disabled, or you have something incorrectly entered.
10. Click “Connect” and you’re in!
Using your new database
Using your new database is a whole other process, but I’ll try to simply explain some of it here.
Once connected to your database, you will want to create a database and name it something. I like to keep different projects in different databases (but still using the same connection), for organization sake. I named mine “playground,” because I’m deleting this entire database later.
To create a table in Sequel Ace, there’s a really easy import CSV under File > Import. For MySQLWorkbench, I had to write my insert statements to put the data in.
Once you have some data uploaded, you’ll want to write queries to join tables, aggregate data in tables, calculate values, match values, and union tables.
To learn how to write SQL queries, check out the W3 Schools, which is a great, free resource (https://www.w3schools.com/sql/).