SQL (AWS RDS, SQL File)

윤·2023년 4월 28일

sql

SQL

목록 보기

5/11

📌 AWS RDS

Amazon Web Service Relational Database Service 의 약자로,

AWS 에서 제공하는 관계형 데이터베이스 서비스이다.

✍ AWS RDS 서비스가 필요한 이유

AWS RDS 서비스는 관리형 데이터베이스 서비스로서, 사용자가 데이터베이스 인스턴스를 쉽게 생성하고 구성할 수 있도록 해준다. 쉽게 말하면, 데이터를 간단하고 편리하게 관리하며 안정성과 확장성을 보장하기 위해서 AWS RDS 서비스를 사용하는 것이 유용하다.

📒 AWS RDS 생성

마스터 사용자 이름 admin은 로컬에서 root 계정과 같은 것

퍼블릭 액세스 : RDS에 어떤 식으로 접근하는지에 따라서 활성화 또는 비활성화를 한다.

가상PC에서 RDS에 접근하겠다면 비활성화 해도 된다.
외부에서 직접 RDS에 접근한다면 활성화를 해야 한다.

📒 MySQL RDS 외부 접속

MySQL Command Line Client 를 실행하면 바로 비밀번호를 입력해야 해서 로컬접속으로 된다.
그래서 명령프롬프트로 실행해봤다.

파일 경로와 mysql 외부접속 하는 법 알아두기 !
-h : host
주의할 점은 엔드포인트를 입력할 때 "" 로 하거나 큰따옴표없이 엔드포인트만 입력해야 한다.
admin 유저가 외부(%)로 접속했음을 알 수 있다.

정리하자면,
로컬접속할 땐 => mysql -u root -p
외부접속할 땐 => mysql -h "엔드포인트" -P Port -u username -p

계정의 비밀번호가 mydata라 할 때,
mysql -u root -pmydata : -pmydata 로 붙여쓰면 비밀번호로 인식한다.
mysql -u root -p mydata : use mydata 를 사용하지 않고, mydata 데이터베이스로 바로 이동한다.

📌 SQL File

📒 sql_ws 파일 생성

실습 폴더를 만들고, 현재 폴더를 vscode 로 열어라 !

cd : change directory
mkdir : make directory

📒 SQL File

SQL File 은 SQL 쿼리를 모아놓은 파일이다.

1) vscode 에서 sql_ws폴더에서 sql 파일 생성
2) 터미널 실행 ( ctrl+shift +` )
3) mysql -u root -p 로 mysql 로컬 접속

mysql 에 접속해서 test01 sql파일 실행

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

mysql -u root -p
Enter password: 

mysql> use mydb;
Database changed

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

=> mydb로 이동해서 test01.sql 파일을 실행시켜주면, test01 파일에서 만들어놓은 쿼리가 실행되는 것이다 ! create table police_station 을 해주었기 때문에 mydb 안에 police_station 테이블이 생성이 되는 것 !!!!!!

mysql 에 접속하지 않고, 터미널에서 test02 sql파일 실행

mysql -u root -p mydb < test02.sql  # test02 파일을 mydb에서 실행
Enter password: 

mysql -u root -p
Enter password: 

mysql> use mydb;
Database changed

✍

윈도우환경에서는 vscode 의 터미널에서 powershell 대신 bash를 이용하는 것이 더 편리하다.

bash는 Git 설치할 때 함께 설치됨.

% : 주피터 노트북에서 사용하는 명령어 (매직 커맨드)
$ : bash 셸에서의 프롬프트를 나타기 위해 사용되는 기호 !

✍

cmd 나 vscode 등에서 mysql을 접속할 때 윈도우 환경에서는 경로를 지정해줘야 하는 과정들이 귀찮아서 환경변수에 path를 추가하여 매번 경로를 직접 입력해야 하는 번거로운 과정을 줄였다. 여기에 정리해놨다!

윈도우 환경이라 맥처럼 수월하지가 않아서 오류랑 싸우느라 진 다 뺐다😵‍ 맥이 이토록 부러웠던 순간이 없었다.. 오류를 해결하기 위해 찾아다니는 시간은 고통스러운데 해결했을 때는 힘들었던 시간들이 다 보상받는 기분이다. 그리고 git은 아직 배우지도 않았는데 git bash로 리눅스/유닉스 명령어를 쓰려고 git부터 먼저 깔았다. 너무 편하고 좋다 !

git bash 에서 mysql 을 불러올 때는 winpty 를 붙여서 winpty mysql -u root -p 을 입력한 후 엔터를 쳐야 Enter password: 입력창이 나온다.

winpty 란, 윈도우에서 터미널과 같은 콘솔 애플리케이션을 실행할 때 사용하는 프로그램이다. 일반적으로 윈도우에서는 콘솔 애플리케이션에서 명령어 입력시 입력창이 나타나지 않아서 winpty 를 이용하여 명령어 입력창을 나타나게 한다.

📌 Database Backup

📒 Database Backup

SQL 파일로 Database 를 백업할 수 있다.

# mydb 데이터베이스 내용들을 mydata.sql 파일로 백업(저장)
mysqldump -u root -p mydb > mydata.sql  

# 모든 데이터베이스 내용들을 mydata.sql 파일로 백업(저장)
mysqldump -u root -p --all-databases > mydata.sql

=> bash 셸에서 mydb의 데이터베이스 내용들을 omg.sql 파일에 백업시킨다. (omg.sql 파일이 자동 생성된다.)

📒 Database Backup Restore

데이터베이스를 백업한 SQL 파일을 실행하면 그 시점으로 복구하거나 이전할 수 있다.
=> 위에서 배운 SQL 파일을 실행하는 방법과 동일하다.

1) AWS RDS 서비스가 사용 가능한 상태에서 접속 (외부 접속)
⭐ 2) source omg.sql 로 백업한 SQL 파일을 실행해서 외부 접속한 계정의 데이터베이스에 복구시킨다.

📒 Database Table Backup

Table 단위로도 백업할 수 있다.

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

📒 Database Backup Table Restore

데이터베이스의 Table을 백업한 SQL 파일을 실행해 그 시점으로 복구한다.

mysql -h "endpoint" -P Port -u username -p mydb 

source celeb.sql

📒 Table Schema Backup

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

# mydb의 snl_show 테이블의 schema만 snl.sql 파일로 백업
mysqldump -d -u root -p mydb snl_show > snl.sql  # '-d' 추가

# mydb의 모든 테이블의 schema만 mydata.sql 파일로 백업
mysqldump -d -u root -p mydb > mydata.sql

=> 데이터는 없고, 테이블 생성 쿼리만 존재한다.

📌 실습

vscode 터미널에서 실행해도 되지만, 이번에는 cmd로 실행해보았다.

1) AWS RDS (database-1) 의 mydb 데이터베이스를 백업하세요.
⭐ AWS RDS 데이터베이스를 백업할 경우는 --set-gtid-purged=off 옵션을 추가한다 !

2) AWS RDS (database-1) 에 admin 계정으로 로그인하세요.

3) 'zerodb' 데이터베이스를 생성하세요. (DEFAULT CHRACTER SET utf8mb4)

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

5) 앞서 백업한 mydb 백업 파일을 zerodb 에서 실행하세요.

6) police_station 테이블에 아래 데이터를 INSERT 하기 위한 SQL 파일을 생성하세요.

name	address
서울특별시경찰청	서울시 종로구 사직로8길 31
서울중부경찰서	서울특별시 중구 수표로 27
서울종로경찰서	서울특별시 종로구 율곡로 46
서울남대문경찰서	서울특별시 중구 한강대로 410