1. SQL File 실습환경 만들기, SQL File 실행
cmd에서 cd /d "D:\12. SQL File\sql_ws"로 이동
sql_ws: sql 워크스페이스(작업공간)라는 뜻의 폴더
code .
이 폴더에서 vs code실행
vs code에서
test01.sql 파일 만들어 쿼리문 작성
CREATE TABLE police_station
(
name varchar(16),
address varchar(128)
);vs code 터미널 창에서
D:\12. SQL File\sql_ws>
mysql -u root -p1111
입력 실행
use zerobase;
mysql> source test01.sql
Query OK, 0 rows affected (0.02 sec)
show tables;
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)
<외부에서 접속하는 법>
test02.sql
CREATE TABLE crime_status
(
year YEAR,
police_station varchar(8),
crime_type varchar(16),
status_type char(2),
case_number int
);
<crime_status: 범죄현황 테이블 설명>
- year : 2015~2020
- police_station: 경찰서 타입
- crime_type: 범죄유형(살인, 절도, 강간,..
- status_type: 검거
- case_number: 검거건수
sql_ws % mysql -u root -p zerobase <test02.sql
(내컴퓨터에서는 안됨, sql_ws로 이동 후 아래접속)
또는
mysql -u root -p zerobase <test02.sql
Enter password: 입력
=> zerobase라는 데이터베이스에 test02.sql파일을 넣어서 실행해줘라.
mysql -u root -p0000
=> 비밀번호 바로 입력해주려면 p다음에 비밀번호 붙여써야함
show databases;
use zerobase;
(여기서 zerobase는 데이터베이스를 의미)
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 | |
## 2. Database Backup, Restore
1. <데이터베이스 백업>
1.먼저 웹의 AWS RDS에 접속해서 database-1을 사용가능 상태로 실행시켜준다.
cmd
cd /d "D:\12. SQL File\sql_ws"
cmd, sql_ws 폴더에서 <제로베이스 데이터베이스에 zerobase.sql 파일을 백업함>
mysqldump -u root -p zerobase >zerobase.sql
=> 제로베이스 데이터베이스의 내용을 zerobase.sql로 저장하는 것★
code .
AWS RDS의 database-1가 실행 가능한 상태가 되면
mysql -h "database-1.ㅁㅁㅁㅁㅁㅁㅁㅁㅁ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p
비번입력
show databases;
use zerobase;
source zerobase.sql
=> 현재 remote 페이지에서 zerobase.sql파일을 실행시켜줌
=> 그안에 있는 내용이 다 들어가면서 여러번의 Query OK, 0 rows affected (0.04 sec)를 출력함
show tables;
Tables_in_zerobase |
+--------------------+
| animal |
| celeb |
| crime_status |
| police_station |
| snl_show |
| test1 |
| test2
여기까지 로컬의 내용을 백업을 하고 리모트에 저장함
### Table backup
#<테이블 단위로 백업하기>
vscode 터미널 D:\~~\sql_ws>에서
mysqldump -u root -p zerobase celeb> celeb.sql
Enter password: 입력
=> 제로베이스 데이터베이스에서 셀럽테이블을 celeb.sql이란 이름으로 백업하겠다.
### Table Restore
<AWS RDS로 테이블 백업하기>
vs code 터미널에서
mysql -h "database-1.ㅁㅁㅁㅁㅁㅁㅁㅁ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p비밀번호입력 zerobase
=> 바로앞 시간에 만든 zerobase까지 이동함
show tables;
drop table celeb;
=> 기존에 있던 celeb테이블을 삭제해줌
source celeb.sql
=> 여러번 쿼리 ok 실행됨
=> Table Restore
show tables;
desc celeb;
select * from celeb;
Chapter 12-7 Table Schema backup
exit
로컬 cmd d:\ ~ sql_ws>에서
mysqldump -d -u root -p zerobase snl_show > snl.sql
=> vs code상에서 snl.sql파일이 생겨남
=> Table Schema backup 한 것
(snl.sql을 보면 테이블 형태는 있으나 데이터는 들어가 있지 않다.)
=> CREATE TABLE `snl_show` (
`ID` int NOT NULL AUTO_INCREMENT, ...만 명시되어있고
INSERT INTO `celeb` VALUES (1,~이런 테이블 데이터 내용은 없음
(celeb.sql과 다른 점)
-----
문제9개 p52~60
- 1. AWS RDS (database-1) 의 zerobase 데이터베이스를백업하세요.
(backup_zerobase.sql이름으로 백업)
주의> AWS RDS database 를백업할경우다음의옵션을추가합니다.
:vscode 로컬 cmd d:\ ~ sql_ws>에서
mysqldump --set-gtid-purged=off -h"database-1.ㅁㅁㅁㅁㅁㅁㅁㅁ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p비밀번호입력 zerobase > backup_zerobase.sql
=> 로컬 폴더인 sql_ws폴더에 backup_zerobase.sql이 생성됨
- 2. AWS RDS (database-1) 에 admin 계정으로 로그인하세요.
:vscode 로컬 cmd d:\ ~ sql_ws>에서
mysql -h "database-1.ㅁㅁㅁㅁㅁㅁㅁㅁ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p비밀번호입력
=> mysql> 바뀌면 ok, admin 계정으로 접속함
- 3. Database (zerodb) 를생성하세요.
(DEFAULT CHARACTER SET utf8mb4)
AWS RDS (database-1) 에 admin 계정으로 로그인한 상태에서 (2번답)
mysql>
CREATE DATABASE zerodb DEFAULT CHARACTER SET utf8mb4;
show databases;
=>
| Database
| zerodb |
- 4. 앞서생성한사용자 (zero@%) 에게 zerodb 의모든권한을부여하세요.
mysql>
grant all on zerodb.* to 'zero'@'%';
mysql>
show grants for 'zero'@'%';
=>
Grants for zero@% |
+----------------------------------------------------+
GRANT ALL PRIVILEGES ON `zerobase`.* TO `zero`@`%` |
GRANT ALL PRIVILEGES ON `zerodb`.* TO `zero`@`%` |
-5. 앞서백업한 zerobase 백업파일을 zerodb 에서실행하세요.
use zerodb;
show tables;
=> Empty set (0.04 sec)
mysql>
source backup_zerobase.sql
(뒤에 ;없음)
=> 여러번의 쿼리 ok되며 실행됨
show tables;
- 6. police_station 테이블에아래데이터를 INSERT 하기위한 SQL 파일을생성하세요.
p57
vs code에서 새파일 insert.sql을 만듬
INSERT INTO police_station VALUES(N'서울특별시경찰청', N'서울시 종로구 사직로8길 31');
INSERT INTO police_station VALUES(N'서울중부경찰서', N'서울시 중구 수표로 27');
INSERT INTO police_station VALUES(N'서울종로경찰서', N'서울시 종로구 율곡로 46');
INSERT INTO police_station VALUES(N'서울남대문경찰서', N'서울시 중구 한강대로 410');
=> 한글깨짐 방지를 위해 N삽입: values(N'한글')
<한글깨짐방지>
insert into [테이블](칼럼) values (N'한글')
select * from [테이블] where [칼럼] LIKE N'%한글%'
- 7. SQL 파일을실행하여
AWS RDS (database-1) zerobase 의 police_station 테이블에 데이터를 INSERT 하고확인하세요.
use zerobase;
show tables;
select * from police_station;
=> Empty set (0.04 sec)
mysql>
source insert.sql
select * from police_station;
- 8. AWS RDS (database-1) zerobase 의 police_station 테이블을 SQL 파일로백업하세요.
mysql>
exit
로컬 cmd d:\ ~ sql_ws>에서
mysqldump --set-gtid-purged=off -h"database-1.ㅇㅇㅇㅇㅇㅇㅇㅇ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p zerobase police_station > backup_police.sql
=>
--set-gtid-purged=off : AWS RDS에서 작업을 할 때는 이 옵션을 써줘야함,
이렇게 안하면 sql파일 내에서 다른 옵션코드가 들어가서 쿼리 실행시 에러가 날 수 있음
-p zerobase 제로베이스 데이터베이스 선택해서
police_station 테이블만 가져와서
backup_police.sql 파일로 저장해라.
Enter password: 입력
- 9. SQL 파일을실행하여
AWS RDS (database-1) zerodb 의 police_station 테이블을 zerobase 와동일하게만들고확인하세요.
로컬 cmd d:\ ~ sql_ws>에서
mysql -h "database-1.ㅁㅁㅁㅁㅁㅁㅁㅁ.ap-northeast-1.rds.amazonaws.com" -P 3306 -u admin -p비밀번호 입력
use zerodb;
show tables;
select * from police_station;
=> Empty set (0.04 sec)
source insert.sql
select * from police_station;