SQL - AWS RDS, SQL File : 데이터 취업 스쿨 스터디 노트 12/22

1. AWS RDS

• Amazon Relational Database Service
• 아마존 웹 서비스에서 제공하는 관계형 데이터베이스 서비스
• 클라우드 상에 데이터베이스 구축해보기

1-1. AWS RDS 생성

• 인스턴스 구성 : db.t2.micro
• 스토리지 자동 조정 : n
• 퍼블릭 엑세스 : y
• 자동 백업 활성화 : n
• 삭제 방지 활성화 : y

퍼블릭 엑세스 가능

• "DB 인스턴스를 호스팅하는 VPC 외부에서 EC2 인스턴스 및 디바이스를 DB 인스턴스에 연결하려면 Yes를 선택합니다. No를 선택할 경우 Amazon RDS가 DB 인스턴스에 퍼블릭 IP 주소를 할당하지 않으며, VPC 외부의 EC2 인스턴스 또는 디바이스를 연결할 수 없습니다."
• RDS에 어떻게 접근할지에 따라서 활성화 or 비활성화
• AWS에 EC2라는 가상 PC도 설정해두고, 그 PC에서 RDS에 접근하겠다면 퍼블릭 엑세스를 하지 않아도 된다.(외부에서 RDS에 직접 접근하지 않는다면)
• 수업에서는 외부에서 RDS에 접근할 예정이라 퍼블릭 엑세스를 활성화 해주어야 한다.

1-2. AWS RDS 접속

• RDS > 데이터베이스 > database-1 > 연결 & 보안 : 엔드포인트 및 포트 정보
• 원격으로 MySQL 접근하기

mysql -h "엔드포인트" -P 3306 -u admin -p

MySQL 환경 변수 설정 방법

1-3. AWS RDS 중지 및 시작

• 중지 : RDS > 데이터베이스 > DB 식별자 선택 후 [작업] - [일시적으로 중지]
• 시작 : RDS > 데이터베이스 > DB 식별자 클릭 후 [작업] - [시작]
• 수정 : RDS > 데이터베이스 > DB 식별자 선택 후 [수정]

1-4. 추가 실습

# 데이터베이스 생성
create database zerobase default character set utf8mb4;

# 사용자 추가
# 외부 접속 ➡ % 옵션
create user 'zero'@'%' identified by '1234';

# 사용자 정보 확인
use mysql;
select host, user from user;

# 새로고침
flush privileges;

# 사용자에게 외부에서 zerobase에 접근해서 사용하기 위한 권한 부여
grant all on zerobase.* to 'zero'@'%';

# 권한 확인
show grants for 'zero'@'%';

# 권한 삭제
revoke all on zerobase.* to 'zero'@'%';

# 접속 종료
exit

# AWS RDS (database-1)의 zerobase에 zero로 접속하기
mysql -h "엔드포인트" -P 3306 -u zero -p

2. SQL File

2-1. sql_ws 폴더에서 VSCode 실행하기

# Documents로 이동
cd Documents

# sql_ws 폴더 만들기
mkdir sql_ws

# sql_ws로 이동
cd sql_ws

# vscode 실행
code .

2-2. sql 파일 실행

MySQL에 접속해서 파일을 실행하는 방법

# test01.sql
CREATE TABLE police_station
(
    name varchar(16),
    address varchar(128)
);

Command Prompt 또는 Git Bash 터미널을 열어준다.

# 터미널에서
mysql -u root -p

use zerobase;
source test01.sql

desc police_station;

>>>
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(16)  | YES  |     | NULL    |       |
| address | varchar(128) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

exit

외부에서 바로 실행하는 방법
(MySQL에 접속해서 파일을 실행하는 게 아니라, 터미널 단계에서 파일을 실행함)

# test02.sql
CREATE TABLE crime_status
(
    year YEAR,
    police_station varchar(8),
    crime_type varchar(16),
    status_type char(2),
    case_number int
);

# 터미널에서
mysql -u root -p zerobase < test02.sql

# 비밀번호 입력 후 mysql 접속
mysql -u root -p

use zerobase;
desc crime_status;

>>>
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year           | year        | YES  |     | NULL    |       |
| police_station | varchar(8)  | YES  |     | NULL    |       |
| crime_type     | varchar(16) | YES  |     | NULL    |       |
| status_type    | char(2)     | YES  |     | NULL    |       |
| case_number    | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

exit

3. database backup & restore

# backup
mysqldump -u root -p zerobase > zerobase.sql

데이터베이스를 백업한 sql 파일을 실행하여 그 시점으로 복구하거나 이전할 수 있다.

# AWS RDS 서비스 사용
# restore
mysql -h "엔드포인트" -P 3306 -u admin -p
use zerobase;

source zerobase.sql

4. table backup & restore

테이블 단위로도 백업할 수 있다.

# backup
mysqldump -u root -p zerobase celeb > celeb.sql

테이블을 백업한 sql 파일을 실행하여 그 시점으로 복구하거나 이전할 수 있다.

# restore
mysql -h "엔드포인트" -P 3306 -u admin -p
use zerobase;

# celeb 테이블 삭제
drop table celeb;

source zerobase.sql

5. table schema backup

데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다.

mysqldump -d -u root -p zerobase snl_show > snl.sql

6. 연습문제

6-1. AWS RDS database backup

# backup
mysqldump --set-gtid-purged=OFF -h "엔드포인트" -P 3306 -u admin -p
zerobase > zerobase_aws.sql

# database 생성
create database zerodb default character set utf8mb4;

# user 권한 부여
grant all on zerodb.* to 'zero'@'%';

# backup 파일을 zerodb에서 실행
use zerodb;
source zerobase_aws.sql

6-2. sql 파일 만들고 실행하기

# police_insert.sql 파일 만들기
INSERT INTO police_station VALUES ('서울특별시경찰청', '서울시 종로구 사직로8길 31');
INSERT INTO police_station VALUES ('서울중부경찰서', '서울특별시 중구 수표로 27');
INSERT INTO police_station VALUES ('서울종로경찰서', '서울특별시 종로구 율곡로 46');
INSERT INTO police_station VALUES ('서울남대문경찰서', '서울특별시 중구 한강대로 410');

# sql 파일 실행
source police_insert.sql

6-3. AWS RDS database의 table backup

# backup
mysqldump --set-gtid-purged=OFF -h "엔드포인트" -P 3306 -u admin -p
zerodb police_station > police.sql

# restore
source police.sql