Developer Tools

Configure and Connect to a Serverless MySQL or PostgreSQL Database

with Amazon Aurora Serverless v2

Introduction

In this tutorial, you will learn how to configure and connect to Amazon Aurora Serverless v2.

Amazon Aurora is a relational database service with PostgreSQL and MySQL compatible editions. It offers the performance and availability of enterprise databases at a fraction of the cost. Aurora Serverless v2 is an on-demand auto-scaling configuration for Aurora. With Aurora Serverless v2, your database will automatically scale up or down capacity based on your application’s needs – so you’re never paying for what you don’t use, while still benefiting from Aurora’s high availability, scale, and speed.

Traditionally, database administration requires a fair amount of guesswork: keeping ahead of unknowable and sometimes volatile compute and storage growth for new applications, managing variable workloads like development and test databases, and keeping lots of database capacity online - only for it to be used infrequently. With Aurora Serverless v2, you can eliminate the guesswork. Aurora Serverless v2 grows your database storage and compute capacity when your application needs it, and shrinks capacity when your app needs less. You pay on a per-second basis for the database capacity you use when the database is active, and migrate between provisioned and serverless configurations with a few clicks in the AWS Management Console.

This tutorial is not within the free tier. It will cost you less than $1 provided you follow the steps in the tutorial and delete your resources at the close of the tutorial.

What you will accomplish

In this tutorial, you will learn how to:

Create an Aurora Serverless v2 DB cluster
Create a client environment with AWS Cloud9
Enable client network access to the Aurora Serverless v2 DB cluster
Connect to the Aurora Serverless v2 DB cluster
Delete your resources

Implementation

AWS experience

Beginner

Time to complete

35 minutes

Cost to complete

Less than $1, if you follow the steps in the tutorial and delete your resources at the end of the tutorial.

Requires

An AWS account

Services used

Get help

Troubleshooting for Amazon Aurora

Last updated

July 02, 2024

Step 1: Create an Aurora Serverless v2 DB cluster
In this step, you will navigate to the Relational Database Service (RDS) console and create an Aurora Serverless v2 DB cluster.

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

2. In the left-hand navigation, select Databases, and choose Create database.
3. On the Create database page, make the following selections:

For Choose database creation method, select Standard create.
In the Engine options section, for Engine type, choose Aurora (PostgreSQL Compatible).

If you are more familiar with MySQL, it's OK to select Aurora (MySQL Compatible) here. The steps in this procedure are the same for both database engines until you run a client command to connect to the database.

In the Engine version section, for Available versions, select a database version that offers the Serverless v2 option.

Notes:

At the time of publication, the option is available for all the selectable choices with Aurora PostgreSQL version 13 and higher and Aurora MySQL version 3 (MySQL 8.0-compatible).

For this tutorial, version 15.4 is selected.
For Templates, select Dev/Test.
In the Settings section, for DB cluster identifier, enter MyClusterName.

In the Credentials management section, make the following selections:

Leave the Master username as the default.

Choose Self managed.

Set the Master password fields with values of your choice. Record the username and password for further use later.
In the Instance configuration section, choose Serverless v2.

For Minimum ACUs, enter 0.5.

For Maximum ACUs, enter 8.

Notes: Each Aurora Capacity Unit (ACU) provides the equivalent of approximately 2 gigabytes (GiB) of RAM and associated CPU and networking. The range of 0.5 to 8 ACUs means that each Serverless v2 DB instance in the cluster can scale down to 1 GiB of RAM when idle, and up to 16 GiB of RAM when busy. In a real-world situation, you pick minimum and maximum ACU values to allow the database to scale up to handle the busiest periods in your workload, while not exceeding the limit of your budget.

Advanced features such as Performance Insights use some of the ACUs of your cluster to do the work that is needed to collect and display the data. When you turn such features on, you'll need to pick a higher minimum ACU to ensure the feature works properly. For more information, see the Aurora User Guide.
In the Availability & durability section, keep the default value: Don't create an Aurora Replica.
In the Connectivity section, keep the default values for Connectivity and Network type.

In the Virtual private cloud (VPC) list, select Create new VPC.

In the Subnet group list, select Create new DB Subnet Group.

For the Public access radio buttons, keep the default value of No.

In VPC security group (firewall) list, select Create new VPC security group and enter MyClusterGroup for New VPC security group name.

You will modify this security group to allow network traffic from your database client to access your new Aurora Serverless cluster in a later step.

For the Availability Zone list, keep the default value of No preference.
In the Monitoring section, deselect the checkbox for Turn on Performance Insights.

As mentioned previously, using this feature requires your minimum ACU setting to be higher than 0.5.

Keep the remaining default selections, and select Create database.
4. The Databases screen shows the cluster being created, wait until the Status changes to Available. Then, choose myclustername, to show the information about your cluster.
5. On the myclustername page, in the Connectivity & security section, copy the Endpoint name for the Writer entry under the Type column.

This cluster endpoint lets you specify a consistent connection string even if you add or remove DB instances from your cluster, or promote a reader DB instance to be the writer.
6. On the myclustername page, choose the myclustername-instance-1. In the Connectivity & security tab, in the Networking section copy the VPC value, and in the Security section, copy the VPC security groups value.

ProTip: To make navigation easier, you can open new browser tabs for the VPC and the security group that you see on this page. That way you can retrieve those identifiers when you need them later.
Step 2: Create a Client Environment with AWS Cloud9
After creating the MyClusterName cluster, you need to create a database client inside the same VPC. In this step, you will create an AWS Cloud9 environment to use as your database client.

1. Sign in to the AWS Management Console and open the AWS Cloud9 console at https://console.aws.amazon.com/cloud9/.

2. On the AWS Cloud9 screen, select Create environment.
3. On the Create environment page, make the following changes:

In the Details section, for Name enter MyCloud9Env.

For Environment type, leave the default selection New EC2 instance.
In the New EC2 instance section, leave the default selections for Instance type, Platform and Timeout.
In the Network settings section, leave the default selection for Connection as AWS Systems Manager (SSM).

With this connection method, you will be able to use a web-based interface that simplifies logging into the Cloud9 system.

Expand the VPC settings section, using the dropdown, choose the VPC that you copied in the previous section.

4. Then, choose Create.
5. After your new Cloud9 environment has been created, you can select Open under Cloud9 IDE to experiment with the Cloud9 user interface in your browser.

Note: You can't connect to your Aurora cluster yet, you will do this in the next step.
Step 3: Enable Client Network Access to your Aurora Serverless v2 Cluster
In this step, you will enable network access from your Cloud9 environment to your Serverless v2 DB Cluster. To do this, you will add the security group assigned to MyCloud9Env as a traffic source for the security group assigned to the DB instance in your Aurora cluster.

Note: A security group is a virtual firewall that controls network traffic in your network.

1. In a new browser tab, sign in to the AWS Management Console and open the VPC console at https://console.aws.amazon.com/vpc/.

2. On the VPC dashboard page, in the left hand navigation pane under Security, choose Security groups.

3. Find the security group that begins with aws-cloud9-MyCloud9Env, and select the Security group ID to open the details page.

4. On the Security Groups Details page, copy the Security group ID.

5. In the Inbound rules tab, select Edit inbound rules.

6. On the Edit inbound rules page, select Add rule.
7. In the Inbound rules section, make the following changes:

For Type, choose PostgreSQL from the drop down list.

If you chose MySQL as your engine in the previous step, choose MySQL/Aurora here.

For Source, choose Custom and paste the Security group ID in the entry field that you copied in the previous step.

Then, choose Save rules.
Step 4: Connect to your Aurora Serverless v2 DB Cluster
In this step, you will access your Aurora Serverless DB cluster from your AWS Cloud9 environment.

1. Navigate to the AWS Cloud9 console, and open the Cloud9 IDE browser.
2. In the bash terminal tab in the MyCloud9Env, type the following command for the engine option you selected when you created your cluster.

Notes:

You will need to substitute your master username and database endpoint for the values in the command. Since we suggested leaving the master username as the default, the value to use will be postgres.

You can find this information in your RDS (myclustername) databases dashboard.

If you selected Aurora (PostgreSQL compatible) use the following command:

sudo dnf update sudo dnf install postgresql15 -y psql -U [your Master username] -h [your Aurora cluster endpoint] postgres

If you selected Aurora (MySQL compatible) use the following command:

mysql --user=[your master username] --password -h [your database endpoint]

3. When prompted, enter your master password, and press Enter.

You should now be connected to the MyClusterName Aurora Serverless DB cluster!
4. You can issue SQL statements and client commands to the Aurora Serverless DB cluster using the connection you have established.

For example, if you set up a PostgreSQL-compatible Aurora cluster, you can show the databases on the server by pasting the following command into the psql session running in the MyCloud9Env bash tab:

\l

Note: If you set up a MySQL-compatible Aurora cluster, the equivalent mysql command to list databases is:

show databases;

5. You can display the current date and time in the UTC time zone, and the version of the database engine in your Aurora cluster, by pasting the following SQL statements.

Note: The same statements work for both MySQL-compatible and PostgreSQL-compatible Aurora.

select now(); select version();
Note: If you run SQL statements that require more memory, CPU, or network capacity than your cluster currently has, the writer DB instance scales up so that it has enough resources to handle the workload. This scaling operation doesn't disrupt your connection, any transactions that are in progress, or any SQL statements that are running.
Step 5: Clean up Resources
In this step, you will delete your Aurora DB cluster and AWS Cloud9 environment.

Important: Cleaning up resources that are not being used reduces costs and is a best practice.
1. From the Amazon RDS console page, in the left hand navigation, select Databases.

2. From the Clusters list, select myclustername-instance-1.

Note: If you didn’t already make a note of the VPC ID and security group ID, choose the instance name to bring up the details page for that instance, and record the VPC and security group details.

3. From the Actions menu, select Delete.

4. When prompted, confirm deletion by typing delete me into the appropriate field.

If you set up a multi-AZ cluster or added any reader instances so that the cluster contains more than one DB instance, repeat this step for each DB instance in the cluster.
5. Once all DB instances in the cluster are in Deleting state, select the myclustername cluster, and from the Actions menu select Delete.

You don't need to wait until the DB instances are actually gone. The cluster item on the console page represents the storage for your database tables and other objects. The data in the cluster remains intact until you delete the cluster, regardless of how many instances are in the cluster.
6. On the confirmation page, uncheck the box for Create final snapshot.

7. Select the acknowledgment checkbox to confirm it's OK to delete the data in the Aurora cluster.

If you created any information during your testing that you want to preserve, you can leave Create final snapshot selected. This will allow you to retrieve the data later in a new Aurora cluster.

8. Then, enter delete me to confirm deletion.

Note: Don’t proceed to the next step until you see confirmation in the AWS console that the Aurora cluster is gone.
Once the Aurora cluster is deleted, you need to locate the security group that was associated with the Aurora cluster.

9. Navigate to the VPC dashboard and choose the Security Groups page.

10. Select the MyClusterGroup security group. Then, from the Actions menu, choose Delete security groups.

11. Navigate to the AWS Cloud9 console, from the Your environments page, select MyCloud9Env and select Delete.

12. When prompted, confirm the MyCloud9Env deletion.
13. Navigate back to the VPC console, in the left hand navigation, choose Your VPCs.

14. Choose the VPC you created for this tutorial. The, choose Actions, and select Delete VPC.

If you haven't finished deleting the Cloud9 environment or the Aurora cluster yet, you must wait until those resources are gone before proceeding.

15. Confirm the VPC deletion by typing delete in the text field.

Note: Deleting the VPC also deletes other network resources such as the Cloud9 and Aurora security groups that would otherwise be left behind.