RDS Connect EC2 To RDS MySQL

Hey there!

In this guide we will set up a MySQL database on AWS Relational Database Service (RDS) and connect it to a new EC2 instance. If you already have an EC2 instance set up or both EC2 and RDS set up, but are unable to link the two, you can still follow along and you’ll surely get some useful knowledge along the way!

Without further ado, let’s get cracking! 🚀

Head over to Services > EC2 > Instances. Click on Launch Instance to create a new EC2 instance.

I’m utilizing the free tier to its fullest, so I’m going to check Free Tier on the sidebar and select Amazon Linux AMI. You may choose whichever you like.

NOTE: Moving ahead in the article we’ll set up Node.js to test our DB connection. If you choose a different flavor of Linux that step will likely be a little different that what I do in this guide. But it should be an easy google search.

Again, out of the several options provided, I choose to go for the free tier. You may choose what’s appropriate for your purpose. For the sake of learning/practicing, the free tier server is more that enough. I’m not configuring this one, if you’d like to know more about EC2 configurations, check out our EC2 Setup guide.

Once everything is configured, go ahead and click Launch.

If you have a key pair, you can use the same or create a new key pair. Make sure you download the key pair and do not lose it. Once chosen your option, click Launch Instances.

Next up, we need to create a database instance with RDS. You can do so by going to Services > RDS (Under Database) > Databases. Now Click on Create database.

My weapon of choice is MySQL, however you can pick whichever you like. Note that Aurora and Oracle do not have free tiers, so be careful if you don’t want to be charged. You can choose a specific version but I’m going to stick to the default.

Here make sure you choose Free Tier if that’s what you’re going for. Add an DB instance identifier that’ll be used by AWS to identify your RDS Instance. Add your credentials as well. These are the database credentials which you’ll use to connect to your database.

Under Additional Options, you may enter a database name that AWS will use to create a default database for you.You can disable backups if you like, however backups upto 20GB are free in the free tier, at least as of writing this article, it doesn’t really matter as such. I’ll leave the rest as the defaults.

Now let’s take a peek at our server. It should be ready by now. One thing to pay attention to is that it doesn’t have an IAM role, which will be needed to give EC2 permissions to access to the  AWS RDS service. Let’s create an attached one. Make sure you’re in the same VPC as the EC2 server and click Create database.

Select your server, click on Actions > Instance Settings > Attach/Replace IAM Role.

If you have already created an IAM role for EC2 that provides RDS access, you may choose that. For the purposes of this article, I’ll create a new one and attach the same. Click Create new IAM role.

A new tab will open up showing the following screen. Let’s click on Create role.

Select AWS service and EC2, and then click Next: Permissions.

Look for RDS. There are several roles that you can choose. I’m not going into the details of the different options and simply choose RDSFullAccess and clicking Next: Tags.

Give the role a name! 👶

Once created, go back to the previous tab and click the small refresh icon next to the dropdown. This should refresh the list of IAM roles. Select the new role that you just made.

Now go back to EC2, select your instance, and click Connect. Copy the SSH string and paste it in your terminal. Make sure you’re in the same folder as your ssh key (.pem file).

You’ll be prompted to trust this host (the new EC2 server), type yes.

Enter the following:

aws rds describe-db-instances –region your-region

You can find your region code here.

If you can see at least one DB instance, your IAM role is working fine. If not, check if the database instance is up and if there is an error check if your IAM role is configured correctly.

Next, let’s install Node.js. (You may skip this step but you need to update your security group as explained below).

Steps to install Node.js can be found in the AWS Documentation as linked here.

Create a new folder and switch to it.

mkdir testApp

cd testApp

npm init

Create an index.js file and open it in your favorite editor. I’m using nano here.

Paste the sample code from the mysql npm package page:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});
 
connection.connect();
 
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});
 
connection.end();

Before we run our code we need to install mysql driver from npm. (Don’t tell anyone I had forgotten to do so 🤫)

Go to your database instance and find the DNS name. Copy it and we need to update the index.js file with the correct URL and credentials.

Once that is done, save and exit the editor.

Pro Tip: The server and database are terminated so you cannot “hack” into them.

If you try to run the code now, it will return an error. This is because even though both our EC2 server and the database instance are in the same VPC, they have different security groups. Services in different security groups cannot reach each other. Think of it as a firewall.

Open your database instance in the AWS console and find the VPC Security Groups section under security (at the right).

Open the Security Group attached and then click on Edit Inbound Rules.

Switch back to your EC2 tab to check the name of its Security Group. In my case it’s launch-wizard-4.

Add a new rule, Select MySQL/Aurora from the dropdown and in Source, find the Security Group you saw in the EC2 server config. Type sg- and you will see all your security groups listed. Select the correct one and click Save Rules.

That’s it! Run you node app to see your app connect to the database ⚡️

Thanks for going through this article. Lemme know your thoughts/questions below. See ya later Alligator! 👋