SQL file

SQL file은 쿼리를 모아놓은 파일을 말한다.
쿼리를 SQL file에 작성하고 SQL file을 실행하면 한 번에 모든 쿼리를 실행할 수 있게 된다.
그래서 자료를 백업하거나 리스토어를 하는데 이용할 수 있다.

소스 코드 에디터를 사용하여 파일을 생성하고 실행할 수 있는데, 그 중에서 Visual Studio Code(VSCode)를 사용하여 진행하도록 한다.

환경

먼저 cmd 프롬프트 창을 열어서 SQL file을 저장하거나 실행할 폴더까지 접근하여 VSCode를 실행한다.
그리고 확장자 이름을 .sql로 생성하면 file이 생성된다.

실행

SQL 프롬프트에 접속을 한 후, 'source' 또는 '역슬레쉬('\').'을 하고 경로와 파일 이름을 실행하면 된다.

mysql> source </path/filename.sql>
mysql> \. </path/filename.sql>

먼저 .sql 파일에 쿼리를 입력하고(현재 해당 sql file이 있는 곳에서 프롬프트 실행하여 mysql 로그인) 저장한다.

그리고 sql 프롬프트에서 .sql file을 실행한다.

.sql file 실행 전

mysql> show tables;
+-------------------+
| Tables_in_project |
+-------------------+
| celeb             |
| person            |
+-------------------+
2 rows in set (0.00 sec)

.sql file 실행 후

#  파일 실행
mysql> source test01.sql
Query OK, 0 rows affected (0.02 sec)
# 파일 실행
mysql> \. test01.sql
Query OK, 0 rows affected (0.02 sec)

# 결과
mysql> show tables;
+-------------------+
| Tables_in_project |
+-------------------+
| celeb             |
| person            |
| test01            |
+-------------------+
3 rows in set (0.00 sec)

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

mysql 로그인 하지 않고 실행

C:\Users>mysql -u root -p < </path/filename.sql>

실행할 .sql file을 생성한다.

터미널/cmd 프롬프트에서 mysql에 접속하지 않고 바로 실행하도록 한다.
실행을 하면 비밀번호를 입력하면 실행이 된다.

C:\MySQL>mysql -u root -p project < test02.sql # 화살표가 왼쪽 방향

이제 제대로 .sql file이 실행되었는지 확인해보자.

mysql> show tables;
+-------------------+
| Tables_in_project |
+-------------------+
| celeb             |
| person            |
| test01            |
| test02            |
+-------------------+
4 rows in set (0.00 sec)

mysql> desc test02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| job   | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Database backup(백업)

mysqldump -u username -p database_name > backup.sql # 특정 database backup, 화살표가 이번엔 오른쪽 방향
mysqldump -u username -p database_name table_name > backup.sql # 특정 database의 특정 table backup
mysqldump -u username -p --all-databases > backup.sql # 모든 database backup

실행은 cmd 프롬프트/터미널에서 실행
실행하고 비밀번호를 입력하면 실행이 된다.

C:\MySQL>mysqldump -u root -p project > project_backup.sql # 화살표가 오른쪽 방향

이제 project_backup.sql라는 파일이 생기고,
파일의 내용은 아래와 같다.
파일은 기존의 table을 삭제하고 table 구성을 생성하고 데이터 입력까지 할 수 있도록 생성이 된다.

Database Restore

Database를 백업한 SQL file을 실행하여 그 시점으로 복구하거나 이전할 때 사용할 수 있다.

실행 방법은 SQL file을 실행하는 방법과 동일하다.
mysql 안에서든 아니면 로그인하지 않고 터미널/cmd 프롬프트에서든 동일하게 실행할 수 있다.

project1이라는 database를 생성하고 진행해보자.

mysql> create database project1; # database 생성
Query OK, 1 row affected (0.01 sec)

mysql> use project1;
Database changed

mysql> show tables; # table은 하나도 생성이 되지 않은 상태
Empty set (0.00 sec)

그리고 backup file을 실행해보자.
한 번에 여러 쿼리를 진행하였기 때문에 쿼리 수 만큼 Query OK가 출력된다.

mysql> source project_backup.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
.
.
...

table을 확인해보면 비어있었던 내용에서 restore가 잘 진행되었음을 알 수 있다.

mysql> show tables;
+--------------------+
| Tables_in_project1 |
+--------------------+
| celeb              |
| person             |
| test01             |
| test02             |
+--------------------+
4 rows in set (0.00 sec)