AWS RDS Tutorial
Published: 2024-05-22 07:28:49

Please have a look at this first: https://dev.to/hungrushb/amazon-rds-create-database-deep-dive-2m8j



# I. Preparation

### Create VPC


1 . Create a simple VPC with name **labRDS**. Keep everything as default and create.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aeu3pjj1k3z8r6rjxoev.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aeu3pjj1k3z8r6rjxoev.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/569a91uc7rgopvim5aul.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bbzc6wdkwhyot6o3w8zo.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qtmsu605dzhexo5qofl6.png)
2 . Update **Enable auto-assign public IPv4 address** for 2 public subnets.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/shzuzvpq04d1qlm4wro1.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fyay35lp5vwcwca6m1nc.png)
Repeat for public subnet 2.

### Create EC2 security group
3 . Head to **EC2 console**, choose **Security Groups**, and **Create security group**.

Choose your VPC.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zo2lt2ouf35fo0nxj99o.png)


Add inbound rules:
- HTTP (80): Select HTTP from the list or enter port 80.
- HTTPS (443): Select HTTPS from the list or enter port 443.
- Custom TCP Rule (5000): Select Custom TCP Rule and enter port 5000.
- SSH (22): Select SSH from the list or enter port 22.

All source is **Anywhere IPv4**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ufzcidv4trsx3d0d5axi.png)

Scroll down and create.


### Create a Security Group for a DB Instance

4 . Create another SG with name **labRDS-DB-SG**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lj7rentc31ickgj0ncsy.png)

- Choose MYSQL/Aurora and port 3306.
- For source, choose the EC2 SG we've created from last step.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2n83778lzixzhsu367ba.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8k9zw0b0l1ylg27q24t9.png)

Scroll down and create.


### Creating a DB Subnet Group


5 . Go to **RDS console**, create a new **subnet group**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dvpxxqc1ujlh0a4v2we3.png)

6 . Enter name **labRDS-subnet-group** and choose VPC correctly.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/klsmc5e3chrt0dquwlqy.png)


Select 2 AZs that had 2 private subnet we created.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bm3sp9pjl4bxoe76x12u.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0uuwhsk5rqommhoebq5i.png)

Scroll down and create.

An **Amazon Relational Database Service (RDS) Subnet Group** is an **Amazon Virtual Private Cloud (VPC)** resource used to define a particular set of network addresses that are accessible by an RDS instance. The subnet group defines the IP ranges for each Availability Zone, which allows for increased availability and reliability of the database instance. It also ensures that only authorized databases can access the associated subnets and prevents any unauthorized access from outside sources. Additionally, by using a Subnet Group, the user has full control over which resources have access to their database instances.





# II. Create EC2 instance

7 . Go to **EC2 console** and launch a new instance.

Enter name **labRDS-server**. From the **Amazon Machine Image (AMI)**, choose an HVM version of **Amazon Linux 2023**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i12e3ermm56xywhhz3zm.png)


Under the **Instance type** section, choose the **t2.micro** instance type, which is pre-selected by default.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zdm7yf3aezcjzenz7tyt.png)



8 . Create a new key pair **labRDS**, download the key and choose it from the options.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w4mpfbuo7banzzkp10iy.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/05arrht1wy23kgnkb6xe.png)


9 . Edit **Networking**, choose **VPC**, **Subnet**, enable **public IP** and **security group** exactly like in picture.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/70rkoloxd5ds3uc8bvyi.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mepilswv5zegec96tyl7.png)



Check again and launch.


10 . Access EC2 instance with the downloaded keypair. You can use **MobaXTerm** or **Putty**.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cqkes0jvnlqbksvruros.png)



# III. Creating a RDS DB Instance 

### Install Git and NodeJS

11 . First, update your system packages to make sure you’re using the latest version. 
Find Git Packages.
Install Git.
Finally, check the Git version was successfully installed.

```
sudo dnf update -y
sudo dnf search git
sudo dnf install git -y
git --version
```



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rcpiwfvk8t9r6nuxdy2q.png)




12 . Install Node.js with the script below.

```
#!/bin/bash

# Color for formatting
GREEN='\033[0;32m'
NC='\033[0m' # Colorless

# Check if NVM is installed
if ! command -v nvm &> /dev/null; then
  # Step 1: Install nvm
  curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.0/install.sh | bash
  source ~/.nvm/nvm.sh
fi

# Verify nvm installation
nvm --version

# Install the LTS version of Node.js
nvm install --lts

# Use the installed LTS version
nvm use --lts

# Verify Node.js and npm installation
node -v
npm -v

# Step 4: Create package.json file (if it doesn't exist yet)
if [ ! -f package.json ]; then
  npm init -y
  echo -e **${GREEN}Created file package.json.${NC}**
fi

#Step 5: Install necessary npm packages
echo -e **Installing required npm packages...**
npm install express dotenv express-handlebars body-parser mysql

#Step 6: Install nodemon as a development dependency
echo -e **Installing nodemon as a development dependency...**
npm install --save-dev nodemon
npm install -g nodemon

# Step 7: Add npm start script to package.json
if ! grep -q '**start**:' package.json; then
  npm set-script start **index.js** # Replace **your-app.js** with your entry point file
  echo -e **${GREEN}Added npm start script to package.json.${NC}**
fi

echo -e **${GREEN}Installation completed. You can now start building and running your Node.js application using 'npm start'.${NC}**
```




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f0dh7a58pdt8wj6bf07c.png)





### Create RDS DB Instance

13 . Navigate to **RDS console** to create a new database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p6usj8ch2kmy0eo8uem9.png)

14 . Choose **Standard**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/etq77a2ft9l39ywvgvie.png)

Choose **MySQL**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fbsgjtyei0kdmdkzn2ww.png)

For **Templates**, choose **Dev/Test**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kh55o8085rotsne17nep.png)


Choose **Single DB instance**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/59f86olubr70dyd4cjgj.png)


- Enter **database-labRDS** for **DB indentifier**.
- Open **Credential Settings**. If you want to specify a password, uncheck the Auto generate a password box if it’s already selected.
- Change the **Master username** value if you want.
- Enter the same password in both **Master password** and **Confirm password**.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b7mvde9qsxnw6p8us92g.png)




For **configuration**, choose **Burstable classes** and **db.t3.micro**.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z7tlgvww3pt6mul8047p.png)




For **Storage**, choose **General purpose (gp2)**, change **Allocated storage** to **20 GiB**.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qxds0h3mls2qqn6dcchx.png)



For **Connectivity**, choose **Connect to an EC2 compute resource**, then choose your server instance.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nsm4rw6nx1w3eh57xmlg.png)



In **Additional VPC security group**, choose your DB security group.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/90bnpk1etsgnc9jeqj51.png)


Keep the rest default like below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/til1ze7dwc5qz7axefuz.png)

Scroll down and create.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pqr4bdywtdxlcma6ue2o.png)



View your connection detail.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/avvd6te32v3topltkpbl.png)




![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v44rbnq8h9jj3jslgwnh.png)


15 . Inspect your new database


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7b9z0397432x0xzuryls.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kk23sw6bslfbzstb6kfk.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lwqanzw8dj0gaurgqylu.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/umxh5d1b4fvkbao06j1j.png)






### Viewing Logs and Events on AWS RDS

16 . Click on the Log & events tab. Here, you can view various logs such as:

- Error log: Records errors that occur on the instance.
- General log: Records general activities on the instance.
- Slow query log: Records slow queries.
- Event log: Displays important events related to the instance.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/30c24rzicqgruwoqmvz1.png)


Choose one log and view it.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jj706iblrh2vt3szwuvn.png)




### Viewing Maintenance Information

Here, you will see information about the maintenance schedule, including the times when the DB instance will be automatically backed up and maintenance tasks will be performed. You can also view the history of previous maintenance events.

You can also view automatic backups and manual backups. You can also configure and manage backup settings.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zq50mfqik2nbubwiqe48.png)






# IV. Deploy the application


17 . Clone this repo.

```
git clone https://github.com/AWS-First-Cloud-Journey/AWS-FCJ-Management
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xjsmqrtgc6t26e8w9ei3.png)






18 . Install MySQL.

First, go save your database endpoint.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ojl6h62nmn3aj0bqizqc.png)



Note: To execute this script, you need to have sudo permissions and make sure you have provided the correct database information (RDS Endpoint, database name, username and password) before run script.

```
#!/bin/bash

# Set variables for MySQL RPM and database information
MYSQL_RPM_URL="https://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm"
DB_HOST="replace this with your database endpoint"
DB_NAME="first_cloud_users"
DB_USER="admin"
DB_PASS="12341234"

# Check if MySQL Community repository RPM already exists
if [ ! -f mysql80-community-release-el9-1.noarch.rpm ]; then
  sudo wget $MYSQL_RPM_URL
fi

# Install MySQL Community repository
sudo dnf install -y mysql80-community-release-el9-1.noarch.rpm

# You need the public key of mysql to install the software.
sudo rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2023

# Install MySQL server
sudo dnf install -y mysql-community-server

# Start MySQL server
sudo systemctl start mysqld

# Enable MySQL to start on boot
sudo systemctl enable mysqld

# Check MySQL version
mysql -V

# Create or update the .env file with database information
echo "DB_HOST=$DB_HOST" >> .env
echo "DB_NAME=$DB_NAME" >> .env
echo "DB_USER=$DB_USER" >> .env
echo "DB_PASS=$DB_PASS" >> .env

# Connect to MySQL
mysql -h $DB_HOST -P 3306 -u $DB_USER -p 
```
Then enter your password to login.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t4z10tpszghw82fs0djd.png)


```mysql
CREATE DATABASE IF NOT EXISTS first_cloud_users;

USE first_cloud_users;

CREATE TABLE `user`
(
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(45) NOT NULL,
    `last_name` VARCHAR(45) NOT NULL,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `phone` VARCHAR(15) NOT NULL,
    `comments` TEXT NOT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active'
) ENGINE = InnoDB;

INSERT INTO `user`
(`first_name`, `last_name`, `email`, `phone`, `comments`, `status`)
VALUES
('Amanda', 'Nunes', 'anunes@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Alexander', 'Volkanovski', 'avolkanovski@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Khabib', 'Nurmagomedov', 'knurmagomedov@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Kamaru', 'Usman', 'kusman@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Israel', 'Adesanya', 'iadesanya@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Henry', 'Cejudo', 'hcejudo@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Valentina', 'Shevchenko', 'vshevchenko@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Tyron', 'Woodley', 'twoodley@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Rose', 'Namajunas', 'rnamajunas@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Tony', 'Ferguson', 'tferguson@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Jorge', 'Masvidal', 'jmasvidal@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Nate', 'Diaz', 'ndiaz@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Conor', 'McGregor', 'cmcGregor@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Cris', 'Cyborg', 'ccyborg@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Tecia', 'Torres', 'ttorres@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Ronda', 'Rousey', 'rrousey@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Holly', 'Holm', 'hholm@ufc.com', '012345 678910', 'I love AWS FCJ', 'active'),
('Joanna', 'Jedrzejczyk', 'jjedrzejczyk@ufc.com', '012345 678910', 'I love AWS FCJ', 'active');
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wsmbe36y4mv0yqr2pj0i.png)




```
SHOW DATABASES;
USE first_cloud_users;

SHOW TABLES;
DESCRIBE user;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6ru00xqsb77v95sazc2p.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8vmmnmid1qv7sihziq5.png)






19 . 

Go to application directory.

```
cd AWS-FCJ-Management/
```

Once you are in the application directory, run the following command to start the application:

```
npm start
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bnz9cim37t5zktkslv51.png)


Get your instance's public IPv4, access it with http through port 5000 and you should see the app is running:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2gt82af83kwlm46l51dw.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y3m35q3243nfzj6ohay8.png)

Try to add new user and check.



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sukzwh0d5xz2vx699doz.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eidmzzet2tqg1uytds1y.png)



# VI. Clean up

- Terminate EC2 instance
- Delete DB Instance, also release the Elastic IP addresses if there is any left.
- Delete DB Snapshots
- Delete VPC










![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7ll47mp5ij7kohiyhgye.png)




Congratulation!


20 . You can go and check log to see the difference:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wrpbeddg93j35mcid372.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sjfsf32o7chxvtw0mfj7.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fs0ac0ygoxfiymqf6j3h.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ept01pf181b0ljk20pic.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6ns4awwtzppyt8iqwn2m.png)



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mlrlzba3brh1gx9tffjt.png)






# V. Create snapshot and restore

Create a snapshot.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f94ntrpi2q39mq0rwf7l.png)

Wait for it to be **Available**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/awknalg8kuinvylz7x6c.png)

Choose **Restore**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ob0br5wpsyjflup0iz18.png)

Enter **labRDS-restore**. Also remember to choose **Burstable classes** and **db.t3.micro**. Then **Restore DB instance**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zip7jzyzb56wu00ip6pd.png)



You then can return to step **18** to set up a new database connection to **labRDS-restore**. Re-run the app and you will see it can fetch data normally again.