SQL : 12. SQL FILE

❇️ 요약

• SQL FILE 실행
• Database Backup, Restore
• Table Backup, Restore
• Table Schema Backup

📖 SQL FILE 실행

🔆 Workspace 생성

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

  Document % mkdir sql_ws
  Document % cd sql_ws
  sql_ws %

  • 나는 C 드라이브에 생성

    C:\> mkdir sql_ws
    
    C:\>cd sql_ws
    
    C:\sql_ws>

🔆 SQL File 생성 방법

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

sql_ws % code .

• vscode 열린 후 sql_ws 폴더 안에 test01.sql 파일 만들기
  

🔆 SQL FILE

• SQL 쿼리를 모아놓은 파일
• 파일안에 실행할 쿼리를 한번에 모아서 작성한 후 파일을 실행함으로써 작성된 쿼리들을 한번에 실행 가능

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

mysql> source /path/filename.sql
mysql> \. /path/filename.sql    # source 대신 \. 사용가능
mysql> \. filename.sql          # 현재 폴더에 파일이 있으면 path 생략 가능 
# sql_ws 폴더를 만들어서 그 안에서 실습할예정이기에 파일명만 실행할 예정 

• sql_ws 폴더 위치로 이동하여 zerobase에 접속

C:\>cd sql_ws
C:\sql_ws>mysql -u root -p zerobase # -p이후에 데이터베이스 이름까지 주면 바로 접속가능 

☁️ SQL File 실행 1 - 로그인 이후 - 예제

• vscode : 경찰서 정보를 테이블로 만들기

  

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

• 터미널(cmb창) : sql_ws 폴더 위치로 이동하여 zerobase에 접속

  C:\>cd sql_ws
  C:\sql_ws>mysql -u root -p zerobase # -p 이후에 데이터베이스 이름까지 주면 바로 접속가능 

• 터미널(cmb창) : SQL File 실행

  mysql> source test01.sql
  Query OK, 0 rows affected (0.01 sec)

• 터미널(cmb창) : 결과 확인

  mysql> DESC police_station;
  +---------+--------------+------+-----+---------+-------+
  | Field   | Type         | Null | Key | Default | Extra |
  +---------+--------------+------+-----+---------+-------+
  | name    | varchar(16)  | YES  |     | NULL    |       |
  | address | varchar(128) | YES  |     | NULL    |       |
  +---------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

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

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

☁️ SQL File 실행 2 - 외부에서 바로 실행 - 예제

• 터미널(cmb창) : MySQL 연결을 종료

mysql> exit
Bye

• vscode : 서울 특별시 관서별 5대 범죄 현황 정보를 테이블로 만들기

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

• 터미널(cmb창) : zerobase Database에 접속하면서 SQL File 을 실행

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

• 터미널(cmb창) : zerobase에 접속하여 결과를 확인

-- mysql의 zerobase로 접속 
C:\sql_ws>mysql -u root -p zerobase
Enter password: ****

mysql> DESC crime_status;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| year           | year        | YES  |     | NULL    |       |
| police_station | varchar(8)  | YES  |     | NULL    |       |
| crime_type     | varchar(16) | YES  |     | NULL    |       |
| status_type    | char(2)     | YES  |     | NULL    |       |
| case_number    | int         | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

---

📖 Database Backup

🔆 Database Backup

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

mysqldump -u username -p dbname > backup.sql             # 특정 Database Backup
mysqldump -u username -p --all-databases > backup.sql    # 모든 Database Backup 
mysqldump -u root -p dbname -r backup.sql                # 한글 깨짐 방지 

☁️ Database Backup - 예제

• zerobase Database Backup

# 한글 깨짐 이슈 발생 
mysqldump -u root -p zerobase > zerobase.sql

문제 해결

mysqldump -u root -p zerobase -r zerobase.sql

🔆 Database Backup Restore

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

source backup.sql

☁️ Database Backup Restore - 예제

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

 C:\sql_ws> mysql -h database-1.cvgki66kg3x4.ap-northeast-2.rds.amazonaws.com -P 3306 -u admin -p

• zerobase Database 이동

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zerobase           |
+--------------------+
5 rows in set (0.01 sec)

mysql> USE zerobase;
Database changed

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

mysql> source zerobase.sql
Query OK, 0 rows affected (0.01 sec)
...
Query OK, 0 rows affected (0.01 sec)

• 결과확인

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.01 sec)

📖 Table Backup

🔆 Table Backup

• Table 단위로도 백업할 수 있다.
• 테이블 1개만 백업하고 싶을 때 사용

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

☁️ Table Backup - 예제

• Local Database에서 movie Table을 백업

mysqldump -u root -p zerobase celeb > celeb.sql
mysqldump -u root -p zerobase celeb -r celeb.sql  # 한글 깨짐 방지용 -r

🔆 Table Restore

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

source backup.sql

☁️ Table Restore - 예제

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

PS C:\sql_ws> mysql -h database-1.cvgki66kg3x4.ap-northeast-2.rds.amazonaws.com -P 3306 -u admin -p zerobase

...

mysql> DROP TABLE celeb;
Query OK, 0 rows affected (0.01 sec)

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

mysql> source celeb.sql
Query OK, 0 rows affected (0.01 sec)
...
Query OK, 0 rows affected (0.01 sec)

• 결과 확인

mysql> SELECT * FROM celeb;
+----+-----------+------------+------+------+-------------------------+--------------------------+
| ID | NAME      | BIRTHDAY   | AGE  | SEX  | JOB_TITLE               | AGENCY                   |
+----+-----------+------------+------+------+-------------------------+--------------------------+
|  1 | 아이유    | 1993-05-16 |   29 | F    | 가수,텔런트             | EDAM엔터테이먼트         |
|  2 | 이미주    | 1994-09-23 |   28 | F    | 가수                    | 울림엔터테이먼트         |
|  3 | 송강      | 1994-04-23 |   28 | M    | 텔런트                  | 나무엑터스               |
|  4 | 강동원    | 1981-01-18 |   41 | M    | 영화배우, 텔런트        | YG엔터테이먼트           |
|  5 | 유재석    | 1972-08-14 |   50 | M    | MC, 개그맨              | 안테나                   |
|  6 | 차승원    | 1970-06-07 |   48 | M    | 영화배우, 모델          | YG엔터테이먼트           |
|  7 | 이수현    | 1999-05-04 |   23 | F    | 가수                    | YG엔터테이먼트           |
+----+-----------+------------+------+------+-------------------------+--------------------------+
7 rows in set (0.01 sec)

📖 Table Schema Backup

🔆 Table Schema Backup

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

mysqldump -d -u username -p dbname tablename > backup.sql   # 특정 Database Backup
mysqldump -d -u username -p dbname > backup.sql             # 모든 Database Backup 
mysqldump -d -u root -p dbname tablename -r backup.sql      # 한글 깨짐 방지 

☁️ Table Backup - 예제

• Local Database에서 snl_show Table Schema 백업

PS C:\sql_ws> mysqldump -d -u root -p zerobase snl_show -r snl.sql 

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