[SQL] 12-SQL File

📍 실습환경 세팅 - workspace 생성

• sql_ws 폴더를 만들고 그 하위에서 실습

% cd Documents
Documents > mkdir sql_ws
Documents > cd sql_ws
sql_ws > code . 	 	# 해당 위치에서 VSCode 실행

📍 SQL File 생성 방법
1) VSCode의 sql_ws 폴더에서 새 파일 선택
2) 생성할 파일 이름 지정 및 파일 확장자를 sql로 입력하면 비어있는 SQL 파일 생성

---

📂 SQL File

• SQL 쿼리를 모아놓은 파일
• 파일 내에 실행할 쿼리를 모아 작성 후 쿼리들을 한번에 실행 가능
  
  

📍 실행방법1. 로그인 이후 (mysql 접속)

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

1) test01.sql 파일에 경찰서 정보 테이블 생성 쿼리 작성

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

2) sql_ws 폴더 위치로 이동 후 zerobase db로 접속

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

3) SQL File 실행 및 결과 확인

mysql> source test01.sql
mysql> desc police_station;

📍 실행방법2. 외부에서 바로 실행 (mysql 미접속)

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

1) test02.sql 파일에 서울시 관서별 5대 범죄 현황정보 테이블 생성 쿼리 작성

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

2) sql_ws 폴더 위치에서 zerobase db 접속하면서 sql 파일 실행

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

3) zerobase db 접속하여 결과 확인

sql_ws > mysql -u root -p zerobase
mysql> desc crime_status;

---

📂 Database Backup

📍 SQL File로 db 백업 가능

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

• local의 zerobase db를 백업

sql_ws > mysqldump -u root -p zerobase > zerobase.sql

---

📂 Database Restore

📍 데이터베이스를 백업한 SQL File을 실행하여 그 시점으로 복구하거나 이전 가능 (SQL File을 실행하는 방법과 동일)

1) AWS RDS 서비스가 '사용 가능' 상태에서 접속

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

2) db 목록 확인 후 zerobase db로 이동

SHOW databases;
USE zerobase;

3) AWS RDS(database-1)의 zerobase db에 백업해놓은 zerobase.sql 파일 실행하여 복원

source zerobase.sql

4) 복원 결과 확인

SHOW TABLES;

---

📂 Table Backup

📍 Table 단위로도 백업 가능 (테이블 하나만 백업하고 싶을때)

sql_ws > mysqldump -u username -p dbname tablename > backup.sql

• local에서 celeb 테이블 백업

sql_ws > mysqldump -u root -p zerobase celeb > celeb.sql

---

📂 Table Restore

📍 테이블을 백업한 SQL File을 실행하여 해당 테이블을 복구하거나 이전 가능 (SQL File을 실행하는 방법과 동일)

1) AWS RDS(database-1)의 zerobase에서 celeb 테이블 삭제

sql_ws > mysql -h 엔드포인트 -P 3306 -u admin -p zerobase	# -p DB이름 : 바로 해당 DB로 이동 가능
mysql> drop table celeb;

2) AWS RDS(database-1)의 zerobase에서 celeb.sql 실행하여 테이블 복구

mysql> source celeb.sql

3) 복원 결과 확인

mysql> select * from celeb;

---

📂 Table Schema Backup

📍 데이터를 제외하고 테이블 생성 쿼리만 백업

# -d 옵션 : 데이터를 제외한다는 의미
% mysqldump -d -u username -p dbname tablename > backup.sql		# 특정 table schema 백업
% mysqldump -d -u username -p dbname > backup.sql				# 모든 table schema 백업

• local에서 snl_show 테이블 schema 백업

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

---

실습

1. AWS RDS(database-1)의 zerobase 데이터베이스 백업
   ✔ AWS RDS database를 백업하는 경우 --set-gtid-purged=OFF 옵션 추가

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

2. AWS RDS(database-1)에 admin 계정으로 로그인

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

3. DB(zerodb) 생성 (DEFAULT CHARACTER 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에서 실행

USE zerodb;
source backup_zerobase.sql;
show tables;

6. police_station 테이블에 데이터를 INSERT 하기 위한 SQL 파일 생성 (insert.sql)

INSERT INTO police_station VALUES ('name값', 'address값');

7. SQL 파일 실행하여 AWS RDS zerobase의 police_station 테이블에 데이터 insert하고 확인

USE zerobase;
source insert.sql
select * from police_station;

8. police_station 테이블을 SQL 파일로 백업

mysqldump --set-gtid-purged=OFF -h 엔드포인트 -P 3306 -u admin -p zerobase police_station > backup_police.sql   

9. AWS RDS zerodb의 police_station 테이블을 zerobase와 동일하게 만들고 확인

use zerodb;
source insert.sql
select * from police_station;