Serverless & RDBS (Part 2) — Set up EC2 instance to securely connect to your Aurora DB
This story is part of a series to get started with Relational Databases in Serverless:
In the last post we saw how to create the right resources so connect Lambda functions to Aurora. We created triggers that took a complete query in argument such as
DROP TABLE user; 😱 But going forward, that’s not something I’d recommend…
This post is not much about Serverless and Lambda, but more on how you can securely connect to the database you just created to execute specific queries like creating, altering, dropping tables…
Let’s get started
1. Create an EC2 instance in the same region as your Aurora database
Keep the default configuration: leave it in the default VPC (configured by AWS for easy access via SSH) — see AWS doc on how to connect to your instance
2. Create a VPC Peering Connection
We fall in the scenario “DB Instance In a VPC / Accessed By An EC2 Instance in a Different VPC”
Get the VPC ID of your EC2 Instance and your Aurora Instance
1. Open the Amazon VPC console at https://console.aws.amazon.com/vpc/.
2. In the navigation pane, choose Peering Connections, Create Peering Connection.
3. Configure the following information, and choose Create Peering Connection when you are done:
- Peering connection name tag: You can optionally name your VPC peering connection. Doing so creates a tag with a key of Name and a value that you specify.
- VPC (Requester): Select the VPC in your account with which you want to create the VPC peering connection.
- Under Select another VPC to peer with: Ensure My account is selected, and select another of your VPCs.
4. In the confirmation dialog box, choose OK.
5. Select the VPC peering connection that you’ve created, and choose Actions, Accept Request.
6. In the confirmation dialog, choose Yes, Accept. A second confirmation dialog displays; choose Modify my route tables now to go directly to the route tables page, or choose Close to do this later.
3. Update Routes Tables for both VPC
Now that the VPC Peering Connection is created, we need both VPC to be able to find each other.
Let’s start with the default VPC (where the EC2 Instance lives)
- Note the CIDR of your Aurora VPC
- Create the route table for the EC2 VPC if not already created
- Add the route to Aurora’s VPC using its CIDR and VPC Peering Connection (it will autocomplete)
Do exactly the same for Aurora’s VPC Route table (add EC2 VPC’s CIDR)
4. Update Aurora Security Group to allow inbound and outbound traffic from EC2 Security Group
- Check Aurora’s Security Group
- Click on the security group
- Allow Inbound and Outbound traffic from EC2’s security group (you can check on the instance which one it uses)
- Connect to your EC2 instance
- Connect to your database
mysql -h <DATABASE)ENDPOINT> -P <DATABASE_PORT> -u <USERNAME> -pand type your password
- And you should be all set 🎊 💥 ⭐️!
- We created an Aurora instance via
serverless.ymland Lambda functions which can query the database.
- We created an EC2 instance to securely connect to the database via our local machine. That way we can create our tables and schema (and any other query we want).
Though, it a bit painful to always have to manually connect to the DB, create or alter tables when needed. 😱
In software engineering, schema migration (also database migration, database change management) refers to the management of incremental, reversible changes to relational database schemas. A schema migration is performed on a database whenever it is necessary to update or revert that database’s schema to some newer or older version. […] Schema migration allows for fixing mistakes and adapting the data as requirements change. They are an essential part of software evolution, especially in agile environments. (#wikipedia)
That’s what we are going to see next 🙃!
Checkout the other stories of the series: