Final RDS blog

[anawandh]

Setting Up Amazon RDS with MySQL for a Spring Boot Application

In this guide, I'll walk you through setting up Amazon RDS with MySQL for your Spring Boot application. We will cover creating an RDS database instance, setting up MySQL, configuring Spring Boot to use different databases for development and production, and dynamically switching between them.

Example project -

link

git clone https://github.com/Firestorm0986/RDS-testing.git

Step 1: Creating an RDS Database Instance

1. Log in to AWS Management Console: Navigate to the RDS service.

2. Create Database:

   • Click on "Create database".

   • Choose "Standard Create".

   • Select "MySQL" as the engine type.

   • Set the "Templates" to "Free tier" if you are eligible, otherwise choose the configuration that suits your needs.

3. Configure Database Settings:

   • DB Instance Identifier: your-db-instance-id

   • Master Username: your-master-username

   • Master Password: your-master-password

   • 

   • DB Instance Class: Choose an instance class based on your requirements.

   • 

   • Storage: Allocate the necessary storage for your database.

   • Availability & Durability: Configure Multi-AZ deployment for high availability if needed.

4. Connectivity:

   • Virtual Private Cloud (VPC): Select the default VPC.
   • Subnet Group: Default.
   • Public Access: Yes
   • VPC Security Group: Create a new security group and allow inbound traffic on port 3306.
   • 

5. Create Database: Click "Create Database" and wait for the instance to be available.

Step 2: Setting Up MySQL

Once your RDS instance is available, you can connect to it and set up your database schema.

1. Connect to RDS: Use an SQL client or terminal to connect to your RDS instance.

   mysql -h your-rds-endpoint -P 3306 -u your-master-username -p

   

2. Create Database Schema:

   CREATE DATABASE yourdatabase;
   USE yourdatabase;

Step 3: Configuring Spring Boot for Different Environments

We will create different application.properties files for development and production environments.

Production Configuration (MySQL)

Create src/main/resources/application-prod.properties:

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://testing-data.ctenoof0kzic.us-east-2.rds.amazonaws.com:3306/test?useSSL=false&serverTimezone=UTC
# spring.datasource.url=jdbc:mysql://your-rds-endpoint:3306/yourdatabase?useSSL=false&serverTimezone=UTC
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=admin
spring.datasource.password=Nighthawk123 # password created during setup

spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=false
spring.jpa.open-in-view=false

Step 4: Running Spring Boot with Profiles

To activate a specific profile, you can run your Spring Boot application with an environment variable.

For Development:

Keep it how it is and do not change the application properties

For Production:

First once creating the application-prod.properties, do:

export SPRING_PROFILES_ACTIVE=prod
mvn clean install
cd target
java -jar spring-0.0.1-SNAPSHOT.jar --spring.profiles.active=prod

*Important after every change mvn clean install must be executed in project directory for the changes to transfer to the production.

Alternatively, you can set the active profile in your IDE or build tool configurations.

Step 5: Dockerfile

You can set up the docker file to directly run the production when the docker container is ran -

# syntax=docker/dockerfile:1
FROM openjdk:18-alpine3.14
WORKDIR /app

# Install required packages
RUN apk --no-cache update && \
    apk --no-cache upgrade && \
    apk --no-cache add nodejs npm git maven

# Copy application source code to the container
COPY . /app

# Build the application
RUN mvn clean install

# Set the command to change directory to target and run the application with the production profile
CMD ["sh", "-c", "cd target && java -jar spring-0.0.1-SNAPSHOT.jar --spring.profiles.active=prod"]

# Expose the application port
EXPOSE 8085

Security/VPC settings

Add security/inbound rule for Ipv4 and Ipv6 for all trafic so u can connect to the RDS from anywhere and not only the place where it was made. The example only creates one more inbound rule for IPv4 create it for Ipv6 as well.

Common Problems

Syntax

• The syntax can be different between MySQL and Sqllite for example instead of Jsonb u will need to change it to just Json in column type.
• The column size can be limited in MySql which might not have occured in Sqlite. To fix this just connect to the MySQL through terminal using the command above in the issue and then you can increase the column length using MySQL commands. Maybe change Varchar to Longtext or increase the limit from 200 to 1000. Can ask ChatGPT for such things.

Conclusion

By following this guide, you have set up an Amazon RDS MySQL instance, configured MySQL, and adjusted your Spring Boot application to dynamically switch between SQLite for development and MySQL for production. This setup allows for efficient development and robust production deployment, leveraging the strengths of both databases.