Migrating your Database to the AWS Cloud

Getting Started with the Relational Database Service in AWS

In this post I'll show how you can get started with RDS in AWS. The good news is once you have the database started in the cloud it runs seamlessly as if it was in running on bare metal. RDS is an AWS fully managed service and hence they have the responsibility for managing updates and this can give you the freedom to focus on your application development and database management. After getting RDS up and running I'll show how you can connect to it from and Ec2 instance and make a basic PHP web application that will read data out of RDS.

RDS Netwokring and Security

Part of managing a database is security. This is why it's important to be well versed with the VPC service and have a general understanding of public and private subnets. For our purposes here we will make a dedicated VPC with two public subnets in different availability zones. While you are making subnets be sure their NACL's allow traffic on port 3306. There is a lot to say about security where this is concerned but I'm basing most of my decisions on the ability to produce a working example with low friction.

Setting up RDS

To get setup head over the the RDS section in the AWS console. Before you actually walk through the create instance option it's important to set up the subnet group. Create a subnet group and make sure the VPC is set to the one we created earlier with the multi AZ subnets. You can now create your RDS instance. This is a straightforward process but there are a few important notes to make it smoother. Make your instance "Free Tier" by checking the box.

, this limits the amount of set up you need. Also be sure to make the database publicly accessible so we can connect to it. Be sure to take note of your username and password because you will need it to connect. Notice the RDS service will make a security group with port 3306 open. This is awesome but it defaults the source of that traffic to your ip. You'll need to alter this if you want to connect from an Ec2 instance. The following pic shows how I have my security group set.

When database is ready the status will be available and from within the instance details you'll see the connection string.

Prepare for Database Connection

I'm going to connect to the database from an Ec2 instance using mysql from the command line. You can use other clients but this is simple for demonstration purposes. Once you are shelled into box run the following commands to set up a simple web server and install the basic mysql client.

sudo apt-get install apache2  
sudo apt-get install php7.0  
sudo apt-get install php7.0-mysql  
sudo apt-get install libapache2-mod-php7.0  

Alternativly, add the above commands to the user-data for your instance.

Connect Via the Command Line

From inside your Ec2 instance connect to rds via mysql using the command

mysql -h mine.c1gk77fnmjzn.us-east-1.rds.amazonaws.com -P 3306 -u test -p

Notice in this context my user is named "test" and after entering this command it will ask for the password.

Now that you have the "mysql" prompt you can easily make a table and insert data. Notice the first command USE name;. This is showing I named the database "name" when setting it up in rds earlier.

After our preliminary data load we can view the data in the table we made.

Finally, we load up the table a bit more for completeness

Displaying the data

We have already installed a simple Apache Server so we can navigate over to /var/www/html and create a simple php file with the following contents.

    // Check connection
    if (mysqli_connect_errno())
      echo "Failed to connect to MySQL: " . mysqli_connect_error();

    $result = mysqli_query($con,"SELECT * FROM Employee");

    while($row = mysqli_fetch_array($result))
      echo $row['id'] . " " . $row['name']; //these are the fields that you have stored in your database table employee
      echo "<br />";


If you are not familiar with PHP the above snippet of code will show the data in the table we just made. The key facets are the connection string which includes the user name and password. Also notice the "SELECT" query which is giving back the data. We can now start our Apache server with the command sudo systemctl start apache2.service. If you point your browser to http://public-ip/file.php we do indeed see the contents of the Employee table.

Wrapping Up

The database portion of any application or business is arguably one of the most important pieces of data that you deal with. In this post we just got a taste of the capability of this service. However, RDS also automates backsups, makes multiple availability zone deployments and allows read replicas to be made to offload stress.