AWS RDS

• aws 에서 제공하는 관계형 데이터베이스 서비스

1. AWS 회원가입
   https://portal.aws.amazon.com/billing/signup#/start
2. MySQL RDS 생성
   • AWS 관리 콘솔
   • 검색에 Amazon RDS 검색
   • 데이터베이스 생성 클릭
   • 표준 생성> 엔진 유형 MySQL 선택
   • 탬플릿 프리티어 선택
   • root의 비밀번호 잊으면 안되듯이 마스터 암호 잊으면 안됨.
   • 버스터블 클래스, t2 micro 선택
   • 스토리지: SSD, 20 , 스토리지 자동 조정 비활성화(G 넘어가지 않게)
   • 퍼블릭 액세스: 외부에서 RDS에서 접근하기 때문에 활성화 해줘야함.
   • 자동 백업 (비활성화) : 스냅샷을 찍으면 20G가 금방 참.
   • 모니터링 비활성화
   • 삭제방지 활성화

RDS 외부 접속 설정

1. 연결 및 보안
   -Default VPC 보안 그룹
   -인바운드 규칙 편집>규칙 추가 > 유형 MySQL/AURORA 선택
   -소스 유형>anywhere-IPv4 선택
2. RDS 접속
   • DB 식별자 클릭
   • 엔드포인트 ,포트 정보 copy
   • cmd 터미널에 % mysql -h <엔드포인트> -P <포트> -u <마스터 사용자 이름> -p (mysql 커맨드에서 진행할땐 맨앞에 system 적으면됨)
     - cmd에 명령어 읽을 수 있게 작업하기.
     - C드라이브> program files>mysql>mysql server 8.0>bin 경로 복사
     - 윈도우 키> 설정>시스템>정보>고급시스템 설정>환경변수>path>새로만들기>경로 붙여넣기
     - 완료 확인 터미널에서 mysql -V[mysql -u root -p]하면 cmd에서 mysql 실행 가능

show databases;

use mysql
select host, user from user;

3. RDS 사용 중지

• 프리티어는 750시간이 제한이 있기때문에 안쓸때는 꺼야함
  1.RDS 목록 들어가서 데이터베이스 선택하고 작업: 중지, 스냅샷: 아니오
  2.완전 중지시 중지됨 표시

4. RDS 다시 시작
   1. 데이터베이스 클릭후 작업: 시작
      

SQL file

• 실습환경 만들기

저장할 루트 폴더 만들기

cd Documents
mkdir sql_ws
cd sql_ws
code . #vscode 실행

• vscode에 sql 쿼리문 작성하기 : sql_ws폴더인지 확인하고, test01.sql 새파일 생성

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

• vscode에서 터미널> 새 터미널 실행> +누르고 git bash

mysql -u root -p zerobase

• test01.sql 파일 실행

source test01.sql
desc police_station;

• 외부에서 실행법 (꼭 Ctrl+s 해야함)

mysql -u root -p zerobase < test02.sql

database backup

• 백업 시작

mysqldump -u root -p zerobase>zerobase.sql

zerobase.sql 라는 파일이 생성된다. zerobase의 테이블들이 백업 됨.

• RDS 시작(database backup restore) : 백업 데이터베이스 복구
  백업한 SQL file 을 실행하여 그 시점으로 복구하거나 이전할 수 있다.

mysql -h 엔드포인트 -P 3306 -u admin -p
create database zerobase;
use zerobase;

• RDS의 zerobase database 복원

source zerobase.sql

show tables;

로컬에 있는것들 가지고오기

• table 단위로도 백업 가능 (celeb 테이블 백업하기)

mysqldump -u username -p dbname tablename>celeb.sql

• table을 백업한 sql file을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다.

rds에서 celeb 테이블 삭제하기

drop table celeb;
source celeb.sql;

• table schema backup : 데이터 제외 테이블 생성 쿼리만 백업 할 수 있다.

snl_show table schema 백업

%mysqldump -d -u root -p zerobase snl_show>snl.sql ##데이터는 없고 스키마만 있음.

python with MySQL

• 실습 환경 만들기

---콘다
conda activate ds_study
cd Documents
cd sql_ws
---cmd
use zerobase;
delete from police_station;

• Python으로 MysQL 접속하기

pip install mysql-connector-python
import mysql.connector

-- 로컬에 연결
mydb=mysql.connector.connect(host='localhost',user='root',password='*******')
-- AWS RDS 에 연결
remote=mysql.connector.connect(host='엔드포인트',
                             port=3306,user='admin',password='*******')

• 사용이 끝났을때

mydb.close()

• 데이터 베이스 지정 접속

mydb=mysql.connector.connect(host='localhost',user='root',password='*******',database='zerobase')
mydb.close()
remote=mysql.connector.connect(host='엔드포인트',port=3306,user='admin',password='*******',database='zerobase')
remote.close()

쿼리 실행하기

cur=remote.cursor()
cur.execute('create table sql_file (id int,filename varchar(16))')
remote.close()

• 확인하기

desc sql_file;

• 테이블 삭제하기

cur=remote.cursur()
cur.execute('drop table sql_file')

• sql file 실행하기1

create table sql_file (id int,filename varchar(16));

cur=remote.cursor()
sql=open('test03.sql').read()
cur.execute(sql)
remote.close()

• sql file 실행하기2

insert into sql_file(1,'test01.sql');
insert into sql_file(2,'test02.sql');
insert into sql_file(3,'test03.sql');
insert into sql_file(4,'test04.sql');

cur=remote.cursor()
sql=open('test04.sql').read()
for result_iterator in cur.execute(sql,multi=True):
    if result_iterator.with_rows:
        print(result_iterator.fetchall())
    else:
        print(result_iterator.statement)
remote.commit()
remote.close()

select * from sql_file;

python with Mysql Csv

• csv 에 있는 데이터 python 으로 insert

import pandas as pd
df = pd.read_csv('police_station.csv')
df.head()

• police_station 테이블에 집어넣기 (읽어올 양이 많은 경우 cursor 생성 시 buffer를 설정해준다.

cursor=remote.cursor(buffered=True)
sql='insert into police_station valeus (%s,%s)'
for i, row in df.iterrows():
    cursor.execute(sql,tuple(row))
    print(tuple(row))
    remote.commit() # 커밋을 해야하는 순간 적용된다.

• 넣은 데이터 확인하기

cursor.execute('select * from police_station')
result=cursor.fetchall()
for row in result:
    print(row)

• 결과 판다스로 읽기

df=pd.DataFrame(result)
df

• 한글이 깨지는 경우 encoding 값을 'euc-kr'로 설정 (우리나라 사이트에서 제공받은 csv 파일들.)

df=pd.read_csv('2020_crime.csv',encoding='euc-kr')
df.head()

1. crime_status 테이블에 2020_crime.csv 데이터 입력 코드 작성

sql='''insert into crime_status values("2020",%s,%s,%s,%s)'''
cursor=remote.cursor(buffered=True)
for i,row in df.iterrows():
    cursor.execute(sql,tuple(row))
    print(tuple(row))
    remote.commit()

• 데이터 확인

cursor.execute('select * from crime_status')
result=curosr.fetchall()
for row in result:
    print(row)