EC2 에서 Mysql을 사용해보자

EC2 인스턴스를 만들 때 저는 보안그룹에서 3306 포트는 제 컴퓨터에서만 접속 가능하게 열어 줬습니다

ssh, pem키로 인스턴스에 접속해줍니다

이제부터 리눅스를 안다루어 보았다면 생소할 수도 있지만 ubuntu의 패키지 매니저인 apt-get를 통해 mysql을 받아줄 겁니다

# package manager update
$ sudo apt-get update
# mysql-server download(5.7version, 8.version을 받고싶다면 구글링..)
$ sudo apt-get install mysql-server

# mysql 실행
$ sudo systemctl start mysql
# 서버가 재시작 되더라도 자동 시작하게
$ sudo systemctl enable mysql

일단, 여기까지 하면 mysql server에 접속이 가능합니다

$ sudo mysql -u root

저는 mysql_secure_installation을 통해 초기 설정을 해줄 생각입니다.

$ sudo mysql_secure_installation

여기선 첫번째 질문인 VALIDATE PASSWORD PLUGIN 이것만 접근성을 위해 No로 하고 나머지 질문에는 y로 답하는 게 좋을 겁니다!

mysql> use mysql;
# 현재 mysql에 등록되어 있는 계정 정보를 살펴보는 쿼리입니다.
mysql> select Host, User, authentication_string from user;
+-----------+------------------+-------------------------------------------+
| Host      | User             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             |                                           |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *2FA443E0840787263071A7660995BD2CB61B5D62 |
+-----------+------------------+-------------------------------------------+

여러 번 시도해보았으나 뭐 때문인지 설정이 저장 안 되네요; 신비한 ec2의 세계.. 혹은 나의 실수(후자일 가능성이 훨씬 높음)

쿼리로 설정해 봅시다..

mysql> use mysql;
# 현재 담겨있는 유저 정보를 확인합니다. mysql version에 따라 password, authentication_string 필드가 있다고 하네요
mysql> select Host, User, authentication_string from user;
+-----------+------------------+-------------------------------------------+
| Host      | User             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             |                                           |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *0D34A601EC121BE1FA2C63A6A5B66C48F8F0C3AD |
+-----------+------------------+-------------------------------------------+
# 비어 있는 것을 확인 할 수 있습니다.
# 아래 쿼리로 추가해줍니다.
mysql> update user set authentication_string=password('비밀번호') where user='root';

# 잘 들어갔죠?
mysql> select Host, User, authentication_string from user;
+-----------+------------------+-------------------------------------------+
| Host      | User             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *2FA443E0840787263071A7660995BD2CB61B5D62 |
+-----------+------------------+-------------------------------------------+

# 변경사항을 저장해줍니다.
mysql> FLUSH PRIVILEGES;

# 일단 mysql을 빠져나올게요.
mysql> exit;

mysqld.cnf

현재 저희가 사용하는 mysql 버전은 5.7 ... 버전일 겁니다.(ubuntu 18 버전 default)

따로 저장소를 추가해주지 않았기 때문에 8.0... 버전을 사용하려면 구글링 해주세용!

후에 char set 에러를 해결하다가 알게 된 사실인데, 5.7 버전에서는 로컬 환경에서 설정해주던(mysql 8.0...버전) my.cnf 파일 말고 여기서 charset 설정을 해주면 되는 것 같습니다.(검증은 해보았으나 제가 틀렸을 수도 있습니다.)

# ubuntu 환경 shell에서 mysqld.cnf를 찾습니다.
$ sudo find / -name "mysqld.cnf"
/etc/mysql/mysql.conf.d/mysqld.cnf

저는 vi 편집기를 자주 사용하기 때문에 vi 편집기로 해당 파일을 열게요! 슈퍼유저 권한으로 열지 않게 되면 readonly라 편집이 불가능할 겁니다.
vi 편집기 사용법을 모르면 조금 찾아보셔야 할 것 같습니다.. 하하

$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address 를 0.0.0.0으로 고침 →제 추측으론 RDS의 퍼블릭 액세스 가능성과 설정과 같은 것 같습니다. 역시나 정확하진 않습니다만 이걸 안 하면 인텔리제이에서 db 서버를 사용할 수 없습니다. 그리고 여기서 charset 설정까지 해주고 빠져나옵니다. [mysqld]와 같은 block은 정확히 지켜주세요!

[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init_connect = set collation_connection = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
..
..
bind-address            = 0.0.0.0

[Db]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqldump]
default-character-set=utf8mb4

이후 변경한 설정을 반영하기 위해 mysql을 재시작해줄게요

$ sudo systemctl restart mysql

인텔리제이 설정

현재 저는 spring boot로 개발하고 있습니다.

인텔리제이에서 위에서 설정한 db서버를 사용하기 위해 다음의 내용을 진행해보겠습니다.

인텔리제이 설정으로 가기 전에 다시 mysql로 돌아가 한 가지 설정을 해줄게요.

$ sudo mysql -u root

mysql> grant all privileges on *.* to root@{내 컴퓨터 ip주소} identified by '{비밀번호}' with grant option;

안 하신다면 아래와 같은 에러가 나올 겁니다.

Host '{내 컴퓨터 ip주소}' is not allowed to connect to this MySQL server".

root라는 이름의 설정한 비밀번호를 사용하는 사용자가 내 컴퓨터 ip에서 접근을 가능하게 해 주었습니다. 확인하고 싶으시다면

mysql> use mysql;
mysql> select Host, User, authentication_string from user;

# 컬럼 아래에 아래와 같은 부분이 추가되었을거에요
| 내 로컬 ip | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

# 사용할 데이터베이스를 만들어 줍니다.
mysql> create database test;

이제 인텔리제이로 가봅시다.

우측 상단 Database - '+' - Data Source - MySQL을 선택합니다.

Name은 멋지게 지어주시고,

Host엔 내 ec2 IP,

User엔 아까 만들어놓은 계정(따라오셨다면 root)

password도 아까 넣어준 password,

Database엔 아까 만들어놓은 db

넣어주고 Test Connection을 누르겠습니다.

와우 아주 나이스 하네요~! OK를 눌러줍니다.

application.properties

이제 거의 다 왔습니다.. (힘을 내요)

application.properties에 설정을 추가해주어서, 이제까지 했던 내용을 프로젝트에 반영하겠습니다.

spring.datasource.url=jdbc:mysql://{ec2 IP}:3306/{database 이름}?serverTimezone=UTC&characterEncoding=UTF-8
spring.datasource.username={사용자 이름(root)}
spring.datasource.password={비밀번호}
spring.jpa.hibernate.ddl-auto=update

설정을 깔끔하게 해 주셨다면 제일 아랫부분은 create으로 두어도 무방합니다. 코드가 실행될 때 테이블이 이미 있다면 어떡할 거냐의 문제인데, 새로 만들든 변경사항을 덮어 씌우든 우리는 character set 설정까지 깔끔하게 해 주었기 때문이죠.

이후 프로젝트를 run하고 검증해보시면 되겠습니다.

만났던 에러들

로컬에서는 mysql 8.0... 버전을 사용하기 때문에 '똑같이 하면 되겠지' 하고 my.cnf 파일에 char set 설정을 해주었습니다. 5.7 버전에서는 mysqld.cnf 에 설정을 해줍시다! char set 설정이 제대로 되어 있지 않다면 만날 수 있는 에러입니다.

테이블 인코딩 에러

java.sql.SQLException: Incorrect string value: '\xED\x99\x8D\xEC\xA2\x85...' for column ..

당시 해결 방법

테이블마다 character set을 쿼리로 변경해주었습니다.

alter table '테이블명' convert to character set utf8 collate utf8_general_ci;

하지만 이렇게 되면 application.properties 파일에서,

spring.jpa.hibernate.ddl-auto 부분을 create 말고 update로 해주어야 테이블이 새로 만들어질 때 설정이 안 날아갑니다.

당시 해결 방법 2

위처럼 해결하면 당장은 사용 가능하겠으나 해당 데이터베이스 안의 테이블을 만들 때마다 쿼리를 날려줘야 합니다.

→ database를 만들 때부터 character set을 명시해주고 collate를 사용합니다.(이후 만들어질 테이블은 collate를 통해 char set을 적절히 가지게 됩니다.)

# utf8
mysql> create database test default character set utf8 collate utf8_unicode_ci;
# utf8mb4
mysql> create database test default character set utf8mb4 collate utf8mb4_general_ci;

무엇보다 좋은 방법은 설정 파일을 제대로 설정하는 것이겠네요

추가내용(4/12)

실제 프로젝트에서 사용해보려 EC2에 Spring boot 프로젝트 빌드 파일을 올리고, 위의 설정을 해주었습니다.
DB를 사용하는 사용자가 EC2 이므로 applciation properties에서 localhost로 설정 후 빌드해주어야 하는데
빌드 시에 오류를 반환하기 때문에, db에서 사용자 추가를 해줍니다.

mysql> grant all privileges on *.* to root@{ec2 ip주소} identified by '{비밀번호}' with grant option;