[CS] 데이터베이스 - 1

sarang_daddy·2023년 2월 21일
RDBMSdockermysql
0

Computer Science 😵‍💫

목록 보기
9/12
post-thumbnail
post-custom-banner

🎯 학습목표

데이터베이스에 대해 학습하고 Docker와 MySQL을 이용하여
데이터베이스를 설치하고 연결하여 SQL문법을 이용한 Table을 만들어 보자.


🌱 학습 키워드

데이터베이스

데이터베이스는 "데이터의 잡합"이라고 할 수 있다.

DBMS

Database Management System의 약자로서
"데이터의 집합"인 데이터베이스를 관리하고 운영하는 소프트웨어를 말한다.

과거의 데이터 저장방식(파일시스템)의 단점을 보관하고 대량의 데이터를 효율적으로 관리하고 운영하기 위해서
DBMS라는 소프트웨어가 나오게 되었다.

데이터의 저장소인 데이터베이스는 다수의 사용자나 응용 프로그램과 공유하고 접근이 가능해야 하는데
이를 지원해주는 소프트웨어 DBMS가 있기에 가능하다.

이런 DBMS에 데이터를 구축하고 관리하는데 사용되는 언어가 SQL이다.

DBMS 종류

DBMS제작사작동 운영체제기타
MySQLOracleUnix, Linux, Windows, Mac오픈 소스(무료), 상용
MariaDBMariaDBUnix, Linux, Windows오픈 소스(무료),MySQL 초기 개발자들이 독립해서 만듦
PostgreSQLPostgreSQLUnix, Linux, Windows, Mac오픈 소스(무료)
OracleOracleUnix, Linux, Windows상용 시장 점유율 1위
SQL ServerMicrosoftWindows주로 중/대형급 시장에서 사용
DB2IBMUnix, Linux, Windows메인프레임 시장 점유율 1위
AccessMicrosoftWindowsPC용
SQLiteSQLiteAndroid, iOS모바일 전용, 오픈 소스(무료)

DBMS의 특징

  • 데이터의 무결성 : 데이터는 어떤 경로를 통해 들어 오건 데이터에 오류가 있어서는 안 된다.
  • 데이터의 독립성 : DB의 크기가 변경되거나 저장소를 변경하더라도 기존 응용프로그램에는 영향이 없어야 한다.
  • 보안 : 사용자가 제한되어야 하며 계정에 따라 권한을 가져야 한다.
  • 데이터 중복의 최소화 : 동일한 데이터가 여러 개 중복되어 저장되는 것을 방지한다.
  • 응용 프로그램 제작 및 수정의 용이성 : 파일 각각의 포맷이 다르더라도 통일된 방식으로 작성이 가능하다.
  • 데이터의 안전성 : 백업, 복원 기능으로 데이터의 복원 또는 복구가 가능하다.

RDBMS (관계형 DBMS)

Relational DBMS 관계형 DBMS라 불리며, 대부분의 DBMS가 RDBMS형태로 사용된다.
데이터들이 하나 이상의 열과 행으로 이루어진 테이블(table)에 저장된다.

SQL

관계형 DBMS를 사용하고 관리하는데 필요한 언어다.

  • DBMS 제작 회사와 독립적이다. (표준 SQL이 존재한다.)
  • 다른 시스템으로 이직성이 좋다. (표준이 존재하기에)
  • 표준이 계속 발전한다.
  • 대화식 언어다.
  • 분산형 클라이언트/서버 구조를 지원한다. (클라이언트에서 질의하면 서버에서 처리 후 다시 클라에 전달해준다.)

SQL 간단 문법 정리

테이블

데이터베이스에서 데이블은 데이터를 구조화하여 저장하는 가장 기본적인 단위다.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   .....
);

/*
CREATE TABLE : DDL 명령어
table_name : 테이블 이름
column : 테이블의 열을 정의
datatype : 데이터 타입

위처럼 정의된 테이블은 'INSERT' 명령어로 데이터를 추가하거나 'SELECT'로 조회할 수 있다.
*/

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary FLOAT
);

MySQL

MySQL은 오픈 소스 관계형 데이터베이 관리 시스템(RDBMS) 중 하나로,
사용자가 데이터를 저장하고 조회하는 데 필요한 도구와 기능을 제공한다.

MySQL은 클라이언트/서버 아키텍처를 사용하여 데이터베이스에 연결되는 클라이언트 프로그햄과
데이터베이스 서버 프로그램으로 구성된다. 클라이언트는 데이터베이스 서버에 연결하고
데이터베이스 서버는 클라이언트의 요청에 따라 데이터를 저장하고 검색한다.

MySQL은 다양한 운영 체제에서 실행할 수 있으며, 다양한 프로그래밍 언어와 연동하여 사용할 수 있다.
또한, MySQL은 다른 RDBMS와 비교하여 빠른 속도, 안정성, 확장성, 보안성 등의 장점을 가지고 있다.

DDL

DDL(Data Definition Language)은 데이터베이스 객체를 정의하거나 수정하는 데 사용되는 SQL 명령어다.
데이터베이스에서 테이블을 생성, 수정 또는 삭제하거나, 인덱스를 추가 또는 제거하거나, 제약 조건을 추가하거나 제거하는 등의 작업에 DDL을 사용한다.
DDL 명령어는 데이터베이스 스키마를 정의하거나 변경하는 역할을 한다.

  • CREATE : 데이터베이스, 테이블, 인덱스, 뷰 등의 객체를 생성한다.
  • ALTER : 데이터베이스, 테이블, 인덱스, 뷰 등의 객체를 수정한다.
  • DROP : 데이터베이스, 테이블, 인덱스, 뷰 등의 객체를 삭제한다.
  • TRUNCATE : 테이블에서 모든 데이터를 삭제한다.
  • GRANT : 데이터베이스 객체에 대한 사용 권한을 부여한다.
  • REVOKE : 데이터베이스 객체에 대한 사용 권한을 취소한다.

DQL

DQL(Data Query Language)은 데이터베이스에서 데이터를 조회하는 데 사용되는 SQL 명령어다.
SELECT 문이 DQL의 가장 대표적인 예로 DQL 명령어는 데이터베이스에서 데이터를 검색, 필터링, 정렬하는 등의 작업을 수행하는 역할을 한다.
DQL은 데이터베이스 스키마를 변경하지 않고, 데이터를 조회하는 데 사용됩니다.

  • SELECT: 데이터베이스에서 데이터를 조회한다.
  • FROM: 조회할 테이블을 지정한다.
  • WHERE: 조건을 지정하여 데이터를 필터링한다.
  • ORDER BY: 데이터를 정렬한다.
  • GROUP BY: 데이터를 그룹화한다.
  • JOIN: 두 개 이상의 테이블을 연결하여 데이터를 조회한다.

도커(Docker)

도커(Docker)는 애플리케이션을 컨테이너화하여 배포 및 실행하는 오픈 소스 플랫폼이다.

다양한 프로그램, 실행환경을 컨테이너로 추상화하고 동일한 인터페이스를 제공하여
프로그램의 배포 및 관리를 단순하게 해준다.
백엔드 프로그램, 데이터베이스 서버, 메시지 큐등
어떤 프로그램도 컨테이너로 추상화하여 어디서든 실행이 가능하게 해준다.

초보를 위한 도커 안내서

도커 컨테이너

컨테이너는 격리된 공간에서 프로세스가 동작하는 기술로 가상화 상태의 개념이다.
컨테이너는 프로세스이기에 실행중인 프로세스가 없으면 종료된다.

  • 기존의 가상화 기술은 주로 OS를 가상화했다(VirtualBox 같은 가상머신).
  • 사용법은 간단하지만 무겁고 느려서 실제 서버 운영에는 사용의 어려움이 있다.
  • 이후 전체 OS를 가상화하는 대신 CPU 가상화, 반가상화 기술을 활용한 기술들이 등장 (KVM, Xen..)
  • 이것은 클라우드서비스의 가상컴퓨팅 기술의 기반이 된다.
  • 발전하면서 OS를 추가설치하는 가상화 방식이 아닌, 프로세스를 격리하는 방식이 등장한다.
  • 가볍고 빠르며, CPU나 메모리를 딱 프로세스가 필요한 만큼만 추가로 사용하는 방식이다.
  • 하나의 서버에 여러 컨테이너를 실행해도, 서로 영향을 미치지 않고 독립적으로 실행된다. (가벼운 VM을 사용하는 느낌)

도커 이미지

도커의 핵심 개념 중 하나는 이미지(Image)다.

도커 이미지는 애플리케이션과 그것이 실행되기 위해 필요한 모든 것을 포함하는 패키지라 할 수 있다.
예를 들어, 웹 애플리케이션을 실행하려면 웹 서버, 라이브러리, 설정 파일 등이 필요한데,
도커 이미지는 이러한 모든 것들을 포함하며, 애플리케이션을 실행하기 위해 필요한 모든 것을 제공해준다.

이미지는 Dockerfile이라는 텍스트 파일을 사용하여 작성된다.
Dockerfile은 애플리케이션을 빌드하는 데 필요한 모든 단계를 정의해준다.
예를 들어, 먼저 어떤 기반 이미지를 사용할지, 필요한 라이브러리를 설치할지, 설정 파일을 추가할지 등을 지정한다.

도커 이미지는 Docker hub에 등록하거나 Docker Registry 저장소를 직접 만들어 관리할 수 있다. 누구나 이미지를 쉽게 만들고 배포할 수 있다.

이미지는 컨테이너(Container)를 실행하는 데 사용된다.
즉, 컨테이너는 이미지를 기반으로 실행되며, 컨테이너는 애플리케이션을 실행하는데 필요한 모든 것을 포함한다.
이러한 컨테이너는 호스트 시스템과 격리되며, 각각의 컨테이너는 자체 파일 시스템 및 네트워크 인터페이스를 갖는다.
이를 통해 여러 컨테이너를 사용하여 애플리케이션을 분리하고, 더 안정적으로 실행할 수 있다.


🔥 구현해보기

1. Docker 설치

공식페이지에서 다운받거나 아래 명령어 사용

curl -fsSL https://get.docker.com/ | sudo sh

설치 확인

docker version

버전정보를 확인하면 클라이언트와 서버가 나뉘어져 있음을 알 수 있다.
도커는 하나의 실행파일이지만 실제로 클라이언트와 서버역할 각각 할 수 있다.

도커 커맨드를 입력하면 도커 클라이언트가 도커 서버로 명령을 전송하고
결과를 받아 터미널에 출력해준다.

도커 실행 명령어

docker run [OPTIONS] IMAGE[:TAG|@DIGEST] [COMMAND] [ARG...]
OPTIONS설명
-ddetached mode 흔히 말하는 백그라운드 모드
-p호스트와 컨테이너의 포트를 연결 (포워딩)
-v호스트와 컨테이너의 디렉토리를 연결 (마운트)
-e컨테이너 내에서 사용할 환경변수 설정
–name컨테이너 이름 설정
–rm프로세스 종료시 컨테이너 자동 제거
-it-i와 -t를 동시에 사용한 것으로 터미널 입력을 위한 옵션
–link컨테이너 연결 [컨테이너명:별칭]

2. MySQL Docker 컨테이너 생성

Docker를 통해 mysql 이미지를 가져오면 mysql설치 없이 Docker 컨테이너에 mysql 환경을 만들 수 있다.
아래와 같이 입력한다.

docker run -d -p 3307:3306 --platform linux/amd64 -e MYSQL_ROOT_PASSWORD=비밀번호 --name 이름 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
  • 3307포트로 한 이유는 mysql 디폴트 포트가 3306이라 로컬 컴퓨터에 mysql이 깔려있으면 포트가 겹친다.
  • character-set-server와 collation-server은 환견변수를 설정해주는거다.
  • 구현을 mysql 5.7 버전을 사용하기에 한글과 이모지 사용을 위해 필요한 설정이다.

🚫 최신 버전의 mysql을 사용하는 경우는 아래처럼 입력하면 된다.

$ docker run --name 컨테이너이름 -e MYSQL_ROOT_PASSWORD=비밀번호 -d -p 3306:3306 mysql:latest

생성된 컨테이너를 확인 할 수 있다.

docker ps -a

3. 컨테이너 실행

MySQL 컨테이너 bash 쉘 접속 (컨테이너에 명령어 사용 위해?)

docker exec -it 컨테이너이름 bash

sarang-mysql에 접속한다. (컨테이너 생성 당시 비밀번호 입력)

mysql -u root -p

4. 데이터베이스 생성

생성할 데이터베이스 이름을 설정하고 생성한다.

CREATE DATABASE testDB;

생성한 데이터베이스를 선택한다. (사용)

USE testDB;

생성된 데이터베이스 확인

show databases


5. 사용자 생성 및 권한부여

root 사용자는 전체 권한을 가지고 있기에 위험이 따른다.

새로운 사용자를 만들고 필요한 권한을 부여하자.

MySQL 사용자 추가/ DB 생성 / 권한 부여 방법

CREATE USER 'userID'@'%' identified by 'userpassword';
GRANT ALL PRIVILEGES ON dbname.* to userID@'%';

권한 설정을 적용해준다.

FLUSH PRIVILEGES;
exit

새로운 사용자로 접속한다.

mysql -u userID -p

데이터베이스에 테이블을 만든다.

CREATE TABLE tablename (field1 field1-type PRIMARY KEY, field2 field2-type NOT NULL);
  • NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE 등의 제약 조건을 할당해줄 수 있다.


6. 데이터베이스 DATA 생성하기

JavaScript와 nodeJS로 TXT파일 생성하기

Table에 들어갈 data를 임의로 만들고 txt 파일화 한다.

const fs = require("fs"); // 파일 처리 모듈
const randomWords = require("random-words"); // 랜덤 영어 단어

// 닉네임 필드
const getNickname = () => {
  const word = randomWords();
  const string = getStr(3);
  const number = getNum(4);
  return word + string + number;
};

const getStr = (number) => {
  const CHARACTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
  const length = CHARACTERS.length;
  let str = "";
  for (let i = 0; i < number; i++) {
    str += CHARACTERS.charAt(Math.floor(Math.random() * length));
  }
  return str;
};

const getNum = (number) => {
  let num = "";
  for (let i = 0; i < number; i++) {
    num += Math.floor(Math.random() * 10);
  }
  return num;
};

// 돈 필드
const getMoney = () => {
  const RANGE = 100000;
  return Math.ceil(Math.random() * RANGE);
};

// 방문기록 필드
const getVisitTime = () => {
  const DAYS = 30;

  // 지금으로부터
  const end = new Date();
  // 30일 전
  const start = new Date(new Date().setDate(end.getDate() - DAYS));

  return new Date(
    start.getTime() + Math.random() * (end.getTime() - start.getTime())
  ).toLocaleString();
};

// 100만개의 랜덤 샘플 생성
const insertBulk = () => {
  const COUNT = 1000000;
  let txt = "";
  for (let i = 1; i <= COUNT; i++) {
    const nickName = getNickname();
    const money = getMoney();
    const last_visit = getVisitTime();
    txt += `${nickName},${money},${last_visit}\n`;
  }

  fs.writeFileSync("./userlog.txt", txt, { encoding: "utf-8" });
};

insertBulk();

fs 모듈 정리
random-words 모듈


7. TXT DATA를 데이터베이스에 로드하기

생성된 TXT 경로 확인

/Users/sarang_daddy/testDB/userlog.txt

위에서 생성된 TXT를 Docker 컨테이너에 추가하기

docker cp [host 파일경로] [container id]:[container 내부 파일경로]

Docker 파일 붙여넣기, cp 명령어

데이터베이스 테이블에 txt 불러오기

mysql> LOAD DATA LOCAL INFILE '/home/userlog.txt'
    -> INTO TABLE user_log
    -> FIELDS TERMINATED BY ',';
  • 100만개의 log를 추가하는데 15.62초가 걸렸다.

MySQL LOAD DATA

테이블 data 삭제하기

truncate user_log;

위 과정 중 잘못된 data가 들어갔을 때 테이블 내용을 삭제하고 다시 한다.
(내가 그랬다... js 파일을 load해 버렸다.....)

MySQL 테이블 내용 삭제

모든 table data 확인하기

select * from user_log;


+) nodeJS에서 데이터베이스에 data 로드하기

const mysql = require("mysql");
const randomWords = require("random-words");
const { performance } = require("perf_hooks");

const connection = mysql.createConnection({
  host: "localhost",
  user: "sarang",
  password: "1234",
  database: "testDB2",
  port: "3307",
});

connection.connect();

const getNickname = () => {
  const word = randomWords();
  const string = getStr(3);
  const number = getNum(4);
  return word + string + number;
};

const getStr = (number) => {
  const CHARACTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
  const length = CHARACTERS.length;
  let str = "";
  for (let i = 0; i < number; i++) {
    str += CHARACTERS.charAt(Math.floor(Math.random() * length));
  }
  return str;
};

const getNum = (number) => {
  let num = "";
  for (let i = 0; i < number; i++) {
    num += Math.floor(Math.random() * 10);
  }
  return num;
};

const getMoney = () => {
  const RANGE = 100000;
  return Math.ceil(Math.random() * RANGE);
};

const getVisitTime = () => {
  const DAYS = 30;
  const end = new Date();
  const start = new Date(new Date().setDate(end.getDate() - DAYS));
  return new Date(
    start.getTime() + Math.random() * (end.getTime() - start.getTime())
  ).toLocaleString();
};

const insertBulk = () => {
  const COUNT = 1000000;
  let values = [];
  for (let i = 1; i <= COUNT; i++) {
    const nickName = getNickname();
    const money = getMoney();
    const last_visit = getVisitTime();
    values.push([nickName, money, last_visit]);
  }

  const query = "INSERT INTO user_log (nickname, money, last_visit) VALUES ?";

  const start = performance.now();
  connection.query(query, [values], (error, results, fields) => {
    if (error) throw error;
    console.log("Inserted", results.affectedRows, "rows");
    connection.end();
    const end = performance.now();
    console.log(`myProgram 실행 시간: ${(end - start) / 1000} 초`);
  });
};

insertBulk();
  • 실행결과 Error: Packets out of order. Got: 1 Expected: 4 발생
  • 1000개의 data는 성공적으로 로드 되는데 100만개의 data는 실패했다.

디폴트 Packet 용량(4mb)를 초괴해서 한번에 데이터를 INSERT하게 되면 에러가 발생한다.

mysql의 전송 packet 용량 늘리기

SET GLOBAL max_allowed_packet=1000000000;

Packet 오류 문제

Data 100만개 load 결과

packet 용량 늘리기로 Data load에 성공했다.

  • 앞서 진행한 txt(csv) 형식보다는 느린 결과를 확인 할 수 있다.

csv 파일형식 알아가기

CSV 파일은 Comma-Separated Values 파일의 약자로, 쉼표로 구분된 텍스트 파일입니다. 이 파일 형식은 표 형태의 데이터를 저장하기 위해 일반적으로 사용됩니다. 각각의 행은 개별 레코드를 나타내며, 각 레코드는 쉼표로 구분된 값들의 집합으로 구성됩니다.

CSV 파일은 텍스트 파일이므로, 대부분의 텍스트 편집기나 스프레드시트 프로그램에서 열 수 있습니다. 데이터를 CSV 파일로 내보내는 경우, 보통의 경우에는 파일 확장자가 ".csv"로 지정됩니다.

CSV 파일은 데이터베이스나 스프레드시트 프로그램에서 사용되는 데이터를 다른 프로그램에서 사용하기 쉽도록 내보낼 때 자주 사용됩니다. 또한, 웹 애플리케이션에서도 많이 사용됩니다. 데이터베이스에서 쿼리 결과를 CSV 파일로 내보내거나, 웹 페이지에서 표 형태의 데이터를 표시하기 위해 CSV 파일을 사용할 수 있습니다.


참고자료

MySQL 공식 git
MySQL 한글설치
MySQL 비빌번호 등급 조절
Docker를 사용하여 MySQL 설치하고 접속하기
[Docker] MySQL설치 및 접속하기
우분투 20.04에 MySQL 5.7 설치
MySQL 5.7 설치
SQL 기본 문법

profile
sarang_daddy
한 발자국, 한 걸음 느리더라도 하루하루 발전하는 삶을 살자.
이전 포스트

[CS] 네트워크 - 2

다음 포스트

[CS] 데이터베이스 - 2

post-custom-banner

0개의 댓글