1. 실습환경 만들기

Workspace 생성

• sql_ws 폴더를 만들고 그 하위에서 실습하기로 합니다.

Document % mkdir sql_ws
Document % cd sql_ws
sql_ws %

# cmd 창에서

cd Documents   # Documents (내문서)로 이동
mkdir sql_ws   # sql_ws 폴더를 만듦
cd sql_ws      # sql_ws 폴더로 이동
code .         # sql_ws 폴더에서 vscode 실행 : vscode 가 실행됨

SQL File 생성 방법

• sql_ws 폴더 하위에서 다음을 실행합니다. (현재 폴더를 VSCode 로 열라는 뜻)

sql_ws % code .

• VSCode 의 sql_ws 폴더에서 새파일(New File)을 선택합니다.

• 생성할 파일 이름을 정하고, 파일 확장자를 .sql 로 입력하면 비어있는 SQL 파일이 생성됩니다.

• test01 이라는 sql 파일을 만들었다.

2. SQL File

• SQL 쿼리를 모아놓은 파일

SQL File 실행

SQL File 실행 1 - 로그인 이후

• 로그인한 상태에서 실행하는 방법

mysql> source </path/filename.sql>
mysql> \. </path/filename.sql>    # source 대신 \. 사용 가능
mysql> \. <filename.sql>  # 현재 폴더에 파일이 있으면 path 생략 가능 

• 경찰서 정보를 테이블로 만들어봅니다(vscode 에서 작성)
  (EDA 에서 범죄현황데이터 등을 이용할 예정)
  (저장은 Ctrl + S)

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

---

• 아래부터는 다시 터미널에서 작성
  (vscode 상단 terminal > new terminal 하면 아래에 터미널 창 열림. 여기서 아래 터미널 오른쪽 아래 화살표 > split terminal > git bash 가 있는데, git bash 를 쓰면 더 편하다고 함)

• mysql -u root -p

# vscode cmd 창에서
mysql -u root -p

show databases;

(zerobase 테이블이 보인다)

• zerobase 테이블이 없다면 만들자

create database zerobase default character set utf8mb4;

• (다시 수업으로 돌아와서) sql_ws 폴더 위치로 이동하여 zerobase 에 접속합니다.

% cd sql_ws
sql_ws % mysql -u root -p zerobase

use zerobase #  난 이렇게 들어감

차이를 구분하자
mysql -u root -pzerobase : -pzerobase 이렇게 붙이면
zerobase 를 비밀번호로 인식하고,

mysql -u root -p zerobase : -p zerobase 이렇게 띄우면
zerobase라는 이름의 데이터베이스로 인식한다.
그래서 이렇게 쓰면
use zerobase 처럼 zerobase 라는 데이터베이스에 바로 접속하는게 된다

• SQL File 을 실행합니다.
  (zerobase 에 test01 table이 생성될 것)

mysql> source test01.sql  
# source 다음에 경로를 써줘야하는데 현재 경로에 파일이 
# 있기 때문에 source 다음에 바로 파일 이름을 씀

> Query OK, O rows affected (0.05 sec)

• 결과를 확인합니다.

SQL File 실행 2 - 외부에서 바로 실행

• mysql 에 접속하지 않은 상태에서 sql file 바로 실행
  (터미널에서 sql file 바로 실행한다는 의미)

mysql -u username -p <database> < </path/filename.sql>

• 우선 기존 MySQL 연결을 종료합니다.

mysql>exit

> Bye

• vscode 에서 test02.sql 을 만든다
• 서울특별시 관서별 5대 범죄 현황 정보를 테이블로 만들어봅니다.

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

• zerobase Database 에 접속하면서 바로 SQL File 을 실행합니다.

sql_ws % mysql -u root -p zerobase < test02.sql

mysql -u root -p zerobase < test02.sql 
# vscode 터미널에서 위대로 넣음

• Zerobase 에 접속하여 결과를 확인합니다.

3. Database Backup

• SQL File 로 Database 를 백업할 수 있다.

# 특정 Database 백업 mysqldump, >
% mysqldump -u username -p dbname > backup.sql
# 모든 Database 백업 --all
% mysqldump -u username -p --all-databases > backup.sql

Database Backup 예제

• zerobase Database Backup

# 특정 Database 백업
% mysqldump -u root -p zerobase > zerobase.sql

• VSCode 에서 zerobase.sql 확인(열어보자)
  ( 백업 파일을 구경해보자)

참고. AWS RDS 서비스 시작

• AWS 콘솔에 로그인

• 서비스 > RDS 선택

• RDS 관리 페이지 > 데이터베이스 - 중지된 상태

• database-1 선택 > 시작

• database-1 시작 중 - 오래걸립니다. (새로고침으로 상태 확인)

• database-1 사용 가능

4. Database Restore (복구)

• 로컬에서 저장했던 백업 파일을 AWS 리모트 데이터베이스로 보내보자

• 데이터베이스를 백업한 SQL File 을 실행하여 그 시점으로 복구하거나 이전 할 수 있다.
  (SQL File 을 실행하는 방법과 동일함, 즉 실행하면 자동으로 복구됨, 없는 곳에 실행하면 이전하는 것처럼 됨)

1. CMD 창에서 AWS RDS 접속: mysql -h ~~
2. show databases; 으로 데이터베이스 확인
3. use zerobase; 처럼 특정 데이터베이스로 이동
4. source zerobase.sql 처럼 위에서 만든 백업파일을 실행
5. show tables; 로 확인

Database Restore 예제

• AWS RDS (database-1) 서비스가 사용 가능한 상태에서 접속

sql_ws % mysql -h database-1.cwzfvdhk3vok.ap-northeast-2.rds.amazonaws.com -P 3306 -u
admin -p

• zerobase Database 이동

• AWS RDS (database-1) 의 zerobase Database 를 복원

• 결과 확인

5. Table Backup

• Table 단위로도 백업할 수 있다

sql_ws % mysql -u username -p dbname tablename > backup.sql

Table Backup 예제

• Local Database 에서 celeb Table 을 백업합니다.
  (vscode 에 celeb.sql 파일이 새로 만들어짐)

sql_ws % mysql -u root -p zerobase celeb > celeb.sql

• VSCode 에서 celeb.sql 파일을 확인합니다

6. Table Restore (복구)

• Table 을 백업한 SQL File 을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다.

  • (SQL File 을 실행하는 방법과 동일함, 즉 실행하면 자동으로 복구됨, 없는 곳에 실행하면 이전하는 것처럼 됨)

Table Restore 예제

• AWS RDS (database-1) 서비스가 사용 가능한 상태에서 접속

• AWS RDS (database-1) 의 zerobase 에서 celeb 테이블을 삭제합니다

sql_ws % mysql -h database-1.cwzfvdhk3vok.ap-northeast-2.rds.amazonaws.com -P 3306 -u
admin -p

...
mysql > drop table celeb;

• AWS RDS (database-1) 의 zerobase 에서 celeb 테이블을 복구

mysql> source celeb.sql

• 결과 확인

7. Table Schema Backup

• 데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다.
• -d 옵션을 추가함

# 특정 Table Schema 백업
% mysqldump -d -u username -p dbname tablename > backup.sql
# 모든 Table Schema 백업
% mysqldump -d -u username -p dbname > backup.sql

Table Schema Backup 실습

• Local Database 에서 snl_show Table Schema 백업

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

• VSCode 에서 snl.sql 생성 확인 (Data 는 포함하지 X)

문제

• 문제 1. AWS RDS (database-1) 의 zerobase 데이터베이스를 백업하세요.
  (주의> AWS RDS database 를 백업할 경우 다음의 옵션을 추가합니다.)

mysqldump --set-gtid-purged=OFF -h "엔드포인트" -P 3306
-u admin -p비번 zerobase > backup_zerobase.sql

backup_zerobase.sql 이 생김.

• 문제 2. AWS RDS (database-1) 에 admin 계정으로 로그인하세요.

# cmd 창에서
mysql -h "엔드포인트" -P 3306 -u admin -p

• 문제 3. Database (zerodb) 를 생성하세요.
  (DEFAULT CHRACTER SET utf8mb4)

create database zerodb default character set utf8mb4;

• 문제 4. 앞서 생성한 사용자 (zero@%) 에게 zerodb 의 모든 권한을 부여하세요.

# 권한 부여하기
grant all on zerodb.* to 'zero'@'%';
# 권한 확인하기
show grants for 'zero'@'%';

• 문제 5. 앞서 백업한 zerobase 백업 파일을 zerodb 에서 실행하세요.

# zerodb 로 이동
use zerodb;
# 테이블 확인
show tables;
# 백업파일 실행
source backup_zerobase.sql
# 테이블 확인
show tables;

• 문제 6. police_station 테이블에 아래 데이터를 INSERT 하기 위한 SQL 파일을 생성하세요.

1. vscode > sql_ws > new file 로 insert.sql 이라는 파일을 만든다.

2. insert.sql 안에 아래와 같이 작성한다. 끝
   

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

• 문제 8. AWS RDS (database-1) zerobase 의 police_station 테이블을 SQL 파일로 백업하세요.

(일단 exit)

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

---

위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.