[031] AWS RDS와 SQL File / SQL 심화 - ①

이연희·2023년 9월 22일

Chapter
1. AWS RDS
(1) 계정 생성 및 데이터베이스 설정
(2) MYSql에 외부 접속하기
2. SQL File
(1) SQL File 생성 및 실행
(2) 데이터베이스 백업과 복원
(3) 테이블 백업과 복원

1. AWS RDS

(1) 계정 생성 및 데이터베이스 설정

지난 시간까지 SQL 기초과정을 마치고, 이제는 좀 더 심화된 내용을 배우게 되었다. 이번 시간에는 AWS에서 제공하는 관계형 데이터베이스 서비스를 이용하여 coul상에 database를 구축해본다.

AWS 서비스는 처음이용해보는 것이어서 회원가입과 개인설정까지 전부 마친 다음, 데이터베이스까지 만들어 외부 접속 또한 가능케 설정해주는 기초작업을 해주었다.(https://portal.aws.amazon.com/billing/signup#/start/email)

설정할 때 알아두면 좋은 주의점이 몇 가지 있다.

계정생성할 때에는 결제수단을 선택해야 회원가입이 완료됨
'탬플릿'을 '프리티어'로 선택하면 무료버전이 사용하능
하지만, 시간제한이 있으므로 데이터베이스를 사용하지 않을때는 7일 일시중지를 하는 것이 바람직함.
마스터 암호는 반드시 기억해 둘 것
스토리지 자동 조정은 비활성화를 해둬야 모르는 새에 요금청구되는 일을 막을 수 있음
퍼블릭 액세스는 활성화하면 외부 접근이 가능함.
보안 및 규칙 > 인바운드 규칙에서 새로이 'MYSQL/Aurora'로 유형을 설정해 보안 설정하기
데이터베이스는 사용하지 않는다면 데이터베이스 '일시중지(7일)'로 설정해두기. 재사용할 때 다시 시작하면 무료 사용 제한 시간을 헛되이 흘려보내지 않을 수 있음

(2) MYSql에 외부 접속하기

이제 외부 접근 설정까지 마쳤다면 외부접속을 해본다.
지난시간까지 cmd로 mysql을 접속할 때는 다음과 같은 코드를 사용했다.

mysql -u root -p비밀번호

하지만 AWS RDS로 만든 데이터베이스를 이용해 mysql에 외부 접속하려면 부여된 정보를 이용해 코드를 작성해야 한다.

mysql -h "엔드포인트” -P 포트번호 -u 사용자이름 -p비밀번호

이제 새로운 데이터베이스와 외부 유저를 설정해주자.

베이스 데이터베이스 생성 (DEFAULT CHARACTER SET utf8mb4)

# utf8mb4: 한글+이모지 사용 설정
CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;

#확인
SHOW DATABASES

외부 사용자(zero) 추가

# 유저 생성
CREATE USER ‘zero’@’%’ IDENTIFIED BY ‘1234’

# 유저 확인
USE mysql;
SELECT host, user FOR user;

사용자에게 외부에서 zerobase에 접근해서 사용하기 위한 권한 부여.

# ‘zero’권한 확인
SHOW GRANTS FOR ‘zero’@’%’;

# 모든 권한 부여
GRANT ALL ON zerobase.* TO ‘zero’@’%’;

# 확인
SHOW GRANTS FOR ‘zero’@’%’;

.
.
.
.

2. SQL File

sql file은 sql쿼리를 모아놓은 파일로, 이번에는 SQL file을 여러 개 만들어서 다양한 작업을 해보자.

(1) SQL File 생성 및 실행

이번 실습은 작동을 좀더 편리하게 하고 결과를 쉽게보기 위해서 VSCode를 사용했다.

먼저 sql file을 모아놓을 새로운 폴더를 만들어서 그 안에서 VSCode를 작동시켰다. 시스템이 실행되었다면 새로운 파일을 만들 때 확장자명에 '.sql'를 입력하면 새로운 sql파일을 생성할 수 있다.

1) 로컬환경에서 실행하기

'police_station'테이블이 든 sql file(test01.sql)을 만들어서 로컬환경의 zerobase 데이터베이스에서 불러와보자.
다음과 같이 테이블 구조 생성 코드가 든 파일을 먼저 만들었다.

그 다음 zerobase db를 사용해서 코드 파일을 실행했다.

use zerobase;

# 파일 실행
source test01.sql

2) 외부에서 실행하기

이번에는 외부환경에서 sql파일을 바로 실행해보자.

먼저 새로운 sql file(test2.sql)을 만들어서 서울시 관서별 5대 범죄 현황 정보를 테이블을 만들었다.

cmd에서 mysql로 진입할 때 바로 파일명까지 적어주면 되는데, 실행할 db 옆에 '> 실행파일.sql' 을 적으면 된다.

mysql -u 유저이름 -p db이름 < 실행파일.sql

테이블을 확인해보자.

.
.

(2) 데이터베이스 백업과 복원

이번에는 sql파일을 사용해서 db를 백업해보자.
다음과 같이 특정 db만을 백업할 수도 있고, 모든 db를 백업할 수도 있다.

mysqldump -u username -p dbname > backup.sql  #특정 db backup
mysqldump -u username -p --all-databases > backup.sql  # 모든 db backup

우선 zerobase.sql을 만들어서 zerobase데이터베이스를 백업해보자.

mysql -u root -p zerobase > zerobase.sql

다음과 같이 백업파일을 확인할 수 있다. 그런데 코드를 살펴보면 기존에 만들어졌던 테이블을 drop시킨다음에 새로 만들어서 실행된다는 것을 살펴볼 수 있다.

이제 데이터베이스를 복원해보자. 복원할 때에는 sql file을 실행하는 것으로 간단하게 해결할 수 있다.

AWS RDS서비스를 이용해서 데이터베이스를 복원해보자.
먼저 접속한 다음, 기존에 있던 zerobase db에 sql파일을 사용해서 로컬 db와 똑같게 복원해주었다.

복원이 완료되었으면 로컬 zerobase db와 테이블 목록이 같은지 확인한다.

복원이 잘 되었음을 확인할 수 있다.
.
.

(3) 테이블 백업과 복원

데이터베이스뿐만 아니라 그 안의 하나의 테이블만을 복원할 수도 있다. 마찬가지로 sql파일을 이용한다.

mysql -u 유저이름 -p db이름 table이름 > 복원파일.sql

로컬 zerobase 데이터베이스의 celeb 테이블을 백업시켜보자.

mysql -u root -p zerobase celeb > celeb.file

복원파일이 확인이 되었으면 이번에도 AWS RDS의 zerobase db를 이용해서 테이블을 복원해본다.
먼저 이전에 로컬 db를 복원해서 테이블이 이미 존재하므로 celeb테이블을 삭제한 다음 복원시켰다.

# 이미 존재하는 celeb 테이블 확인
Show tables;

# celeb 테이블 제거
DROP celeb;

# celeb 테이블 복원
source celeb.sql

# 복원 확인
show tables;

# 구조와 전체 데이터 확인
desc celeb;
select * from celeb;