AWS에 구성한 RDS는 MariaDB를 사용 중이다. MariaDB에서 스프링부트 프로젝트를 실행하기 위해선 몇 가지 작업이 필요하다.
위 3단계를 차례로 설정하여 RDS를 연동해보자.
2가지 종류의 테이블을 생성해야 한다. 각 테이블의 생성 방법은 다음과 같다.
로그 내용
Hibernate: create table posts (id bigint not null auto_increment, created_date datetime(6), modified_date datetime(6), author varchar(255), content TEXT not null, title varchar(500) not null, primary key (id)) engine=InnoDB
Hibernate: create table user (id bigint not null auto_increment, created_date datetime(6), modified_date datetime(6), email varchar(255) not null, name varchar(255) not null, picture varchar(255), role varchar(255) not null, primary key (id)) engine=InnoDB
file 검색(Mac: cmd + shift + O) 으로 schema-mysql.sql 을 검색하여 찾을 수 있다.
schema-mysql.sql 내용
CREATE TABLE SPRING_SESSION (
PRIMARY_ID CHAR(36) NOT NULL,
SESSION_ID CHAR(36) NOT NULL,
CREATION_TIME BIGINT NOT NULL,
LAST_ACCESS_TIME BIGINT NOT NULL,
MAX_INACTIVE_INTERVAL INT NOT NULL,
EXPIRY_TIME BIGINT NOT NULL,
PRINCIPAL_NAME VARCHAR(100),
CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);
CREATE TABLE SPRING_SESSION_ATTRIBUTES (
SESSION_PRIMARY_ID CHAR(36) NOT NULL,
ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
ATTRIBUTE_BYTES BLOB NOT NULL,
CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
위 테이블을 생성하기 위해 RDS에 접속하여 쿼리문을 실행하였다.
mysql -u '마스터사용자이름' --host '엔드포인트' -P '포트번호' -p
$ mysql -u admin --host freelec-springbooot2-webservice.cw0aig3wx9yi.ap-northeast-2.rds.amazonaws.com -P 3306 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 29238
Server version: 10.6.10-MariaDB managed by https://aws.amazon.com/rds/
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| freelec |
| information_schema |
| innodb |
| mysql |
| performance_schema |
| sys |
| tmp |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [sys]> use freelec;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [freelec]> show tables;
+-------------------+
| Tables_in_freelec |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
MariaDB [freelec]> create table posts (id bigint not null auto_increment, created_date datetime(6), modified_date datetime(6), author varchar(255), content TEXT not null, title varchar(500) not null, primary key (id)) engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)
MariaDB [freelec]> show tables;
+-------------------+
| Tables_in_freelec |
+-------------------+
| posts |
| test |
+-------------------+
2 rows in set (0.00 sec)
MariaDB [freelec]> create table user (id bigint not null auto_increment, created_date datetime(6), modified_date datetime(6), email varchar(255) not null, name varchar(255) not null, picture varchar(255), role varchar(255) not null, primary key (id)) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
MariaDB [freelec]> show tables;
+-------------------+
| Tables_in_freelec |
+-------------------+
| posts |
| test |
| user |
+-------------------+
3 rows in set (0.00 sec)
MariaDB [freelec]> select * from posts;
Empty set (0.00 sec)
MariaDB [freelec]> select * from user;
Empty set (0.00 sec)
MariaDB [freelec]> CREATE TABLE SPRING_SESSION (
-> PRIMARY_ID CHAR(36) NOT NULL,
-> SESSION_ID CHAR(36) NOT NULL,
-> CREATION_TIME BIGINT NOT NULL,
-> LAST_ACCESS_TIME BIGINT NOT NULL,
-> MAX_INACTIVE_INTERVAL INT NOT NULL,
-> EXPIRY_TIME BIGINT NOT NULL,
-> PRINCIPAL_NAME VARCHAR(100),
-> CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)
-> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 1 warning (0.01 sec)
MariaDB [freelec]> CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [freelec]> CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [freelec]> CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [freelec]> CREATE TABLE SPRING_SESSION_ATTRIBUTES (
-> SESSION_PRIMARY_ID CHAR(36) NOT NULL,
-> ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
-> ATTRIBUTE_BYTES BLOB NOT NULL,
-> CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
-> CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE
-> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 1 warning (0.01 sec)
MariaDB [freelec]> show tables;
+---------------------------+
| Tables_in_freelec |
+---------------------------+
| SPRING_SESSION |
| SPRING_SESSION_ATTRIBUTES |
| posts |
| test |
| user |
+---------------------------+
5 rows in set (0.00 sec)
참고
인텔리제이에서 build.gradle와 application-real.properties 를 다음과 같이 내용을 추가하고 깃허브로 푸시한다.
MariaDB 드라이버를 build.gradle에 등록한다.
기존에는 H2 드라이버만 있는 상태이기 때문에 MariaDB 드라이버를 별도로 등록해줘야 한다.
//use mariadb in RDS
implementation ('org.mariadb.jdbc:mariadb-java-client')
서버에서 RDS와 연동할때 필요한 설정은 application-real.properties 파일에 추가한다.
application-real.properties 로 파일을 만들면 profile=real인 환경이 구성된다.
spring.profiles.include=oauth, real-db
#쿼리 로그 세팅
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
spring.jpa.properties.hibernate.dialect.storage_engine=innodb
#세션저장소 jdbc 설정
spring.session.store-type=jdbc
spring.session.jdbc.initialize-schema=always
# UTF-8 설정
server.servlet.encoding.charset=UTF-8
server.servlet.encoding.force=true
OAuth와 마찬가지로 RDS 접속 정보도 보호해야 할 정보이기 때문에 EC2 서버에 직접 설정 파일을 구성하자.
$ vim ~/app/application-real-db.properties
# 쿼리 로그 세팅
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type=trace
# DB 세팅
spring.datasource.hikari.jdbc-url=jdbc:mariadb://rds주소:포트번호/database이름
spring.datasource.hikari.username=DB계정
spring.datasource.hikari.password=DB계정-비밀번호
spring.datasource.hikari.driver-class-name=org.mariadb.jdbc.Driver
# ddl-auto 세팅
spring.jpa.hibernate.ddl-auto=none
deploy.sh가 real profile을 쓸 수 있도록 다음과 같이 수정하자.
$ cat /home/ec2-user/app/step1deploy.sh |tail -5
echo "> JAR Name: $JAR_NAME"
nohup java -jar \
-Dspring.config.location=classpath:/application.properties,/home/ec2-user/app/application-oauth.properties,/home/ec2-user/app/application-real-db.properties \
-Dspring.config.profiles.active=real \
$REPOSITORY/$JAR_NAME 2>&1 &
-Dspring.config.profiles.active=real
옵션은 application-real.properties를 활성화시킨다. application-real.properties에서는 spring.profiles.include=oauth,real-db
옵션때문에 real-db 파일이 함께 활성화 대상에 포함된다.
설정을 모두 마친후 deploy.sh를 실행하여 애플리케이션을 배포해보자.
$ bash ~/app/step1/deploy.sh
> Git Pull
remote: Enumerating objects: 28, done.
remote: Counting objects: 100% (28/28), done.
remote: Compressing objects: 100% (15/15), done.
remote: Total 20 (delta 6), reused 19 (delta 5), pack-reused 0
Unpacking objects: 100% (20/20), 1.99 KiB | 339.00 KiB/s, done.
From https://github.com/KangJuHui/springboot2-webservice
8cc9cb5..8587db7 master -> origin/master
Updating 8cc9cb5..8587db7
Fast-forward
src/main/resources/application-real.properties | 16 +++++++++++++---
1 file changed, 13 insertions(+), 3 deletions(-)
> 프로젝트 Build 시작
Starting a Gradle Daemon (subsequent builds will be faster)
> Task :test
2023-02-17 18:12:00.025 INFO 2983 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2023-02-17 18:12:00.033 INFO 2983 --- [ionShutdownHook] .SchemaDropperImpl$DelayedDropActionImpl : HHH000477: Starting delayed evictData of schema as part of SessionFactory shut-down'
Hibernate: drop table if exists posts
2023-02-17 18:12:00.038 WARN 2983 --- [ionShutdownHook] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90121, SQLState: 90121
2023-02-17 18:12:00.041 ERROR 2983 --- [ionShutdownHook] o.h.engine.jdbc.spi.SqlExceptionHelper : Database is already closed (to disable automatic closing at VM shutdown, add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL) [90121-200]
2023-02-17 18:12:00.046 WARN 2983 --- [ionShutdownHook] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 90121, SQLState: 90121
2023-02-17 18:12:00.048 ERROR 2983 --- [ionShutdownHook] o.h.engine.jdbc.spi.SqlExceptionHelper : Database is already closed (to disable automatic closing at VM shutdown, add ";DB_CLOSE_ON_EXIT=FALSE" to the db URL) [90121-200]
2023-02-17 18:12:00.048 WARN 2983 --- [ionShutdownHook] o.s.b.f.support.DisposableBeanAdapter : Invocation of destroy method failed on bean with name 'entityManagerFactory': org.hibernate.exception.JDBCConnectionException: Unable to release JDBC Connection used for DDL execution
2023-02-17 18:12:00.049 INFO 2983 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2023-02-17 18:12:00.061 INFO 2983 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
BUILD SUCCESSFUL in 34s
8 actionable tasks: 5 executed, 3 up-to-date
> step1 디렉토리로 이동
> Build 파일 복사
> 현재 구동중인 애플리케이션 pid 확인
현재 구종 중인 애플리케이션 pid:
> 현재 구동 중인 애플리케이션이 없으므로 종료하지 않았습니다.
> 새 애플리케이션 배포
> JAR Name: springboot2-webservice-1.0-SNAPSHOT.jar
$ nohup: appending output to ‘nohup.out’
#어플리케이션 배포가 성공했다면 아래와 같은 로그를 확인할 수 있다.
$ cat nohup.out |grep -A2 http
2023-02-17 18:12:15.111 INFO 3533 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2023-02-17 18:12:15.137 INFO 3533 --- [ main] s.a.ScheduledAnnotationBeanPostProcessor : No TaskScheduler/ScheduledExecutorService bean found for scheduled processing
2023-02-17 18:12:15.166 INFO 3533 --- [ main] com.spring.book.Application : Started Application in 12.905 seconds (JVM running for 14.171)
#curl명령어로 html 코드가 정상적으로 보인다면 잘 실행된 것이다.
$ curl localhost:8080
<!DOCTYPE HTML>
<html>
<head>
<title>스프링부트 웹서비스</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<h1>스프링부트로 시작하는 웹 서비스 Ver.2</h1>
<div class="col-md-12">
<div class="row">
<div class="col-md-6">
<a href="/posts/save" role="button" class="btn btn-primary">글 등록</a>
<!-- 로그인 영역 시작 -->
<a href="/oauth2/authorization/google" class="btn btn-success active" role="button">Google Login</a>
<a href="/oauth2/authorization/naver" class="btn btn-secondary active" role="button">Naver Login</a>
<!-- 로그인 영역 끝 -->
</div>
</div>
<br>
<!-- 목록 출력 영역 -->
<table class="table table-horizontal table-bordered">
<thead class="thead-strong">
<tr>
<th>게시글번호</th>
<th>제목</th>
<th>작성자</th>
<th>최종수정일</th>
</tr>
</thead>
<tbody id="tbody">
</tbody>
</table>
</div>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<!--index.js 추가-->
<script src="/js/app/index.js"></script>
</body>
</html>
curl 명령어를 통해 EC2에 서비스가 잘 배포된 것은 확인하였다.
이제 브라우저에서 확인해보자.
그 전에 어플리케이션의 외부 접근을 위한 몇가지 설정을 확인해야 한다.
게시글 포스팅 시 403에러 발생
원인
해결 방법
SELECT * FROM USER;
UPDATE user SET role = 'USER' where name='강주희';
$ mysql -u admin --host freelec-springbooot2-webservice.cw0aig3wx9yi.ap-northeast-2.rds.amazonaws.com -P 3306 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 53981
Server version: 10.6.10-MariaDB managed by https://aws.amazon.com/rds/
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| freelec |
| information_schema |
| innodb |
| mysql |
| performance_schema |
| sys |
| tmp |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> use freelec;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [freelec]> show tables;
+---------------------------+
| Tables_in_freelec |
+---------------------------+
| SPRING_SESSION |
| SPRING_SESSION_ATTRIBUTES |
| posts |
| test |
| user |
+---------------------------+
5 rows in set (0.00 sec)
MariaDB [freelec]> select * from user;
+----+----------------------------+----------------------------+-----------------------+-------------+----------------------------------------------------------------------------------------+-------+
| id | created_date | modified_date | email | name | picture | role |
+----+----------------------------+----------------------------+-----------------------+-------------+----------------------------------------------------------------------------------------+-------+
| 1 | 2023-02-18 14:01:58.740955 | 2023-02-18 14:01:58.740955 | a4141407@gmail.com | 강주희 | https://lh3.googleusercontent.com/a/AEdFTp6SVePx6mVgcwkTrS0VBO7BvOtT05cojUg52oRL=s96-c | GUEST |
| 2 | 2023-02-18 14:10:41.515595 | 2023-02-18 14:10:41.515595 | rkdwngmlwkd@naver.com | 강주희 | https://ssl.pstatic.net/static/pwe/address/img_profile.png | GUEST |
+----+----------------------------+----------------------------+-----------------------+-------------+----------------------------------------------------------------------------------------+-------+
3 rows in set (0.01 sec)
MariaDB [freelec]> UPDATE user SET role = 'USER' where name='강주희';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [freelec]> select * from user;
+----+----------------------------+----------------------------+-----------------------+-------------+----------------------------------------------------------------------------------------+-------+
| id | created_date | modified_date | email | name | picture | role |
+----+----------------------------+----------------------------+-----------------------+-------------+----------------------------------------------------------------------------------------+-------+
| 1 | 2023-02-18 14:01:58.740955 | 2023-02-18 14:01:58.740955 | a4141407@gmail.com | 강주희 | https://lh3.googleusercontent.com/a/AEdFTp6SVePx6mVgcwkTrS0VBO7BvOtT05cojUg52oRL=s96-c | USER |
| 2 | 2023-02-18 14:10:41.515595 | 2023-02-18 14:10:41.515595 | rkdwngmlwkd@naver.com | 강주희 | https://ssl.pstatic.net/static/pwe/address/img_profile.png | USER |
+----+----------------------------+----------------------------+-----------------------+-------------+----------------------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)
이제 포스팅 권한을 획득하였으니 글을 작성한 후 애플리케이션이 재시작된 후에도 데이터가 잘 유지되는지 확인해보자.
글 작성
게시글 확인
배포 스크립트를 실행하여 어플리케이션 재시작해보자.
$ ./deploy.sh
...(생략)...
> 새 애플리케이션 배포
> JAR Name: springboot2-webservice-1.0-SNAPSHOT-plain.jar
$ nohup: appending output to ‘nohup.out’
$ cat nohup.out |egrep 'http|JVM'
2023-02-07 17:57:47.250 INFO 11932 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2023-02-07 17:57:54.253 INFO 11932 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
2023-02-07 17:57:54.322 INFO 11932 --- [ main] com.spring.book.Application : Started Application in 12.643 seconds (JVM running for 13.856)