Aurora Database with EC2

Create an Amazon Aurora DB and connect it with Amazon EC2!

Introduction

⚡️ 30 second Summary

It's time for another absolutely classic AWS service: Amazon Aurora.

Aurora is a type of relational database (like a table with rows and columns) in AWS.

Most of the time we use databases to hold information we want to store, update, and use.

In this project, we're creating an Amazon Aurora database to store and display data for our very own web application!

We won't create the web app itself just yet - that's for the next project in this series. But, this project is going to teach you how to connect your Aurora database to the web server (EC2 instance) hosting that web app! Yup, get ready to connect a database service to EC2 😎

Want a complete demo of how to do this project, from start to finish? Check out our 🎬 walkthrough with Maya 🎬

If you're up for a bit of a challenge, quiz yourself on the key concepts up ahead in this project.

Before we start Step #1...

Before we get started, it's important that you know what we're trying to do today.

Login with your IAM user

For this project you'll need your IAM user, not your root user.

In this step, get ready to:

Create/log in as an IAM user

What is an IAM user? Why are we setting one up?

In AWS, a user is a person or a computer that can do things on the AWS cloud.

When you create an AWS account for the first time, the login you get is called the root user of the AWS account. AWS actually recommends to not use your root user for everyday tasks to protect it from security breaches.

You should create IAM users instead. If a root user is a master key to your AWS account, think of IAM users as key copies. IAM users have separate usernames and passwords to your root user, and you can set them to have limited access to your account's resources.

Do you have an IAM user?

Yes, and I have an IAM user

Awesome!

No, I need to create one

No worries - here are the steps to create one (this takes less than 10 mins).

Head to your AWS Account as the root user.
Open the AWS IAM console.
From the left hand navigation panel, choose Users.
Choose Create user.
For the User name, name it:

[[YOURNAME="enter your name"]]-IAM-Admin

Make sure to select the checkbox next to Provide user access to the AWS Management Console - optional.‍

Note

This does not apply to all accounts, but if you're prompted with a pop up panel that says Are you providing access to a person?, choose I want to create an IAM user.‍

For the console password, choose Custom password.
Type in a password that you will be able to remember/access in the future.

Top tip

You will use this password for all future projects, so make sure to choose a secure one!

Deselect the checkbox for Users must create a new password at next sign-in - Recommended.

Choose Next.
In the permissions set up page, choose Attach policies directly.
From the list of Permissions policies, select AdministratorAccess.
Choose Next.
Choose Create user.
Voilà - you've just created your new user! Stay on this page.
Choose Download .csv file.

Copy the Console sign-in URL.

Now you're ready to start using your IAM user. 🏁
Log out of your root user's AWS Account.
Paste and go to your copied console sign-in URL.
Open your downloaded .csv file containing your user's access instructions.
Log in using your IAM user's username and password in the .csv file.

Note

PLEASE make sure you log in to your IAM Admin User instead of the root user - it's truly best practice for account security.

Create an Aurora MySQL Database

First things first... we need a relational database. Then we can connect it to our web app server and get cracking.

In this step, get ready to:

Create an Aurora relational database from scratch

What IS a relational database?

A relational database is a type of database that organizes data into tables, which are collections of rows and columns. Kind of like a spreadhsheet! We call it "relational" because the rows relate to the columns and vice-versa.

💡 How is it different from a normal database?

When a database is relational we can query it using a special language called SQL (Structured Query Language)

Strangely enough, there isn't really such a thing as a "normal" database. We have relational databases and non-relational databases (also known as NoSQL)... but that's for another project 🤐 No spoilers!

First - check your Region

Take note of your Region in the top right of your AWS Console.
It doesn't matter exactly which Region you have, as long as you're aware of it.

Why does our Region matter?

We're working with a couple of different services today so we need to make sure we create them all in the same Region. Otherwise things are going to get very complicated on the networking side!

Let's create your Aurora database

Head to your Aurora and RDS console - search for Aurora in search bar at the top of the screen.

In the left navigation bar, select Databases.
In the Database section, select Create database.
On the Create database page, choose Standard create.
In the Configuration section, make the following changes:
- For Engine type, choose Aurora (MySQL Compatible).

What is engine type?

The engine type is like the core software that powers our database. Imagine it as the operating system of a computer, but for databases.

💡 Aha! So this is Aurora. How is it different to other databases?

AWS Aurora is a type of relational database. As you can tell from the first few steps of creating a relational database, there are plenty of options to choose from!

We'd use AWS Aurora if we needed something large-scale, with peak performance and uptime. This is because Aurora databases use clusters (more on that later!). Ordinary relational databases, like MySQL and Oracle are more generic and cost-effective. They suit smaller databases and less demanding workloads.

In summary, Aurora is for the big jobs.

Fun fact: Coca-Cola uses Amazon Aurora to store their consumer data globally!

For Engine Version, choose Aurora MySQL 3.05.2 (compatible with MySQL 8.0.32)

What is Engine Version?

The engine version is like choosing the specific version of software that your database will use. For example, Aurora MySQL 3.08.2 (compatible with MySQL 8.0.39) - default for major version 8.0 is a specific version of Aurora that's compatible with a particular MySQL version (8.0.32).

For Templates, choose Dev/Test

What are Templates?

Templates are pre-set settings that help you quickly set up your database environment according to your needs. It's basically AWS helping you make selections for the rest of this set-up page! The Dev/Test template is designed for development and testing environments, helping you pick lower cost options.

In the Settings section, set these values:
- DB cluster identifier: nextwork-db-cluster
- Master username: admin
- For Credentials management select Self managed.
- Master password: Type a password (eg. n3xtw0rk)
- Confirm password: Retype the password.
- Make sure you save your database login details somewhere safe! You'll need them later on.

Leave the Cluster storage configuration settings as default.
In the Instance configuration section, set these values:
- Burstable classes (includes t classes)
- db.t3.medium
- 🚨 Alert: Students have reported getting charged when they spend more than one hour with an active db.t3.medium instance.

What is Instance configuration?

Instance configuration is where we can select the type of virtual computer that our Aurora database will run on. Just like opening up a Word Document runs on your own computer, we need a computer for our Aurora database to run on too.

The instance type that we chose determines how powerful, and how much memory, our virtual computer has. The more powerful it is, the better it will perform - but also the more expensive the price!

The Burstable classes (includes t classess) are cost-effective types of database instances that are best when you have a consistent baseline level of traffic with occassional, random spikes in demand...a sudden "burst" of traffic. By choosing burstable classes, our database can perform well under high traffic, but also save us costs when things are quiet.

Extra for Experts: The other two DB instance classes are Serverless V2, which let you only pay for what you use (scales automatically from zero traffic), and memory optimized classes, which are designed for high-performance databases that need to do heavy, memory-intensive tasks.

Serverless V2: Imagine you're building a new app that doesn’t have steady traffic yet. With Serverless V2, your database scales automatically based on usage, making it perfect for unpredictable workloads or when you expect low traffic but want to handle sudden spikes without paying for constant capacity.
Memory optimized classes: If you're running a large e-commerce site with lots of products, you need fast search and processing power. Memory optimized classes are ideal here, they can retrieve data quickly and handling complex queries (e.g. filtering) efficiently.

A shoutout to Sunmeet, Abdulrahman and Udit in the community for inspiring us to write this explanation!

In the Availability and durability section, use the default values. We'll jump into what this means later!
In the Connectivity section, the first thing it asks us is whether we need to connect to an EC2 instance...

Before we go any further, have a think for yourself. Do you think we need to connect our Aurora database to an EC2 instance? What is it we're trying to achieve here?

We're trying to connect a web app server to our Aurora database. Yup! That's our cue to set up an EC2 instance to be that web app server.

Why do we need an EC2 instance to be a web app server?

Any web app needs to run on a computer. But are we going to go buy a physical computer to do this?

No! We're going to use a virtual computer that we rent through AWS. This is exactly what EC2 is here for. Think of an EC2 instance as a rented virtual computer that you use to run your web app. It's the place where all the processing, data handling, and user interactions happen.

Bottom line: we definitely need an EC2 instance.

For Compute resource, choose Connect to an EC2 compute resource.
Now select the drop down for EC2 instance, and choose.... hold on a minute! We haven't even created an EC2 instance. Let's do that first, then come back to this.

Launch an EC2 instance

In this step, we're going to create an Amazon EC2 instance using our default VPC and default subnets.

In this step, get ready to:

Create an EC2 instance to host a web app (we'll create the web app itself in the next project)

...and this is what it's going to look like:

Open a new tab in your web browser and go to your AWS console (this means we can keep our Aurora database set-up in progress!)
In the upper-right corner of the AWS Management Console, make sure your AWS Region is the same as that in your Aurora database creation.
In the AWS console search bar, search for EC2.

Select Instances in the left hand menu and choose Launch instances
Choose the following settings in the Launch an instance page.
- Under Name and tags, for Name, enter nextwork-ec2-instance-web-server
- Under Application and OS Images (Amazon Machine Image), choose Amazon Linux.
- Choose the Amazon Linux 2023 AMI.
- Keep the defaults for the other choices.

Under Instance type, choose t2.micro.
Under Key pair (login), choose a Create new key pair

What is a key pair? Why are we creating a new key pair?

A key pair in EC2 is quite literally the keys to access our EC2 instance.

We need keys to our EC2 instance if we want to add, change, or update how our EC2 instance is running.

Since a EC2 instance is a virtual machine, a good way to think about key pairs is like the login credentials to your own computer...except it's all virtual!

For your Key pair name, enter NextWorkAuroraApp.
Leave your Key pair type as RSA.
Leave your Private key file format as .pem since we're using SSH later on to access our EC2 instance.

Select Create key pair.

A new file will automatically download to your computer - don't be alarmed! This is your new key pair. We'll need it for later in the project to access our EC2 instance. Notice that it's a .pem file - cool!

Back in our EC2 creation, under Network settings, set these values and keep the other values as their defaults:
- For Allow SSH traffic from, choose your IP address if it's correct (you can check your IP by clicking here). Otherwise select Anywhere.
Check the boxes for Allow HTTP traffic from the internet.

Leave the default values for the remaining sections.
Review the Summary panel of your instance configuration (on the right of the window).

What does this Summary panel tell us?

💻 Number of instances - the number of machines you're running.

🎨 Software Image (AMI) - the operating system of your virtual machine.

⚙️ Virtual server type - the CPU, memory, and network performance package you selected.

👮 Firewall - firewalls control the inbound and outbound traffic for your instances. In EC2, we use security groups for this.

🪣 Storage - the storage options for your instance.

When you're ready, choose Launch instance.
Navigate back to your list of EC2 instances, and then select the checkbox next to your new instance.
In the Details tab, note the following important details:
- In Instance summary, note the Public IPv4 DNS.
- Note the value for Key pair name.

Note

Think about the Public IPv4 DNS as the location of a house, and then the Key pair name like the keys that let you in. It's no good just having the location without any keys, and you can't have keys without knowing where to go! Both are critical for accessing our EC2 instance.

Wait until Instance state for your instance is Running before continuing.

Create your Aurora Database

Nice work! Now that we've got our EC2 instance ready to go, we can go back to creating our Aurora database. This time, we'll connect it to our new EC2 instance.

In this step, get ready to:

Create your Aurora database
Connect the database to the EC2 instance

Navigate back to your open tab that you were creating our Aurora database in.
We were up to the Connectivity section. But this time we have one big advantage...we have an EC2 instance! Woohoo!
- For Compute resource, choose Connect to an EC2 compute resource.
- Now select the drop down for EC2 instance, and choose nextwork-ec2-instance-web-server.
- NOTE: You may need to select the refresh button to the right of the EC2 instance drop-down.

Scroll down and open the Additional configuration section.
Enter sample for Initial database name.

Keep the default settings for the other options.
Select Create database.

Did you notice some scary looking monthly costs?

Don't worry! As long as we delete our database as soon as we've finished with this project, we'll be absolutely fine. If you run into any issues you can always ask in the NextWork community

Close any pop-ups that appear.
Your new DB cluster will show in the Databases list with the status Creating.

Do you notice that your database has the name nextwork-db-cluster and that there's three of them? What's with that?

Note

It may take five or more minutes for the database to be created. Time for a tea and bit of an explanation ☕

What's this 'database cluster' business?

Remember how we said that Aurora is really good for the big jobs? The reason for this is clusters.

A database cluster in Aurora is a group of database copies that work together so your data is always available.

Each cluster consists of a primary instance (where all write operations occur) and multiple read replicas as back-ups. If your database's primary instance fails, one of the replicas can be promoted to primary automatically.

Wait for the Status of your new DB cluster to show as Available.
Select the DB identifier of your top database to take a look at the details.
Notice that there are two Endpoints in our Database. Cool! This is our cluster in action.

Understanding Reader vs Writer instances

Our writer database instance is our primary instance that handles all the "write" operations like INSERT, UPDATE, and DELETE.

Our reader database instance is our backup instance that can do very basic operations like SELECT. This is used to get data, but not to add or change data.

Why separate read and write?

We only want one writer instance at a time so that things stay focused and controlled...but we want multiple reader instances so that we can share the workload for read requests as our database grows and have a backup if our writer instance fails.

What is an endpoint?

In general, endpoints are like contact points where data flows in and out. A super popular example is a website URL! When you enter a website's address (like nextwork.org), your browser uses that endpoint to receive data and load the page. For databases, an endpoint is how your app finds a database to ask for data or update it.

Yay! We've created a new relational database and have an EC2 instance waiting for our beautiful new web app. Can't wait to build that web app and connect it to our database in the next project!

Delete Your Resources

👀 Do you have time to do the next project in the series today?

Yep, let's go!

Note

You don't need to delete your resources if you're doing the next project in this series today.

Get your documentation and head straight to the next project!

Nope, not today.

Make sure you delete all your resources to avoid getting charged. This is a super important task for every single project you set up. Don't keep your resources in your account if you'll wait to come back to this series on another day.

Important

Deleting resources that are not actively being used stops you getting charged and is a best practice. Not deleting your resources will result in charges to your account.

Do you think you can delete the resources you've created today?

Yep, of course.

The database cluster
The EC2 instance
The key-pair

I know, but also, I don't...

If you're feeling stuck (we've all been there!), here's a little guide:

The database cluster

Open the Amazon RDS console, select Databases.
Choose nextwork-db-cluster.
From the Actions drop-down menu, choose Delete.
- 🚨 When deleting your database cluster, make sure to uncheck Create final snapshot and uncheck Retain automated backups so you don't get charged.

The EC2 instance

Open the Amazon EC2 console, select Instances.
Choose the instance you created.
Click Actions at the top, then Terminate instance.

The key-pair

Open the Amazon EC2 console, select Key Pairs.
Choose the key pair you created.
Click Actions at the top, then Delete Key Pair.

Nice Work!

Woohooooo!! You are an absolute legend and should be very proud of yourself. That was not easy work but you gave it a go, did your best, and absolutely crushed it.

Today you've learnt how to:

🧱 Create an Aurora MySQL Database: You created an Aurora MySQL database instance from scratch in AWS, configured it with the appropriate settings, and connected it to an EC2 instance to prepare for hosting a web application.
💻 Launch and Configure an EC2 Instance: You successfully launched an EC2 instance using the Amazon Linux 2023 AMI, configured the instance to connect to your Aurora database, and set up the necessary security groups to allow traffic.

Ready to quiz yourself? You got this! 💪

It's wild that all these learnings are packed in one project. Great work and we'll see you in the next one, Connect A Web App with Aurora!

Get ready to:

🌐 Build and Connect a Web Application: Build a basic web application on your EC2 instance, connect it to your Aurora MySQL database, and enter data into your database through your web app!
🔗 Test and Verify Database Interactions: Run SQL queries and view data entered through the web app to verify you've set up a successful conneciton between your database and web app!

p.s. Does it say "Still tasks to complete!" at the bottom of the screen?

This means you still have screenshots left to upload, or questions left to answer!

Press Ctrl+F (Windows) or Command+F (Mac) on your keyboard.
Search for the text Return to later.
Jump straight to your incomplete tasks!
🙋‍♀️ Still stuck? Ask the community!