MySQL SQL File

1. 실습환경 만들기

1.1 Workspace 생성

1) 파일 저장할 root 폴더 만들기

2) sql_ws 폴더를 만들고 그 하위에서 실습하기

cd Documents
Document % mkdir sql_ws
# make directory(mkdir): 현재 위치에서 새로운 디렉토리 만들기

Document % cd sql_ws
sql_ws %
# Documnet 폴더 위치에 sql_ws 폴더 만들기

3) sql_ws로 이동하여 SQL File 생성하기

4) sql_ws 폴더 하위헤 다음을 실행하기

sql_ws % code .
# 현재 위치(.)에 폴더를 VSCode로 열기

여기서 두가지 문제점이 발생했다. 아무리 code .를 입력해도 VSCode(Visual Studio Code) 실행이 안되고 zsh: command not found: code 라고 뜬다.

구글링하여 찾아본 결과, VSCode가 PC에 설치 되어야하며 VSCode가 설치되었을 시 cmd + shift + p를 눌러 명령 팔레트를 열어 code라고 검색하여 아래의 Shell Command: Install 'code' command in PATH를 클릭하여 설치하면 해결된다.

또 다른 sql_ws 실행 방법은 VSCode에서 상단의 File에서 Open Folder..를 클릭하여 Documents 폴더 안에 sql_ws 폴더를 열면 된다.

5) VSCode의 sql.ws 폴더에서 새파일 버튼 선택하기

6) 생성할 파일 이름을 정하고, 파일 확장자를 sql(.sql)로 입력하여 비어있는 sql 파일 생성하기

2. SQL File

SQL File은 .sql 확장자를 가진 파일로 SQL 퀴리를 모아놓은 파일이다.

2.1 로그인 이후 SQL File 실행

MySQL 로그인 이후 SQL File 실행하는 방법

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

1) employee 정보를 테이블로 만들어보기

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

VSCode에서 terminal 실행하는 방법은 위와 같이 상단의 terminal에서 new terminal 클릭하기

2) sql_ws 폴더 위치로 이동하여 database1에 접속하기

% cd sql_ws
sql_ws % mysql -u root -p database1
# local sql workspace에 접속하여 database(database1)로 접속

3) SQL File 실행하기

mysql> source test1.sql
# database1이라는 database에 test1.sql이라는 테이블 생성

4) 테이블 생성 결과 확인하기

mysql> desc employee;

2.2 외부에서 바로 SQL File 실행

MySQL에 접속하지 않은 상태에서 Terminal을 통해 SQL File을 바로 실행하는 방법

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

1) MySQL 연결 종료하기

mysql> exit

2) VSCode의 sql_ws 폴더에서 새파일 버튼 클릭하여 sql_ws 폴더의 새 하위파일 test2.sql 생성하기

3) 서울특별시 공원별 공원 시설 현황 정보를 테이블로 만들기

create table park_facilities
(
    park_name varchar(18),
    park_location varcar(128),
    facility_type varchar(50),
    facility_count int
);
# park_name: 공원의 이름
# park_location: 공원의 위치
# facility_type: 시설 유형
# facility_count: 해당 유형의 시설 수

4) database1 database에 접속하면서 SQL File 실행하기

sql_ws % mysql -u root -p database1 < test2.sql

5) database1에 접속하여 결과 확인하기

sql_ws % mysql -u root -p database1
mysql> desc park_facilities;

3. Database 백업(Backup)

SQL File로 database 백업이 가능하다.

특정 database 백업

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

모든 database 백업

% mysqldump -u username -p --all-databases > backup.sql

1) database1 database 백업하기

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

2) VSCode에서 zerobase.sql 확인하기

sql_ws % code .

4. AWS RDS 서비스 시작

1) AWS 콘솔 로그인하기
2) 서비스 > RDS
3) RDS 관리 페이지에서 데이터베이스 중지된 상태 확인하기
4) database-1 선택하여 작업을 시작으로 선택하기
5) 시작 중으로 바뀐 작업 상태를 새로고침으로 상태 확인

5. Database 복구(Restore)

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

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

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

2) database 중 database1 확인하고 이동하기

show databases;
use database1;

3) AWS RDS (database-1)의 database1 데이터베이스를 복원하기

mysql> source database1.sql

sql_ws %은 완료 표시로 데이터베이스 복원이 끝났다는 의미이다.

4) 결과 확인하기

mysql> show tables;

6. Table 백업 (Backup)

Table 단위로 백업이 가능하다.

Table 백업

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

1) Local database에서 family table 백업하기

sql_ws % mysqldump -u root -p database1 family > family.sql

2) VSCode에서 family.sql 파일 확인하기

7. Table 복구(Restore)

Table을 백업한 SQL File을 실행하여, 해당 테이블을 복구하거나 이전이 가능하며, SQL File을 실행하는 방법과 동일하다.

1) AWS RDS (database-1)의 database1에서 family 테이블 삭제하기

sql_ws % mysql -h database-1.cr8sy4u8g6l2.us-east-2.rds.amazonaws.com -P 3306 -u admin -p database1

mysql> show tables;
mysql> drop table family;

2) AWS RDS (database-1)의 database1에서 family 테이블 복구하기

mysql> show tables;
mysql> source family.sql

3) 결과 확인하기

mysql> show tables;
mysql> desc family;
mysql> select * from family;

8. Table Schema 백업 (Backup)

데이터를 제외하고 테이블 생성 퀴리만 백업이 가능하다.

Table 백업과 동일하지만 한가지 다른점은 -d option인 data를 제외하고 table schema만 복사한다.

특정 table schema 백업

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

모든 table schema 백업

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

1) Local database에서 k_drama schema 백업하기

sql_ws % mysqldump -d -u root -p database1 k_drama > drama.sql

2) VSCode에서 data는 포함하지 않은 drama.sql 확인하기