Add DB & configure in ec2

To set up a MySQL database in an EC2 instance and use Flyway for database migrations when your Spring Boot application starts up, you'll need to follow these steps:

Step 1: Launch an EC2 Instance

Create an EC2 Instance:
- Go to the AWS Management Console.
- Navigate to EC2 and click on "Launch Instance."
- Choose an Amazon Machine Image (AMI). A good choice is the Amazon Linux 2 AMI or Ubuntu Server.
- Choose an instance type (e.g., t2.micro for a small-scale setup).
- Configure instance details, storage, and security groups (allow inbound traffic on MySQL port 3306).
- Review and launch the instance.
Connect to the EC2 Instance:
- Use SSH to connect to your EC2 instance from your local machine:
```
ssh -i /path/to/your-key.pem ec2-user@your-ec2-public-ip
```

Step 2: Install MySQL on the EC2 Instance

Update the Instance:

Update the package list:

sudo yum update -y
# or for Ubuntu
sudo apt-get update

Install MySQL:

Install MySQL server on your EC2 instance:

sudo yum install mysql-server -y
# or for Ubuntu
sudo apt-get install mysql-server -y

Start MySQL:

Start the MySQL service:

sudo service mysqld start
# or for Ubuntu
sudo systemctl start mysql

Secure MySQL Installation:
- Run the security script to remove insecure default settings:
```
sudo mysql_secure_installation
```

Create a Database and User:

Inside the MySQL shell:

CREATE DATABASE myapp_db;
CREATE USER 'myapp_user'@'%' IDENTIFIED BY 'myapp_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'%';
FLUSH PRIVILEGES;
EXIT;

Step 3: Configure Security Groups for Remote Access

Update Security Group:
- Go to the AWS Management Console and find the security group associated with your EC2 instance.
- Add an inbound rule to allow traffic on port 3306 for MySQL. Set the source to your IP or 0.0.0.0/0 (not recommended for production).

Step 4: Configure Spring Boot to Use MySQL

Update application.yml or application.properties:

Configure your Spring Boot application to connect to the MySQL database on EC2:

spring:
datasource:
url: jdbc:mysql://your-ec2-public-ip:3306/myapp_db
username: myapp_user
password: myapp_password
driver-class-name: com.mysql.cj.jdbc.Driver

jpa:
hibernate:
 ddl-auto: none  # Flyway will manage the schema
show-sql: true
properties:
 hibernate.dialect: org.hibernate.dialect.MySQL8Dialect

Step 5: Set Up Flyway for Database Migrations

Add Flyway Dependency:

Ensure you have Flyway in your pom.xml:

<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>

Create SQL Migration Files:
- Place your migration scripts in the src/main/resources/db/migration directory. Flyway will automatically execute these scripts on application startup.
Example migration file V1__init.sql:
```
CREATE TABLE example_table (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(100) NOT NULL
);
```

Step 6: Start Your Spring Boot Application

Run Your Application:
- When you start your Spring Boot application, Flyway will automatically run the migration scripts against the MySQL database on your EC2 instance.
- Monitor the logs to ensure that Flyway successfully applies the migrations.

Optional: Automate the Process

Automate EC2 Setup:
- Consider using AWS CloudFormation, Terraform, or Ansible to automate the EC2 and MySQL setup process.
Use a Managed Database (RDS):
- For production setups, consider using Amazon RDS instead of managing MySQL yourself on EC2. This removes the need to handle database maintenance and backups manually.

By following these steps, you'll set up a MySQL database on an EC2 instance, configure Spring Boot to connect to it, and use Flyway for automated database migrations.

AdelaideBaron / mrc-members-hub