231204 Cloud

AWS EC2 인스턴스 MariaDB 설정

1. rpm -qa maria*
   → 서버에 MariaDB 존재 확인
   → mariadb-5.5.68-1.amzn2.0.1.x86_64 만 출력되면 설치가 필요한 상황
   
   
2. MariaDB 설치
   sudo yum install -y mariadb mariadb-server
   →mariadb : 서버에 접속하기 위한 클라이언트 패키지
   →mariadb-server : 현재 서버를 DB 서버로 만드는 패키지
   
   
3. MariaDB 동작확인
   sudo systemctl status mariadb
   → Active: inactive (dead) 중지 중
   → start 필요
   
   
4. MariaDB 시작
   sudo systemctl start mariadb
   
   
5. 마리아DB 접속
   mysq ( = mysql -u root)
   → MariaDB monitor 프로그램을 통해 MariaDB에 접근
   → root는 리눅스 운영체제의 슈퍼 유저(관리자)와 이름은 같지만 다른 계정이며 MariaDB 패키지(프로그램)의 관리자      계정을 의미
   
   ※root 비밀번호 설정 방법
   1) 리눅스에서 mysqladmin 명령어로 설정하는 방법(권장하지 않음)
   → mysqladmin -u root password '1234'
   → 명령어를 통해서 쉽게 root 비밀번호를 설정할 수 있지만 history에 명령어 입력 흔적이 남아 보안에 좋지 않음
   → history -c(모든 줄 삭제) 또는 history -d [라인번호] 로 해당 명령줄을 삭제해줘야함!
   → 접속 시 mysql -u root -p 엔터
   →Enter password : 1234 엔터 (history에 남지 않음)
   ※접속을 한 번 하고 exit한 후 mysql(엔터)를 통해서도 접속할 수 있다. 이는 익명 사용자(anonymous user)로 접속
      → 보안 초기 설정을 통해서 익명 사용자 접근을 거부 시키는 것이 좋음.
           보안 초기 설정은 ' mysql_secure_installaion' 명령어를 통해서 설정할 수 있음.
   2) MariaDB 내부에서 user 테이블을 수정해서 설정하는 방법
   3) mysql_secure_installaion 명령어를 사용해서 설정하는 방법 
   
   
6. mysql_secure_installaion 실행
   Enter current password for root (enter for none):
   → root 비밀번호가 설정되기 전이라면 엔터(none)을 눌러서 다음 단계로 이동,
       단, 비밀번호가 설정되어 있다면 해당 비밀번호 입력
   → mysqladmin 명령어를 통해서 1234로 설정이 되어 있기 때문에 1234로 입력 후 엔터
   
   Change the root password? [Y/n]
   → root 비밀번호를 바꾸겠는지 질문. 'n'을 눌러서 다음 단계로 이동
   
   Remove anonymous users? [Y/n]
   → 익명 사용자 삭제할 것인지에 대한 질문. 'y'를 눌러서 다음 단계로 이동
   
   Disallow root login remotely? [Y/n]
   → root 계정의 원격 접속(인터넷을 통해서 접근)을 거부할 것인지에 대한 질문, 'y'를 눌러 다음 단계로 이동
   
   Remove test database and access to it? [Y/n]
   → 기본 사항으로 만들어져 있는 test 데이터베이스를 삭제할 것인지에 대한 질문, 'y'를 눌러 다음 단계로 이동
   
   Reload privilege tables now? [Y/n]
   → 재시작을 통해서 적용, 'y'를 눌러 마무리
   
   설정 후 mysql 명령어만으로 접근이 불가
   
   ※ MariaDB 패스워드 복구
   1. sudo systemctl stop mariadb → 중지
   2. /user/bin/mysqld_safe --skip-grant &
   3. mysql -uroot mysql
   4. update user set password=password('변경할 비밀번호') where user='root';
       flush privileges;
       exit;
   5. mysql -u root -p → 패스워드 변경이 잘되었는지 테스트
   6. sudo systemctl start mariadb
   =======================
   sudo systemctl start mariadb.service
   Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
   → 만약 위의 에러가 발생한다면
   
   sudo killall -u mysql
   →  mariadb 관련 프로세스 종료
   
   sudo systemctl start mariadb

윈도우 Workbench을 통해 MariaDB 서버 접근

1. Workbench 프로그램 실행
   
   
2. MySQL Connections '+' 버튼을 눌러 새 연결 생성(그림 1)
   -Connection Name : AWS EC2
   -Connection Method : 기본값
   -Hostname : EC2 인스턴스 퍼블릭 IP 주소
   -Port : 3306(기본값)
   -Username : root
   -Store in Vault.. : 패스워드 설정. 패스워드를 자동으로 기억하게 하려면 'store in Vault'버튼을 누른 후 비밀번호 입력                               후 OK
   

   

   그림 1. Workbench를 통한 MariaDB 접속

3. 입력 후 'Test Connection'이 되지 않는다면
   1) 방화벽 설정 확인
   → AWS 웹 페이지의 보안그룹에 3306 포트가 허용(설정)되어 있는지 확인, 되어 있지 않다면 '인바운드 규칙 편집'에
       들어가 3306번 포트 번호 허용
   → 설정이 필요하면 MYSQL/Aurora, 3306(변경불가) 설정, 소스 : anywhere IPv4 입력 후 추가
   2) MariaDB 내에서 root 원격 접속 불가되어 있는 경우
   만약 root 원격 접속이 필요하다면(보안상 권장하지 않음)
   mysql -u root -p
   Enter password : 1234 로 MariaDB접속
   
   grant all on *.* to root@'IP주소' identified by '1234';
   flush privileges;
   ※ on *.* : MariaDB 내의 모든 데이터베이스와 모든 테이블 지정
   → 공인IP주소 IP 주소를 가지는 클라이언트에서 root 계정으로 접속 시 허용
   
   주소를 왜 넣는지?
   ※NAT(Network Address Translation)
   -IP 주소는 컴퓨터가 가지는 주소
   -IP 주소는 기본적으로 겹치지 않는 주소
   -IP 주소가 겹치면 충돌(Collision)이 발생 → 충돌이 발생한 두 장치 중 하나의 장치는 인터넷 사용 불가
   -IP 주소는 개수가 한정되어 있음 2^32개 = 약 42억 9천여개
   -한정된 주소를 조금 더 효율적으로 사용하기 위해서 NAT라는 기술을 만듦
   -NAT = Network Address Translation = 네트워크 주소 변환
   -공인 IP 주소와 사설 IP 주소가 생겨남.
    사설 IP 주소는 누구나 공용으로 사용할 수 있는 주소로 지정
    10.0.0.0~10.255.255.255
    172.16.0.0 ~ 172.31.255.255
    192.168.0.0 ~ 192.168.255.255 가 사설 IP 주소 범위
   -사설 IP 주소에 폼함되지 않는 주소를 공인 IP 주소라고 함
   -반드시 인터넷을 사용하려면 공인 IP 주소를 사용
    사설 IP 주소 ↔ 공인 IP 주소로 변경해주는 장치 : 공유

MariaDB 서버의 설정

cat /etc/my.cnf

→ cat : 파일의 내용을 화면에 출력

→ my.cnf 파일의 내용이 부족함

→ my.cnf 파일은 MariaDB를 설치하면 기본적으로 생성되는 설정파일. 이 설정 파일은 기본적인 내용만 있기 때문에
    운용이 부족
→ MariaDB에서 제공하는 샘플 설정 파일로 대체

샘플 설정 파일 위치 : /usr/share/mysql 디렉터리(폴더)

ls /usr/share/mysql

→파란색 : 지원하는 언어(Character-set), .cnf : config(설정) 파일, .sql : 스키마 파일(DB에서 사용하는 파일)

my-small.cnf  사용

그림 2. mysql 디렉터리 안에 파일

sudo cp /usr/share/mysql/my-small.cnf /etc/my.cnf

→ /usr/share/mysql/my-small.cnf 파일을 /etc/my.cnf 파일로 복사

→ 이 때, 기존 파일이 있는 경우 -i 옵션이 설정되어 있다면 덮어쓰기(Overwrite) 관련 질문이 나오게 됨

    -i 옵션이 없다면 바로 덮어쓰기됨

→ cp, mv, rm 명령어를 사용하기 전 원본은 백업을 해두는 습관을 가지자

sudo vi /etc/my.cnf

22번 빈 라인에 default-character-set = utf8 추가
→ 기본 문자를 utf8로 설정(기존의 기본값 : 라틴1)

29번 라인 끝에서 엔터한 후 30번 라인 : skip-name-resolve

→ 쿼리 구문(SQL문) 실행 시 사용자의 소속을 알아보는 과정을 생략

→ 성능 향상을 위해 설정

30번 라인에서 엔터 31번 라인 : character-set-server = utf8

작성 후 ESC :wq

sudo systemctl restart mariadb

 

MariaDB 서버 응용

mysql -u root -p

1234

→MariaDB 접속

 

SHOW DATABASES;

 

CREATE DATABASE shopping_db;

→shopping_db 데이터베이스(스키마) 생성

 

USE shopping_db;

→ shopping_db 데이터베이스 선택

 

CREATE TABLE customer(id VARCHAR(20) NOT NULL, name VARCHAR(20) NOT NULL, age INT(11) NULL, address VARCHAR(100) NOT NULL, PRIMARY KEY(id));

→ customer 테이블 생성

※ INT(11) 관례적으로 사용

 

CREATE TABLE purchase(no INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, cust_id VARCHAR(20) NOT NULL, date DATETIME NOT NULL, product VARCHAR(10) NOT NULL);

→ purchase 테이블 생성

 

SHOW DATABASES;

 

INSERT INTO customer(id, name, age, address) VALUES('xyz', 'Park',30,'Seoul');

INSERT INTO customer(id, name, age, address) VALUES('abc','Go',20,'Busan');

→ customer 테이블에 임의의 row 값 입력

 

SELECT * FROM customer;

→ 데이터 확인

 

DB 서버에 계정 생성

GRANT ALL ON shopping_db.* to itwill@'localhost' identified by '1234';

→ 해당 서버 내에서 접속하는 itwill 계정에 대해 shopping_db 데이터베이스의 모든 테이블들에 CRUD 권한을 주겠다.

flush privileges;

→ 변경된 내용을 적용

 

exit를 입력해서 root 계정을 로그아웃

---

mysql -u itwill -p

1234

→ itwill 계정 로그인 확인

 

SHOW databases;

→ shoppng_db는 보이지만 mysql 데이터베이스(스키마)는 보이지 않음. mysql은 root 유저만 볼 수 있음

 

윈도우에서 Workbench로 접속 시 itwill로 접속하도록 하려면?

DB서버에서 윈도우 IP 주소에서 접속하는 itwill 계정에 대한 허가를 DB 서버에 설정

grant all on shopping_db.* to itwill@'IP 주소' identified by '1234';

ERROR 1044 (42000): Access denied for user 'itwill'@'localhost' to database 'shopping_db'

단, grant 관련 설정은 'root' 사용자만 할 수 있음

exit 후

mysql -u root -p

1234

grant all on shopping_db.* to itwill@'IP 주소' identified by '1234';

flush privileges;