Post

SQL_3 (AWS 활용)

Posted
By 고준환 J.K
9 min read

🙅‍♂️휴대폰으로 볼 때 혹시 글자나 숫자가 화면에 다 안나오면, 휴대폰 가로로 돌리시면 됩니다

1
2
3
4
5
6
7

<목차>

1. AWS 활용
2. SQL 파일 연동
3. 백업
4. AWS와 연동
5. 실습

1. AWS 활용

MySQL RDS 외부 접속을 합니다 http://portal.aws.amazon.com/billing/signup#/start
우선 이 링크에서 회원가입을 합니다. 완료 후에는 원격으로 mysql 접근합시다

why?
—> MySQL Command Line Client 를 실행하면 바로 비밀번호를 입력해야 해서 로컬접속으로 되기에 명령프롬프트로 실행했습니다

cmd창에 들어가기

Desktop View

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 경로 이동
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

# 로그인
mysql - h <엔드포인트> -P <포트> -u <마스터 사용자 이름> -p
⬇️
mysql -h "아마존 고유 주소" -P 3306 -u admin -p

그리고 암호 입력했습니다

# 확인
show databases;

# mysql 사용
use mysql;

# mysql의 tables 보기
show tables;

# host, user라는 컬럼을 user라는 테이블에서 가져옴
select host, user from user;





2. SQL 파일 연동

1
2
3
4
5
6
7
8
9
10
11
12

# 우선 cmd 창을 열어 아래 경로로 갑니다
cd C:\Users\withj\Documents

# sql_ws 라는 폴더 만듭니다, 그리고 이동
mkdir sql_ws
cd sql_ws

# vscode 실행
code .

# 새파일 만들기
test1.sql


2-1) vscode에서 작성

1
2
3
4
5
6
7

# test1.sql

CREATE TABLE police_station
(
    name varchar(16),
    address varchar(128)
);

1
2
3
4
5
6
7
8
9
10

# test2.sql

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


2-2) ctr shift ` (터미널 열기)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# vscode terminal
mysql -u root -p --default-character-set=utf8mb4

# db 확인
show databases;

# zerobase 쓰기
use zerobase;

# 파일 실행
source 경로/파일명
⬇️
source C:/Users/withj/Documents/sql_ws/test1.sql

# 파일 확인(police_station 있을 것임)
show tables;

# 새파일 확인
desc police_station;

# 닫기
exit;


test2.sql 추가

1
2
3
4
5
6
7
8
9

# vscode 터미널에서 mysql 로그아웃하기
exit;

# zerobase에 test2.sql 파일을 넣어줘라
GET-Content test2.sql | mysql -u root -p zerobase

# db 사용후 crime_status 확인
use zerobase;
desc crime_status;





3. 백업

로그아웃하고 시작

1
2
3
4
5
6
7
8
9
10

#1 특정 db 복제
#2 전체 db 복제
mysqldump -u 유저명 -p db이름 > 파일명 --default-character-set=utf8mb4
mysqldump -u 유저명 -p --all-databases > 파일명 --default-character-set=utf8mb4
⬇️
# `zerobase` 데이터베이스의 내용을 zerobase.sql에 복제(백업)
mysqldump -u root -p zerobase > zerobase.sql --default-character-set=utf8mb4

# 파일들 확인
ls





4. AWS와 연동

cmd창

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 경로이동
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

# aws 로그인
mysql -h "아마존 주소" -P 3306 -u admin -p

#확인
show databases;
use zerobase;
SELECT host, user FROM mysql.user;

# admin에 권한 부여
GRANT ALL PRIVILEGES ON zerobase.* TO 'admin'@'%';

# aws로 로컬에 있던 zerobase db의 모든 table 내용을 백업하고 `zerobase.sql`에 저장 
source zerobase.sql

# 확인
show tables;





5. 실습

5-1) 예제1

로컬에 있는 DB 중 2개 테이블인 celab.sql, snl_show을 복사본으로 추출

1
2
3
4
5
6
7
8
9
10
11

# 경로이동
cd C:/Users/withj/Documents/sql_ws

# `zerobase` 데이터베이스의 celab 테이블을 celab.sql에 복제(백업)
# `zerobase` 데이터베이스의 snl_show 테이블을 snl.sql에 복제(백업)

# celab.sql 전체 복사
# snl.sql 컬럼만 복사
# vscode 좌측에 보면 celab.sql, snl.sql 생성됨
mysqldump -u root -p zerobase celab> celab.sql --default-character-set=utf8mb4
mysqldump -d -u root -p zerobase snl_show > snl.sql --default-character-set=utf8mb4


아까 만든 celab.sql, snl_show aws에 넣기

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# aws 로그인
mysql -h "아마존 주소" -P 3306 -u admin -p --default-character-set=utf8mb4

# celab 삭제
use zerobase;
show tables;
drop table celab;

# 전송
source celab.sql;

# 최종 확인
show tables;
SELECT * FROM celab;


5-2) 예제

1
2

# 이 경로에서 시작 
cd C:\Program Files\MySQL\MySQL Server 8.0\bin

(1) 백업

zerobase에 있는 모든 table을 backup_zerobase.sql에 백업

1

mysqldump --set-gtid-purged=OFF -h "아마존주소" -P 3306 -u admin -p zerobase > backup_zerobase.sql

(2) zerodb를 만드세요

1
2
3
4
5
6

# 먼저 확인(중복 여부)
show databases;

# 생성후 확인
create database zerodb default character set utf8mb4;
show databases;

(3) zero@%에 모든 zerodb의 권한을 부여하세요

1
2
3
4

grant all on zerodb.* to 'zero'@'%';

# zero라는 외부접속자에 대한 권한 확인 
show grants for 'zero'@'%';

(4) 앞서 백업한 zerobase 백업파일을 zerodb에서 실행하세요

1
2
3
4
5
6
7
8
9
10
11

# zerodb 사용
use zerodb;

# table 비었는지 차있는지 확인
show tables;

# 백업본.sql 생성
source backup_zerobase.sql

# 잘 들어왔나 확인
show tables;

(5) police_station 테이블에 아래 데이터를 INSERT 하기 위한 SQL 파일을 생성하세요

1
2
3
4
5
6
7

# 우선 vscode에 insert.sql에 들어갈 내용
(테이블이름: police_station)

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');

(6) SQL 파일을 실행하여 AWS RDS (database-1) zerobase의 police_station 테이블에 데이터를 INSERT하고 확인하세요

1
2
3
4
5
6
7
8
9
10
11
12

# 이때까지 zerodb에서 작업을 했으니 이제 zerobase로 이동
use zerobase;
show tables;

# 비었나 확인
SELECT * FROM police_station;

# zerodb의 police_station 테이블에 insert.sql 내용을 추가할 것임
source insert.sql;

# 잘 들어왔나 확인
SELECT * FROM police_station;

(7) AWS RDS (database-1) zerobase의 police_station 테이블을 SQL 파일로 백업하세요

1
2
3
4
5
6

# mysql 로그아웃
exit;

# zerobase db의 police_station 테이블만 백업본 생성
# (vscode 좌측에 backup_police.sql 생성됨)
mysqldump --set-gtid-purged=OFF -h "아마존주소" -P 3306 -u admin -p zerobase police_station > backup_police.sql

(8) SQL 파일을 실행하여 AWS RDS (database-1) zerodb의 police_station 테이블을 zerobase와 동일하게 만들고 확인하세요

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 로그아웃
exit;

# 로그인
mysql -h "아마존 주소" -P 3306 -u admin -p --default-character-set=utf8mb4

# 확인
(police_station 테이블에 아마 비어있을 겁니다)
show databases;
use zerodb;
show tables;
select * from police_station;

# police_station 테이블에 data 추가후 확인
source insert.sql;
select * from police_station;
language, sql
sql 제로베이스
This post is licensed under CC BY 4.0 by the author.
3D GIF