Data Base & Docker & mySQL

가상환경을 위한 Docker에 mysql 서버를 설치 후 DataBase시스템을 다뤄보자.

1. Docker설치

Docker란 ?

각 요소들이 설치된 모습을 이미지로 박제 후, 도커허브에 업로드 & 공유됨.

컨테이너라는 독립된 가상 공간에 실행 환경을 그대로 재현할 수 있음.

Docker 검색 후 OS에 맞는 docker설치

2. 설치 확인 , mysql images search & pull , mysql server container 생성

2-1 설치 확인을 위해 터미널에 docker -v 로 도커 버젼확인

2-2 docker search mysql 로 images searching.

2-3 docker pull mysql 로 image pull

• Mysql:version
• 명령어 뒤에 :(태그)로 특정 버전을 다운할 수 있으며, 명시하지 않으면 최신 버전인 latest를 가져온다.
  
  

2-4 docker images 로 이미지 다운된 것 확인

2-5 docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password --name container_name mysql 로 컨테이너 생성

• -p 3306:3306 : 호스트의 3306포트와 컨테이너의 3306포트를 연결한다.

  즉, 호스트에 3306포트 접근 발행 시 해당 컨테이너에 접속이 되게끔 설정한 것.

• -e MYSQL_ROOT_PASSWORD=password : 컨테이너를 생성하면서 환경변수를 지정한다. root계정의 비밀번호를 설정한다.

• -name mysql_test : 컨테이너의 이름은 mysql_test로 지정한다. 이름에 공백이 있으면 에러가 나니 유의할 것.

• 참고

  --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

  로 컨테이너 생성 시에 utf-8mb4 (한글 및 이모지 포함) 설정 가능.

3. 컨테이너 생성 확인 , 컨테이너 배쉬로 실행

3-1 docker ps -a 로 컨테이너 상태 확인

3-2 docker exec -it container_name bash 로 컨테이너 실행

3-3 mysql -uroot -p 로 mysql 서버 접속

4. 컨테이너에 vim 편집기 설치, config 파일 설정으로 한글 및 이모지 깨짐 안되게 설정

4-1 컨테이너에 vim 편집기 설치 안되어있어 설치 진행

Vi /etc/my.cnf 로 config 파일에 utf8mb4 설정

5. 데이터 베이스 생성 및 사용자 생성 , 사용자에게 컨테이너 내 mysql에서 모든 권한 부여

5-1 create database db_name 으로 데이터 베이스 생성

5-2 use db_name 으로 사용할 DB 설정

5-3 create user 'id'@'localhost' identified by 'password' 로 유저 생성

• localhost 대신 컨테이너 외부에서 mysql 로그인 가능한 유저를 만들기 위해 %를 사용.
  
  

5-4 grant all privileges on de_name.* to 'id'@'%' 로 유저에게 모든 권한 부여

5-5 flush privileges 로 grant 테이블을 reload 함으로서 변경 사항을 즉시 반영.

6. Mac OS mysql GUI인 Sequel Ace 설치 , 계정 연결 후 쿼리문 실행

6-1 Mac App store에서 Sequel Ace 설치

6-2 IP , 생성한 유저 정보로 데이터베이스 접속

6-3 select version() 쿼리문으로 정상 작동 확인

7. 데이터베이스에 테이블 생성

--- 테이블 요구사항 ---

대용량 데이터 생성

다음과 같은 정보를 포함하는 user_log 테이블을 생성한다.

    nickname varchar(64),
    money dec(10, 2),
    last_visit datetime
코드복사

• user_log 테이블에 100만건의 대용량 데이터를 생성해서 넣어야 한다.
• 다음 조건을 만족하는 데이터를 직접 테이블에 넣거나 또는 INSERT 구문을 작성하는 스크립트를 작성한다.

데이터 생성 규칙

• 사용자 nickname 은 영어 단어 100개 + 랜덤 문자열 3자리 + 랜덤 숫자 4자리로 생성한다.
• money 는 1부터 100,000 사이 값을 적당하게 분포하게 만든다.
• last_visit 은 최근 한 달 사이로 랜덤 시각으로 생성한다.

---

7-1 user_log 테이블 생성

• create table user_log( // user_log 테이블 생성

  id INT NOT NULL AUTO_INCREMENT, // id라는 컬럼 생성, INT : 숫자타입 , NOT NULL : 데이터 없으면 안됨 설정, AUTO_INCREMENT : row 생성 시 id 자동으로 하나씩 증가하며 생김.

  nickname VARCHAR(64) // nickname 컬럼 생성, VARCHAR(64) : 가변문자열이며 64개 까지 설정 가능. 64개보다 적으면 자동으로 설정 숫자 줄어듬.

  money DEC(10,2) // money 컬럼 생성 , DEC(10,2) : 소숫점 2개까지 , 소숫점 앞에 숫자 10개까지 설정

  last_visit DATETIME // last_visit 컬럼 생성 , DATETIME : 날짜와 시간을 같이 나타내는 타입 설정

  PRIMARY KEY (id) // id를 기본 키로 설정
  
  

7-2 show tables 로 table 생성되었는지 확인

7-3.desc user_log 로 table 생김새 확인

7-4 nickname중 영어 단어 100개 생성을 위해 따로 randomnickname 테이블을 생성했다.

7-5 100개의 닉네임을 생성하는 procedure 생성

7-6 프로시저 생성 확인 , 콜 프로시저 , 테이블 데이터 확인

• show procedure status where db="db_name" 으로 해당 데이터베이스에 생성된 프로시저 확인
• call procedure_name() 으로 프로시저 실행
• select * from table_name 으로 해당 테이블 데이터 확인

7-7 100만건 row 생성을 위해 프로시저 생성

delimiter $$
drop procedure if exists makeData;
create procedure makeData()
begin
declare i int default 0;
while i < 1000000 do
    insert into user_log(nickname, money, last_visit) values(
      concat((select name from randomnickname order by rand() limit 1), 					(char(floor(rand() * 1000 % 26) + 65)), 
				(char(floor(rand() * 1000 % 26) + 65)), 
				(char(floor(rand() * 1000 % 26) + 65)), 
      	(floor(rand() * (10000 - 1000) + 1000))),
      (rand() * (100000 - 1) + 1),
      (FROM_UNIXTIME(UNIX_TIMESTAMP('2021-01-01 00:00:01') + FLOOR(
        RAND() * 2592000)))
    	)
    );
set i = i + 1;
if (i % 1000 = 0) then
commit;
end if;
end while;
end; $$
delimiter;

• (select name from randomnickname order by rand() limit 1)

  -> 100개 랜덤 닉네임이 들어간 randomnickname table에서 limit 1 : 한개씩 데이터 뽑아 사용

• (char(floor(rand() * 1000 % 26) + 65))

  -> 랜덤 문자열을 만드는 코드

• (floor(rand() * (10000 - 1000) + 1000))

  -> 랜덤한 숫자 4자리를 만드는 코드

• concat을 사용해 닉네임+랜덤문자열3개+랜덤숫자4자리를 value로 지정

• SET AUTOCOMMIT = false; 로 오토커밋 안하게 만들고(시간문제) 1000번 돌때마다 커밋하게 만듬.

중요 ! multiple insert

위의 방식으로 100만건의 데이터를 넣었더니, 시간이 너무 오래 걸려서 다른 방법을 갈구했다.

그러던중 multiple insert를 통해 시간을 획기적으로 줄였다.

결과는 while문을 50000건으로 줄이고, 밸류를 쿼리 한번에 20번 넣어서 시간을 줄였다.

한개의 sql문에 길이 제한이 있어 20번을 했지만 config파일에서 max_allowed_packet 확인해보고 조정 가능하다.

7-8 100만건 데이터 row 확인

• Count(*)를 하면 해당 테이블의 row 갯수를 알 수 있다.

---

참고자료들

docker에서 mysql설치

유저 생성,조회,등등

mysql 대량 insert하는법

mysql컨테이너에 vim설치

도커가 뭐고, 왜 쓰는가?