브래의 슬기로운 코딩 생활
데이터베이스 14주차 정리 본문
데이터베이스 관리
데이터베이스 관리의 중요성
안정적인 데이터베이스 운영을 위해 정기적인 관리가 필요하다.
데이터베이스 관리 업무
데이터베이스 관리 기본 명령어
(root 계정으로) 시스템에 있는 데이터베이스 현황을 살펴보시오.
#DB에 있는 데이터베이스가 어떤 것이 있는지 보여준다.
기본적으로 mysql, sys 등 관리용 데이터베이스를 확인할 수 있다.
- SHOW DATABASES;
#데이터베이스 mysql을 사용하는 명령을 내린다.
- USE mysql;
#데이터베이스 mysql에 있는 테이블들을 보여준다.
- SHOW TABLES;
#사용자 정보를 저장하는 User 테이블의 구조를 살펴본다.
- DESC User;
#사용자 계정 테이블 user의 내용을 확인하고 사용자 계정을 살펴본다.
- SELECT * FROM User;
보안과 권한
보안과 권한
DBMS는
① 로그인 단계에서 DBMS 접근을 제한하는 로그인 사용자 관리
② 로그인한 사용자별로 특정 데이터로의 접근을 제한하는 권한 관리의 기능 제공
로그인 사용자 관리
로그인 사용자 생성
- CREATE USER [사용자 이름] IDENTIFIED BY [비밀번호];
비밀번호는 IDENTIFIED BY 다음에 있는 ‘madang’이다.
CREATE USER madang@localhost IDENTIFIED BY 'madang';
외부에서 접속 가능한 madang 사용자는 다음과 같이 생성한다.
CREATE USER madang@'%' IDENTIFIED BY 'madang';
특정 사이트 ‘happy.md.kr’ 사이트에서 접속 가능한 madang 사용자 계정은 다음과 같이 생성한다.
CREATE USER madang@'%.happy.md.kr' IDENTIFIED BY 'madang';
로그인 사용자 삭제
- DROP USER [사용자 이름] CASCADE;
다른 사용자로 로그인
- Workbench 상단 [Database] 탭에서 [Connect to Database – Username]을 이용
권한 관리
권한 관리
소유한 개체에 대한 사용 권한을 관리하기 위한 명령을 DCL(Data Control Language)이라고 함
대표적 DCL 문 : 권한 허가 GRANT 문, 권한 취소 REVOKE 문
권한 허가 - GRANT
GRANT 권한 [(column_list)]
[ON 객체] TO {사용자 | ROLE}
[WITH GRANT OPTION]
- 권한 : 허가할 권한
- column_list : 사용 권한을 부여할 테이블의 열 이름들 (꼭 괄호 안에 표시해야 한다)
- 객체 : 사용 권한을 부여할 객체 지정 (테이블이나 뷰이름이 올 수 있다)
- 사용자 : 권한을 부여할 사용자를 지정
- ROLE : 권한 묶음
- WITH GRANT OPTION : 허가받은 권한을 다른 사용자에게 다시 부여할 수 있다. 없으면 재부여는 허가하지 않는다.
권한 취소 - REVOKE
REVOKE 권한 [column_list]
[ON 객체] FROM {사용자 | ROLE}
[CASCADE]
CASCADE는 사용자가 다른 사용자에게 부여한 권한까지 연쇄적으로 취소하라는 의미로,
사전에 주의 깊게 확인하고 사용해야 함
실습
백업과 복원
백업과 복원
백업(backup) : 데이터베이스에서도 역시 예상하지 못한 장애에 대비하여 데이터베이스를 복제하여 보관하는 작업
복원(recovery) :장애가 발생하여 운영 중인 데이터에 손상이 발생했을 때 기존에 복사해 둔 백업 파일을 사용하여 원래대로 되돌려 놓는 작업
- 하드웨어 장애
백업의 종류
전체 백업 :
- 데이터베이스 개체, 시스템 테이블, 데이터 등 데이터 베이스 전체를 백업하는 것이다.
- 백업 시점의 데이터베이스 복사본을 만들어 두는 것
차등 백업 :
- 전체 백업을 수행한 이후 변경된 데이터만 저장한다.
- 전체 백업이 만들어 둔 복사본과의 차이를 백업한다.
트랜잭션 로그 백업 :
- 데이터베이스에서 수행한 작업을 기록하고 있는 트랜잭션 로그 파일을 저장하는 방법이다.
데이터베이스 백업 방법
물리적 백업 :
데이터베이스를 구동하기 위해 필요한 모든 파일을 물리적으로 ‘복사’하는 방법
콜드 백업 : 데이터베이스를 셧다운(shutdown)한 후에 백업을 진행하는 방법
핫 백업 : 운영 중인 데이터베이스의 파일을 복사하는 방법
논리적 백업 :
실제 데이터베이스를 구성하는 물리적 파일을 직접 복사하는 방법이 아닌,
데이터베이스의 콘텐츠(내용)를 별도의 파일로 옮기는 백업 방법
MySQL 백업 및 복원 실습
백업 :
복원 :
스토리지 엔진
스토리지 엔진
스토리지 엔진(Storage Engine) 혹은 데이터베이스 엔진(Database Engine)은
DBMS이 필요로 하는 물리적인 데이터를 가져오는 장치이다.
- 스토리지 엔진의 특성에 따라 데이터 접근이 얼마나 빠른지, 얼마나 안정적인지,
트랜잭션 등의 기능을 제공하는지 등의 차이점이 발생한다
흔히 쓰는 스토리지 엔진 종류 :
스토리지 엔진의 역할
•트랜잭션(Transaction Manager): 트랜잭션을 스케줄링하고 데이터베이스 상태의 논리적 일관성 보장.
•잠금 매니저(Lock Manager): 트랜잭션에서 접근하는 데이터베이스 객체에 대한 잠금을 제어. 동시 수행 작업이 물리적 데이터 무결성을 침해하지 않도록 함.
= 트랜잭션과 잠금 매니저는 동시성 제어. 논리적 물리적 데이터 무결성을 보장하고,
동시 수행 작업의 효율적 수행을 담당.
•엑세스 메서드(Access Method): 디스크에 저장된 데이터에 대한 접근 및 저장 방식을 정의. 힙 파일/B-트리/LSM 트리 등의 자료구조 사용.
•버퍼 매니저(Buffer Manager): 데이터 페이지를 메모리에 캐시.
•복구 매니저(Recovery Manager): 로그를 유지 관리하고 장애 발생 시 시스템을 복구
InnoDB와 MyISAM 비교
InnoDB
•MyISAM보다 데이터 저장비율이 낮고, 데이터 로드 속도가 느리다.
•레코드 기반의 락(Lock)을 제공한다. 즉, 테이블 레벨이 아닌 ROW 레벨의 락을 지원한다. 이로 인해 높은 동시성 처리가 가능한 특징이 있고 안정적이다. (Row - Level Lock)
→ Insert, Update, Delete에 대한 속도가 빠르다
•외부키를 지원한다
•자동 데드락 감지 : 감지 시 변경된 레코드가 가장 작은 트랜잭션을 롤백해버려서 데드락을 풀어준다.
•자동 장애 복구 : 완료하지 못한 트랜잭션이나 일부만 기록되어 손상된 데이터 페이지 등을 자동 복구한다.
•데이터 무결성 보장(트랜잭션, 외래키, 제약조건, 동시성 등) - ACID
•Primary Key를 기준으로 Clustering되어서 저장된다. 즉, PK를 기준으로 순서대로 디스크에 저장되는 구조로 PK에 의한 Range Scan이 빠르다.
→ 데이터를 PK순서에 맞게 저장한다는 뜻이므로 Order By 등 쿼리에 유리할 수 있다.
•인덱스와 더불어 데이터까지 버퍼풀에 저장하기 때문에 모든 데이터가 메모리에 있으면 디스크를 읽지 않아도 된다.
→ 데이터 접근 속도가 빠르다
→ 인덱스와 데이터 모두 메모리에 적재되므로 메모리 사용 효율에 좋지 않다.
→ 메모리 때문에 로그 수집에 대한 용도로 InnoDB를 사용하면 안된다.
MyISAM
•구조가 단순해, 속도가 빠르다.
•데이터 저장에 실제적인 제한이 없고 매우 효율적으로 저장한다.
→ 메모리 효율이 InnoDB보다 좋음
•테이블 작업시 특정 행을 수정하려고 하면 테이블 전체에 락이 걸려서 다른사람이 작업 할 수 없다. (Table-level Lock)
→ 갱신이 많은 용도로는 성능적으로 불리. 동시 서비스에 적합X
→ multi-thread 환경에서는 성능이 저하될 수 있다.
•트랜잭션에 대한 지원이 없기때문에 작업도중 문제가 생겨도 이미 DB안으로 데이터가 들어감. DB 프로세스가 비정상 종료하면 테이블이 파손될 가능성이 높다.
→ 데이터 무결성이 보장되지 않는다.
•Full-text 검색이 가능하다
•주로 select 작업이 많은 경우에 사용된다. 즉, 읽기(READ)작업에 효과적이다.
InnoDB와 MyISAM 간단 비교
정리
InnoDB: 복잡해서 속도가 비교적 느리지만 다양한 기능을 제공하고 데이터를 더 안전하게 저장 가능
MyISAM: 단순해서 속도가 비교적 빠르지만 지원하지 않는 기능들도 있어 성능면에서는 비교적 떨어진다.
'2-1 > 데이터베이스' 카테고리의 다른 글
| 데이터베이스 기말고사 정리 (0) | 2023.06.06 |
|---|---|
| 데이터베이스 13주차 정리 (2) | 2023.06.03 |
| 데이터베이스 12주차 정리 (0) | 2023.05.22 |
| 데이터베이스 11주차 정리 (0) | 2023.05.15 |
| 데이터베이스 10주차 정리 (0) | 2023.05.08 |
