[1주차] Real MySQL 1~3장 발표자료

월간-CS, RealMySQL 토론스터디 1주차 (1~3장) 발표자료입니다. - [Ref]
10.29.화 ~ 11.03.일 까지 작성한 DIL을 기반으로 준비하였습니다. - [Ref]

발표에 앞서

1주차 학습 진도는 DBA*의 업무 중에서도 기본인 관리*에 포커스를 둔 기분입니다.
인프라 담당자라는 직함에 걸맞게 다양한 적용사례*의 인사이트를 얻을 수 있었습니다.
전체 내용 중에서도 이중 비밀번호*, 역할 기반의 RBAC 방향성*이 뜻깊었습니다.

1. 소개

2. 설치와 설정

3. 사용자 및 권한

1장 소개

10.29, 화요일에 책을 처음 읽었을 때에는 큰 감흥이 없었습니다.
스스로가 MySQL의 시스템, 아키텍처 레벨에 대한 지식이 무지하지는 않다고 생각했고
그저 DMG 패지키*파일이 아닌 Brew로 설치를 하면서 가볍게 실습하였습니다.

저자 서문*과 본론 2~5p*에서 도서 Real MySQL은
다양한 이유를 들어서 엔지니어가 MySQL을 선택해야 하는 저자의 견해를 제시합니다.
특히 몇 가지 이유들로 MySQL 선택이 왜 좋은지에 대한 의지를 보여준다고 느낍니다.

하지만 기술 선택의 기준이 트랜드, 점유율 등이 되면 안된다고 생각합니다.

관계형 데이터베이스가 시장의 주류를 차지하는 것은
시장의 대다수의 도메인(웹 서비스)가 관계형 데이터베이스에 적합하기 때문입니다.

실제로 앱, 게임, 채팅, 라이브, 쇼핑몰, 금융권, 블록체인, AI 등의 도메인의 등장이
시장 트랜드 상에도 많은 부분이 반영되어 있다고 생각합니다.

그 중에서도 게임 서버와 웹 서버만 생각하더라도
목표 지연시간, 처리되는 쿼리의 종류 등이 크게 다른 것을 알 수 있습니다.

[리서치] 시장 트랜드

사실 모든 설문조사가 그렇듯 이들은 어느 정도의 편향성을 가지고 있습니다.
이러한 점을 고려하고 Stackoverflow 2022, 2023 설문조사를 보겠습니다.
전체적으로 관계형 데이터베이스들이 시장의 주류를 차지하고 있습니다.

PostgreSQL, MySQL, SQLite, Microsoft SQL Server, Oracle

하지만 2022 대비 2023에는 생소한 이름의 DB들의 점유율이 상승하고 있습니다.
이는 단순히 시장 트랜드 증가나 신기술에 미친 엔지니어의 광기일까요?
아니면 자신이 자신있는 DB가 Cassandra, Firebase라서 고른걸까요?

Redis, ElastiCache, DynamoDB, Firebase, SQLite, Cassanrda

빅테크 기업에서 정말로 트랜드나 점유율 따위의 것들로 DB를 선택할까요?
사실은 이름도 생소한 다양한 DB들은 각각의 목적이 존재하고 있습니다.

Redis : 인메모리 캐시 디비로 수십ms 지연시간의 대량의 조회 및 갱신에서 사용
ElastiSearch : 전문검색과 같이 복잡한 검색 기능에서 사용
DynamoDB : 도큐먼트 기반 데이터베이스로서 유저관리, 게임서버 등에서 사용
Firebase : 모바일 서비스에서 주로 사용하며 푸쉬알림 등의 모바일 전용 기능 지원
SQLite : 모바일 등의 기기에서 로컬 캐시 목적으로 사용
Cassandra : 분산 시스템에서 특정한 요구사항이 필요한 경우 선택적으로 사용

따라서 전혀 다른 목적의 제품군의 트랜드나 점유율을 비교하는 것은 틀렸습니다.
또한 이러한 비교를 기반으로 DB를 선택하는 것은 전혀 논리적이지 못합니다.

• 휘발성 DB인 Redis와 비휘발성 DB인 MySQL의 비교는 옳은가?

• 분석용 DB인 RedShift와 트랜잭션용 DB인 MySQL의 비교는 옳은가?

• 도큐먼트와 도큐먼트 내부 참조 기반의 DB인 MongoDB와
  정규화 테이블과 테이블 칼럼 간 참조 기반의 DB인 MySQL의 비교는 옳은가?

• 샤딩 및 캐시 서버로서의 기능 지원이 되는 DB인 DynamoDB (DAX)와
  리드 레플리카, 샤딩 등의 추가 설정을 해야 하는 DB인 MySQL의 비교는 옳은가?’

[생각] 웹서버와 MySQL 서버의 문제

전통적인 웹서버의 경우
지연시간 보다 기능의 안정성, 시스템의 가용성을 추구하는 경향이 있습니다.

웹서버를 구성하는 다양한 부분에서 이를 느낄 수 있습니다.

1. 네트워크 레벨에서 TCP 통신을 사용한다.

2. 지연시간 관점에서 1,000 ~ 10,000ms 내외의 지연시간을 목표로 합니다.

3. 시스템 가용성 관점에서 99.9 ~ 99.999 uptime을 목표로 합니다.

4. 시스템 가용성 관점에서 병목을 제거하기 위해 메세지, 이벤트, 스케쥴러를 사용합니다.

스터디 주제가 MySQL 인만큼
웹서버를 개발하다보면 대체적으로 CRUD* 중에서 R*의 호출 빈도가 많은 편입니다.

CRUD* : Create, Read, Update, Delete

[생각] 게임서버와 MySQL 서버의 문제

게임서버의 경우
기능의 안정성, 시슽메의 가용성보다 저지연시간을 우선순위로 두는 것 같습니다.

“이 말은 시스템 장애가 나도 괜찮다.” 라는 관점의 말이 아닙니다.
사용자 경험(UX) 측면에서 더 낮은 레벨의 지연시간을 요구한다는 의미입니다.

게임서버를 구성하는 다양한 부분에서 이를 느낄 수 있었습니다.

1. 네트워크 레벨에서 UDP 통신을 사용한다.

2. 지연시간 관점에서 10~200ms 내외의 지연시간을 목표로 합니다.

   멀티 플레이 게임에서 60fps 속도로 프레임 당 통신 횟수 비율에 따라서
   통신 지연시간이 최저 100ms에서 최대 16ms까지 요구될 수 있습니다.
   - fps:udp = 1:1 일 때, 16ms (1,000 ms / 60fps)
   - fps:udp = 6:1 일 때, 100ms (1,000 ms / (60fps / 6)

3. 지연시간 관점에서 속도를 개선하기 위해서 캐시를 적극적으로 활용합니다.

2장 설치와 설정

MacOS의 패키지 매니저 Brew를 사용하여 설치를 하였습니다.
따라서 brew 커맨드를 이용해서 MySQL 서버 제어가 가능했습니다.
그 외에는 동일하게 mysql 커맨드로 MySQL 클라이언트 사용이 가능합니다.

brew install mysql@8.0

brew services start mysql@8.0
brew services stop mysql@8.0

mysql -u root

[설치] MySQL 메이저, 마이너, GA 버전

MySQL의 버전은 메이저 버전, 마이너 버전으로 구분됩니다.
그리고 각 메이저 버전이 릴리즈되고 GA* 버전이 릴리즈 됩니다.

• 메이저 버전 : 5.1, 5.5, ..., 5.6

• 마이너 버전 : 5.1.1, 5.1.2, ... 5.1.8

[설치] MySQL 업그레이드

MySQL 버전 업그레이드는 메이저 버전 1칸씩만 가능합니다.
또한 각 메이저 버전의 마이너 버전이 GA* 버전에 해당해야 합니다.
업그레이드는 인플레이스와 논리적 업그레이드가 있으며 둘 모두 장애 발생합니다.

• 인플레이스 업그레이드 : DB가 꺼지고 켜지는데 장애 발생

• 논리적 업그레이드 : DB가 변경되면서 DNS(IP) 등이 변경되는데 장애 발생

[설치] MySQL 8.0.16~ 인플레이스 업그레이드

아마 MySQL 5.7과 8.0에서 큰 변화가 있었기 때문에
도서에서도 MySQL 5.7에서 8.0으로 인플레이스 업그레이드를 다룹니다.

이 업그레이드는 MySQL 8.0.16 전후로 큰 차이점이 발생합니다.
MySQL 8.0.16에서는 단순히 8.0.16 버전의 바이너리 파일을 다운로드 받고
파일 디렉토리 경로를 일치시킨 상태로 재시작하면 업그레이드가 진행됩니다.

실제로는 7단계에 걸친 작업이 진행되지만, 도서에서는 자세히 다루지 않습니다.

[설치] MySQL ~8.0.15 인플레이스 업그레이드

MySQL 8.0.15 에서는 자동 업그레이드가 지원되지 않습니다.
 

데이터 디렉토리를 공유한 상태에서
MySQL Server 5.7에서 Upgrade Checker로 오류가 없는지 사전에 확인하며
MySQL Server 8.0 Binary를 미리 준비하고 데이터 디렉토리가 MySQL 5.7 쪽을 가리키며 최종적으로 에러 로그가 없다면 MySQL 8.0를 사용하게 됩니다.

이는 세부적으로 7단계에 걸쳐서 진행됩니다.

1. MySQL Server 5.7 가동 중

2. MySQL Server 5.7에 Upgrade Checker 실행하기 - Ref

3. mysqldump 및 mysqlpump로 데이터 백업하기

4. MySQL Server 8.0 Binary 다운로드하기 - Ref

5. mysql_upgrade 진행하기

6. MySQL Server 8.0(5.7) 재시작하기

7. MySQL Server 8.0(5.7) 로그 확인하기

[설정] 시스템 변수

시스템 변수(System Variables)는 MySQL 서버가 가동되며 읽는 설정파일입니다.
메모리나 접속 방식을 초기화하고 접속자를 제어하기 위한 설정값들을 포함합니다.

이러한 시스템 변수는 5가지 특성을 지니고 있으며
MySQL 8.0 이전에는 Global, Session, Both가 있었으며,
MySQL 8.0 이후에는 Static, Dynamic 개념이 도입되었습니다. - [Ref]

[설정] 글로벌, 세션 변수와 Both 타입

시스템 변수는 적용 범위*에 따라서 3가지 타입으로 구분됩니다.

시스템 변수 리스트는 공식 문서*에서 확인할 수 있습니다. - [Ref]
실제 값은 아래와 같이 SQL 쿼리로 이를 조회할 수 있습니다.

SHOW VARIABLES LIKE '<KEY>';
SHOW GLOBAL VARIABLES LIKE '<KEY>';

대표적인 글로벌 변수 중 하나인
max_connections, innodb_buffer_pool_size 등을 조회할 수 있습니다.

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'

[설정] 정적, 동적 변수

시스템 변수는 서버의 재시작 유무에 따라서 2가지로 구분됩니다.

1. Static

[설정] 시스템 변수 변경하기

시스템 변수를 변경하는 3가지 방법을 가지고 있으며,
각 방법 마다 반영 시점*, 재시작 시 유지 여부* 등이 다릅니다.

1. PoC를 위해서 임시로 설정을 바꾸고 싶다 → SET

2. 즉시, 그리고 영속적으로 설정을 바꾸고 싶다 → SET PERSIST

3. 새벽 업데이트에 데이터 변경을 예정하고 싶다 → SET PERSIST_ONLY

3장 사용자 및 권한 (53~75p)

MySQL의 사용자 계정은 아이디@호스트 헤더*로 구성되어 있습니다.
사용자 계정, 권한, 역할과 관련한 5가지 부분을 다루고 있습니다.

1. 사용자 게정 식별

2. 사용자 계정 관리

3. 비밀번호 관리

4. 권한(Privileges)

5. 역할(Role)

[계정] 계정의 구분

MySQL 8.0 부터 계정*은 SYSTEM_USER 권한 유무로 2가지로 구분됩니다.

1. 시스템 계정(System Account)

2. 일반 계정(Regular Account)
    

이와 관련해서 몇 가지 특징이 추가로 존재합니다.

1. 시스템 계정은 일반 계정을 관리할 수 있다.

2. 일반 계정은 시스템 계정을 관리할 수 없다.

3. 시스템 계정으로만 할 수 있는 몇가지 작업들이 존재한다

4. 계정 관리(계정 생성 및 삭제, 그리고 계정의 권한 부여 및 제거)

5. 다른 세션(Connection) 또는 그 세션에서 실행 중인 쿼리를 강제 종료

6. 스토어드 프로그램* 생성 시 DEFINER를 타 사용자로 지정

스토어드 프로그램(Stored Program)이란?* - [Ref]

스토어드 프로그램은 MySQL 내부에서 절차적 SQL을 처리할 수 있는 기능입니다.
PROCEDURE, FUNCTION, TABLE TRIGGER, EVENT 등이 지원됩니다.

[계정] 계정 식별의 기본 규칙

MySQL 8.0 부터 계정*을 식별하는 몇 가지 규칙이 적용됩니다.

1. 따옴표(‘)*는 선택적이며 유효하지 않은 문자열에만 사용합니다.

2. 호스트 IP(127.0.0.1)*는 선택값이며 생략할 경우 와일드카드(%)*가 할당됩니다.

3. 호스트 IP(me)*는 와일드 카드(%)*가 할당됩니다.

4. 따옴표(‘)* 대신 백틱(`), 쌍따옴표(“) 등을 사용할 수 있습니다.

다만 후술할 역할(Role)*은 계정(Account)*과 구분되지 않습니다.

SELECT user, host, super_priv FROM mysql.user;

+------------------+-----------+------------+
| User             | Host      | Super_priv |
+------------------+-----------+------------+
| role_emp_read    | %         | N          | # Role
| role_emp_write   | %         | N          | # Role
| reader           | 127.0.0.1 | N          | # Regular Account 
| writer           | 127.0.0.1 | N          | # Regular Account 
| mysql.infoschema | localhost | N          | # System Account
| mysql.session    | localhost | Y          | # System Account
| mysql.sys        | localhost | N          | # System Account
| root             | localhost | Y          | # System Account
+------------------+-----------+------------+

혹은 현재 사용 중인 계정을 조회할 수도 있습니다.

SELECT CURRENT_USER();

[테스트] 사용자 식별에 대한 시나리오 테스트

따라서 MySQL 클라이언트 연결 시,
아래의 4가지 상황을 예상(expect)하였으나 그 중 2가지는 오답(⛳️)이었습니다.

mysql -u root              # expect:성공, result:성공
mysql -u root@'localhost'  # expect:성공, result:실패 ⛳️
mysql -u root@127.0.0.1    # expect:성공, result:실패 ⛳️
mysql -u root@'hello'      # epxect:실패, result:실패

리서치 한 결과 MySQL 클라이언트의 호스트 정보는 -h HOST의 옵션을 통해서 부여함을 알았습니다.
이후 5가지 상황을 예상(expect)하였으나 그 중 3가지는 오답(⛳️)이었습니다.

mysql -u root                # expect:실패, result:성공 ⛳️
mysql -u root -h localhost   # expect:실패, result:성공 ⛳️
mysql -u root -h 'localhost' # epxect:성공, result:성공
mysql -u root -h 127.0.0.1   # epxect:실패, result:성공 ⛳️
mysql -u root -h hello       # expect:실패, result:실패

결국 사용자 설정의 네이밍 규칙들은 MySQL 서버에 기록되는 상태의 규칙이었음을 알았습니다.
또한 -h 옵션에 대한 설명을 공식 문서에서 찾을 수 있었습니다. - Ref [1] 6.2.4 Connecting to the MySQL Server Using Command Options

1. -h 을 할당하지 않을 경우 기본값은 localhost로 할당된다. -

   The default host name is localhost. On Unix, this has a special meaning, as described later.

2. -h 에서 127.0.0.1과 localhost는 동일한 단어로 인식된다.

   To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1 (instead of localhost), or the IP address or name of the local server. You can also specify the transport protocol explicitly, even for localhost, by using the --protocol=TCP option. Examples:

이와 관련하여 더 정확한 개념은
127.0.0.1 및 ::1은 기본적으로 localhost에 대한 루프백 인터페이스로 사용된다는 점입니다. - Ref [2] 8.2.4 Specifying Account Names

1. 사용자 PC 내부에서 포트 기반의 사설 네트워크망 사용을 위해서 루프백 인터페이스 개념을 사용합니다.

2. IPv4 루프백 인터페이스는 127.0.0.1이며, IPv6의 루프백 인터페이스는 ::1입니다.

3. cat /etc/hosts | grep 'localhost'를 이용해서 루프백 인터페이스 내용을 확인할 수 있습니다.

A host value can be a host name or an IP address (IPv4 or IPv6). The name 'localhost' indicates the local host. The IP address '127.0.0.1' indicates the IPv4 loopback interface. The IP address '::1' indicates the IPv6 loopback interface.

[테스트] 루프백 인터페이스에 대한 시나리오 테스트

3.1.3. 사용자 식별에 대한 시나리오 테스트에서
기본적으로 localhost는 IPv4 혹은 IPv6의 루프백 인터페이스 주소를 가짐을 알게 되었습니다.

하지만 왜 기본값이 IPv4인지는 의문입니다.

mysql -u root -h 127.0.0.1  # expect:성공, result:성공
mysql -u root -h ::1        # expect:성공, result:실패

[리서치] 사용자 계정 정보의 CIDR Block의 활용

추가적으로 충격적인 부분은 호스트 IP에 일반적인 IPv4가 아니라 IPv4 의 CIDR Block을 사용할 수 있다는 점이었습니다.
이를 사용하면 호스트 헤더 칸엔 10.0.1.0/24을 입력하여 10.0.1.0 ~ 10.0.1.255의 IP의 공용 계정을 만들 수 있습니다.

실제로 웹 서버에서도 활용할 수 있는지 모르겠지만,
MySQL 서버에서 특정한 서버(EC2 Instance)가 속하는 특정한 서브넷(Subnet)의 대역폭만 여는 식으로 작업이 가능해 보입니다.

어디까지나 가설 혹은 상상일뿐, 실제로는 PoC를 진행해보어야 합니다.

[계정] 고수준 비밀번호

3.2.2. 계정 생성에서 배운 것처럼
MySQL 서버의 비밀번호는 유효기간, 히스토리 관리 등 재사용 금지가 가능합니다.

mysql> INSTALL COMPONENT 'file://component_validate_password';

mysql> SELECT * FROM mysql.component;

mysql> SELECT GLOBAL VARIABLES LIKE 'validate_password%';

다만 이 기능은 [계정] 이중 비밀번호와 함께 사용할 때,
더 강한 효과를 낼 것 같습니다.

[계정] 이중 비밀번호

MySQL 8.0 부터틑 이중 비밀번호를 사용할 수 있습니다.
쉽게 생각하면 비밀번호 변경 이후에도 구버전의 비밀번호를 사용할 수 있음을 의미합니다.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password';
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

[테스트] 이중 비밀번호 변경 시나리오

Brew로 설치한 MySQL의 루트 계정 'root'@'localhost'는 비밀번호 없이 접속이 가능합니다.

mysql -u root -h localhost

MySQL 클라이언트로 접속한 이후에 다음과 같이 비밀번호가 비어 있는 것을 확인할 수 있습니다.

SELECT user, host, authentication_string FROM mysql.user WHERE user = 'root' AND host = 'localhost';

이후 아래와 같이 hello1234로 비밀번호를 변경할 수 있습니다.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'hello1234';

변경 이후에는 다음과 같이 로그인할때 비밀번호를 명시해줘야 합니다.

mysql -u root -h localhost -p'hello1234'

접속 이후에 이중 비밀번호 기능으로 hi1234를 신규 비밀번호로 사용할 수 있습니다.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'hi1234' RETAIN CURRENT PASSWORD;

SELECT user, host, authentication_string FROM mysql.user WHERE user = 'root' AND host = 'localhost';

변경 이후에는 다음과 같이 신규, 기존 비밀번호를 모두 사용할 수 있습니다.

mysql -u root -h localhost -p'hello1234'
mysql -u root -h localhost -p'hi1234'

[권한] 권한(Privileges)에 대해서

MySQL 5.7 버전에서는 권한을 2종류로 구분합니다.

1. 글로벌 권한 : 데이터베이스, 테이블 이외의 객체에 적용되는 권한

2. 객체 단위 권한 : 데이터베이스, 테이블에 필요한 권한

MySQL 8.0 버전에서는 권한을 2종류로 구분합니다.

1. 정적 권한 : MySQL 소스 코드에 고정적으로 명시되어 있는 권한 (= MySQL 5.7 버전에서의 권한)

2. 동적 권한 : MySQL 서버가 시작되면서 동적으로 지정되는 권한

각 권한과 상세 옵션은 MySQL 8.0 공식 문서에서 확인할 수 있습니다. - Ref [1] 8.2.2 Privileges Provided by MySQL

MySQL 5.7까지는 관리자 권한이 SUPER에 다모여 있었습니다. - Ref [2] 6.2.2 Privileges Provided by MySQL

MySQL 8.0부터는 여전히 SUPER가 있지만, 다양한 동적 권한을 통해서 이를 분산시킬 수 있습니다. - Ref [2] 6.2.2 Privileges Provided by MySQL

권한은 실제 사용을 하면서 축소해나가거나 확장해나가는 것이 일반적입니다.
따라서 이론적으로 권한을 나열하는 것은 큰 의미가 판단하여 넘어갔습니다.

[권한] 역할(Role)에 대하여

MySQL 8.0 버전부터는 여러 권한(Privileges)을 묶어서 권한으로 나눌 수 있습니다.
다음과 같이 role_emp_read와 role_emp_write 역할을 만들었습니다.

CREATE ROLE role_emp_read, role_emp_write;

GRANT SELECT ON employees.* TO role_emp_read;
GRANT INSERT, UPDATE, DELETE ON employees.* TO role_emp_write;

이후 reader, writer 유저를 만들어서 사용할 수 있습니다.
생성 이후에는 권한을 할당할 수 있습니다.

CREATE USER 'reader'@'127.0.0.1' IDENTIFIED BY 'qwert';
CREATE USER 'writer'@'127.0.0.1' IDENTIFIED BY 'qwert';

GRANT role_emp_read TO 'reader'@'127.0.0.1';
GRANT role_emp_read, role_emp_write TO 'writer'@'127.0.0.1';

하지만 권한을 사용하려면, 해당 계정으로 로그인한 이후 권한 설정이 필요합니다.
GRANT로 허용한 role_emp_read를 할당하면 성공하지만,
GRANT로 허용하지 않은 role_emp_writer을 할당하면 실패합니다.

mysql -u reader -h 127.0.0.1 -p'qwert'

SELECT current_role();

SET ROLE 'role_emp_read';   -- 성공
SET ROLE 'role_emp_writer'; -- 실패...

SELECT current_role();

이러한 기능을 사용하면 RBAC 방식으로 계정 및 권한 관리가 가능해지는 장점이 있어 보입니다.