AWS EC2 내부에 MariaDB 설치 및 커넥션 관련 정리

[iknoom]

1. EC2 내에 MariaDB를 설치

EC2 + RDS 가격이 너무 부담되어 EC2 내에 DB를 설치하기로 결정(RDS 6일 켜놨는데 5천원 나감..)
RDS에서는 많은 기능을 지원하지만 거의 사용하지 않을 예정이며 EC2에도 메모리가 충분히 남는다.

참고 자료

• https://daddyprogrammer.org/post/2348/aws-ec2-install-nginx-mariadb/
• https://velog.io/@ckstn0777/AWS-EC2-DB-%EA%B5%AC%EC%B6%95MariaDB

MariaDB 설치

$ sudo yum install mariadb-server
...
$ sudo service mariadb start
Redirecting to /bin/systemctl start mariadb.service
$ mysql -V
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

root 패스워드 변경

$ mysqladmin -u root -p password 'E*********7'
Enter password:
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-MariaDB MariaDB Server

database 생성

MariaDB [(none)]> create database samsam_wing;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| samsam_wing        |
| test               |
+--------------------+
5 rows in set (0.00 sec)

운영 계정 생성

MariaDB [(none)]> use mysql;
MariaDB [mysql]> create user 'samsam'@'localhost' identified by 'E*********7';
MariaDB [mysql]> grant all privileges on samsam_wing.* to samsam@'localhost';
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> select user, host, plugin from user;
+--------+--------------------------------------------------+--------+
| user   | host                                             | plugin |
+--------+--------------------------------------------------+--------+
| root   | localhost                                        |        |
| root   | ip-172-31-11-231.ap-northeast-2.compute.internal |        |
| root   | 127.0.0.1                                        |        |
| root   | ::1                                              |        |
|        | localhost                                        |        |
|        | ip-172-31-11-231.ap-northeast-2.compute.internal |        |
| samsam | localhost                                        |        |
+--------+--------------------------------------------------+--------+
7 rows in set (0.00 sec)

...
// samsam@localhost 접속 확인
$ mysql -u samsam -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.

외부 접속 권한 부여

어차피 ec2 보안 그룹에 인바운드에 내 IP를 지정할 수 있으므로 IP를 '%'으로 모두 열어둔다.

MariaDB [mysql]> grant all privileges on samsam_wing.* to samsam@'%' identified by 'E*********7';
MariaDB [mysql]> select user, host, plugin from user;
+--------+--------------------------------------------------+--------+
| user   | host                                             | plugin |
+--------+--------------------------------------------------+--------+
| root   | localhost                                        |        |
| root   | ip-172-31-11-231.ap-northeast-2.compute.internal |        |
| root   | 127.0.0.1                                        |        |
| root   | ::1                                              |        |
|        | localhost                                        |        |
|        | ip-172-31-11-231.ap-northeast-2.compute.internal |        |
| samsam | localhost                                        |        |
| samsam | %                                                |        |
+--------+--------------------------------------------------+--------+

MariaDB [mysql]> flush privileges;

port 번호 변경

보안을 위해서 3306이 아닌 다른 포트를 이용한다.

$ sudo vim /etc/my.cnf
// port 번호 추가
$ sudo service mariadb restart

외부 접속 허용

ec2 보안 그룹에 인바운드 추가

Type : 사용자 지정 TCP
Protocal : TCP
Port range : 위에서 지정한 port 번호
Source : 내 IP

인텔리제이에서 접속 확인

에러1. The server time zone value 'KST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.

• 해결 : mysql 서버의 타임존을 일치시킨다. ('+9:00')
• https://sungwookkang.com/1325

접속에 성공

기타 주의사항

이제 인스턴스 재부팅 시 db 서버를 켜야함

$ sudo service mariadb start

[iknoom]

2. DB 설정

언어셋 변경

/etc/my.cnf를 먼저 수정 (https://jm4488.tistory.com/23)
나머지 수정 안된 부분을 인텔리제이에서 수정

use samsam_wing;

alter database samsam_wing
character set = 'utf8mb4'
collate = 'utf8mb4_general_ci';

show variables like 'c%';

반영 완료

프로젝트에 필요한 table 추가

spring.jpa.show-sql=true을 설정하여 Hibernate가 보여주는 쿼리문을 복사한다.

use samsam_wing;

create table notice (
    id bigint not null auto_increment, 
    created_date datetime(6), 
    content TEXT not null, 
    title varchar(500) not null, 
    primary key (id)
) engine=InnoDB;

create table pad_box (
    box_id bigint not null auto_increment, 
    address varchar(255) not null, 
    humidity double precision, 
    latitude double precision not null, 
    longitude double precision not null, 
    name varchar(255) not null, 
    pad_amount integer not null, 
    temperature double precision, 
    updated_state_date datetime(6), 
    primary key (box_id)
) engine=InnoDB;

create table pad_box_log (
    id bigint not null auto_increment, 
    created_date datetime(6), 
    used_amount integer, 
    box_id bigint, 
    primary key (id)
) engine=InnoDB;

create table report (
    id bigint not null auto_increment, 
    created_date datetime(6), 
    content TEXT not null, 
    is_resolved bit not null, 
    tag varchar(255) not null, 
    box_id bigint, 
    primary key (id)
) engine=InnoDB;

create table user (
    id bigint not null auto_increment, 
    email varchar(100) not null, 
    password varchar(80) not null, 
    primary key (id)
) engine=InnoDB;

create table user_roles (
    user_id bigint not null, 
    roles varchar(255)
) engine=InnoDB;

show tables;

[iknoom]

3. DB 커넥션

[스프링 부트와 AWS로 혼자 구현하는 웹 서비스]
309페이지 ~ 311페이지

MariaDB 드라이버를 build.gradle에 추가

이미 추가되어있다. runtimeOnly인 부분이 책과 다르다.

runtimeOnly 'org.mariadb.jdbc:mariadb-java-client'

resources에 application-real.properties 추가

책 내용 중 일단 spring.session.store-type=jdbc는 제외했다.

spring.profiles.include=real-db
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

app 디렉토리에 application-real-db.properties 추가

# vim ~/app/application-real-db.properties
spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mariadb://localhost/samsam_wing
spring.datasource.username=samsam
spring.datasource.password=
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver               

deploy.sh가 real profile을 쓸 수 있도록 추가

nohup java -jar \
        -Dspring.config.location=classpath:/application.properties,/home/ec2-user/app/application-real-db.properties,classpath:/application-real.properties \
        -Dspring.profiles.active=real \
        $REPOSITORY/$JAR_NAME 2>&1 &

에러

이해가 잘 안간다. profile을 잘 설정했을텐데

java.lang.RuntimeException: Driver org.mariadb.jdbc.Driver claims to not accept jdbcUrl, jdbc:h2:mem:testdb;MODE=MYSQL
        at com.zaxxer.hikari.util.DriverDataSource.<init>(DriverDataSource.java:110) ~[HikariCP-3.4.5.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.initializeDataSource(PoolBase.java:325) ~[HikariCP-3.4.5.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.<init>(PoolBase.java:114) ~[HikariCP-3.4.5.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:108) ~[HikariCP-3.4.5.jar!/:na]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-3.4.5.jar!/:na]

참고로 jdbc:h2:mem:testdb;MODE=MYSQL에 대한 설정은 real properties에 없다. 이 설정은 default properties(application.properties)에 있다.
그리고 분명 -Dspring.profiles.active=real을 설정해줬고 로그로도 확인을 했다.

2021-04-07 05:16:10.675  INFO 6140 --- [           main] uos.samsam.wing.WingApplication          : The following profiles are active: real,real-db

대처

아무래도 추측하기로는 default properties가 profile=real로 해도 적용이 되는 것 같다. default properties를 dev properties로 이름을 구분하니 에러가 나지 않는다.
변경사항

• rename application.properties -> application-dev.properties
• deploy.sh 수정

nohup java -jar \
        -Dspring.config.location=/home/ec2-user/app/application-real-db.properties,classpath:/application-real.properties \
        -Dspring.profiles.active=real \
        $REPOSITORY/$JAR_NAME 2>&1 &

서버를 띄우는데 성공한 스크린샷

[kir3i]

수정사항: 테이블 재설정

기존 테이블 생성 쿼리에 제약 조건이 빠져있어서 제약조건을 추가한 쿼리로 수정

create table notice (
    id bigint not null auto_increment,
    created_date datetime(6),
    content TEXT not null,
    title varchar(500) not null,
    primary key (id)
) engine=InnoDB;

create table pad_box (
    box_id bigint not null auto_increment,
    address varchar(255) not null,
    humidity double precision,
    latitude double precision not null,
    longitude double precision not null,
    name varchar(255) not null,
    pad_amount integer not null,
    temperature double precision,
    updated_state_date datetime(6),
    primary key (box_id)
) engine=InnoDB;

create table pad_box_log (
    id bigint not null auto_increment,
    created_date datetime(6),
    used_amount integer,
    box_id bigint,
    primary key (id)
) engine=InnoDB;

create table report (
    id bigint not null auto_increment,
    created_date datetime(6),
    content TEXT not null,
    is_resolved bit not null,
    tag varchar(255) not null,
    box_id bigint, primary key (id)
) engine=InnoDB;

create table user (
    id bigint not null auto_increment,
    email varchar(100) not null,
    password varchar(80) not null,
    primary key (id),
    constraint UK_email unique (email)
) engine=InnoDB;

create table user_roles (
    user_id bigint not null,
    roles varchar(255)
) engine=InnoDB;

alter table pad_box_log add constraint FK_boxlog_to_box foreign key (box_id) references pad_box (box_id);
alter table report add constraint FK_report_to_box foreign key (box_id) references pad_box (box_id);
alter table user_roles add constraint FK_roles_to_user foreign key (user_id) references user (id);

FK 연결 관계와 User 테이블의 email 필드에 unique 조건을 추가해줬다.