[2023.11.29] SQL_SQL File

- Workspace 생성

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

Document % mkdir sql_ws
Document % cd sql_ws
sql_ws %

- SQL File 생성방법

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

sql_ws % code .
. = 현재위치

C:\Users\User>cd Documents

C:\Users\User\Documents>mkdir sql_ws

C:\Users\User\Documents>cd sql_ws

C:\Users\User\Documents\sql_ws>code .

- VSCode의 sql_ws 폴더에서 새파일 선택

- 생성할 파일이름 정하고, 확장자를 sql로 입력

- SQL File

SQL 쿼리를 모아놓은 파일
특징: 여러개의 쿼리를 모아서 한꺼번에 작성하고 실행가능
사용: 데이터베이스/테이블 백업, 리스토어

- SQL File 실행1 : 로그인 이후

1)

mysql> source </path/filename.sql>

mysql> \. </path/filename.sql> 
# source 대신 \. 사용가능

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

2) 경찰서 정보를 테이블로 만듬

test01.sql

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

3) sql_ws 폴더 위치로 이동해 zerobase에 접속

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

4) SQL File을 실행

mysql > source test01.sql

5) 결과확인

desc police_station

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

1)

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

2) 서울특별시 관서별 5대 범죄 현황 정보를 테이블로 만들어줌

test02.sql

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

3) zerobase DATABASE에 접속하면서 SQL File을 실행

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

4) zerobase에 접속해 결과 확인

desc crime_status

- DATABASE BACKUP

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

% mysqldump -u username -p dbname > backup.sql
# 특정 database Backup

% mysqldump -u username -p --all-databases > backup.sql
# 모든 database Backup

- 예제

1) zerobase database backup

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

2) VSCode 에서 zerobase.sql 확인

- DATABASE RESTORE

- 데이터베이스를 백업한 SQL File을 실행해 그 시점으로 복구하거나 이전할 수 있다.(SQL File을 실행하는 방법과 동일)

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

sql_ws % mysql -h "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" -P 3306 -u admin -p 비밀번호

2) zerobase Database로 이동

show databases;
use zerobase;

3) AWS RDS의 zerobase Database를 복원

source zerobase.sql

4) 확인

show tables;

- TABLE BACKUP

- 테이블단위로도 백업할 수 있다.

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

1) local database에서 celeb table을 백업

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

2) VScode에서 확인
3) table을 백업한 SQL File을 실행해, 해당 테이블을 복구하거나 이전할 수 있다(SQL File을 실행하는 방법과 동일)

4) AWS RDS의 zerobase에서 celeb테이블을 삭제(카피를 하려면 있는걸 먼저 지워줘야)

sql_ws % -h "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" -P 3306 -u admin -p zerobase;
drop table celeb;

5) AWS RDS의 zerobase에서 celeb 테이블을 복구

source celeb.sql

6) 확인

select * from celeb

- Table Schema Backup

- 데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다

% mysqldump -d -u username -p dbname tablename > backup.sql 
# 특정 table Schema Backup

% mysqldump -d -u username -p dbname > backup.sql
# 모든 Table Schema Backup

1) local database에서 snl_show table schema 백업

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

2) VSCode에서 확인(데이터는 포함x)

---

문제1. AWS RDS의 zerobase의 데이터베이스를 백업하세요

주의) AWS RDS database를 백업할 경우 다음의 옵션을 추가
mysqldump --set-gtid-purged=OFF

mysqldump --set-gtid-purged=OFF -h "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" -P 3306 -u admin -p zerobase > backup_zerobase.sql

문제2. admin계정으로 로그인

mysql -h "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" -P 3306 -u admin -p 

문제3. database(zerodb)를 생성(DEFAULT CHARACTER SET utf8mb4)

create database zerodb default character set
 utf8mb4;

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

show grants for 'zero'@'%';

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

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

여기부터 오류 뜸 다시 하자 
ㅠㅠㅠㅠㅠ



???
source backup_zerobase.sql

문제6. police_station 테이블에 아래 데이터를 insert 하기 위한 sql파일을 생성하세요(name, address)

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 zerobase의 police_station테이블에 데이터를 insert하고 확인

 source insert.sql

문제8. AWS RDS zerobase의 police_station 테이블을 sql파일로 백업하세요

mysqldump --set-gtid-purged=OFF -h "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" -P 3306 -u admin -p police_station > backup_police.sql

문제9. SQL 파일을 실행해 zerodb의 police_station테이블을 zerobase와 동일하게 만들고 확인하세요

mysql -h "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" -P 3306 -u admin -p --default-character-set utf8mb4
source insert.sql