SQL File

• SQL File은 SQL 쿼리를 모아놓은 파일이다.

1. 실습환경 setting

1) cmd 창에서 VS Code 실행

cd Documents
mkdir sql_ws
cd sql_ws
code .

2) test01.sql 생성

create table police_station
(
    name varchar(16),
    address varchar(128)
);

3) VS Code에서 Terminal 실행

mysql -u root -p

2. SQL File 실행 1 - 로그인 이후

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

source test01.sql
show tables;
desc police_station;

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

1) 서울특별시 관서별 5대 범죄 현황 정보를 테이블로 생성

create table crime_status
(
    year YEAR,
    police_station varchar(8),
    crime_stype varchar(16),
    status_type char(2),
    case_number int
);

exit;

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

show databases;
use zerobase;
desc crime_status;

4. Datebase Backup

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

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

• zerobase DB backup

mysqldump -u root -p zerobase > zerobase.sql

• VS Code에서 확인
  

5. Datebase Restore

• Database를 backup한 SQL File을 실행하여 그 시점으로 복구하거나 이전할 수 있다.
  (SQL File을 실행하는 방법과 동일)

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

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

2) zerobase DB로 이동

show databases;
use zerobase;

3) AWS RDS(database-1)의 zerobase DB 복원

source zerobase.sql

4) 결과 확인

show tables;

6. Table Backup

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

# 문법
mysqldump -u username -p dbname tablename > backup.sql             # 특정 DB backup

• local DB에서 celeb table 백업

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

7. Table Restore

• Table을 backup한 SQL File을 실행하여, 해당 table을 복구하거나 이전할 수 있다.
  (SQL File을 실행하는 방법과 동일)

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

mysql -h "엔드포인트" -P 3306 -u admin -p zerobase
show tables;

drop table celeb;
show tables;

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

source celeb.sql
show tables;

3) 결과 확인

desc celeb;
select * from celeb;

8. Table Schema Backup

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

# 문법
mysqldump -d -u username -p dbname tablename > backup.sql         # 특정 Table Schema Backup
mysqldump -d -u username -p dbname > backup.sql                   # 모든 Table Schema Backup

• Local DB에서 snl_show Table Schema 백업

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

• VS Code에서 snl.sql 확인 (Data는 포함하지 X)
  

9. 실습 (문제풀이)

1. AWS RDS (database-1)의 zerobase 데이터베이스를 백업하세요.

(주의> AWS RDS database 를 백업할 경우 다음의 옵션을 추가)
mysqldump --set-gtid-purged=OFF -h <hostname> -P <port> -u <username> -p <databasename> > <filename>.sql

cd Documents
cd sql_ws
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. Database (zerodb)를 생성하세요.

(DEFAULT CHRACTER SET utf8mb4)

show databases;
create database zerodb default character set utf8mb4;
show databases;

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

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

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

use zerobase;
source backup_zerobase.sql
show tables;

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

# VSCode로 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');

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

use zerobase;
show tables;
select * from police_station;
source insert.sql

select * from police_station;

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

exit

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

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

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

show databases;
use zerodb;
show tables;
select * from police_station;

source insert.sql
select * from police_station;