😢 스터디노트(SQL 12)

실습환경 만들기

• workspace 생성

cd Documetns

mkdir ds_sql_ws

cd ds_sql_ws

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

code .

• sql file 생성

---

SQL File

• sql file : sql 쿼리를 모아놓은 파일
  
  
  ※ 로그인 이후 = vs 터미널에서 mysql 로그인
• (로그인 이후) sql file 실행

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

• 예제1 (로그인 이후) 경찰서 정보를 테이블로 생성
• 예제1 (로그인 이후) sql_ws 폴더 위치로 이동하여 zerobase 에 접속

cd ds_sql_ws

mysql -u root -p

show databases;

use zerobase;

※ 마지막 부분에 db 입력하면 바로 해당 db로 이동됨

mysql -u root -p zerobase

• 예제1 (로그인 이후) SQL File 을 실행

source test01.sql # sql 파일 실행

desc police_station;

• 예제2 (외부에서 바로 실행) sql file 실행

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

• 예제2 (외부에서 바로 실행) sql 연결 종료

exit

• 예제2 (외부에서 바로 실행) 서울특별시 관서별 5대 범죄 현황 정보를 테이블로 생성

• 예제2 (외부에서 바로 실행) zerobase Database 에 접속하면서 SQL File 을 실행

mysql -u root -p zerobase < test02.sql

• 예제2 (외부에서 바로 실행) Zerobase 에 접속하여 결과를 확인

mysql -u root -p 

show databases;

use zerobase;

desc crime_status;

---

Database Backup

• SQL file로 database 백업 가능, 백업한 시점의 데이터가 저장됨

• 특정 database backup

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

• 모든 database backup

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

• zerobase Database Backup

cmd

cd Documents/ds_sql_ws

mysqldump -u root -p zerobase > zerobase.sql

code .

---

Database Restore

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

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

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

• 예제 zerobase Database 이동

show databases;

use zerobase;

• 예제 AWS RDS (database-1) 의 zerobase Database 를 복원 (celeb, snl_show 테이블이 복원된 것을 확인할 수 있음)

source zerobase.sql

 show tables;

• 예제 결과확인

---

Table Backup

• table 단위로도 백업 가능

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

• 예제 Local Database 에서 celeb Table 을 백업

vscode

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

• 예제 VSCode 에서 celeb.sql 파일을 확인
  
  
  

---

Table Restore

• Table을 백업한 SQL File을 실행하여, 해당 테이블을 복구하거나 이전 가능(SQL File을 실행하는 방법과 동일)
  
  
• 예제 AWS RDS (database-1) 의 zerobase 에서 celeb 테이블을 삭제

mysql -h database-1.cuiypimjmxdl.us-east-2.rds.amazonaws.com -P 3306 
-u admin -p zerobase    

show tables; 

drop table celeb; # 테이블 삭제

show tables; 

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

source celeb.sql

• 예제 결과 확인

show tables;

select * from celeb;

---

Table Schema Backup

• 데이터를 제외하고 테이블 생성 쿼리만 백업 가능
• 특정 Table Schema Backup

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

• 모든 Table Schema Backup

mysqldump -d -u username -p dbname > backup.sql

- 예제 Local Database 에서 snl_show Table Schema 백업

vscode

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

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

💡 문제 1. AWS RDS (database-1) 의 zerobase 데이터베이스를 백업

• 주의> AWS RDS database 를 백업할 경우 다음의 옵션을 추가합니다. (--set-gtid-purged=OFF)

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

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

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

💡 문제 3. Database (zerodb) 를 생성(DEFAULT CHRACTER SET utf8mb4)

create database zerodb default character set utf8mb4;

show databases;

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

use mysql;

select host, user from user;

show grants for 'zero'

show grants for 'zero';

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

use zerodb;

source backup_zerobase.sql;

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

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

show tables;

source insert.sql;

select * from police_station;

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

exit 

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

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

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

show databases;

use zerobase;

show tables;

source insert.sql

select * from police_station;

어렵다...생소하다..

💻 출처 : 제로베이스 데이터 취업 스쿨