인덱스의 키값을 함수 등으로 변형하여 사용할 경우 인덱스에 저장된 키값과 달라지므로 인덱스 사용이 어려워질 수 있다.

이러한 이유로 인덱스 키값을 변형하는 쿼리문은 지양해야 한다.

[인덱스 키값 변형으로 인덱스를 못 사용하는 쿼리문]

SELECT S.ID, S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND SUBSTRING(S.ID, 1, 3) = 100  # 함수로 인한 인덱스 사용 불가 
   AND LENGTH(S.ID) = 5             # 함수로 인한 인덱스 사용 불가
;

[튜닝 쿼리문]

SELECT S.ID, S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.ID BETWEEN 10001 AND 10099
;

✅ 불필요한 함수 제거

쿼리문에 불필요한 함수가 있는 경우 해당 함수를 제거함으로써 쿼리문의 성능을 높일 수 있다.

테이블 설계 시 NAME과 GENDER 컬럼에 제약 조건으로 NOT NULL이 명시되어 있는 경우 IFNULL() 함수를 사용할 이유가 없어진다.

따라서 테이블의 제약 조건 등의 이유로 함수의 사용이 불필요하다고 판단되면 함수를 제거하는 게 성능상 좋다.

[불필요한 함수가 포함된 쿼리문]

SELECT
    S.ID
    , IFNULL(S.NAME, 'NO_NAME') AS NAME
    , IFNULL(S.GENDER, 'NO_GENDER') AS GENDER
  FROM STUDENT S
 GROUP BY IFNULL(S.GENDER, 'NO_GENDER')
;

[튜닝 후]

SELECT
    S.ID
    , S.NAME
    , S.GENDER
  FROM STUDENT S
 GROUP BY S.GENDER
;

✅ 컬럼 타입을 명확하게 사용하기

MySQL에서 조건문(WHERE)에 컬럼 타입을 틀리게 작성해도 DB엔진에 의해 묵시적으로 데이터 타입 변환이 이루어진다.

하지만, 데이터 타입 변환 과정에서 인덱스가 무시되어 Full Table Scan으로 동작할 가능성이 높다.

따라서 컬럼의 타입과 동일한 타입으로 조건식을 작성해야 한다.

• GENDER 컬럼에서 0은 여자, 1은 남자로 처리한 다 가정
• GENDER 컬럼의 타입은 varcha
  ** [타입 변환이 발생하는 쿼리문]**

  SELECT
   S.ID
   , S.NAME
  FROM STUDENT S
  WHERE 1=1
  AND S.GENDER = 1
  ;

  ** [튜닝 후]**

  SELECT
   S.ID
   , S.NAME
  FROM STUDENT S
  WHERE 1=1
  AND S.GENDER = '1'
  ;

✅ LIKE절 대체하기

LIKE절은 광범위한 데이터 조회를 생각하고 사용해야 한다.

만약 2000년대생의 학생을 조회하기 위해서 조건문에 LIKE '2000%' 처럼 작성하면 와일드 카드('%')에 어떠한 값이라도 들어올 수 있기 때문에 데이터 조회 범위과 증가한다.

아래는 LIKE '2000%'절의 탐색 대상

• 2000-01-01
• 2000-12-31
• 2000-99-99 ....
• 2000adkfygadskfyagsdk

날짜 같은 값의 끝이 결정된 조건문의 경우 LIKE 대신 BETWEEN을 사용하여 범위 조회를 하는 게 이상적이다.

[불필요한 범위 까지 탐색하는 쿼리문]

SELECT
    S.ID
    , S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.BIRTHDAY LIKE '2000%'
;

[튜닝 후]

SELECT
    S.ID
    , S.NAME
  FROM STUDENT S
 WHERE 1=1
   AND S.BIRTHDAY BETWEEN '2000-01-01' AND '2000-12-31'
;

✅ 불필요한 DISTINCT 제거

쿼리문의 중복을 제거하기 위해서는 DISTINCT 키워드를 사용한다.

하지만, 중복이 발생할 수 없는 쿼리문에서 DISTINCT 키워드를 사용하면 불필요한 임시 테이블이 메모리에 생성(Using temporary)된다.

이러한 이유로 쿼리문에 습관적으로 DISTINCT 키워드 사용을 지양해야 한다.

학생 테이블(STUDENT)과 반 테이블(CLASS)을 조인 하여 데이터를 조회할 때 학생 테이블의 PK를 사용했다.

학생 ID(PK)는 유일한 값이고, 특정 반에서도 학생 ID(FK)는 유일한 값이기 때문에 조인한다고 해서 중복이 발생하지 않는다. (여러 반에 동일한 학생 ID를 생성할 수 없다는 가정)

[불필요한 DISTINCT가 포함된 쿼리문]

SELECT DISTINCT
    S.STUDENT_ID
    , S.NAME
  FROM STUDENT S
  JOIN CLASS C
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE 1=1
   AND C.TEACHER_ID = '1001'
;

[튜닝 후]

SELECT
    S.STUDENT_ID
    , S.NAME
  FROM STUDENT S
  JOIN CLASS C
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE 1=1
   AND C.TEACHER_ID = '1001'
;

✅ 인덱스 컬럼 순서 고려하기

인덱스는 데이터를 저장할 때 외쪽 컬럼부터 정렬하여 저장한다.

인덱스 idx_tmp(a,b,c)은 a 컬럼부터 정렬하고 그다음 b, c 순서로 정렬된다.

a->b->c 순서로 정렬

이러한 이유로 인덱스 컬럼의 정렬 순서 및 설계 순서를 고려하여 쿼리문을 작성해야 한다.

[인덱스 설계 순서를 무시한 쿼리문] GROUP BY에서 인덱스 사용을 위해 임시 테이블이 메모리에 생성(Using temporary)하여 데이터 정렬 후 그룹핑을 수행한다.

# INDEX : IDX_STUDENT_NAME_AGE(NAME, AGE)
SELECT
    S.NAME
    , S.AGE
    , COUNT(1) AS COUNT
  FROM STUDENT S
 GROUP BY S.AGE, S.NAME
;

[튜닝 후] GROUP BY에서 인덱스 순서에 맞게 컬럼명을 명시한 경우 별도의 정렬 작업이 필요 없다.

# INDEX : IDX_STUDENT_NAME_AGE(NAME, AGE)
SELECT
    S.NAME
    , S.AGE
    , COUNT(1) AS COUNT
  FROM STUDENT S
 GROUP BY S.NAME, S.AGE
;

✅ 드라이빙 & 드리븐 테이블 확인하기

드라이빙 테이블과 드리븐 테이블은 옵티마이저에 의해 결정된다. 하지만, 옵티마이저의 결정이 꼭 최고의 선택이 아니다.

이러한 이유로 쿼리문이 느리다고 판단되면 드라이빙 테이블과 드리븐 테이블이 정확하게 설정되어 있는지 확인할 필요가 있다.

[잘못된 드라이빙 테이블 선택] 학생 수가 2,000명, 반 수가 36개일 때

드라이빙 테이블이 STUDENT, 드리븐 테이블이 CLASS이면 조건 절에서 4학년인 학생만을 필터링하고 드리븐 테이블과 데이터 비교를 하기 때문에 총 데이터 접근 수는 333 * 36 = 11,988번이다.

하지만 드라이빙 테이블이 CLASS, 드리븐 테이블이 STUDENT이면 드라이빙 테이블을 경량화할 수 있는 조건이 없기 때문에 테이블의 총접근 수는 2000 * 36 = 72,000번이다.

이러한 이유로 드라이빙 테이블은 경량화할 수 있는 테이블이 선택될 수 있도록 힌트를 줘야 한다.

# 튜닝 전 드라이빙 테이블이 CLASS, 드리븐 테이블이 STUDENT 이라 가정
SELECT
    S.STUDENT_ID
    , S.NAME
  FROM STUDENT S    # 학생 수가 2,000명
  JOIN CLASS C      # 반 수가 36개
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE S.GRADE = 4
;

[튜닝 후]

SELECT /*! STRAIGHT_JOIN */
    S.STUDENT_ID
    , S.NAME
  FROM STUDENT S    # 학생 수가 2,000명 -> 333명으로 경량화 (조건문으로 필터링 진행)
  JOIN CLASS C      # 반 수가 36개
    ON S.STUDENT_ID = C.STUDENT_ID
 WHERE S.GRADE = 4
;

⚡️ 불필요한 조인 제거하기

데이터의 불필요한 조인은 데이터의 스캔양만 늘리는 행위다.

특히 조건문 때문에 어쩔 수 없이 조인을 수행한 경우라면, 조인 방식이 아닌 다른 방식으로 쿼리문을 작성할 수 있는지 확인해 보는 게 좋다.

[불필요한 조인이 포함된 쿼리]

# PK : EMP(emp_id)
SELECT COUNT(1) AS cnt
  FROM EMP E
  JOIN DEPT D ON E.emp_id = D.emp_id
 WHERE D.LEVEL = 'A'
;

[튜닝 후]

# 단순 존재 여부만 확인 할 때는 EXISTS을 적극 활용하자!!
SELECT COUNT(1) AS cnt
  FROM EMP E
 WHERE EXISTS (SELECT 1 FROM DEPT WHERE emp_id = E.emp_id AND LEVEL = 'A')
;

테이블 조인(JOIN) 시 가장 먼저 접근되는 테이블을 드라이빙(Driving) 테이블, 이후에 접근하는 테이블을 드리븐(Driven) 테이블이라 부른다.

📌 드라이빙 테이블

드라이빙 테이블은 데이터의 선택 범위를 좁혀 주는 테이블을 의미한다.

즉, 조건절(WHERE) 등을 통해 크기를 최소화할 수 있는 테이블이 우선적으로 드라이빙 테이블로 선택된다.

※ Why 드라이빙 테이블? 드라이빙 테이블이 작을수록 드리븐 테이블에 대한 접근 횟수가 줄어든다.

따라서 조인 성능을 높이기 위해 DB 엔진은 더 경량화된 테이블을 드라이빙 테이블로 우선 선택한다.

📌 드리븐 테이블

드리븐 테이블은 인덱스가 반드시(거의 필수적으로) 필요하다.

드라이빙 테이블의 ROW데이터로 드리븐 테이블을 탐색하기 때문에, 인덱스가 없다면 Full Scan이 발생하여 성능이 급격히 저하된다.

📌 드라이빙 테이블 선택 조건

• 양쪽 테이블 모두 인덱스 존재 -> 더 경량화된 테이블이 드라이빙 테이블로 지정

• 한쪽 테이블만 인덱스 존재 -> 인덱스가 없는 테이블이 드라이빙 테이블로 지정

• 양쪽 모두 인덱스 없음 -> 드라이빙·드리븐 구분 의미가 없으며, Full Scan으로 실행됨

📌 요약

• 조인(JOIN) 시 드라이빙 테이블과 드리븐 테이블로 나뉜다.
• 드라이빙 테이블은 작을수록 성능에 유리하다.
• 드리븐 테이블에는 인덱스가 필수적이다.
• 어떤 테이블이 드라이빙/드리븐으로 선택될지는 옵티마이저의 실행 계획에 따라 결정한다.

✅ 조인 알고리즘

MySQL에서 사용되는 조인(JOIN) 방식은 크게 Nested Loop(NL) 과 Hash 방식이 있다.

※ 참고 조인(JOIN) 쿼리문의 70~80%는 Nested Loop(NL) 방식을 사용한다.

📌 Nested Loop Join

Nested Loop Join은 가장 일반적인 조인(JOIN) 방식이다.

• 드라이빙 테이블의 데이터를 기반으로 드리븐 테이블의 데이터를 찾는 방식을 Nested Loop Join이라 부른다.

Nested Loop Join 과정에서 드리븐 테이블 탐색은 랜덤 I/O 방식으로 이루어지며, 이는 Nested Loop Join의 가장 큰 특징이다.

또한, 드리븐 테이블에 인덱스가 반드시 필요하다. 인덱스가 없다면 Full Scan이 발생하여 성능이 크게 저하된다.

▶︎ Nested Loop Join 성능 향상

Nested Loop Join의 성능을 높이기 위해서는 아래와 같은 검토가 필요하다.

1. 드라이빙 테이블 경량화
2. 드리븐 테이블 인덱스 구성 설계

📌 Hash Join

Hash Join은 스토리지가 아닌 메모리에 해시 테이블을 생성하여 조인(JOIN) 하는 방식이다.

드라이빙 테이블을 기반으로 메모리에 해시 테이블을 생성한다. 이때 조인 키(해시 키)를 기준으로 해시 구조를 만든다.

조인 시 드리븐 테이블의 키값을 해시 키로 만들어 메모리에 있는 해시 테이블과 비교하여 데이터를 반환한다.

※ 참고 해시 조인은 해시 키값을 기반으로 동작하기 때문에 랜덤 I/O가 발생하지 않는다.

✅ 힌트

힌트는 옵티마이저가 실행 계획을 선택할 때 참고 용도로 추가 정보를 주는 기술이다.

※ 참고 DB의 통계 정보에 문제가 있는 경우 옵티마이저는 잘못된 선택을 할 가능성이 있다.

이때 쿼리 작성자가 힌트를 사용하여 옵티마이저에게 보다 효율적인 실행 계획을 만들 수 있도록 유도할 수 있다.

힌트를 줘도 물리적으로 불가능하면 옵티마이저가 무시할 수 있습니다. (예: 조건이 없어 인덱스 탐색이 불가 → 최대 ‘풀 인덱스 스캔’)

📌 STRAIGHT_JOIN

FROM 절에 나열된 테이블 수서로 조인을 유도하는 힌트다.

테이블의 조인 순서를 A -> B 로 유도

SELECT /*! STRAIGHT_JOIN */ 
    A.name
    , A.age
FROM A JOIN B
    ON A.id = B.id
;

📌 USE INDEX

특정 인덱스를 사용하도록 유도하는 힌트다.

테이블 조인 시 PRIMARY 사용 유도

SELECT 
    A.name
    , A.age
FROM A /*! USE INDEX (PRIMARY) */
    JOIN B ON A.id = B.id 
;

📌 FORCE INDEX

특정 인덱스를 사용하도록 강하게 유도하는 힌트다.

테이블 조인 시 PRIMARY 사용 강제 유도

SELECT
    A.name
    , A.age
FROM A
    JOIN B /*! USE INDEX (PRIMARY) */ ON A.id = B.id 
;

📌 IGNORE INDEX

특정 인덱스를 사용 못 하게 유도하는 힌트다.

테이블 조인 시 PRIMARY 사용 금지 유도

SELECT
    A.name
    , A.age
FROM A
    JOIN B /*! IGNORE INDEX (PRIMARY) */ ON A.id = B.id 
;

✅ 실행계획

EXPLAIN 을 사용하여 옵티마이저가 선택한 쿼리 실행계획을 확인할 수 있다.

실행계획은 아래 사진처럼 다양한 항목으로 쿼리문의 실행 방식을 예측할 수 있다.

※ 참고 실행계획은 다양한 항목으로 출력된다. 그 중에서 select_type,type, key, Extra 항목은 쿼리 튜닝에 있어 중요한 지표로 사용된다.

📌 id

id는 최소한의 단위 SELECT문 마다 부여되는 식별자다.

실행 계획에서 id는 같은 값으로 중복되어 보일 수 있는데, 이는 같은 SELECT 블록 안의 테이블이라는 뜻이다.

같은 id별로 출력되는 데이터 중 가장 먼저(위에) 나오는 ROW 데이터가 드라이빙 테이블을 의미하고, 그 다음 나오는 ROW 데이터가 드리븐 테이블을 의미한다.

📌 select_type ⭐️

쿼리문의 SELECT 유형에 따라 다양한 항목으로 분리된다.

• simple : 가장 단순한 SELECT문 쿼리문
• primary : 서브쿼리 또는 UNION 구문이 포함된 전체 쿼리문에서 최초 접근한 테이블
• subquery : 독립적으로 움직이는 하나의 서브쿼리 (다른 테이블 및 쿼리문에 종속X)
• derived : 메모리 또는 디스크에 임시로 생성된 테이블 (서브 쿼리 등)
• union : UNION 또는 UNION ALL이 포함된 구문에서 첫 번째 이후 접근된 테이블 (primary 값 참고)
• union result : UNION이 포함된 구문에서만 보이는 값으로 중복 제거를 위해 메모리나 디스크에 임시로 생성된 테이블 (오직 UNION문 에서만 확인 가능)
• dependent subquery : UNION 또는 UNION ALL이 포함된 구문에서 메인 테이블의 영향을 받는 첫 번째 테이블
• dependent union : UNION 또는 UNION ALL이 포함된 구문에서 메인 테이블의 영향을 받는 첫 번째 이후 테이블
• materialized : 조인 등의 가공 작업을 위해 임시로 만들어진 테이블

📌 table

SELECT문에 사용된 테이블 이름을 의미한다. alias가 있다면 테이블이름 대신 alias이름이 표시된다.

임시 테이블(서브 쿼리 등)을 사용할 경우 테이블 이름, alias 대신 다른 이름으로 표기될 수 있다.

📌 partitions

테이블이 파티션으로 구성되어 있는 경우 출력되는 값이다.

📌 type ⭐️

테이블 데이터를 어떻게 접근할 것인지를 나타내는 항목이다.

아래 항목은 const(가장 빠름) ~ all(가장 느림)으로 분리되어 구분된다.

• const : 단 1건의 ROW 데이터만 접근하는 유형 (PK, 유니크 키 사용이 대표)
• eq_ref : 드리븐 테이블에서 매번 1건의 ROW 데이터만을 조회하는 유형 (드라이빙 테이블의 조인 키가 드리븐 테이블에 유일한 경우)
• ref : ROW 데이터의 접근 범위가 2개 이상인 유형
• range : 연속된 범위 데이터를 접근하게 되는 유형
• index_merge : 특정 테이블에 생성된 2개 이상의 인덱스가 병합되어 동시에 적용되는 유형
• index : 인덱스의 데이터를 Full Scan한 유형
• all : 테이블의 데이터를 Full Scan한 유형

📌 possible_keys

테이블에서 사용할 수 있는 인덱스의 후보군을 확인하는 항목이다.

📌 key ⭐️

인덱스 후보군에서 실제 사용할 인덱스를 알려주는 항목이다.

📌 key_len

사용된 인덱스의 Bytes을 의미한다.

📌 ref

테이블을 접근한 조건을 명시한 항목이다.

📌 rows

접근할 레코드 행의 수(예상 수치)를 의미하는 항목이다.

📌 filtered

MySQL 엔진에서 필터 조건에 의해 최종 반환되는 데이터의 비율(%)을 의미한다.

📌 Extra ⭐️

쿼리문이 어떻게 수행할 것인지에 대한 부가적인 정보를 알려주는 항목이다.

• Distinct : 중복이 제거되어 유일한 값을 찾을 때 출력 정보 (DISTINCT, UNION)
• Using where : where절에 필터 조건을 사용하여 데이터 추출
• Using temporary : 임시 테이블 생성
• Using index : 인덱스만 읽어 쿼리 수행 (커버링 인덱스)
• Using filesort : ORDER BY가 인덱스를 활용 못 하고, 메모리에 올려 별도의 추가 정렬 작업을 수행
• Using index for group-by : 쿼리문에 GROUP BY, DISTINCT 구문이 포함될 때, 정렬된 인덱스를 기준으로 순차적으로 GROUP BY 연산 수행
• Using index for skip scan : 모든 인덱스 데이터를 읽는 게 아닌 필요한 인덱스 값만을 읽어 스캔
• FirstMatch() : 인덱스 스캔 시 첫 번째로 일치하는 레코드만 찾으면 검색 중단

데이터에서 검색 키워드 중심으로 조회하는 방식을 역 인덱스(Inverted Index) 구조라고 부른다.

MySQL의 일반 인덱스는 B-Tree 기반으로, 등가(=) 조건이나 범위(>, <, BETWEEN) 조건에서는 효율적이지만, LIKE '%검색어%'와 같이 문자열의 일부를 검색할 때는 일반 인덱스를 활용하기 어렵다.

하지만 Full-Text Index는 텍스트 데이터를 단어 단위로 분리하여 인덱싱하기 때문에, 키워드 기반의 전문 검색을 빠르게 수행할 수 있다.

이러한 Full-Text Index 방식을 전문 검색(Full-Text Search)이라 한다.

※ 참고 단순 키워드 매칭이나 기본적인 텍스트 검색 수준이라면 MySQL의 Full-Text Search 기능만으로도 충분하다.

하지만 형태소 분석, 문맥 기반 검색, 다양한 조건 조합 등 고급 검색이 필요하다면, [Elasticsearch] (https://www.elastic.co/) 같은 전문 검색 엔진을 별도로 사용하는 것이 바람직하다.

FULLTEXT INDEX

MySQL에서 조건문에 LIKE %검색어% 방식으로 특정 검색어가 포함된 데이터를 조회할 수 있다.

하지만 LIKE %검색어% 조건은 인덱스를 사용할 수 없어서 별도의 FULLTEXT INDEX를 만들어 조회 성능을 높여야 한다.

FULLTEXT INDEX 생성

CREATE FULLTEXT INDEX fidx_posts ON posts(title, content);

전문 검색 (MATCH ~ AGAINST)

전문 검색을 사용하려면 FULLTEXT INDEX를 생성하고, 해당 컬럼을 대상으로 MATCH … AGAINST 구문을 사용해야 한다.

아래 예시는 title, content 컬럼에 "전문 검색"이라는 검색어가 포함된 데이터를 빠르게 조회하는 예시다.

SELECT *
FROM posts
WHERE 1=1
    AND MATCH(title, content) AGAINST('전문 검색' IN NATURAL LANGUAGE MODE)
;

전문 검색은 다양한 모드가 존재한다.

• NATURAL LANGUAGE MODE
  • 가장 기본적인 검색 모드
  • 각 단어의 출현 빈도와 문서 내 중요도(가중치)를 기반으로 결과 조회
• BOOLEAN MODE
  • 불리언 연산자(+, -, *)를 사용하여 검색 가능
• IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
  • 검색 키워드 + 관련 키워드까지 고려하여 검색

INSERT INTO users (name, id, password, address, email) 
VALUES ('kim','kim1234','password','서울시 강남구','kim1234@naver.com');

BULK INSERT

INSERT INTO users (name, id, password, address, email) 
VALUES ('kim','kim1234','password','서울시 강남구','kim1234@naver.com')
       , ('lee','lee999','password','서울시 노원구','lee999@naver.com')
       , ('park','park12','password','서경기도 성남시','park12@naver.com');

동적 쿼리 INSERT

동적 쿼리 INSERT는 클라이언트 레벨에서 사용하는 것을 추천

# 동적 쿼리 준비
PREPARE users_insert FROM
    'INSERT INTO users (name, id, password, address, email) VALUES (?, ?, ?, ?, ?)';

# 변수 지정
SET @name = 'kim';
SET @id = 'kim1234';
SET @password = 'password';
SET @address = '서울시 강남구';
SET @email = 'kim1234@naver.com';

# 동적 쿼리 실행
EXECUTE users_insert USING @name, @id, @password, @address, @email;

# 동적 쿼리 해재
DEALLOCATE PREPARE users_insert;

1만 건 이상의 대용량 데이터의 경우 동적 쿼리 INSERT보다 BULK INSERT가 더 효율적일 수 있다.

INSERT IGNORE

INSERT INTO users (name, id, password, address, email) 
VALUES ('kim','kim1234','password','서울시 강남구','kim1234@naver.com')
       , ('lee','lee999','password','서울시 노원구','lee999@naver.com')
       , ('lee','lee999','password','서울시 도봉구','lee999@naver.com')  #  무시하고 아래 ROW 진행
       , ('park','park12','password','서경기도 성남시','park12@naver.com');

기존 테이블 또는 INSERT 구분에 중복된 데이터가 있으면 에러 반환 없이 해당 중복 데이터 ROW를 무시하고 다음 ROW를 INSERT 한다.

DUPLICATE KEY UPDATE

유니크 키값을 조회/비교(SELECT)한 후 INSERT 또는 UPDATE를 실행한다.

• *DUPLICATE KEY UPDATE는 성능적으로 좋은 방법은 아니다. *INSERT 하기 전 키값을 먼저 조회(SELECT)하기 때문에 부수적인 작업이 추가로 드는 방법이다.

INSERT INTO users (name, id, password, address, email) 
VALUES ('kim','kim1234','password','서울시 강남구','kim1234@naver.com')
ON DUPLICATE KEY UPDATE
    password = VALUES(password)
    , address = VALUES(address)
    , update_dt = CURRENT_TIMESTAMP;
;

REPLACE INTO

INSERT 중 중복 데이터를 발견하면 기존 데이터를 삭제하고 새로운 데이터를 INSERT 하는 방법이다.

이는 물리적 데이터를 삭제하는 방법이기 때문에 매우 위험한 방법이다.

REPLACE INTO users (name, id, password, address, email) 
VALUES ('kim','kim1234','password','서울시 강남구','kim1234@naver.com');

인덱스는 DB에서 대용량의 데이터(수만 건 이상의 데이터)를 빠르게 읽기(SELECT) 위해 사용되는 기술이다.

※ 참고 인덱스를 사용하면 테이블의 Full Scan을 막아 읽기(SELECT) 성능을 높여주지만, 그 외 쓰기(INSERT, UPDATE, DELETE) 성능이 낮아진다.

인덱스 사용 없이 테이블을 Full Scan 하는 행위는 높은 비용을 요구하는 작업이다. 때문에 빈번하게 사용되는 쿼리문에서는 Full Scan이 발생하지 않도록 인덱스를 잘 설계해야 한다.

✅ 인덱스 구조

인덱스는 테이블의 특정 컬럼 데이터를 기반으로 생성되는 별도의 자료 구조다.

인덱스의 가장 큰 특징으로는 인덱스 컬럼 데이터(키값)들은 항상 정렬되어 있다는 점이다.

실제 인덱스는 트리 자료 구조(B-Tree 기반)로 되어 있다.

트리 구조 내부는 최상위에 하나의 root node가 있고, 최하위에는 leaf node가 있으며, 그 사이에는 branch node가 있다.

각 노드는 별도의 주소 값(PK, 참조 값 등)을 가지고 있으며, 마지막 리프 노드는 실제 데이터 파일의 레코드 주소 값(PK, 참조 값 등)을 가지고 있다.

✅ 인덱스 생성/확인/삭제

📌 인덱스 생성

# 인덱스 생성
CREATE INDEX <인덱스명> ON <테이블명>(<컬럼1>, <컬럼2>, ...);

📌 인덱스 확인

# 인덱스 확인
SHOW INDEX FROM <테이블명>;

인덱스 조회 쿼리를 실행하면 아래와 같은 테이블 내 인덱스 정보를 확인할 수 있다.

※ 참고 MySQL에서 PK, FK, UNIQUE으로 지정된 컬럼은 인덱스가 자동 생성된다.

mysql> SHOW INDEX FROM ITEMS;
+-------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          0 | PRIMARY          |            1 | item_id        | A         |          25 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | fk_items_sellers |            1 | seller_id      | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | idx_items_test   |            1 | category       | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | idx_items_test   |            2 | is_active      | A         |           9 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | idx_items_test   |            3 | stock_quantity | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

• Non_unique : 인덱스 중복 허용 여부(0 : 중복 불가, 1 : 중복 가능)
• Key_name : 인덱스 이름
• Seq_in_index : 해당 인덱스의 컬럼 순번
• Column_name : 해당 인덱스의 컬럼
• Collation : 인덱스 정렬 방식(A : 오름차순, D : 내림차순)
• Cardinality : 컬럼의 고유 값 수치 (해당 값이 높을수록 성능에 유리함)
• Index_type : 인덱스 자료 구조(MySQL InnoDB에서는 B-TREE를 기본으로 함)

📌 인덱스 삭제

DROP INDEX <인덱스명> ON <테이블명>;

✅ 인덱스 사용 여부 확인

DB에서 쿼리문의 실행은 옵티마이저에 의해 최적의 실행 계획으로 실행된다. 이는 쿼리문의 인덱스 사용 여부 또한 옵티마이저가 결정한다는 의미로 해석할 수 있다.

특정 쿼리문이 정상적으로 인덱스를 사용하는지 확인하기 위해서는 EXPLAIN 명령어를 사용해야 한다. 해당 명령어를 통해 옵티마이저의 실행 계획(실행 결과 X)을 확인 할 수 있다.

인덱스가 아래와 같은 때 옵티마이저의 실행 계획은 다음과 같다. idx_items_test(category, is_active, stock_quantity)

※ 참고 실행 계획은 예측 정보라는 점을 명심해야 한다. (실제 실행 결과와 상이할 수 있다)

# 인덱스 사용 X (Full Scan)
mysql> EXPLAIN
    -> SELECT * FROM ITEMS WHERE price > 50000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ITEMS | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

# 인덱스 사용 O
mysql> EXPLAIN
    -> SELECT * FROM ITEMS WHERE category = '전자기기';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | ITEMS | NULL       | ref  | idx_items_test | idx_items_test | 402     | const |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

• table : 참조 테이블
• type : 조인/테이블 접근 방식
  • ALL -> Full Scan
  • ref -> = 또는 JOIN에서 인덱스 사용
  • range -> 범위 검색에서 인덱스 사용
• possible_keys : 인덱스 사용 후보
• key : 실행 계획에서 최종적으로 선택된 인덱스
• rows : 옵티마이저가 테이블에서 읽어야 할 예상 레코드 수
• filtered : 조건(WHERE)을 적용했을 때 남을 것으로 예상되는 레코드의 비율 (%)
• Extra : 추가 실행 정보
  • Using where -> 추가 조건(WHERE) 작업 필요
  • Using index -> 커버링 인덱스 사용
  • Using index condition -> 인덱스만으로 조건(WHERE)에 만족하는 데이터를 가져옴
  • Using filesort -> 추가 정렬 작업 필요
    
    >
• ※ 참고* rows 값이 작을 수록, filtered 값이 클 수록 성능상 유리하다.

📌 인덱스 사용 상황

인덱스는 크게 아래 3가지 상황에서 사용된다.

• 동등 비교 (=, IN)

• 범위 비교 (BETWEEN, >, <, LIKE)

  • LIKE 절은 와일드 카드(%)가 검색어 뒤쪽에 위치해야 한다. 이는 인덱스 키값이 정렬되어 저장되기 때문이다.
    와일드 카드(%) 문자가 앞에 있는 경우 정렬된 인덱스 키값과 매칭을 할 수 없어서 Full Scan 방식으로 작업을 처리한다.
    만약 %검색_단어% 처럼 중간에 포함된 검색 단어를 기반으로 조회 성능을 높이고 싶다면, DB에서 제공하는 전문 검색(Full-Text Search) 기능 또는 검색 엔진(Elasticsearch 등)을 별도로 사용해야 한다.

• 정렬 (ORDER BY)

  • DB에서 정렬은 큰 비용이 드는 작업이다.

    이러한 성능 문제는 인덱스를 적절하게 사용하여 정렬 작업(filesort)을 회피할 수 있다.

• ※ 참고 1* 인덱스를 사용하지 않은 쿼리문의 결과는 PK 기준(클러스터링 되어 정렬)으로 결과를 반환 하지만, 인덱스를 사용한 쿼리문의 결과는 정렬된 인덱스 키값을 기준으로 결과를 반환한다.

• ※ 참고 2*

  인덱스를 생성할 때 인덱스 키값을 내림차순으로 생성할 수 있다.

   CREATE INDEX <인덱스명> ON <테이블명>(<컬럼1> DESC);

  사실 오름차순으로 생성된 인덱스는 내림차순 정렬 쿼리를 실행 할 때 MySQL이 이를 역방향으로 자동 스캔해 준다.

  또한, 역방향 스캔은 정방향 스캔 보다 성능이 조금 떨어 지지만 큰 차이가 없다.

  ※ 참고 3 하지만 여러 컬럼에 대해 오름차순과 내림차순이 혼합된 복잡한 쿼리에서 내림차순이 필요한 인덱스 키값을 DESC로 설정하고, 인덱스 정렬 순서에 맞게 인덱스를 생성하면 쿼리의 성능을 극적으로 높일 수 있다.

✅ 옵티마이저와 인덱스

인덱스 사용이 항상 높은 성능을 보이는 건 아니다.

옵티마이저가 쿼리문의 실행 계획을 만들 때 인덱스 사용이 비효율적이라 판단하면, 인덱스 사용을 포기하고 Table Full Scan을 실행 한다.

※ 참고 인덱스 사용 시 많은 랜덤 I/O가 발생하면 옵티마이저는 순차 I/O 방식(Full Scan)으로 데이터를 읽는다.

📌 인덱스 손익분기점

옵티마이저는 인덱스 손익분기점을 기준으로 인덱스 사용 여부를 판단한다.

[손익분기점] 인덱스 사용(인덱스 키값 스캔 + 참조 값 기반으로 데이터 레코드 조회) VS Table Full Scan(테이블의 전체 데이터 레코드 조회)

※ 참고1 일반적으로 테이블 전체 데이터의 20~25% 이상을 조회하는 쿼리문은 Full Scan 방식이 더 효율적일 수 있다.

※ 참고2 여러 인덱스 후보 중 Full Scan이 효율적이라고 판단하면 모든 인덱스 사용을 포기할 수 있다.

※ 참고3 20~25% 기준을 떠나 데이터양이 적으면, 옵티마이저는 Full Scan을 선택할 수 있다.

✅ 커버링 인덱스

쿼리문에서 사용하는 모든 컬럼이 인덱스에 포함되면 이를 커버링 인덱스라 부른다.

일반 인덱스는 참조 값을 기반으로 테이블의 레코드를 조회(랜덤 I/O)한다. 하지만 커버링 인덱스는 인덱스 자료구조 내부에 모든 컬럼 데이터가 존재하기 때문에 참조 값을 기반으로 한 레코드 조회 작업이 필요 없다.

이처럼 테이블 레코드를 조회하는 작업을 생략할 수 있기 때문에 극한으로 효율을 높일 수 있다.

※ 참고 커버링 인덱스는 압도적인 읽기 성능을 자랑하지만, 그만큼 많은 양의 컬럼을 인덱스로 지정해야 하므로 데이터 저장 공간을 기준으로 생각하면 좋은 방법은 아니다.

또한 많은 컬럼을 인덱스로 설정하기 때문에 그만큼 쓰기 성능이 많이 떨어진다.

✅ 복합 인덱스(다중 컬럼 인덱스)

두 개 이상의 컬럼을 조합하여 만든 인덱스를 복합 인덱스라 부른다. 복합 인덱스에서 가장 중요한 부분은 컬럼의 순서다.

📌 인덱스 컬럼 순서 ⭐️

인덱스 생성 시 컬럼의 순서에 따라 성능이 천차만별로 나뉜다. 때문에 복합 인덱스의 컬럼 순서는 정말 중요하다.

아래와 같은 인덱스가 있다고 가정하면 a -> b -> c 순서로 인덱스 키 값을 정렬한다.

즉, 가장 첫 번째 왼쪽 컬럼에 의해 인덱스 키 값의 정렬 구조가 결정된다.

idx_tmp(a, b, c)

📌 인덱스 왼쪽 접두어 규칙 ⭐️⭐️

복합 인덱스의 자료구조는 가장 왼쪽 컬럼을 기준으로 정렬된다. 이러한 이유로 첫 번째 인덱스 키값이 아닌 두 번째부터의 인덱스 키값만으론 인덱스를 사용할 수 없다.

※ 참고 첫 번째 인덱스 키값을 제외한 두 번째 부터의 인덱스 키값은 정렬된 상태라고 볼 수 없어서 인덱스로 사용할 수 없다. (Full Scan 발생)

즉, 첫 번째 인덱스 키값을 무시하면 인덱스 정렬 구조가 깨진다.

하지만 커버링 인덱스의 경우 첫 번째 인덱스 키값이 없어도 부분적으로 인덱스를 사용할 수 있다.

📌 인덱스 설계 대원칙 ⭐️⭐️⭐️

아래 순서를 기반으로 복합인덱스를 사용하면 높은 성능으로 인덱스를 사용할 수 있다.

1. 인덱스 컬럼은 순서대로 사용

첫 번째 인덱스 컬럼을 무시하고 두 번째 인덱스 컬럼부터 사용하면 Full Scan이 발생한다.

모든 인덱스는 첫 번째 컬럼을 기준으로 정렬하기 때문이다.(단, 커버링 인덱스에서는 부분적으로 인덱스를 사용)

2. 동등(=, IN) 조건은 앞으로, 범위(<,>, BETWEEN) 조건은 뒤로

첫 번째 인덱스 컬럼의 범위 조건은 인덱스를 사용하여 데이터를 스캔할 수 있다.

이후 두 번째 컬럼의 동등 비교로 데이터를 스캔할 때는 인덱스 키값이 정렬되어 있지 않기 때문에 두 번째 조건의 인덱스 값을 Full Scan 한다.

※ 참고 범위 조건이 앞에 있다고 해서 인덱스를 못 사용하는 것은 아니다. 하지만 비효율적일 뿐이다.

범위 조건 이후부터는 인덱스 키값의 정렬 구조가 깨지기 때문에 인덱스를 사용할 수 없다.

3. 정렬(ORDER BY) 순서도 인덱스 컬럼 순서대로 사용

복합 인덱스 컬럼은 각 컬럼의 순서대로 정렬되어 있기 때문에 쿼리문의 정렬을 인덱스 순서와 맞게 작성하면 정렬 작업(filesort)을 회피할 수 있다.

✅ 인덱스 컬럼 후보

• 카디널리티가 높은 컬럼

• 조건(WHERE)에 자주 사용되는 컬럼

• JOIN 조건(ON)으로 사용되는 컬럼

※ 참고 MySQL에서 PK, FK, UNIQUE으로 지정된 컬럼은 인덱스가 자동 생성된다.

• 정렬(ORDER BY)에 자주 사용되는 컬럼

✅ 인덱스 단점

인덱스의 단점으로는 아래와 같다.

1. 저장 공간 인덱스도 데이터이기 때문에 각 테이블의 10~20%의 추가 공간이 발생한다.

2. 쓰기(INSERT, UPDATE, DELETE) 성능이 하락 인덱스가 있는 테이블은 정렬된 인덱스 구조를 유지하기 위해 쓰기 작업 시 속도가 떨어진다.

※ 참고 인덱스가 있는 테이블에서 UPDATE는 내부적으로 DELETE -> INSERT 과정으로 진행 되기 때문에 가장 비효율적인 작업으로 전락된다.

✅ MySQL 로그 파일

MySQL 서버의 상태를 진단하는 도구는 정말 많다.

이러한 도구는 높은 지식을 요구하는 경우가 많은데, 로그 파일을 이용한 방법은 높은 지식 없이도 MySQL의 상태나 부하의 원인을 쉽게 찾을 수 있다.

‼️ MySQL 서버에 문제가 발생하면 아래 로그 파일을 우선 확인하는 습관을 키우는 게 좋다.

📌 에러 로그 파일 (Error log)

에러 로그 파일은 MySQL 서버 실행 중 발생하는 에러나 경고 메시지를 기록하는 로그다.

▶︎ 에러 로그 파일 위치

에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log-error라는 이름의 파라미터로 정의된 경로에 생성된다.

linux> cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
>
# 에러 로그 저장 경로
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

log-error가 지정되지 않으면 datadir 경로에 .err 확장자의 파일로 생성된다

설정 파일을 확인한 결과 /var/log/mysqld.log에 에러가 기록되는 것을 확인할 수 있다.

에러 로그는 파일을 직접 확인하는 방법과 쿼리문을 사용하여 확인하는 방법이 있다.

▶︎ 에러 파일로 확인

linux> cat /var/log/mysqld.log
...
2025-08-02T08:07:00.628029Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: aad3bceb-6f77-11f0-819a-0800276f12df.
mysqld: File '/var/lib/mysql/auto.cnf' not found (OS errno 13 - Permission denied)
2025-08-02T08:07:00.628085Z 0 [ERROR] [MY-010183] [Server] Failed to create file(file: '/var/lib/mysql/auto.cnf', errno 13)
2025-08-02T08:07:00.628089Z 0 [ERROR] [MY-010076] [Server] Initialization of the server's UUID failed because it could not be read from the auto.cnf file. If this is a new server, the initialization failed because it was not possible to generate a new UUID.
2025-08-02T08:07:00.628138Z 0 [ERROR] [MY-010119] [Server] Aborting
2025-08-02T08:07:00.628884Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.6)  MySQL Community Server - GPL.
...

에러 로그 파일의 메시지 형식은 아래와 같다. [날짜/시간] [스레드ID] [에러 심각도] [에러 코드] [서브시스템] 메시지

▶︎ 쿼리문으로 확인

performance_schema.error_log 테이블을 조회해 에러 로그를 확인할 수 있다.

mysql> SELECT * FROM performance_schema.error_log\G
...
*************************** 9. row ***************************
    LOGGED: 2025-08-02 17:07:00.628029
 THREAD_ID: 0
      PRIO: Warning
ERROR_CODE: MY-010075
 SUBSYSTEM: Server
      DATA: No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: aad3bceb-6f77-11f0-819a-0800276f12df.
*************************** 10. row ***************************
    LOGGED: 2025-08-02 17:07:00.628085
 THREAD_ID: 0
      PRIO: Error
ERROR_CODE: MY-010183
 SUBSYSTEM: Server
      DATA: Failed to create file(file: '/var/lib/mysql/auto.cnf', errno 13)
*************************** 11. row ***************************
    LOGGED: 2025-08-02 17:07:00.628089
 THREAD_ID: 0
      PRIO: Error
ERROR_CODE: MY-010076
 SUBSYSTEM: Server
      DATA: Initialization of the server's UUID failed because it could not be read from the auto.cnf file. If this is a new server, the initialization failed because it was not possible to generate a new UUID.
*************************** 12. row ***************************
    LOGGED: 2025-08-02 17:07:00.628138
 THREAD_ID: 0
      PRIO: Error
ERROR_CODE: MY-010119
 SUBSYSTEM: Server
      DATA: Aborting
*************************** 13. row ***************************
    LOGGED: 2025-08-02 17:07:00.628884
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-010910
 SUBSYSTEM: Server
      DATA: /usr/sbin/mysqld: Shutdown complete (mysqld 8.4.6)  MySQL Community Server - GPL.
...

📌 제너럴 쿼리 로그 파일 (General log)

제너럴 쿼리 로그는 MySQL 서버에서 실행된 모든 SQL 쿼리를 기록하는 로그다.

주로 디버깅이나 쿼리 실행 추적에 활용되며, 모든 쿼리를 기록하기 때문에 스토리지 사용량이 빠르게 증가할 수 있다.

※ 장기간 활성화는 권장되지 않으며, 필요한 경우 일정 시간만 켜고 로그를 백업하는 방식이 좋다.

▶︎ 제너럴 쿼리 로그 활성화

제너럴 쿼리 로그 기록은 기본적으로 OFF로 되어 있다. 만약 제너럴 쿼리 로그 기록을 희망한다면 이를 ON으로 설정해야 한다.

제너럴 쿼리 로그는 기본값이 OFF다. 기록을 활성화하려면 ON으로 설정해야 한다.

[제너럴 쿼리 로그 활성화 확인]

mysql> show global variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

[제너럴 쿼리 로그 활성화]

mysql> SET GLOBAL general_log = 'ON';

▶︎ 제너럴 로그 파일 위치

제너럴 로그 파일의 위치 정보는 general_log_file 시스템 변수에 설정되어 있다.

mysql> show global variables like 'general_log_file';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log_file | /var/lib/mysql/localhost.log |
+------------------+------------------------------+
1 row in set (0.00 sec)

제너럴 로그 파일 위치 : /var/lib/mysql/localhost.log

▶︎ 제너럴 로그 파일 확인

linux> cat localhost.log 
/usr/libexec/mysqld, Version: 8.0.41 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2025-07-19T13:22:18.076585Z       31 Connect    admin@localhost on  using Socket
2025-07-19T13:22:18.077079Z       31 Query    select @@version_comment limit 1
2025-07-19T13:23:22.111796Z       31 Query    set default role NONE TO 'dev1_user009'
2025-07-19T13:23:33.647180Z       31 Query    set default role ALL TO 'dev2_user009'

📌 슬로우 쿼리 로그 파일 (Slow log) ⭐️

슬로우 쿼리 로그는 설정된 기준 시간보다 오래 걸린 SQL 쿼리를 기록하는 로그다.

주로 성능 튜닝 대상 쿼리를 식별하는 데 활용된다.

▶︎ 슬로우 쿼리 기준 설정

슬로우 쿼리 기준은 long_query_time 시스템 변수로 설정한다.

쿼리문이 5초 이상을 넘어가면 슬로우 쿼리로 지정

mysql> set global long_query_time = 5;

▶︎ 슬로우 쿼리 로그 활성화

슬로우 쿼리 로그는 기본값이 OFF다. 기록을 활성화하려면 ON으로 설정해야 한다.

[슬로우 쿼리 로그 활성화 확인]

mysql> show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

[슬로우 쿼리 로그 활성화]

mysql> SET GLOBAL slow_query_log = 'ON';

▶︎ 슬로우 쿼리 로그 파일 위치

슬로우 쿼리 로그 파일의 위치 정보는 slow_query_log_file 시스템 변수에 설정되어 있다.

mysql> show global variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.00 sec)

슬로우 로그 파일 위치 : /var/lib/mysql/localhost-slow.log

▶︎ 슬로우 쿼리 로그 파일 확인

# Time: 2025-08-12T00:09:04.753866+09:00
# User@Host: root[root] @ localhost []  Id:    13
# Query_time: 8.964597  Lock_time: 0.000011 Rows_sent: 300024  Rows_examined: 36478428
SET timestamp=1754924935;
select A.emp_no, max(A.salary) from salaries A left join salaries B on A.emp_no = B.emp_no group by emp_no;

• Time : 쿼리 종료 시점
• User@Host : 쿼리 실행 계정
• Query_time : 쿼리 실행 시간
• Lock_time : 테이블 잠금 대기(잠금 체크 포함) 시간 (MySQL 엔진 레벨)
• Rows_sent : 출력 행(row) 수
• Rows_examined : 쿼리문 처리를 위해 접근된 레코드 수

📌 쿼리 로그 통계(Percona Toolkit) ⭐️

슬로우 쿼리 로그나 제너럴 로그는 쿼리 실행 내역이 방대해 하나씩 검토하기 어렵다.

이때 Percona에서 개발한 Percona Toolkit의 pt-query-digest를 활용하면 쿼리 빈도, 실행 시간, 순위 등을 한눈에 확인할 수 있다.

▶︎ Percona Toolkit 설치

※ Apple Silicon 기반의 macOS에서 VirtualBox로 구동되는 CentOS 9 VM이며, CPU 아키텍처는 x86_64가 아닌 ARM64(aarch64)를 사용하고 있습니다.

이러한 이유로 x86_64 기반이 아닌 ARM64(aarch64)로 진행되었습니다.

[Toolkit 패키지 다운로드]

linux> wget https://downloads.percona.com/downloads/percona-toolkit/3.7.0-2/binary/redhat/9/aarch64/percona-toolkit-3.7.0-2.el9.aarch64.rpm

[Toolkit 패키지 설치]

linux> dnf install percona-toolkit-3.7.0-2.el9.aarch64.rpm

▶︎ Log 통계 파일 생성(With Toolkit)

[Genera Log 파일 분석]

linux> pt-query-digest --type='genlog' /var/lib/mysql/localhost.log > parsed_general.log

[Slow Log 파일 분석]

linux> pt-query-digest --type='slowlog' /var/lib/mysql/localhost-slow.log > parsed_slow.log

▶︎ Slow Log 파일 분석 확인

Slow Log 파일 기준으로 살펴보면 아래와 같은 통계자료를 확인할 수 있다.(Genera Log 통계 자료도 Slow Log 통계와 비슷함)

linux> cat parsed_slow.log

# 60ms user time, 20ms system time, 36.60M rss, 256.07M vsz
# Current date: Tue Aug 12 02:24:22 2025
# Hostname: localhost.localdomain
# Files: /var/lib/mysql/localhost-slow.log
# Overall: 7 total, 2 unique, 0.00 QPS, 0.00x concurrency ________________
# Time range: 2025-08-11T14:54:42 to 2025-08-12T02:22:36
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            66s      8s     12s      9s     11s      1s      9s
# Lock time          148us     3us    63us    21us    60us    22us    10us
# Rows sent          1.72M 146.48k 292.99k 251.13k 283.86k  63.47k 283.86k
# Rows examine     180.57M   3.31M  34.79M  25.80M  34.72M  14.25M  34.72M
# Query size         2.14k     106     832  313.43  793.42  312.25  102.22

# Profile
# Rank Query ID                            Response time Calls R/Call  V/M
# ==== =================================== ============= ===== ======= ===
#    1 0x572D37876B7D41AC706E4682C09D17E3  43.0714 65.1%     5  8.6143  0.02 SELECT salaries
#    2 0x190AB0B13E319A2297BAAD68D06FAD5A  23.0921 34.9%     2 11.5460  0.00 SELECT salaries titles dept_emp salaries employees d t

# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x572D37876B7D41AC706E4682C09D17E3 at byte 1115
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2025-08-11T14:54:42 to 2025-08-12T00:09:04
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         71       5
# Exec time     65     43s      8s      9s      9s      9s   406ms      8s
# Lock time     89   132us     3us    63us    26us    60us    24us    10us
# Rows sent     83   1.43M 292.99k 292.99k 292.99k 292.99k       0 292.99k
# Rows examine  96 173.94M  34.79M  34.79M  34.79M  34.79M       0  34.79M
# Query size    24     530     106     106     106     106       0     106
# String:
# Databases    employees
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `employees` LIKE 'salaries'\G
#    SHOW CREATE TABLE `employees`.`salaries`\G
# EXPLAIN /*!50100 PARTITIONS*/
select A.emp_no, max(A.salary) from salaries A left join salaries B on A.emp_no = B.emp_no group by emp_no\G

# Query 2: 0.07 QPS, 0.86x concurrency, ID 0x190AB0B13E319A2297BAAD68D06FAD5A at byte 2970
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2025-08-12T02:22:09 to 2025-08-12T02:22:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         28       2
# Exec time     34     23s     11s     12s     12s     12s    99ms     12s
# Lock time     10    16us     5us    11us     8us    11us     4us     8us
# Rows sent     16 292.97k 146.48k 146.48k 146.48k 146.48k       0 146.48k
# Rows examine   3   6.63M   3.31M   3.31M   3.31M   3.31M       0   3.31M
# Query size    75   1.62k     832     832     832     832       0     832
...

[1. 쿼리 통계 요약] 전체 쿼리 수, 실행 시간 평균·최대·최소, 잠금 대기 시간 등.

# 60ms user time, 20ms system time, 36.60M rss, 256.07M vsz
# Current date: Tue Aug 12 02:24:22 2025
# Hostname: localhost.localdomain
# Files: /var/lib/mysql/localhost-slow.log
# Overall: 7 total, 2 unique, 0.00 QPS, 0.00x concurrency ________________
# Time range: 2025-08-11T14:54:42 to 2025-08-12T02:22:36
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            66s      8s     12s      9s     11s      1s      9s
# Lock time          148us     3us    63us    21us    60us    22us    10us
# Rows sent          1.72M 146.48k 292.99k 251.13k 283.86k  63.47k 283.86k
# Rows examine     180.57M   3.31M  34.79M  25.80M  34.72M  14.25M  34.72M
# Query size         2.14k     106     832  313.43  793.42  312.25  102.22

MySQL에서는 기본적으로 InnoDB 스토리지 엔진을 사용한다.

MySQL의 다양한 스토리지 엔진 중 거의 유일하게 InnoDB 스토리지 엔진만 레코드 기반의 잠금 기능을 제공한다. 이러한 이유로 높은 동시성 처리가 가능하며, 안정적인 성능을 자랑한다.

📌 프라이머리 키(PK)에 의한 클러스터링

InnoDB는 기본적으로 PK를 기준으로 클러스터링 되어 저장된다. 즉, PK 값의 순서대로 디스크에 저장된다.

이러한 이유로 PK 기반 조회 속도가 매우 빠르다. (PK는 클러스터링 인덱스이기 때문)

📌 MVCC(Multi Version Concurrency Control)

MVCC는 잠금을 사용하지 않는 일관된 읽기(SELECT) 기능을 제공하는 데 주목적을 둔 기능이다.

InnoDB에서는 위 기능을 언두 로그(Undo log)를 이용해 구현한다.

초기 신짱구의 나이는 5살로 저장되어 있었지만, UPDATE을 통해 10살로 변경했고 아직 COMMIT, ROLLBACK을 하지 않은 상태다.

이때 다른 사용자가 신짱구를 조회하면(COMMIT, ROLLBACK 이전) 짱구의 나이는 몇살로 나올까? 5살? 10살?

이 결과는 시스템 변수 transaction_isolation의 격리 수준 설정에 따라 다르다.

transaction_isolation은 아래처럼 격리 수준을 설정할 수 있다.

• REPEATABLE-READ(기본 값) : 변경되기 이전의 내용을 보관하는 언두 로그 영역의 데이터를 반환
  • 5살 반환
• READ-UNCOMMITTED : 커밋 여부를 떠나 버퍼 풀에 있는 데이터를 반환
  • 10살 반환
• READ-COMMITTED : 항상 최근 커밋된 데이터만을 조회한다. 즉, 디스크에 있는 데이터를 반환
  • 5살 반환
• SERIALIZABLE : 매우 엄격한 격리 수준으로 여러 트랜잭션이 동일한 레코드에 동시 접근할 수 없다.
  • 5살 반환

📌 자동 데드락 감지

InnoDB는 자동 데드락 감지 스레드가 있으며, 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.

감지 스레드가 대기 목록 그래프를 검사하고, 교착 상태에 빠진 트랜잭션을 발견하면 트랜잭션 하나를 강제 종료한다. (트랜잭션 종료의 기준은 언두 로그이며, 작은 양의 언두 로그 트랜잭션을 종료/롤백한다)

※ InnoDB는 MySQL엔진에서 관리하는 테이블 장금을 확인할 수 없어 데드락 감지가 불확실하다.

이러한 이유로 innodb_table_locks 시스템 변수를 ON으로 설정하여 테이블 레벨의 잠금까지 감지할 수 있게 하는 것을 권장한다.

mysql> innodb_table_locks=ON;

동시 처리 스레드가 많아지거나, 트랜잭션이 가진 잠금의 개수가 많아지면 감지 스레드의 속도가 느려진다. 이렇게 되면 서비스 속도에도 악영향을 미치게 되고, 감지 스레드는 더 많은 CPU 자원을 소모한다.

이럴 때는 아예 감지 스레드의 사용을 비활성화하는 방법이 있다.

mysql> innodb_deadlock_detect=OFF;

하지만 감지 스레드를 비활성화 하면 교착 트랜젝션이 발생할 때 해당 트랜젝션이 무한정 대기 상태로 접어들게 된다.

이러한 문제를 방지하기 위해 데드락 상태에서 일정 시간이 지나면 자동으로 요청 실패 되도록 innodb_lock_wait_timeout 시스템 변수를 설정한다.

innodb_lock_wait_timeout 시스템 변수의 기본값은 50s이고 일반적으로 50초 보다 낮게 설정하여 사용한다.

mysql> innodb_deadlock_detect=OFF;
mysql> innodb_lock_wait_timeout=50;

📌 자동화된 장애 복구

InnoDB는 매우 견고하게 만들어져서 데이터 파일 손상, MySQL 서버가 시작되지 못하는 경우가 거의 없다. 하지만, 하드웨어 이슈로 InnoDB가 자동으로 복구하지 못하는 상황이 발생할 수 있다.

InnoDB 데이터 파일은 MySQL 서버가 시작할 때 항상 자동 복구를 수행한다. 하지만, 자동 복구할 수 없는 경우 자동 복구를 중지하고 MySQL 서버를 종료한다. 이때는 innodb_force_recovery 시스템 변수를 설정하여 MySQL 서버를 강제 기동한다.

⚠️ innodb_force_recovery 시스템 변수는 비상 상황에서만 사용해야 한다!

innodb_force_recovery 시스템 변수값은 06 이 있으며, 기본값은 0(정상 실행), 강제 복구 실행은 16의 값을 사용한다.

어떤 부분에서 발생한 문제인지 모를 때는 1~6까지 시스템 변수값을 변경하면서 시도한다.

※ 강제 복구 실행 시에는 SELECT만 사용할 수 있다.

※ 변수값은 innodb_force_recovery 관련 공식 문서를 참고

※ innodb_force_recovery값이 커질수록 심각한 상황을 의미하며, 복구 가능성이 적어진다.

위 방법을 통해 MySQL 서버 강제 실행에 성공하면 가장 먼저 mysqldump를 사용하여 가능한 만큼의 데이터를 백업한다. 이후 MySQL 서버의 DB와 테이블 등을 다시 생성하는 게 좋다.

만약 MySQL 서버 강제 실행이 실패하면, 지금까지의 백업 파일 및 바이너리 로그를 사용하여 최대한 DB를 복구하는 방법밖에 없다...

📌 버퍼 풀(Buffer Pool) ⭐️

InnoDB 버퍼 풀(Buffer Pool)은 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.

또한, 쓰기 작업을 임의 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.

▶︎ 버퍼 풀 크기 설정 ⭐️

버퍼 풀(Buffer Pool)의 사이즈를 결정은 운영체제, 클라이언트 스레드가 사용할 메모리 등 다양한 변수에 대해 고려해야 한다.

가장 이상적인 방법은 "InnoDB 버퍼 풀의 크기를 적절히 작은 값으로 설정하여 점차적으로 늘려가능 방법"이 가장 이상적인 방법이다.

이미 MySQL 서버가 세팅되어 있는 회사라면, 현재 설정된 메모리 크기를 기준으로 버버 풀 크기를 조정하면된다.

하지만, MySQL 서버를 처음 세팅하는 환경이라면 전체 메모리 크기의 50%를 버퍼 풀로 설정하여 조금씩 버퍼 풀의 크기를 늘려나가는게 좋다.

버퍼 풀의 크기는 innodb_buffer_pool_size 시스템 변수를 사용하여 조절한다.

⚠️ innodb_buffer_pool_size 시스템 변수의 크기 조절은 크리티컬한 작업임으로 한가한 시간(새벽)에 조절하는것을 권장한다.

버퍼 풀의 크기를 늘리는 작업은 서비스 운영에 큰 영향이 없지만, 크기를 줄이는 작업은 서비스 운영에 큰 영향이 발생할 수 있다.

마지막으로 버퍼 풀의 크기 조절은 128MB 단위로만 처리된다. 이러한 이유로 버퍼 풀의 크기를 조절할 때는 공식문서를 참고하여 수정을 권장한다.

▶︎ 버퍼 풀 인스턴스 설정 ⭐️

버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합이 자주 발생했다. 이러한 문제를 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선되었다.

innodb_buffer_pool_instances 시스템 변수를 사용하여 버퍼풀을 여러 개로 분리하여 관리 할 수 있다.

이렇게 분리된 버퍼 풀을 "버퍼 풀 인스턴스"라 부른다.

기본적으로 버퍼 풀 인스턴스는 자동 산정 되어 설정된다. (MySQL 8.4 이후)

하지만, 메모리의 크기가 1GB 미만인 경우 버퍼 풀 인스턴스는 1개만 생성된다.

MySQL 서버의 아키텍처는 크게 MySQL 엔진과 MySQL 스토리지 엔진으로 구분된다.

각 엔진에는 다양한 주제가 존재하지만, 그중 제일 중요한 주제는 아래와 같다고 생각한다.

• MySQL 엔진의 "SQL 옵티마이저"
• MySQL 스토리지 엔진의 "InnoDB"

📌 MySQL 엔진

▶︎ MySQL 엔진

클라이언트로부터 접속 및 쿼리 요청을 처리한다. SQL 파싱, 최적화, 캐싱 등의 기능을 담당한다.

▶︎ MySQL 스토리지 엔진

MySQL 엔진으로부터 요청 받아 물리적 데이터의 읽기/쓰기를 담당한다.

▶︎ 핸들러 API

쿼리를 실행 하기 위해 MySQL 엔진은 스토리지 엔진에 읽기/쓰기를 요청한다. 이때 요청을 "핸들러(Handler) 요청"이라 부르며, 여기서 사용되는 API를 "핸들러 API"라 부른다.

이러한 핸들러 API를 통해 얼마나 많은 데이터(레코드) 작업이 있었는지 아래 명령문을 통해 확인할 수 있다.

mysql> SHOW GLOBAL STATUS LIKE 'Handler%';

📌 MySQL 스레딩 구조

MySQL은 프로세스 기반이 아닌 스레드 기반으로 작동되며, 이러한 스레드는 크게 포그라운드(클라이언트) 스레드와 백그라운드 스레드로 구분된다.

※ PostgreSQL은 프로세스 기반으로 동작.

MySQL에서 실행 중인 스레드는 performance_schema DB의 threads 테이블에서 확인할 수 있다.

mysql> select * from performance_schema.threads;

▶︎ 포그라운드(클라이언트) 스레드

포그라운드 스레드는 MySQL에 접속한 클라이언트 사용자가 요청하는 쿼리문을 처리한다.

클라이언트의 작업을 마치고 종료될 때 해당 작업을 담당한 스레드는 스레드 캐시로 반환(되돌아)된다.

이러한 스레드 캐시의 최대 개수를 설정하는 시스템 변수가 thread_cache_size이다.

포그라운드 스레드는 데이터를 버퍼나 캐시로부터 가져온다. 데이터가 없는 경우 직접 디스크나 인덱스 파일에서 읽어온다.

▶︎ 백그라운드 스레드

InnoDB 기준 다양한 작업을 백그라운드 스레드가 처리된다. 그중 중요한 백그라운드 스레드 작업은 "로그 스레드(Log thread)"와 "쓰기 스레드(Write thread)"다.

InnoDB에서 읽기(read) 작업은 주로 클라이언트 스레드에서 처리하지만, 쓰기 작업의 경우 백그라운드 스레드에서 처리하기 때문에 적절한 설정과 확인이 필요하다.

쓰기 스레드의 시스템 변수는 innodb_write_io_threads이다.

📌 쿼리 실행 구조

클라이언트(사용자)가 MySQL 서버에 쿼리문을 전송하면 위 그림처럼 동작한다.

▶︎ 쿼리 파서

쿼리 문장을 토큰 단위로 분리하여 트리 형태로 만든다. 이때 쿼리문의 기본 문법 오류를 검출하고 클라이언트에게 오류 메시지를 전달한다.

토큰 단위 : MySQL이 인식할 수 있는 가장 작은 단위

▶︎ 전처리기

파서 트리에 구조적 문제가 있는지 확인한다. 이때 테이블, 컬럼, 함수 등 각 개체의 이름과 접근 권한, 실제 존재 여부 등을 확인한다.

▶︎ 옵티마이저

요청받은 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할 수 있게 실행 계획을 만든다.

옵티마이저는 일반적으로 적은 비용으로 최고의 성능(속도)이 나오게 실행 계획을 만든다.

하지만, 비용보다 속도를 최우선으로 설정할 수도 있다.

▶︎ 실행 엔진

옵티마이저가 만든 실행 계획에 따라 실행 엔진은 핸들러 API로 스토리지 엔진(InnoDB 등)에 행 단위 읽기/쓰기를 요청하고, 결과 집합을 클라이언트로 반환한다.

📌 스레드 풀(Thread Pool)

MySQL은 기본적으로 사용자 연결(요청 작업)과 스레드가 1대1로 이루어져 있다.

이러한 이유로 동시 연결(요청 작업)이 발생하면 그만큼 스레드도 늘어나게 된다. 즉, 동시에 실행되는 작업이 많을 경우 CPU 사용 또한 늘어난 스레드만큼 사용되게 된다.

단순 연결(요청 작업 X)인 경우는 대기 중인 상태이기 때문에 별도의 CPU 자원을 사용하지 않는다.

스레드 풀(Thread Pool)은 동시에 처리되는 요청 작업이 많아도 처리하는 스레드 개수를 제한하여 CPU가 제한된 개수의 스레드 처리에 집중할 수 있게 한다. 즉, IT Infra 자원 소모를 줄이는 목적으로 사용한다.

스레드 풀(Thread Pool)은 MySQL Enterprise Edition에서 제공되는 기능이지만 Percona Server에서 제공하는 오픈소스 스레드 풀 플러그인 라이브러리를 설치/사용할 수 있다.

※ Percona Server에서 제공하는 스레드 풀은 기본적으로 CPU 코어의 개수만큼 스레드 그룹을 생성한다.

이러한 스레드 풀은 스레드 그룹으로 이루어져 있으며, 스레드 그룹의 개수는 thread_pool_size 시스템 변수로 조절할 수 있다.

스레드 그룹의 개수는 일반적으로 CPU 코어 개수와 맞춰 설정한다.

▶︎ 스레드 풀 동작

MySQL 서버에서 사용자 연결(요청)이 생기면 스레드 풀로 처리를 이관한다.

이때 스레드 풀 내 그룹 스레드의 모든 스레드(워커 스레드)가 작업 중이라면 스레드 풀은 스레드 그룹에 새로운 스레드를 추가할지 또는 기존 스레드 작업의 완료를 기다릴지 판단한다.

이러한 판단은 thread_pool_stall_limit 시스템 변수에 의해 결정된다. 해당 변수에 설정된 시간을 넘기게 되면 그룹 스레드에 새로운 스레드를 추가한다.

• 그룹 스레드는 시스템 변수에 설정된 스레드 수를 넘길 수 없다.
  그룹 스레드의 스레드 수를 경정하는 시스템 변수는 배포판, 플러그인에따라 thread_pool_max_threads 또는 thread_pool_max_active_query_threads를 사용한다.

• 만약 응답 시간에 민감한 서비스라면 thread_pool_stall_limit 시스템 변수값을 낮춰 사용하는 게 좋다.
  하지만, 0에 가까운 값으로 설정하는 것은 권장하지 않는다. (0에 가까운 값을 설정하면 스레드 풀을 사용하는 이유가 없음)

MySQL에서 권한은 정적 권한과 동적 권한으로 나뉘며, 이 중 정적 권한은 글로벌 권한과 객체 단위 권한으로 나뉜다.

• 정적 권한 : MySQL 서버 어딘가에 고정적으로 명시된 권한
  • 글로벌 권한 : 파일, 서버 단위 권한 설정
  • 객체 권한 : 데이터베이스, 테이블, 인덱스 등 객체 단위 권한 설정
• 동적 권한 : MySQL 서버가 실행되면서 동적으로 실행되는 권한(컴포넌트, 플러그인 등으로 등록되는 권한)

권한 종류는 MySQL 공식 사이트 "MySQL 8.4 권한 리스트"참고

권한 부여

[글로벌 권한 부여] 글로벌 권한을 부여할 때는 ON 절에 항상 *.*을 사용해야 한다. *.*은 모든 오브젝트를 의미하며, MySQL 서버 전체를 의미한다.

mysql> GRANT RELOAD ON *.* TO 'user'@'%';

구분자(',')을 사용하여 여러 권한을 동시에 부여할 수 있다.

mysql> GRANT RELOAD, SUPER ON *.* TO 'user'@'%';

[객체 권한 부여] 객체 단위로 권한을 부여할 때는 ON절에 *.*, DB명.*, DB명.DB객체명을 모두 사용할 수 있다.

MySQL 서버 내 모든 DB에 권한 부여

mysql> GRANT SELECT ON *.* TO 'user'@'%';

테이블의 경우 특정 컬럼에만 권한(DELETE를 제외한 DML)을 부여할 수 있다.

하지만 이는 추천하는 방법이 아니다. 특정 컬럼에만 권한을 부여하면, 테이블의 나머지 컬럼에 대한 권한 체크가 이루어지기 때문에 성능에 영향을 미칠 수 있다.

이러한 이유로 테이블의 특정 컬럼에만 권한을 부여할 때는 VIEW 테이블을 만들어 사용하는게 좋을 수 있다.

아래 명령문은 name과 age 컬럼만 조회 할 수 있다.

mysql> GRANT SELECT(name, age) ON tmp_db.student TO 'user'@'%';

권한 부여 확인

권한을 확인하는 방법은 다양하다.

[현재 로그인한 사용자의 권한 확인]

mysql> SHOW GRANTS;

테이블 조회 방식으로 정렬된 형태로도 확인 할 수 있다.

[계정별 권한 확인]

mysql> select * from mysql.user;

[DB별 권한 확인]

mysql> select * from mysql.db;

[테이블별 권한 확인]

mysql> select * from mysql.tables_priv;

[컬럼별 권한 확인]

mysql> select * from mysql.columns_priv;

[스토어드 프로그램(프로시저, 함수)별 권한 확인]

mysql> select * from mysql.procs_priv;

[동적 권한별 권한 확인]

mysql> select * from mysql.global_grants;

역할(Role)

MySQL에서 역할(Role)은 여러 권한의 집합을 의미한다. 1개 이상의 권한을 묶어 하나의 역할로 만들 수 있고, 이를 여러 사용자에게 역할 부여할 수 있다.

MySQL에서 역할은 내부적으로 사용자(user)와 같은 객체로 취급된다.

MySQL 내부적으로 사용자와 역할은 같은 객체로 취급되기 때문에 구분하기 어렵다.

이러한 이유로 역할명의 접두사는 role_을 주로 사용한다.

역할 생성

role_tmp 역할 생성

mysql> CREATE ROLE role_tmp;
또는
mysql> CREATE ROLE 'role_tmp';

역할에 권한 부여

role_tmp 역할에 select, insert, update 권한 부여

해당 역할은 tmp_db의 모든 객체에 대해 delete를 제외한 DML 사용 권한을 가지고 있다.

mysql> GRANT SELECT, INSERT, UPDATE ON tmp_db.* TO role_tmp;

사용자에게 역할 부여

mysql> GRANT role_tmp TO 'user'@'%';

역할 활성화

부여받은 역할은 바로 사용할 수 없다.

부여받은 역할을 별도로 역할을 활성화해야 비로소 역할의 권한/기능을 사용할 수 있다.

[역할 활성화] 현재 접속 계정에 부여받은 역할만 활성화할 수 있다.

mysql> SET ROLE 'role_tmp';

사용자에게 부여된 역할이 활성화되어도 사용자가 로그아웃 하게 되면, 역할의 활성화가 비활성화로 초기화된다.

이처럼 수동적인 역할 활성화가 아닌 자동으로 역할을 활성화하기 위해서는 activate_all_roles_on_login 시스템 변수를 ON으로 설정해야 한다.

[역할 자동활성화 설정]

mysql> SET GLOBAL activate_all_roles_on_login = 'ON';

역할 활성화 (기본 역할)

역할을 기본 역할로 설정/활성화하려면 SET DEFAULT 명령문을 사용해야 한다.

기본 역할로 설정/활성화된 역할은 사용자가 재로그인 해도 역할이 자동 활성화되어 있다.

[특정 역할을 기본 역할로 설정/활성화]

mysql> SET DEFAULT ROLE role_tmp TO 'user'@'%';

[부여받은 모든 역할을 기본 역할로 설정/활성화]

mysql> SET DEFAULT ROLE ALL TO 'user'@'%';

기본 역할 삭제

mysql> SET DEFAULT ROLE NONE TO 'user'@'%';

역할 확인

[활성화된 역활 리스트 확인]

mysql> SELECT current_role();

[계정별 부여된 기본 역할 확인] 기본 역할로 부여받은 경우 사용자가 재로그인을 하면 해당 기본 역할은 자동 활성화된다.

mysql> SELECT * from mysql.default_roles;

[역할에 부여된 관계 확인] 사용자별로 부여받은 역할 정보를 확인

mysql> SELECT * from mysql.role_edges;

[특정 역할에 부여된 권한 확인]

mysql> SHOW GRANTS FOR 'role_tmp';

[특정 사용자에게 부여된 역할/권한 확인]

mysql> SHOW GRANTS FOR 'user'@'%';

MySQL에서 사용자는 단순 ID뿐만 아니라 사용자의 IP 접속 정보도 확인할 수 있다.

📌 사용자 식별

MySQL에서 사용자 계정은 ID(사용자명)와 호스트명(IP 또는 도메인명 포함)을 묶어 하나의 계정으로 사용된다.

이러한 이유로 ID는 같지만, 접속 호스트명이 다른 경우 서로 다른 계정으로 인식된다.

아래 두 계정의 ID는 같지만, 호스트명이 다르므로 서로 다른 계정이다.

'asd'@'192.168.110.202' (비밀번호는 1234)
'asd'@'%'                 (비밀번호는 1234)

또한 ID는 같지만, 호스트명이 다른 경우 호스트 정보가 더 구체적인 계정을 우선하여 인증을 시도한다.

IP가 192.168.110.202인 클라이언트가 MySQL에 접속할 때 아래 순서로 접속을 시도한다.

'asd'@'192.168.110.202'     호스트 정보가 구체적인 계정을 먼저 시도
'asd'@'%'                     위 계정 접속이 실패하면, 해당 계정으로 시도

📌 계정 종류

MySQL에서 계정은 SYSTEM_USER 권한 유무에 따라 시스템 계정과 일반 계정으로 나뉜다.

▶︎ 시스템 계정

시스템 계정은 SYSTEM_USER 권한을 가진 계정으로 DBA를 위한 계정이다.

시스템 계정은 계정 생성 및 세션 관리 등 DB 서버와 관련된 중요 작업을 수행할 수 있다.

▶︎ 일반 계정

일반 계정은 개발자 및 데이터 분석가 등을 위한 계정이다.

📌 계정 생성

MySQL에서 계정 생성 시 다양한 옵션을 추가하여 상황/작업에 맞는 계정을 생성할 수 있다.

아래는 일반적으로 많이 사용되는 계정 생성 명령문과 옵션이다.

mysql> CREATE USER 'user'@'%'
    IDENTIFIED WITH '<플러그인 이름>' BY '<비밀번호>'        # 인증 방식(인증 플러그인)을 명시적으로 설정하여 비밀번호 설정
    IDENTIFIED BY '<비밀번호>'                # 기본 인증방식 사용
    REQUIRE NONE                            # SSL 인증서 없이 접속 허용
    PASSWORD EXPIRE INTERVAL 30 DAY            # 30일 후 비밀번호 만료
    ACCOUNT UNLOCK                            # 계정 장금 해제 상태로 생성
    PASSWORD HISTORY DEFAULT                # 이전 비밀번호 재사용 제한 횟수
    PASSWORD REUSE INTERVAL DEFAULT            # 비밀전호 재사용 가능 기간
    PASSWORD REQUIRE CURRENT DEFAULT        # 비밀전호 변경 시 이전 비밀번호 확인 여부
;

▶︎ 계정 생성 옵션

• IDENTIFIED : 사용자 인증 방식 지정
  • IDENTIFIED BY '<비밀번호>'
  • IDENTIFIED WITH <플러그인 이름> BY '비밀번호'
• REQUIRE : SSL/TLS 접속 조건 설정
  • SHA-2 인증 방식은 암호화된 채널만으로 MySQL 서버에 접속할 수 있다.
  • REQUIRE NONE : SSL 없이도 접속 가능 (기본)
  • REQUIRE SSL : SSL 필수
• PASSWORD EXPIRE : 비밀번호 만료 정책 설정
  • PASSWORD EXPIRE : 즉시 만료 (다음 로그인 시 비밀번호 변경)
  • PASSWORD EXPIRE INTERVAL 30 DAY : 30일 유효기간 설정
  • PASSWORD EXPIRE NEVER : 비밀번호 만료 없음
  • PASSWORD EXPIRE DEFAULT : 시스템 변수를 기준으로 만료 처리
  • 시스템 변수 : default_password_lifetime
• PASSWORD HISTORY : 이전에 사용했던 비밀번호 재사용 제한
  • PASSWORD HISTORY 10 : 최근에 사용한 10개 비밀번호 재사용 금지
  • PASSWORD HISTORY DEFAULT : 시스템 변수를 기준으로 처리
  • 시스템 변수 : password_history
• PASSWORD REUSE INTERVAL : 이전 비밀번호 재사용까지 최소 기간 설정
  • PASSWORD REUSE INTERVAL 365 DAY : 1년 내 재사용 불가
  • PASSWORD REUSE INTERVAL DEFAULT : 시스템 변수를 기준으로 처리
  • 시스템 변수 : password_reuse_interval
• PASSWORD REQUIRE CURRENT : 비밀번호 변경 시, 기존 비밀번호 입력을 요구할지 여부 설정
  • PASSWORD REQUIRE CURRENT : 반드시 현재 비밀번호 입력
  • PASSWORD REQUIRE OPTIONAL : 비밀번호 입력 필요 없음
  • PASSWORD REQUIRE CURRENT DEFAULT : 시스템 변수를 기준으로 처리
  • 시스템 변수 : password_require_current
• ACCOUNT LOCK / UNLOCK
  • ACCOUNT LOCK : 계정 생성 후 접속 차단
  • ACCOUNT UNLOCK : 계정 생성 후 접속 허용 (기본)

📌 계정 비밀번호 관리

▶︎ 비밀번호 수준 관리

MySQL에서 비밀번호 설정 시 유효성 체크 규칙을 적용하려면 validate_password 컴포넌트를 사용해야 한다.

비밀번호 유효성 체크 규칙 컴포넌트가 없다면 아래 명령문으로 설치/확인 작업을 수행한다.

[validate_password 컴포넌트 설치]

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

[validate_password 컴포넌트 설치 확인]

mysql> SELECT * FROM mysql.component;

validate_password 컴포넌트를 설치하면 다음과 같은 시스템 변수를 추가로 확인할 수 있다.

validate_password.check_user_name            # 사용자명 포함 여부 확인(ON/OFF)
validate_password.dictionary_file            # 금지 단어 설정 파일
validate_password.length                    # 최소 길이 설정
validate_password.mixed_case_count            # 대소문자 최소 개수 설정
validate_password.number_count                # 숫자 최소 개수 설정
validate_password.policy                    # 정책 설정 (LOW: 길이만, MEDIUM: 길이 + 숫자 + 대소문자 + 특수문자, STRONG: 모든 시스템 변수 검사)
validate_password.special_char_count        # 특수문자 최소 개수 설정

▶︎ 듀얼(Dual) 비밀번호

MySQL 8.0 이후부터 1개 계정에 2개의 비밀번호를 설정할 수 있다.

2개의 비밀번호는 각각 프라이머리(Primary)와 세컨더리(Secondary)로 구분된다.

• 프라이머리(Primary) : 최근 설정된 비밀번호(new password)
• 세컨더리(Secondary) : 이전 비밀번호(ord password)

듀얼 비밀번호를 설정하려면, 비밀번호 변경 명령어에 RETAIN CURRENT PASSWORD 옵션을 추가하면 된다.

# 신규 비밀번호 추가
mysql> ALTER USER 'admin'@'localhost' IDENTIFIED BY '<새로운 듀얼 비밀번호>' RETAIN CURRENT PASSWORD

세컨더리(Secondary) 비밀번호를 삭제는 아래 명령어를 사용하면 된다.

# 세컨더리 비밀번호(ord password) 삭제
mysql> ALTER USER 'admin'@'localhost' DISCARD OLD PASSWORD;

유닉스 계열(리눅스 포함)에서 MySQL 설정 파일은 my.cnf라는 이름으로 사용된다.

※ 윈도우 OS의 경우 my.ini라는 이름으로 사용된다.

MySQL 서버가 실행될 때 my.cnf 파일을 참조한다. 이때 특정 경로 하나만 참조하는 것이 아니라, 여러 디렉터리를 순차적으로 탐색하면서 가장 먼저 발견한 my.cnf 파일만을 사용하고, 이후 경로의 설정 파일은 무시한다.

만약 MySQL 서버를 실행할 때 어떤 경로의 my.cnf 파일을 사용하는지 확인하려면 아래 명령어를 사용하여 확인할 수 있다.

$> mysqld --verbose --help | grep 'my.cnf'
또는
$> mysql --help | grep 'my.cnf'

위 명령어를 실행하면 MySQL 서버가 참조할 수 있는 my.cnf 파일을 순서대로 확인할 수 있다.

출력 결과를 보면 아래 순서대로 my.cnf 파일을 참조한다.

1. /etc/my.cnf (1 순위 : 가장 먼저 탐색)
2. /etc/mysql/my.cnf
3. /usr/etc/my.cnf
4. ~/.my.cnf

설정 파일은 일반적으로 /etc/my.cnf 또는 /etc/mysql/my.cnf을 사용한다.

그러나 하나의 DB 서버에 여러 개의 MySQL 서버를 실행할 때 설정 파일 충돌이 발생할 수 있다. (충돌을 방지하기 위해 공유 디렉터리가 아닌 별도 디렉터리를 만들어 각 MySQL 서버에 맞는 설정 파일을 적용해야 한다)

📌 my.cnf 파일

my.cnf 파일에는 여러 개의 설정 그룹(실행 프로그램명)을 담을 수 있다. MySQL에서는 일반적으로 실행 프로그램명을 설정 그룹명으로 지정하여 참조/사용한다.

아래는 참고 예시며, 프로그램명이 2개 이상의 설정 그룹을 참조하는 경우도 있다.

+------------------+-----------------+
| 프로그램명          | 설정 그룹         |
+------------------+-----------------+
| mysqldump        | [mysqldump]     |
| mysqld           | [mysqld]        |
| mysqld_safe      | [mysqld_safe]   |
+------------------+-----------------+

또한, 각 프로그램은 같은 설정 파일을 참조/공유하지만, 서로 독립적으로 무관하게 적용된다.

📌 MySQL 시스템 변수

MySQL 서버는 실행 방식과 제어 설정을 위한 다양한 값들을 시스템 변수(System Variables)라는 이름으로 관리한다.

MySQL의 시스템 변수는 아래 명령문을 통해 확인할 수 있다.

# 글로벌 변수
mysql> SHOW GLOBAL VARIABLES;
>
# 세션 변수 (현재 접속 세션에 적용된 값을 보여준다)
mysql> SHOW VARIABLES;

MySQL에서 각 시스템 변수는 여러 속성으로 정의되어 있다. 시스템 변수 속성은 변수마다 서로 상이하기 때문에 공식 문서 확인이 필요하다.

이러한 시스템 변수는 다음과 같은 기준으로 분류된다.

• 적용 범위에 따른 분류
  • 글로벌 변수(Global Variables) : 전체 서버에 적용됨
  • 세션 변수(Session Variables) : 현재 접속 세션에만 적용됨
• 변경 가능 여부에 따른 분류
  • 정적 변수(Static Variables) : 서버 실행 중에는 변경할 수 없음
  • 동적 변수(Dynamic Variables) : 서버 실행 중에도 변경 가능함

▶︎ 글로벌 변수(Global Variables)

글로벌 변수는 하나의 MySQL 서버 인스턴스에 전체적으로 영향을 미치는 시스템 변수이다. 때문에 글로벌 변수는 MySQL 서버 전체에 대한 설정이 필요할 때 사용된다.

▶︎ 세션 변수(Session Variables)

세션 변수는 각 클라이언트 접속 세션 단위로 적용되는 시스템 변수다. 클라이언트가 MySQL 서버에 접속하면, 해당 세션에만 적용되는 기본값이 할당된다. 이를 통해 클라이언트별 개별 커넥션 단위로 시스템 변수값을 다르게 지정할 수 있다.

일반적으로 세션 변수로 적용된 시스템 변수는 세션 변수와 글로벌 변수에도 동시에 존재한다.

이러한 경우 아래 그림처럼 표시(MySQL 8.4 기준)된다.

▶︎ 정적 변수(Static Variables)

정적 변수는 MySQL 서버 시작 시 설정된 값이 서버가 종료될 때까지 유지되는 변수다.

시스템 정적 변수는 디스크에 저장된 설정 파일(my.cnf)을 변경하더라도 MySQL을 재시작하기 전에는 적용되지 않는다.

▶︎ 동적 변수(Dynamic Variables)

동적 변수는 SET 명령을 사용하여 서버의 재시작 없이 시스템 변수값을 변경할 수 있다. 이는 설정 파일(my.cnf)에 적용된 것은 아니며, 현재 실행 중인 MySQL 인스턴스에서만 유효하다. (MySQL이 재시작하면 다시 초기화)

mysql> SET GLOBAL max_connections = 500;
>
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+

이러한 이유로 영구적인 변수값을 적용하려면 설정 파일(my.cnf)에 변수값을 설정하거나 PERSIST 옵션을 사용하여 변수값을 수정해야 한다.

SET 또는 SHOW 명령을 사용할 때 GLOBAL 키워드를 사용하면 글로벌 변수에 대한 목록과 설정을 확인/수정할 수 있으며, GLOBAL 키워드를 제외하면 세션 변수의 목록과 설정을 확인/수정할 수 있다.

마지막으로 변수의 범위가 Global이면서 Session인 경우 Global 변수의 값을 변경해도 이미 클라이언트 세션으로 연결된 변수값은 변경되지 않고 그대로 유지된다.

▶︎ SET PERSIST

동적 변수(Dynamic Variables)는 MySQL 서버 실행 중 SET 명령를 사용하여 시스템 변수값을 변경할 수 있다. 하지만 이는 현재 실행 중인 MySQL 인스턴스에만 유효하기 것이기 때문에 MySQL 서버가 종료되면 시스템 변수값은 초기화가 된다.

초기화를 방지하고 변수 설정값을 영구 적용하고 싶다면 SET PERSIST 명령문을 사용해야 한다.

SET PERSIST 명령문을 사용하여 동적 시스템 변수를 설정할 경우 해당 시스템 변수값은 즉시 반영됨과 동시에 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 기록한다.

mysql> SET PERSIST max_connections = 1000;
>
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+

MySQL 서버가 실행할 때 my.cnf 파일 뿐만 아니라 mysqld-auto.cnf 파일도 같이 참조하여 실행한다.

즉, SET PERSIST 명령문을 사용한 경우 별도의 my.cnf 파일 수정 없이 변경된 시스템 변수를 사용할 수 있다.

※ SET PERSIST 명령문은 세션 변수(Session Variables)에는 적용되지 않는다.

이러한 이유로 아래 두 명령문은 같은 결과를 보여준다.

# 권장 O
mysql> SET PERSIST max_connections = 1000;
>
# 사용은 가능, 권장 X
mysql> SET GLOBAL PERSIST max_connections = 1000;

▶︎ SET PERSIST_ONLY

일반적으로 시스템 변수는 SET 명령을 통해 서버 실행 중 즉시 적용할 수 있다.

그러나 정적 변수는 서버 실행 중에는 변경할 수 없기 때문에, 서버 재시작 이후에 적용되도록 미리 저장하고 싶을 때는 SET PERSIST_ONLY 명령을 사용한다.

mysql> SET PERSIST max_connections = 5000;
>
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
>
mysql 서버 재시작...
>
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+

SET PERSIST_ONLY는 값을 mysqld-auto.cnf 파일에만 기록하고, 현재 실행 중인 MySQL 인스턴스에는 적용하지 않는다.

이후 MySQL 서버가 재시작될 때 이 설정 파일을 읽어, 해당 변수값이 적용된다.

▶︎ mysqld-auto.cnf 파일

SET PERSIST와 SET PERSIST_ONLY을 사용하여 생성/기록된 mysqld-auto.cnf 파일은 JSON 형태로 데이터를 기록한다.

$> cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "5000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1754217803622744}}}}

[mysqld-auto.cnf 파일의 JSON 데이터 정렬 후]

{
   "Version":2,
   "mysql_dynamic_parse_early_variables":{
      "max_connections":{
         "Value":"5000",                        # 수정 값
         "Metadata":{
            "Host":"localhost",
            "User":"root",                        # 수정자 
            "Timestamp":1754217803622744        # 수정 시간
         }
      }
   }
}

▶︎ RESET PERSIST

SET PERSIST와 SET PERSIST_ONLY을 사용하여 시스템 변수를 설정한 후 해당 설정 내용을 삭제해야 할 때도 있다.

이때 mysqld-auto.cnf 파일에 들어가 직접 내용을 수정하는 방법은 매우 위험한 방법이다.

mysqld-auto.cnf 파일 내용을 삭제할 때는 RESET PERSIST 명령문을 사용하여 설정된 시스템 변수 내용을 삭제하는 것이 안전한 방법이다.

RESET PERSIST명령문을 사용한 직후 시스템 변수값이 즉시 반영되진 않는다. MySQL 서버 재시작 이후 시스템 변수값을 확인해 보면 값이 변경됨을 확인할 수 있다.

RESET PERSIST max_connections;
>
# 전체 초기화
RESET PERSIST;

▶︎ 중요 시스템 변수 (my.cnf 파일)

버전에 따라 다르지만, MySQL 서버의 시스템 변수는 600개 전후로 보인다. 상황에 따라 플러그인/컴포넌트에 따라 시스템 변수의 수는 증가할 수 있다.

아래 리스트 중요 시스템 변수 목록이다.

---- 파일 및 디렉터리 관련 ----
datadir          : 데이터베이스 파일이 저장되는 기본 디렉터리입니다.
socket             : 서버 연결에 사용되는 Unix 소켓 파일의 경로입니다.
tmpdir              : 임시 파일이 생성되는 디렉터리입니다.
secure_file_priv : LOAD DATA INFILE과 같은 작업 시 파일을 읽거나 쓸 수 있는 디렉터리를 제한합니다.


---- 연결 및 권한 관련 ----
max_connections             : 동시에 접속할 수 있는 최대 클라이언트 수를 지정합니다.
max_connect_errors             : 연결 실패 횟수가 이 값을 초과하면 해당 호스트를 차단합니다.
activate_all_roles_on_login : 로그인 시 모든 역할을 자동으로 활성화할지 여부를 결정합니다.
skip_name_resolve             : 클라이언트 연결 시 호스트 이름 대신 IP 주소를 사용해 DNS 조회를 건너뜁니다.
local_infile                 : LOAD DATA LOCAL INFILE 명령의 사용 가능 여부를 설정합니다.


---- 서버 동작 및 기타 옵션 관련 ----
default_storage_engine             : 테이블을 생성할 때 기본으로 사용될 스토리지 엔진입니다.
default_tmp_storage_engine         : 임시 테이블에 사용될 기본 스토리지 엔진입니다.
table_open_cache                 : 모든 스레드에서 열어놓을 수 있는 테이블 파일의 캐시 크기입니다.
table_open_cache_instances         : table_open_cache를 분할하여 동시 접근 성능을 향상시킵니다.
open_files_limit                 : MySQL이 열 수 있는 파일의 최대 개수를 설정합니다.
explicit_defaults_for_timestamp : TIMESTAMP 데이터 타입의 기본 동작 방식을 변경합니다.
sql_mode                         : MySQL이 지원하는 SQL 구문과 유효성 검사 규칙을 설정합니다.
max_allowed_packet                 : 서버가 처리할 수 있는 패킷의 최대 크기입니다.
time_zone                         : 서버의 시간대를 설정합니다.


---- 보안 및 암호화 관련 ----
block_encryption_mode       : AES_ENCRYPT() 및 AES_DECRYPT() 함수에 사용될 블록 암호화 모드를 지정합니다.
default_password_lifetime : 비밀번호 만료 기간을 설정합니다.


---- 오류 및 코어 파일 관련 ----
log_error             : 오류 로그 파일의 경로를 지정합니다.
log_error_verbosity : 오류 로그에 기록될 메시지의 상세 수준을 설정합니다.
core_file             : 서버가 비정상 종료될 경우 코어 파일을 생성할지 여부를 결정합니다.


---- 버퍼 및 캐시 관련 ----
max_heap_table_size          : 메모리 테이블의 최대 크기를 제한합니다.
tmp_table_size                  : 메모리 임시 테이블의 최대 크기를 설정합니다.
innodb_buffer_pool_size      : InnoDB 데이터와 인덱스를 캐싱하는 메인 메모리 영역의 크기입니다.
innodb_buffer_pool_instances : 버퍼 풀을 여러 개로 분할하여 동시 접근 성능을 향상시킵니다.


---- 인덱스 및 검색 관련 ----
ngram_token_size              : ngram 전문 검색 파서에서 토큰의 크기를 설정합니다.
innodb_adaptive_hash_index      : 자주 접근하는 페이지에 대해 해시 인덱스를 생성하여 성능을 높입니다.
innodb_cmp_per_index_enabled : 압축된 테이블의 압축 효율을 모니터링합니다.


---- 문자셋 및 정렬 관련 ----
character_set_server      : 서버의 기본 문자 집합입니다.
character_set_filesystem : 파일 시스템의 문자 집합을 설정합니다.
collation_server         : 서버의 기본 정렬 방식입니다.


---- 바이너리 로그 관련 ----
log_bin                 : 바이너리 로그 활성화 여부를 결정합니다.
binlog_format         : 바이너리 로그의 기록 형식을 설정합니다 (STATEMENT, ROW, MIXED).
max_binlog_size         : 바이너리 로그 파일의 최대 크기를 지정합니다.
sync_binlog             : 바이너리 로그를 디스크에 얼마나 자주 동기화할지 설정합니다.
binlog_checksum         : 바이너리 로그에 체크섬을 기록할지 여부를 지정합니다.
binlog_order_commits : 커밋 순서대로 바이너리 로그를 기록할지 여부를 설정합니다.
binlog_row_image     : ROW 기반 바이너리 로그에 전체 행을 기록할지, 변경된 부분만 기록할지 결정합니다.
gtid_mode             : GTID(Global Transaction Identifier)를 사용할지 여부를 설정합니다.


---- 슬로우 쿼리 로그 설정 ----
slow_query_log              : 실행 시간이 긴 쿼리를 기록하는 슬로우 쿼리 로그를 활성화합니다.
slow_query_log_file          : 슬로우 쿼리 로그 파일의 경로를 지정합니다.
long_query_time               : 이 시간(초)을 초과하는 쿼리만 슬로우 쿼리 로그에 기록합니다.
log_slow_admin_statements : ALTER TABLE, ANALYZE TABLE 같은 관리 구문도 로그에 기록할지 여부를 설정합니다.
log_slow_extra              : 로그에 추가적인 정보를 기록할지 여부를 결정합니다.


---- InnoDB 설정 ----
innodb_data_home_dir           : 테이블스페이스 파일의 기본 디렉터리입니다.
innodb_data_file_path           : InnoDB 시스템 테이블스페이스 파일의 이름과 크기를 설정합니다.
innodb_temp_data_file_path       : InnoDB 임시 테이블스페이스 파일의 경로와 크기를 지정합니다.
innodb_log_group_home_dir       : 리두 로그 파일이 저장되는 디렉터리입니다.
innodb_log_files_in_group       : 리두 로그 그룹에 속한 파일의 개수를 설정합니다.
innodb_log_file_size           : 각 리두 로그 파일의 크기를 지정합니다.
innodb_file_per_table           : 각 테이블을 별도의 .ibd 파일에 저장할지 여부를 결정합니다.
innodb_undo_directory           : UNDO 로그 파일이 저장되는 디렉터리입니다.
innodb_rollback_segments       : 롤백 세그먼트의 개수입니다.
innodb_undo_tablespaces           : UNDO 테이블스페이스의 개수를 설정합니다.
innodb_max_undo_log_size       : UNDO 로그 파일의 최대 크기를 제한합니다.
innodb_undo_log_truncate       : UNDO 로그 파일의 크기가 innodb_max_undo_log_size를 초과할 경우 잘라낼지 여부를 설정합니다.
innodb_sort_buffer_size           : 인덱스 생성 시 사용되는 정렬 버퍼의 크기입니다.
innodb_flush_log_at_trx_commit : 커밋 시 리두 로그를 디스크에 플러시하는 방식을 제어하여 성능과 내구성 사이의 균형을 맞춥니다.
innodb_flush_method               : 데이터와 로그 파일에 접근할 때 사용되는 플러시 방법을 설정합니다.
innodb_io_capacity               : InnoDB가 백그라운드 I/O 작업을 위해 사용할 수 있는 최대 I/O 작업량입니다.
innodb_io_capacity_max           : I/O 부하가 높을 때 InnoDB가 사용할 수 있는 최대 I/O 작업량입니다.
innodb_doublewrite               : 데이터 페이지의 이중 쓰기를 활성화하여 데이터 손상을 방지합니다.
innodb_checksum_algorithm       : 데이터 페이지의 손상 여부를 확인하는 체크섬 알고리즘을 설정합니다.
innodb_print_all_deadlocks       : 데드락 발생 시 모든 데드락 정보를 오류 로그에 출력합니다.
innodb_status_output_locks       : SHOW ENGINE INNODB STATUS 명령에 잠금 정보를 추가합니다.
innodb_ft_enable_stopword       : 전문 검색(Full-Text Search)에서 불용어 사용 여부를 설정합니다.


---- 성능 스키마 설정 ----
performance_schema                                        : 성능 스키마의 활성화 여부를 결정합니다.
performance_schema_events_stages_history_long_size        : 스테이지 이력 테이블에 저장될 최대 이벤트 수를 설정합니다.
performance_schema_events_stages_history_size           : 스테이지 이력 테이블의 크기를 지정합니다.
performance_schema_events_statements_history_long_size : 구문 이력 테이블에 저장될 최대 이벤트를 설정합니다.
performance_schema_events_statements_history_size       : 구문 이력 테이블의 크기를 지정합니다.
performance_schema_events_waits_history_long_size        : 대기 이력 테이블에 저장될 최대 이벤트를 설정합니다.
performance_schema_events_waits_history_size           : 대기 이력 테이블의 크기를 지정합니다.


---- 비밀번호 유효성 검사 설정 ----
password_history                     : 재사용할 수 없는 이전 비밀번호의 개수를 지정합니다.
validate_password.policy             : 비밀번호 유효성 검사 정책을 설정합니다. (LOW, MEDIUM, STRONG)
validate_password.length             : 최소 비밀번호 길이를 지정합니다.
validate_password.mixed_case_count     : 소문자, 대문자의 최소 개수를 설정합니다.
validate_password.number_count         : 숫자의 최소 개수를 설정합니다.
validate_password.special_char_count : 특수 문자의 최소 개수를 설정합니다.
validate_password.dictionary_file     : 비밀번호에 사용할 수 없는 단어 목록이 담긴 파일을 지정합니다.

🔗 참고

MySQL 8.4 시스템 변수 공식 문서

Main PC : macOS (Apple Silicon) Virtual PC : Oracle VirtualBox 기반 Linux OS(CentOS 9) Architecture : ARM64(aarch64)

Apple Silicon 기반의 macOS에서 VirtualBox로 구동되는 CentOS 9 VM이며, CPU 아키텍처는 x86_64가 아닌 ARM64(aarch64)를 사용하고 있습니다.

따라서 모든 작업이 x86_64 기반이 아닌 ARM64(aarch64)로 진행되었습니다.

✅ MySQL 업그레이드(업데이트)

MySQL을 업그레이드하는 방법은 크게 두 가지가 있다.

1. MySQL 서버의 데이터가 있는 상태에서 업그레이드 (In-Place Upgrade)
2. MySQL 서버의 데이터를 백업한 후, 업그레이드된 서버에 데이터 이관 (Logical Upgrade)

인플레이스 업그레이드(In-Place Upgrade)는 여러 제약 사항이 존재하지만, 업그레이드 시간이 짧다는 장점이 있다. 논리적 업그레이드(Logical Upgrade)는 여러 제약이 없지만, 업그레이드 시간이 길어질 가능성이 있다. (데이터의 양이 많은 경우 시간이 오래 걸릴 수 있다.)

📌 In-Place Upgrade

업그레이드의 규모/크기를 볼 때 크게 패치 버전 업그레이드와 메인 버전 업그레이드로 나뉜다.

패치 버전 업그레이드 : 8.4.2 -> 8.4.6 메인 버전 업그레이드 : 5.1.X -> 8.4.X

▶︎ 패치 버전 업그레이드

패치 버전 업그레이드의 경우 패치 버전 간 데이터 파일 및 구조적으로 큰 차이가 없어서 MySQL 서버 프로그램만 재설치하면 업그레이드가 된다.

패치 버전 업그레이드는 버전의 순서를 건너뛰고 업그레이드할 수 있다. MySQL 8.4.2 -> 8.4.6

▶︎ 메인 버전을 업그레이드

메인 버전 업그레이드는 데이터 파일 및 구조적으로 큰 차이가 있을 수 있기 때문에 직전 버전에서의 업그레이드만을 허용한다.

메인 버전 업그레이드의 경우 직전 버전에서의 업그레이드만을 허용하기 때문에 MySQL 5.1에서 MySQL 8.4로 업그레이드를 진행한다면 아래와 같은 순서로 업그레이드해야 한다.

MySQL 5.1 -> 5.5 -> 5.6 -> 5.7 -> 8.0 -> 8.4

메인 버전 업그레이드는 특정 패치 버전에서만 가능한 경우가 있다. 가령 GA 버전이 아닌 패치 버전에서는 메이저 버전 업그레이드가 불가능하다.

GA(General Availability)버전은 안정성이 확인된 버전임을 의미한다. 때문에 불안전한 패치 버전에서 메인 버전 업그레이드가 불가능한 경우가 발생한다.

이러한 이유로 ⭐️In-Place Upgrade를 진행할 때는 꼭 MySQL 공식 문서를 확인해야 한다.⭐️

MySQL 8.4의 업그레이드 관련 공식 문서

📌 Logical Upgrade

- 추후 업로드 예정 -

Main PC : macOS (Apple Silicon) Virtual PC : Oracle VirtualBox 기반 Linux OS(CentOS 9) Architecture : ARM64(aarch64)

Apple Silicon 기반의 macOS에서 VirtualBox로 구동되는 CentOS 9 VM이며, CPU 아키텍처는 x86_64가 아닌 ARM64(aarch64)를 사용하고 있습니다.

따라서 모든 작업이 x86_64 기반이 아닌 ARM64(aarch64)로 진행되었습니다.

✅ MySQL 설치

리눅스 환경에서 MySQL을 설치하는 방법은 크게 두 가지가 있다.

제가 CLI 기반으로 설치 하는 방법을 아래 두 방법만 알고 있습니다...

1. MySQL 공식 리포지토리를 등록해 원하는 버전을 설치하는 방법 (권장)
2. Linux OS 기본 패키지를 이용해 간편하게 설치하는 방법 (비권장)

Linux OS에서 기본으로 제공하는 MySQL 패키지를 이용한 방법은 MySQL 설치가 간단하다는 장점을 가지고 있지만, 특정 버전을 선택할 수 없다는 점과 MySQL이 아닌 MariaDB로 설치될 가능성이 있다는 단점이 있다.

OS 라이선스 이슈 및 OS 버전에 따라 기본적으로 채택되는 DB가 달라질 수 있다.

때문에 MySQL을 설치하려 했지만, 의도치 않게 MariaDB가 설치될 수도 있다.

📌 MySQL 리포지토리를 활용한 방법 (권장)

MySQL을 설치하려면 먼저 공식 리포지토리를 시스템에 등록해야 한다.

CentOS 09 기준 아래 MySQL 리포지토리를 설치하면 된다.

▶︎ RPM 설치 명령문

$> wget https://dev.mysql.com/get/mysql84-community-release-el9-2.noarch.rpm

$> dnf install mysql84-community-release-el9-2.noarch.rpm

정상적으로 설치가 완료되면 아래와 같은 결과를 확인 할 수 있다.

$> ls -alh /etc/yum.repos.d/*mysql*
-rw-r--r--. 1 root root 3.0K Jul  7 14:41 /etc/yum.repos.d/mysql-community-debuginfo.repo
-rw-r--r--. 1 root root 2.7K Jul  7 14:41 /etc/yum.repos.d/mysql-community.repo
-rw-r--r--. 1 root root 2.8K Jul  7 14:41 /etc/yum.repos.d/mysql-community-source.repo

리퍼지토리가 정상 설치가 되었으면 패키지 명령어(yum, dnf)을 사용하여 설치 가능한 MySQL 소프트웨어 목록을 확인 할 수 있다.

▶︎ RPM 패키지 확인

$> dnf search mysql-community
MySQL Connectors Community                                                72 kB/s |  90 kB     00:01    
MySQL 8.4 LTS Community Server                                           591 kB/s | 1.2 MB     00:02    
MySQL Tools 8.4 LTS Community                                            404 kB/s | 659 kB     00:01    
================================ Name & Summary Matched: mysql-community ================================
mysql-community-debugsource.aarch64 : Debug sources for package mysql-community
===================================== Name Matched: mysql-community =====================================
mysql-community-client.aarch64 : MySQL database client applications and tools
mysql-community-client-plugins.aarch64 : Shared plugins for MySQL client applications
mysql-community-common.aarch64 : MySQL database common files for server and client libs
mysql-community-devel.aarch64 : Development header files and libraries for MySQL database client
                              : applications
mysql-community-icu-data-files.aarch64 : MySQL packaging of ICU data files
mysql-community-libs.aarch64 : Shared libraries for MySQL database client applications
mysql-community-libs-compat.aarch64 : Shared compat libraries for MySQL 8.0.37 database client
                                    : applications
mysql-community-server.aarch64 : A very fast and reliable SQL database server
mysql-community-server-debug.aarch64 : The debug version of MySQL server
mysql-community-test.aarch64 : Test suite for the MySQL database server

▶︎ 설치 가능한 MySQL 버전 확인

$> dnf --showduplicates list mysql-community-server
Last metadata expiration check: 0:04:52 ago on Sat 02 Aug 2025 01:34:20 PM KST.
Available Packages
mysql-community-server.aarch64                    8.4.0-1.el9                     mysql-8.4-lts-community
mysql-community-server.aarch64                    8.4.2-1.el9                     mysql-8.4-lts-community
mysql-community-server.aarch64                    8.4.3-1.el9                     mysql-8.4-lts-community
mysql-community-server.aarch64                    8.4.4-1.el9                     mysql-8.4-lts-community
mysql-community-server.aarch64                    8.4.5-1.el9                     mysql-8.4-lts-community
mysql-community-server.aarch64                    8.4.6-1.el9                     mysql-8.4-lts-community

설치 가능한 MySQL을 확인한 후 상황에 맞는 MySQL 버전을 선택하여 설치한다.

▶︎ MySQL 설치

$> dnf install mysql-community-server-8.4.6
Last metadata expiration check: 0:21:31 ago on Sat 02 Aug 2025 01:34:20 PM KST.
Dependencies resolved.
=========================================================================================================
 Package                             Architecture Version             Repository                    Size
=========================================================================================================
Installing:
 mysql-community-server              aarch64      8.4.6-1.el9         mysql-8.4-lts-community       49 M
Installing dependencies:
 mysql-community-client              aarch64      8.4.6-1.el9         mysql-8.4-lts-community      3.2 M
 mysql-community-client-plugins      aarch64      8.4.6-1.el9         mysql-8.4-lts-community      1.5 M
 mysql-community-common              aarch64      8.4.6-1.el9         mysql-8.4-lts-community      578 k
 mysql-community-icu-data-files      aarch64      8.4.6-1.el9         mysql-8.4-lts-community      2.3 M
 mysql-community-libs                aarch64      8.4.6-1.el9         mysql-8.4-lts-community      1.4 M
>
Transaction Summary
=========================================================================================================
Install  6 Packages
>
Total download size: 58 M
Installed size: 331 M
Is this ok [y/N]:

📌 기본 MySQL 패키지를 활용한 방법 (권장 X)

▶︎ MySQL 설치

기본 패키지 저장소에서 설치할 경우, 아래 단일 명령어로 설치가 가능하다.
단, 이 경우 MariaDB가 설치될 수 있으므로 주의가 필요하다.

$> dnf install mysql-server

📌 MySQL 설치 확인

아래 단일 명령어를 통해 MySQL 설치 여부를 확인할 수 있다.

$> mysql --version
mysql  Ver 8.4.6 for Linux on aarch64 (MySQL Community Server - GPL)

데이터베이스를 사용하면서 예상치 못한 장애 발생 및 비정상적인 쿼리문과 자원 소모 등 이슈가 발생할 수 있다.

이러한 이슈는 서비스를 운영하는 데 있어 치명적인 문제로 이어질 수 있다.

때문에 실시간 모니터링을 통해 장애 예방, 성능 최적화, 운영 효율성 확보 등의 모니터링 작업이 필요하다.

직접 DB 서버에 접속하여 성능 관련 쿼리를 수동으로 작성해 확인할 수도 있지만, 시간에 따른 데이터베이스의 변화 추이를 파악할 때는 전용 모니터링 툴을 사용하는 것이 효과적이다.

모니터링 툴은 Zabbix, Prometheus, MaxGauge, Datadog 등이 있지만, 무료로 사용할 수 있는 점과 실시간으로 데이터베이스의 변화를 확인할 수 있는점에서 Prometheus가 가장 적합한 선택이라 생각한다.

📌 Prometheus

Prometheus는 오픈소스 모니터링 툴이다. 다양한 기능을 제공하고 있으며, 특히 시계열 데이터(Time-series data)를 수집하고 분석하는 데 최적화된 툴이다.

[시계열 데이터(Time-series data)] 시간에 따라 저장된 데이터를 의미한다.

Prometheus는 시간에 따라 생성되는 메트릭(지표) 데이터를 수집(Pull) 해와야 하는데, 이때 사용되는 개념이 Exporter이다. 이러한 Exporter는 사용하는 서비스에 따라 다양한 종류가 있다.

• Node Exporter : IT Infra(하드웨어 및 운영체제)의 메트릭 데이터를 수집
• MySQL Exporter : MySQL 데이터베이스 서버의 메트릭 데이터 수집
  위 Exporter 외에도 다양한 Exporter가 존재한다.

📌 Grafana

Grafana는 시간에 따라 생성되는 메트릭(지표) 데이터를 시각화하는 오픈소스 대시보드 툴이다.

Prometheus를 사용한다면 거의 필수로 함께 사용하는 툴로 메트릭 데이터를 불러와 다양한 차트와 테이블, 그래프로 표현할 수 있다.

✅ MySQL 모니터링

MySQL 모니터링은 아래와 같은 구조로 설계/실행할 계획이다.

Prometheus의 mysqld_exporter는 MySQL 서버에서 주요 성능 지표(메트릭)를 수집한다.

수집된 데이터는 Prometheus의 시계열 데이터베이스에 저장되며, Grafana가 이를 시각화하여 대시보드 형태로 표현한다.

📌 디렉터리 구조

/opt/monitoring/
├── download/             # 모니터링 관련 압축 파일 및 압축 해제 파일 저장
│
├── prometheus/
│   ├── prometheus        # binary
│   ├── promtoll          # binary
│   ├── prometheus.yml    # 설정 파일
│   └── data/              # 수집된 시계열 데이터 저장
└── mysqld_exporter/
    └── mysqld_exporter   # binary

Grafana는 패키지 매니저(dnf, yum, apt)를 통해 설치되므로 별도의 디렉터리를 수동으로 생성하지 않아도 된다.

Prometheus와 mysqld_exporter는 압축 파일을 직접 다운로드한 후 수동으로 설치하는 방식이므로 /opt/monitoring/ 하위에 디렉터리를 생성하여 구성 파일을 정리한다.

📌 MySQL 모니터링용 계정 생성

▶︎ MySQL 모니터링 전용 계정 생성

CREATE USER 'exporter'@'%' IDENTIFIED BY 'password';

▶︎ MySQL 모니터링 계정 권한 부여

• PROCESS : 현재 실행 중인 쿼리/스레드 조회
  • REPLICATION CLIENT : 복제(마스터/슬레이브) 상태 조회
  • SELECT : 모든 DB/테이블 조회

    GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

📌 mysqld_exporter 설치

프로메테우스 공식 사이트에 접속하여 mysqld_exporter 설치 URL을 확인한 후 설치 명령어를 통해 설치한다.

mysqld_exporter 설치는 서버 환경에 맞게 설치해야 정상적인 모니터링이 가능하다.

• 현 서버 환경 스펙 : ARM64 기반의 centOS 09

프로메테우스 다운로드 사이트 mysqld_exporter 릴리즈 다운로드 사이트

▶︎ mysqld_exporter 설치

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.17.2/mysqld_exporter-0.17.2.linux-arm64.tar.gz

▶︎ mysqld_exporter 압축 해제

tar -xvf mysqld_exporter-0.17.2.linux-arm64.tar.gz

▶︎ mysqld_exporter 실행

mysqld_exporter를 실행할 때는 MySQL 설정 파일(.my.cnf)을 사용하여 mysqld_exporter를 실행한다.

설정 파일에는 모니터링에 사용할 계정ID와 PW를 기입한다.

[.my.cnf 파일 내부]

[client]
user=<모니터링용 mysql 계정>
password=<모니터링용 mysql 계정 비밀번호>

[mysqld_exporter 실행]

# 백그라운드에서 실행
./mysqld_exporter --config.my-cnf=/root/.my.cnf &

▶︎ mysqld_exporter 실행 확인

mysqld_exporter 실행 후 http://<설치 IP 주소>:9104 접속을 통해 정상 실행되고 있는지 확인 할 수 있다.

• mysqld_exporter는 기본적으로 9104 포트를 사용한다.

📌 Prometheus 설치

프로메테우스 공식 사이트에 접속하여 prometheus 설치 URL을 확인한 후 설치 명령어를 통해 설치한다.

prometheus 설치는 서버 환경에 맞게 설치해야 정상적인 모니터링이 가능하다.

• 현 서버 환경 스펙 : ARM64 기반의 centOS 09

프로메테우스 다운로드 사이트 prometheus 릴리즈 다운로드 사이트

▶︎ prometheus 설치

wget https://github.com/prometheus/prometheus/releases/download/v3.5.0/prometheus-3.5.0.linux-arm64.tar.gz

▶︎ prometheus 압축 해제

tar -xvf prometheus-3.5.0.linux-arm64.tar.gz 

▶︎ prometheus 설정

prometheus 파일 압축 해제 후 prometheus.yml 내부에 MySQL 모니터링 작업 타겟을 설정한다.

prometheus.yml 하단부에 아래와 타겟 설정 코드를 기입한다.

scrape_configs:
  - job_name: "mysqld_exporter"
    static_configs:
      - targets: ["localhost:9104"]

▶︎ prometheus 실행

타겟 설정이 완료된 prometheus.yml을 기반으로 prometheus를 실행한다.

./prometheus --config.file=prometheus.yml &

▶︎ prometheus 실행 확인

prometheus 실행 후 http://<설치 IP 주소>:9090 접속을 통해 정상 실행되고 있는지 확인 할 수 있다.

• prometheus 기본적으로 9090 포트를 사용한다.

📌 Grafana 설치

그라파나 공식 사이트에 접속하여 grafana 설치 URL을 확인한 후 설치 명령어를 통해 설치한다.

grafana 설치는 서버 환경에 맞게 설치해야 정상적인 대시보드 제작이 가능하다

• 현 서버 환경 스펙 : ARM64 기반의 centOS 09

현재 모니터링 구축은 Enterprise 아닌 OSS 버전으로 설치 진행했다.

그라파나 다운로드 사이트

▶︎ grafana 설치

sudo yum install -y https://dl.grafana.com/oss/release/grafana-12.1.0-1.aarch64.rpm

▶︎ grafana 실행

systemctl start grafana-server.service

▶︎ grafana 실행 확인

systemctl status grafana-server.service

Active 부분이 active (running)으로 되어 있으면 정상 실행되고 있다는 의미이다.

▶︎ grafana 접속

grafana 실행 확인 후 http://<설치 IP 주소>:3000 접속을 통해 grafana에 접속할 수 있다.

• grafana 기본적으로 3000 포트를 사용한다.

grafana 처음 접속 시 ID와 PW는 admin이다. (첫 로그인 후 PW를 변경하라는 화면이 나온다)

📌 Prometheus -> Grafana 데이터 연동

Prometheus와 Grafana를 연동하기 위해서는 Grafana 접속 후 좌측 상단의 "Grafana 로고" 클릭 후 Configuration -> Data sources 순서로 클릭을 진행한다.

이후 "Add data source" 버튼을 클릭하여 데이터를 연동한다.

데이터 연동은 Prometheus 기반의 데이터이기 때문에 화면에 보이는 "Prometheus"를 선택한다.

이후 이름과 URL 등 다양한 옵션을 설정하고 하단의 "Save & Test" 버튼을 클릭한다.

"Save & Test" 버튼을 클릭한 후 위와 같은 성공 문구가 나오면 정상적으로 연동이 되었다는 의미다.

📌 Grafana MySQL 대시보드 제작

대시보드를 제작하기 위해서는 grafana 접속 후 좌측 상단에 "Grafana 로고" 클릭 후 "Dashboards"를 선택한다.

이후 "+ Create dashboards" 버튼을 클릭한다.

"+ Create dashboards" 버튼을 클릭한 후 위 이미지 화면에서 "import dashboard" 버튼을 클릭한다.

다음 화면에서 불러올 대시보드를 설정할 수 있다.

• MySQL의 경우 ID값 7362를 사용하면 된다.
• ID 7362는 MySQL Exporter 전용 대시보드를 의미한다.
• MySQL Overview

이후 "Load" 버튼을 클릭하면 된다.

대시보드의 데이터를 Prometheus로 설정한 뒤 "Import" 버튼을 클릭하면 된다.

📌 대시보드 확인

✅ 외부 PC에서 대시보드 확인

지금까지 위 대시보드 확인은 모두 서버 PC에서 확인한 결과이다.

이러한 서버의 결과를 클라이언트 PC에서 확인하려면 별도의 포트 방화벽 설정이 필요하다.

만약 클라이언트 PC로 아래 URL 접근이 가능하다면 별도의 설정 없이 서버의 대시 보드를 확인 할 수 있다.

http://<서버 PC IP>:3000

📌 포트 방화벽 설정

Grafana는 기본 3000번 포트를 사용하고 있기 때문에 3000번 포트가 열려 있어야 외부 접근이 가능하다.

현재 열려 있는 포트 확인

firewall-cmd --list-ports

만약 3000번 포트가 안 열려 있는 경우 아래 명령어를 통해 3000번 포트를 열어준다.

firewall-cmd --add-port=3000/tcp --permanent
firewall-cmd --reload

📌 외부 PC에서 대시보드 확인

✅ 모니터링 주요 항목

▶︎ MySQL Uptime

MySQL 서버가 현재까지 가동된 시간.

▶︎ Current QPS

초당 처리된 쿼리 수 (데이터베이스의 부하 상태 확인 가능, 트래픽 변화에 따른 성능 상태 파악)

▶︎ InnoDB Buffer Pool Size

InnoDB의 버퍼 풀 크기 (MySQL의 메모리 활용 상태)

▶︎ Buffer Pool Size of Total RAM

전체 메모리 대비 버퍼풀이 차지하는 비율(%)

▶︎ MySQL Connections

현재 연결된 클라이언트 수와 최대 사용된 연결 수

▶︎ MySQL Client Thread Activity

MySQL 클라이언트의 활성 스레드 수 처리 중인 스레드 수가 급증하면 동시 처리 병목 가능성이 높아진다.

• Peak Threads Connected : 특정 시간 동안 MySQL에 동시 접속된 클라이언트 수
• Peak Threads Running : 특정 시간 동안 동시에 실행 중인 스레드 수
• Avg Threads Running : 특정 시간 동안 평균 실행 스레드 수

▶︎ MySQL Questions

시간별 전체 쿼리 수

• 그래프는 초당 질문 수(QPS)의 시계열 평균을 보여줌

▶︎ MySQL Thread Cache

MySQL의 스레드/캐시 사용량

▶︎ MySQL Slow Queries ⭐️⭐️⭐️

성능 병목 원인의 슬로우 쿼리 확인 대시보드에서는 슬로우 쿼리 수량을 보여줄 뿐, 쿼리 내용을 확인 할 순 없다.

아래는 슬로우 쿼리 로그 활성화 방법이다. 또한 임시 기록 방법이며, 만약 영구적으로 로그 기록을 활성화하려면 my.cnf 파일에 설정이 필요하다.

[활성화 현황 확인 및 저장 위치 확인]

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';

[슬로우 쿼리 로그 기록 활성화]

SET GLOBAL slow_query_log = ON;
-- 1초 이상 소요되는 쿼리문을 슬로우 쿼리로 지정
SET GLOBAL long_query_time = 1;

[슬로우 쿼리 확인] 슬로우 쿼리 로그 파일 내부를 확인하면 아래와 같이 슬로우 쿼리를 확인 할 수 있다.

[root@localhost mysql]# cat localhost-slow.log 
/usr/libexec/mysqld, Version: 8.0.41 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2025-07-27T02:49:52.528663Z
# User@Host: admin[admin] @  [172.30.1.25]  Id:  4008
# Query_time: 32.438005  Lock_time: 0.000003 Rows_sent: 0  Rows_examined: 13716675
use nba_db;
SET timestamp=1753584560;
with tmp_tb as (
    select A.game_id from game A left join line_score B
        on A.game_date = B.game_date_est
    left join team_details C
        on B.team_id_home = C.team_id
)
select *
from play_by_play A left join tmp_tb B
    on A.game_id = B.game_id;

▶︎ MySQL Aborted Connections

비정상적으로 끊긴 연결 수 (오류 및 접속 문제 판단)

▶︎ MySQL Table Locks

테이블 잠금 발생 수 (락 경합 및 병목 확인)

▶︎ I/O Activity

디스크 성능 지표 (버퍼풀 미스 및 과도한 쓰기 작업 감지)

조직에서 사용하는 데이터베이스는 사용 목적과 규모에 따라 데이터의 양과 객체 종류는 다양하다. 또한 서비스 운영에 있어 중요도가 높은 데이터베이스 객체와 민감한 정보(개인정보 및 금융 정보 등)가 저장되어 있을 수 있다.

내가 속한 조직만 봐도 200개 이상의 테이블과 100개 이상의 프로시저와 함수 등이 있다. 이 외에도 인덱스, 트리거 등 다양한 데이터베이스 객체가 존재한다.

만약 조직 내 데이터베이스를 모든 사람에게 전체 공개하게 되면 아래와 같은 크고 작은 문제가 발생할 수 있다.

• 민감 데이터 도용
• 데이터 임의 삭제 및 변경
• 조직 내 데이터 대내/외 유출

이러한 이유로 조직 내 각 사용자에게 맞는 데이터베이스 설정과 권한 정책이 필요하다.

✅ 시나리오

[운영서버를 기준으로 시나리오 설계]

1. 조직 내 30명의 직원이 데이터베이스 사용이 필요하다.
2. 30명의 직원은 각자 소속된 부서가 있다. 이러한 이유로 각 부서에 맞는 권한을 부여해야 한다.
3. 부서는 총 다섯으로 나뉘며 각 부서에는 부서장이 있다. 부서장의 권한은 DBA가 부여해 주며, 부서장은 각 부서 직원에게 권한을 임의로 부여할 수 있다.
4. 각 부서는 다음과 같다.
   • 개발 1팀 (10명)
   • 개발 2팀 (10명)
   • 데이터 분석팀 (4명)
   • 데이터 엔지니어링팀 (3명)
   • 재무/회계팀 (3명)
5. 초기에 부여된 권한 외 단기적으로 필요한 권한의 경우 일정 시간이 지나면 자동으로 권한을 회수할 수 있도록 한다.
6. 계정/권한 변경 이력은 별도의 로그파일로 기록한다.
7. 장기 휴가 및 휴직을 신청한 직원의 계정은 잠금 처리한다.
8. 신규 입사자와 퇴사자의 계정과 권한을 관리한다.
9. 민감 데이터는 특정 사용자 외 조회가 불가능하다.

📌 데이터베이스 현황 및 권한 계획

각 부서별로 사용할 데이터베이스와 테이블이 다르기 때문에 각 부서별로 어떤 데이터베이스, 데이터베이스 객체를 사용할지 조사가 필요하다.

[아래는 운영 DB 사용을 전제로 수립한 계획입니다.]

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| fn_db              |
| hr_db              |
| information_schema |
| mysql              |
| nba_db             |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+

• 개발 1팀은 sakila와 world를 사용한다. (추후 ERP 작업 때문에 hr_db 사용 예정)
  • DML의 SELECT만 사용할 수 있다. 단 부서장의 경우 INSERT와 UPDATE, DELETE를 사용할 수 있으며, EXECUTE 권한 또한 가지고 있다.
  • 추후 부여받을 hr_db에서 급여 관련 정보는 부서장만 확인할 수 있다. (monthly_salary : 월급, annual_salary : 연봉)

• 개발 2팀은 sakila와 world를 사용한다.
  • DML의 SELECT만 사용할 수 있다. 단 부서장의 경우 INSERT와 UPDATE, DELETE를 사용할 수 있으며, EXECUTE 권한 또한 가지고 있다.

• 데이터 분석팀은 hr_db와 fn_db, nba_db를 사용한다.
  • 데이터 분석팀은 DML의 SELECT만 사용할 수 있다.
  • 테이터 분석팀 전원 hr_db에서 급여 관련 정보 조회가 불가능하다. (monthly_salary : 월급, annual_salary : 연봉)

• 데이터 엔지니어링팀은 hr_db, fn_db, nba_db, sakila, world를 사용한다.
  • DML의 SELECT만 사용할 수 있다. 단 부서장의 경우 INSERT와 UPDATE, DELETE를 사용할 수 있으며, EXECUTE 권한 또한 가지고 있다.
  • 데이터 엔지니어링팀 전원 hr_db에서 급여 관련 정보 조회가 불가능하다. (monthly_salary : 월급, annual_salary : 연봉)

• 재무/회계팀은 fn_db를 사용한다.
  • 재무/회계팀은 DML의 SELECT만 사용할 수 있다.
    
    > ### 요약 |팀|부서장|일반 팀원|사용 DB|특이사항| |-|-|-|-|-| |개발 1팀|SELECT, INSERT, UPDATE, DELETE, EXECUTE|SELECT|sakila, world, hr_db|부서장만 급여 정보 확인 가능| |개발 2팀|SELECT, INSERT, UPDATE, DELETE, EXECUTE|SELECT|sakila, world|-| |데이터 분석팀|SELECT|SELECT|hr_db, fn_db, nba_db|데이터 분석팀은 1개의 계정을 공용으로 사용,
    SELECT만 사용 가능,
    급여 정보 확인 불가| |데이터 엔지니어링팀|SELECT, INSERT, UPDATE, DELETE, EXECUTE|SELECT|hr_db, fn_db, nba_db, sakila, world|급여 정보 확인 불가 |재무/회계팀|SELECT|SELECT|fn_db|재무/회계팀은 1개의 계정을 공용으로 사용,
    SELECT만 사용 가능|

✅ 계정 생성

각 부서에 대한 계정은 아래처럼 생성할 예정이다.

• 실습 편의를 위해 비밀번호는 "password"로 통일하여 진행.
• 실습 환경이 집, 카페, 스터디룸에 따라 IP가 달라지기 때문에 IP 접근 허용을 "%"으로 설정.

  -- 개발 1팀 (10명)
  CREATE USER 'dev1_admin'@'%' IDENTIFIED BY 'password';  -- 부서장
  CREATE USER 'dev1_user001'@'%' IDENTIFIED BY 'password';
  ...
  CREATE USER 'dev1_user009'@'%' IDENTIFIED BY 'password';
  >
  >
  -- 개발 2팀 (10명)
  CREATE USER 'dev2_admin'@'%' IDENTIFIED BY 'password';  -- 부서장
  CREATE USER 'dev2_user001'@'%' IDENTIFIED BY 'password';
  ...
  CREATE USER 'dev2_user009'@'%' IDENTIFIED BY 'password';
  >
  >
  -- 데이터 분석팀 (4명)
  CREATE USER 'da_team001'@'%' IDENTIFIED BY 'password';  -- 팀 공용
  >
  >
  -- 데이터 엔지니어링팀 (3명)
  CREATE USER 'de_admin'@'%' IDENTIFIED BY 'password';  -- 부서장
  CREATE USER 'de_user001'@'%' IDENTIFIED BY 'password';
  CREATE USER 'de_user002'@'%' IDENTIFIED BY 'password';
  >
  >
  -- 재무/회계팀 (3명)
  CREATE USER 'fin_team001'@'%' IDENTIFIED BY 'password';  -- 팀 공용

📌 계정 생성 실패 (번외)

계정을 생성하는 과정에서 실패하는 경우가 발생할 수 있다. 실패 원인을 보면 보통 명령문이 틀리거나 비밀번호 정책 문제일 가능성이 높다.

[비밀번호 정책 확인 및 재설정]

아래 설정을 보면 policy가 MEDIUM으로 되어 있다. 이는 대/소문자, 특수문자가 포함된 비밀번호를 사용해야 한다는 의미를 가지고 있다.

추가로 length가 8로 되어 있다. 즉, 비밀번호는 최소 8자 이상이어야 한다.

mysql> show variables like 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| validate_password.dictionary_file               |        |
| validate_password.length                        | 8      |
| validate_password.mixed_case_count              | 1      |
| validate_password.number_count                  | 1      |
| validate_password.policy                        | MEDIUM |
| validate_password.special_char_count            | 1      |
+-------------------------------------------------+--------+
8 rows in set (0.00 sec)

"password"처럼 단순한 비밀번호를 사용하려면 validate_password.policy를 'LOW'로 설정해야 한다.

mysql> set global validate_password.policy = 'LOW';
Query OK, 0 rows affected (0.00 sec)

📌 계정 생성 확인

아래 명령문를 통해 MySQL 서버에 생성된 모든 계정을 확인할 수 있다.

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | %         |
| da_team001       | %         |
| de_admin         | %         |
| de_user001       | %         |
| de_user002       | %         |
| dev1_admin       | %         |
| dev1_user001     | %         |
...
| dev2_user009     | %         |
| fin_team001      | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
30 rows in set (0.00 sec)

✅ 역할(ROLE)

모든 계정에 일일이 권한을 부여하여 관리할 수 있지만, 역할(ROLE)에 특정 권한을 부여하고 해당 역할을 사용자에게 부여하는 방식으로도 권한을 할당할 수 있다.

📌 역할 생성

[읽기 전용(SELECT) 권한 생성]

※ 민감 정보의 경우 아래 방식처럼 일일이 권한을 부여하는 것보다 VIEW를 만들어 권한을 허용하는 게 일반적이다.

-- hr_db 읽기 전용(SELECT) 권한 생성
CREATE ROLE role_hr_db_readonly;
-- hr_db의 경우 테이블이 1개이기 때문에 아래처럼 권한 설정
-- 급여 관련 컬럼(monthly_salary : 월급, annual_salary : 연봉) 조회 항목에서 제외
GRANT SELECT (no,first_name,last_name,gender,start_date,
              years,department,country,center,job_rate,
              sick_leaves,unpaid_leaves,overtime_hours)
    ON hr_db.hr_information TO role_hr_db_readonly;
>
>
-- fn_db 읽기 전용(SELECT) 권한 생성
CREATE ROLE role_fn_db_readonly;
-- fn_db에 있는 모든 테이블 조회 가능
GRANT SELECT
    ON fn_db.* TO role_fn_db_readonly;
>
>
CREATE ROLE role_nba_db_readonly;
GRANT SELECT
    ON nba_db.* TO role_nba_db_readonly;
>
>
CREATE ROLE role_sakila_readonly;
GRANT SELECT
    ON sakila.* TO role_sakila_readonly;
>
>
CREATE ROLE role_world_readonly;
GRANT SELECT
    ON world.* TO role_world_readonly;

📌 역할 부여

[읽기 전용 역할 권한 부여]

GRANT role_hr_db_readonly TO 'dev1_user001'@'%'; 
...
GRANT role_hr_db_readonly TO 'dev1_user009'@'%';
GRANT role_hr_db_readonly TO 'da_team001'@'%';
GRANT role_hr_db_readonly TO 'de_user001'@'%';
GRANT role_hr_db_readonly TO 'de_user002'@'%';

인사 정보 외 다른 DB의 역할 부여는 생략

📌 역할 활성화

역할 및 권한을 부여 받으면 해당 역할을 바로 사용할 수 없다. 별도의 역할 활성화 명령문을 통해 부여받은 역할을 활성화해야 한다.

역할(ROLE)이 아닌 권한(SELECT, INSERT 등)의 경우 별도의 활성화 과정 없이 즉시 사용 가능하다.

일일이 모든 역할을 활성화하는 과정은 시간적 소비가 크기 때문에 사용자마다 부여받은 모든 역할을 기본 역할로 설정한다.

-- 개발 1팀 역할 활성화
SET DEFAULT ROLE ALL TO 'dev1_admin';
...
SET DEFAULT ROLE ALL TO 'dev1_user009';

개발 1팀 외 다른 부서/사용자의 역할 활성화 명령문은 생략

위에서 사용한 방법은 역할을 기본 역할로 설정하는 방법이다. 기본 역할로 설정하는 방법 외 현재 세션에서만 역할을 활성화하는 방법도 있다. 하지만 이는 추천하는 방법이 아니다.

▶︎ 기본 역할로 활성화하기(추천 O)

DEFAULT 키워드를 사용하여 역할이 자동으로 활성화 되도록 설정할 수 있다. 이를 통해 DB서버가 재시작되더라도 DEFAULT로 활성화된 역할은 자동 활성화된다.

DEFAULT 키워드를 사용한 활성화는 자기 자신 또는 역할/권한 부여가 가능한 계정(root, DBA)만 활성화가 가능하다.

-- 부여받은 모든 역할을 기본 역할로 활성화
SET DEFAULT ROLE ALL TO 'dev1_user001'@'%'
>
-- 특정 역할만 기본 역할로 활성화
SET DEFAULT ROLE 'role_world_readonly' TO 'dev1_user001'@'%'
>
-- 기본 역할 없애기
SET DEFAULT ROLE NONE TO 'dev1_user001'@'%'

▶︎ 현재 세션에서만 역할 활성화하기(추천 X)

현재 DB서버 세션에서만 활성화 되도록 설정할 수 있다. DB서버가 종료될 때 활성화된 역할은 비활성화된다.

부여받은 역할을 1회성(현재 세션)으로 활성화할 수 도 있다. 현재 로그인한 사용자가 직접 부여받은 ROLE만 활성화할 수 있다.

SET ROLE 'role_world_readonly';

📌 역할 부여 및 활성 여부 확인

▶︎ 역할 생성 확인 (비공식적 방법)

MySQL에서 역할(ROLE)은 USER 테이블에 저장된다. ROLE을 특정하는 컬럼이 별도로 없기 때문에 아래와 같은 명령문으로 확인할 수 있다.

아래 쿼리는 MySQL에서 공식적으로 제공하는 조회 방식이 아니기 때문에 조회 결과가 틀릴 수 있다.

SELECT DISTINCT user AS role_name, host
FROM mysql.user
WHERE 1=1
    AND account_locked = 'Y'
    AND authentication_string = ''
;

▶︎ 역할에 부여된 권한 확인

SHOW GRANTS FOR '<역할(ROLE)명>'@'%';

▶︎ 특정 사용자와 접속자의 역할/권한 확인

-- 특정 사용자에게 부여된 역할/권한 확인
-- DBA, root 계정으로만 확인 가능하다.
SHOW GRANTS FOR '<사용자명>'@'%';
>
-- 현재 접속자에게 부여된 역할/권한 확인
SHOW GRANTS FOR CURRENT_USER();

▶︎ 역할 부여 현황 확인

아래 명령문을 통해 어떤 사용자에게 어떤 권한이 부여되었는지 확인할 수 있다. (DBA, root 계정으로만 확인할 수 있다)

-- 어떤 사용자에게 어떤 역할이 부여되었는지 확인
SELECT * FROM mysql.role_edges;

▶︎ 기본 역할 설정 여부 확인

아래 명령문를 통해 특정 사용자가 어떤 기본 역할을 설정했는지 확인 할 수 있다. (DBA, root 계정으로만 확인할 수 있다)

SELECT * FROM mysql.default_roles;

▶︎ 현재 계정에서 역할이 활성화되었는지 확인

현재 로그인한 계정에서 활성화된 역할을 조회할 수 있다.

다른 사용자의 역할 활성화는 확인 할 수 없다. (세션 격리 때문에 확인 불가)

SELECT CURRENT_ROLE();

✅ 권한 및 역할 변경 로그 기록

MySQL Enterprise 버전에는 Audit Plugin 기능이 있어 권한 부여와 테이블 접근 등의 감사를 기록할 수 있지만, MySQL Community 버전에는 Audit Plugin을 사용할 수 없기 때문에 권한에 대한 로그기록을 다른 방법으로 작성/확인해야 한다.

▶︎ Mariadb의 audit_log 활용 (추천 X)

MySQL Enterprise 버전이라면 MySQL 전용 Audit Plugin 기능을 사용하는 게 좋지만, MySQL Community 버전에서는 직접적으로 Audit Plugin 기능을 사용할 수 없다.

Mariadb의 Audit Plugin 기능 설치하여 간접적으로 사용할 순 있지만, 권장하진 않는다. 호환성 문제 및 보안 문제 때문에 적절한 방법은 아니다. (MySQL 내부에 무리하게 설치하여 사용할 때 보안 문제 및 누락, 충돌이 발생할 수 있다)

▶︎ general_log 활용

general_log을 ON으로 설정하여 모든 명령문에 대한 로그기록을 작성 및 확인할 수 있지만, 모든 SQL 명령문이 기록되기 때문에 사용 환경에 따라 성능 저하가 발생할 수 있다.

-- 설정 활성화
SET GLOBAL general_log = 'ON';
>
>
-- 로그 저장 위치 확인
SHOW VARIABLES LIKE 'general_log_file';

아래 localhost(general_log_file)로그를 확인하면 admin 계정이 dev1_user009 및 dev2_user009 계정의 역할을 변경한 내역을 확인할 수 있다.

• admin계정이 세션 ID 31로 연결(Connect)
• 세션 ID 31이 dev1_user009 계정의 전체 역할을 NONE으로 처리
• 세션 ID 31이 dev2_user009 계정의 전체 역할을 기본 역할로 활성화 처리

  [root@localhost mysql]# cat localhost.log 
  /usr/libexec/mysqld, Version: 8.0.41 (Source distribution). started with:
  Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
  Time                 Id Command    Argument
  2025-07-19T13:22:18.076585Z       31 Connect    admin@localhost on  using Socket
  2025-07-19T13:22:18.077079Z       31 Query    select @@version_comment limit 1
  2025-07-19T13:23:22.111796Z       31 Query    set default role NONE TO 'dev1_user009'
  2025-07-19T13:23:33.647180Z       31 Query    set default role ALL TO 'dev2_user009'

▶︎ binary_log 활용 (추천 X)

binary_log는 기본적으로 ON으로 설정되어 있으며, MySQL의 대부분 명령문이 기록되어 있다.

권한 관련 명령문은 기록되어 있지만, 누가 해당 권한을 수정/설정했는지는 확인할 수 없다.

사실 binary_log는 주로 복제 및 복구 용도로 사용되며, 감사(audit) 용도로는 사용되지 않는다.

# 바이너리파일 조회 예시
mysqlbinlog --base64-output=DECODE-ROWS binlog.000038 | grep -i -B 3 'SET DEFAULT ROLE ALL TO'

✅ 일정 기간 권한 부여

조직 내 부서 이동 또는 신규 프로젝트 등의 이유로 기존에 사용할 수 없던 DB, 테이블 등을 사용해야 하는 경우가 발생할 수 있다. 영구적으로 사용하는 경우라면 GRANT와 SET 명령문을 통해 기본 역할 및 권한으로 설정하면 되지만, 특정 기간동안 사용해야 하는 경우 이벤트(EVENT) 기능을 사용하여 사용 기간에 대한 타이머를 설정할 수 있다.

개발 1팀(dev1)은 ERP 개발 때문에 90일 동안 hr_db(인사 정보)를 사용한다는 전제로 아래처럼 권한/역할을 90일 동안 부여하는 작업을 수행해 볼 예정이다.

▶︎ 권한/역할 부여 및 활성화

개발 1팀에게 인사 관련 권한을 부여한다.

-- 개발 1팀 직원에게 hr_db관련 역할 부여
GRANT role_hr_db_readonly TO 'dev1_admin';
GRANT role_hr_db_readonly TO 'dev1_user001';
...
GRANT role_hr_db_readonly TO 'dev1_user009';
>
>
-- 부여받은 hr_db관련 역할을 기본 역할로 지정 및 역할 활성화
SET DEFAULT ROLE ALL TO 'dev1_admin';
SET DEFAULT ROLE ALL TO 'dev1_user001';
...
SET DEFAULT ROLE ALL TO 'dev1_user009';

▶︎ EVENT 스케줄러

90일 이후 특정 작업(event)이 이루어져야 하므로 별도의 EVENT 스케줄러를 만들어 사용하는 게 가장 이상적이라 생각된다.

물론 수기로 DBA가 90일 이후 권한을 회수하는 방법도 있다...

EVENT 스케줄러 사용과 생성은 아래와 같은 작업을 통해 사용한다.

[이벤트 스케줄러 활성화]

-- 이벤트 활성화
SET GLOBAL event_scheduler = ON;
>
-- 이벤트 활성화 확인
SHOW VARIABLES LIKE 'event_scheduler';

[이벤트 스케줄러 생성 및 실행] 아래 이벤트 스케줄러는 1회성 스케줄러이다. 만약 아래 이벤트를 다시 사용하려면 삭제 후 재생성해야 한다.

-- 기존 동명으로 작성된 이벤트가 있을 수 있기 때문에 삭제 후 생성/실행
DROP EVENT event_revoke_hr_db_dev1;

-- ON SCHEDULE : 이벤트 예약어
-- AT : 단일 시점에 한 번 실행(스케줄러 X)
-- CURRENT_TIMESTAMP : 현재 시각
-- + INTERVAL 90 DAY : 현재 시각에서 90일을 더한 시점
DELIMITER $$
    CREATE EVENT event_revoke_hr_db_dev1
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO
    BEGIN
        REVOKE role_hr_db_dept FROM 'dev1_admin'@'%';
        REVOKE role_hr_db_readonly FROM 'dev1_user001'@'%';
        ...
        REVOKE role_hr_db_readonly FROM 'dev1_user009'@'%';
    END$$
DELIMITER ;

위 코드처럼 명령문을 실행하면 90일 뒤 개발 1팀은 인사 정보(hr_db)에 대한 권한을 박탈할 수 있다.

✅ 일정 기간 계정 잠금 처리

조직 내 장기간 DB를 사용할 일이 없는 사용자의 경우 계정을 잠금 처리하여 관리/유지하는 게 보안 측면에서 좋다. 일정 기간 계정을 잠금 처리하는 방법은 EVENT 스케줄러를 통해 설정할 수 있지만, DBA가 인사 정보(휴직 및 휴가 등)를 확인하여 상황에 따라 처리하는 방법이 제일 정확할 수 있다.

가령 육아휴직을 1년 신청했지만, 회사 사정으로 9개월 만에 복직해야 하는 경우 인사 정보를 통해 수기로 잠금 처리를 해지해야 한다.

EVENT 스케줄러를 통해 설정하면 처음 1년 신청과 다르기 때문에 결국 DBA가 수기로 잠금 처리를 해지해야 한다.

잠금 처리 해지 시점은 상황에 따라 달라질 수 있지만, EVENT 스케줄러 등록을 통해 1차적으로 관리하고 상황에 맞게 DBA가 수기로 작업하는 방법이 가장 이상적이라고 생각된다.

▶︎ 계정 잠금

[계정 잠금 처리]

-- dev1_user001 사용자 계정 잠금
ALTER USER 'dev1_user001'@'%' ACCOUNT LOCK;

▶︎ 계정 잠금 해지

[계정 잠금 해지 이벤트]

-- 현재 시간을 기준으로 1년 뒤 계정 잠금 해지
DELIMITER $$
    CREATE EVENT event_unlock_365
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 YEAR
    DO
    BEGIN
        ALTER USER 'dev1_user001'@'%' ACCOUNT UNLOCK;
    END $$
DELIMITER ;

[계정 잠금 수기 해지]

-- dev1_user001 사용자 계정 잠금 해지
ALTER USER 'dev1_user001'@'%' ACCOUNT UNLOCK;

서비스 중인 DB에서 데이터가 유실되면 전체 서비스에 심각한 문제가 발생할 수 있으므로, 신속한 데이터 복구 절차가 필요하다.

데이터 복구에는 여러 가지 방법이 있지만, 이번 글에서는 Binary log 파일을 활용하여 데이터를 복구하는 과정을 다룰 예정이다.

이 방법은 Binary log 기록이 활성화되어 있다는 전제하에 수행할 수 있다.

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

🎬 데이터 복구 시나리오

1. 현재 사용 중인 DB는 매일 새벽 2시에 전체 백업을 진행하고 있다.

2. sakila 데이터베이스에 만든 테이블 tmp_order_m은 오늘(2025-07-11) 오후에 생성했으며, 다양한 작업에 사용 중이라 가정한다. (단순 조회뿐만 아니라 INSERT, UPDATE, DELETE 등의 작업이 이루어지고 있다)

3. 직원의 실수로 sakila 데이터베이스에 film, film_actor, film_category, film_text, tmp_order_m 테이블을 DROP했다. 해당 테이블은 서비스 운영에 중요한 테이블이라 가정한다.

4. DROP된 film, film_actor, film_category, film_text 테이블은 오늘 새벽 2시에 백업한 데이터로 복구할 수 있지만, 새벽 2시 이후에 작업이 이루어진 tmp_order_m 테이블 데이터는 복구할 수는 없다.

5. 새벽 2시 이후에 작업이 이루어진 데이터를 Binary log와 mysqlbinlog, mysqldump를 사용하여 복구해 볼 예정이다.

🔎 데이터 확인

[테이블 확인]

[작업 전 테이블]                         [작업 후 테이블]                          [삭제 후 테이블]
mysql> show tables;                   mysql> show tables;                   mysql> show tables;
+----------------------------+        +----------------------------+        +----------------------------+
| Tables_in_sakila           |        | Tables_in_sakila           |        | Tables_in_sakila           |
+----------------------------+        +----------------------------+        +----------------------------+
| actor                      |        | actor                      |        | actor                      |
| actor_info                 |        | actor_info                 |        | actor_info                 |
| address                    |        | address                    |        | address                    |
| category                   |        | category                   |        | category                   |
| city                       |        | city                       |        | city                       |
| country                    |        | country                    |        | country                    |
| customer                   |        | customer                   |        | customer                   |
| customer_list              |        | customer_list              |        | customer_list              |
| film                       |        | film                       |        | film_list                  |
| film_actor                 |        | film_actor                 |        | inventory                  |
| film_category              |        | film_category              |        | language                   |
| film_list                  |        | film_list                  |        | nicer_but_slower_film_list |
| film_text                  |        | film_text                  |        | payment                    |
| inventory                  |        | inventory                  |        | rental                     |
| language                   |        | language                   |        | sales_by_film_category     |
| nicer_but_slower_film_list |        | nicer_but_slower_film_list |        | sales_by_store             |
| payment                    |        | payment                    |        | staff                      |
| rental                     |        | rental                     |        | staff_list                 |
| sales_by_film_category     |        | sales_by_film_category     |        | store                      |
| sales_by_store             |        | sales_by_store             |        +----------------------------+
| staff                      |        | staff                      |        19 rows in set (0.00 sec)
| staff_list                 |        | staff_list                 |        
| store                      |        | store                      |        
+----------------------------+        | tmp_order_m                |        
23 rows in set (0.00 sec)             +----------------------------+        
                                      24 rows in set (0.00 sec)            

⏰ 작업 시간 확인

Binary log를 활용해 DROP된 데이터를 복구하려면, 마지막으로 DROP 명령어가 수행된 정확한 시점을 반드시 확인해야 한다.

아래 타임라인과 같이, 전체 백업 이후 데이터 생성·수정·삭제 시점을 기준으로 복구 범위를 결정하게 된다.

▶︎ Binary log 파일 확인

일반적으로 Binary log 파일은 /var/lib/mysql 경로에 있다.

정확한 위치를 알고 싶으면 mysql 서버에 접속하여 확인할 수 있다.

log_bin_basename을 참고하여 경로와 파일명을 확인 할 수 있다.

mysql> show variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)

Binary log 파일은 일반적인 방법으로 로그 내용을 확인할 수 없다. 이름 그대로 바이너리(2진법) 형식으로 저장되기 때문에 별도의 유틸리티 명령어인 mysqlbinlog를 사용해야 파일 내 기록을 조회할 수 있다.

일반적인 파일 확인 명령어(cat, vi 등)로 바이너리 파일을 확인할 수 없다.

mysql 바이너리 로그파일 확인 명령어인 mysqlbinlog를 사용하면 파일을 확인할 수 있다.

▶︎ DROP 시간 확인

mysqlbinlog와 grep명령어를 사용하여 로그 파일 내 특정 명령어가 언제 실행되었는지 확인할 수 있다.

[root@localhost mysql]# mysqlbinlog binlog.000025 | grep -i -B 3 'drop'
#250711 19:33:05 server id 1  end_log_pos 3766 CRC32 0x05654fdb     Query    thread_id=11    exec_time=0    error_code=0    Xid = 215
SET TIMESTAMP=1752229985/*!*/;
SET @@session.foreign_key_checks=0/*!*/;
DROP TABLE `film`,`film_actor`,`film_category`,`film_text`,`tmp_order_m` /* generated by server */

여기서 실행 시간은 아래라인을 확인하면 된다.

#250711 19:33:05 server id 1 end_log_pos 3766 CRC32 0x05654fdb Query thread_id=11 exec_time=0 error_code=0 Xid = 215

해당 행 앞 부분 250711 19:33:05이(가) 바로 DROP이 실행된 시간이다.

▶︎ 다른 쿼리문 작업 시간 확인

mysqlbinlog명령어로 다른 작업을 확인할 때 명확한 조회가 안 되는 경우가 있다. 이러한 이유로 아래 옵션과 설정을 통해 상세한 작업 시간을 확인할 수 있다.

• --base64-output=DECODE-ROWS : ROW 기반 로그를 사람이 읽을 수 있도록 디코딩

• -vv : 상세하게 표시

  mysqlbinlog --base64-output=DECODE-ROWS -vv binlog.000025 | grep -i -B 3 'insert'

🔁 삭제된 데이터 복구

▶︎ 데이터 복구 파일 준비

전체 백업을 수행한 시점과 테이블이 삭제된 시점 사이에 진행된 모든 작업을 하나의 SQL 파일로 모아두고, 이 파일을 다시 실행하면 삭제된 데이터를 복구할 수 있다.

복구에 필요한 파일은 2025-07-11 02:00 ~ 19:33 사이 데이터다.

아래 바이너리 파일을 기준으로 보면 binlog.000014 ~ binlog.000025 파일이 복구에 필요한 대상임을 확인할 수 있다.

# ls -al | grep 'binlog.0'
-rw-r-----.  1 mysql mysql       157 Jul  6 17:36 binlog.000011
-rw-r-----.  1 mysql mysql       157 Jul  9 22:44 binlog.000012
-rw-r-----.  1 mysql mysql       157 Jul 11 01:02 binlog.000013
-rw-r-----.  1 mysql mysql       157 Jul 11 16:29 binlog.000014
-rw-r-----.  1 mysql mysql       157 Jul 11 16:31 binlog.000015
-rw-r-----.  1 mysql mysql       157 Jul 11 16:37 binlog.000016
-rw-r-----.  1 mysql mysql       157 Jul 11 17:58 binlog.000017
-rw-r-----.  1 mysql mysql       157 Jul 11 18:06 binlog.000018
-rw-r-----.  1 mysql mysql       180 Jul 11 18:33 binlog.000019
-rw-r-----.  1 mysql mysql       180 Jul 11 18:41 binlog.000020
-rw-r-----.  1 mysql mysql       157 Jul 11 18:48 binlog.000021
-rw-r-----.  1 mysql mysql       180 Jul 11 18:49 binlog.000022
-rw-r-----.  1 mysql mysql       180 Jul 11 18:54 binlog.000023
-rw-r-----.  1 mysql mysql       180 Jul 11 18:58 binlog.000024
-rw-r-----.  1 mysql mysql      3766 Jul 11 19:33 binlog.000025

아래처럼 binlog.000014 파일부터 binlog.000025 파일을 하나로 묶어 하나의 복구 파일로 만든다. 여기서 binlog.000025 파일에는 주의가 필요하다.

binlog.000025 파일에는 복구하려는 테이블을 삭제한 DROP 쿼리문이 포함되어 있기 때문에 해당 쿼리문 이전까지만 복구 파일에 포함되어야 한다.

DROP이 이루어진 시점이 250711 19:33:05이기 때문에 250711 19:33:04 이전 기록만 복구 파일에 포함한다.

실제 기록에는 --stop-datetime="2025-07-11 19:33:06"에 DROP 관련 기록이 있다. 이는 DROP 이벤트 시점과 이벤트가 플러시(쓰기)된 시점이 다르기 때문이다. 이러한 이유로 DROP된 시점의 시간을 확실하게 확인하는 게 중요하다. (사실 이번 복구에서 시간 설정을 --stop-datetime="2025-07-11 19:33:05"으로 설정해도 복구가 된다.)

# mysqlbinlog binlog.000014 > /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000015 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000016 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000017 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000018 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000019 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000020 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000021 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000022 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000023 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000024 >> /backup/restore/restore_20250711.sql
# mysqlbinlog binlog.000025 \
--start-datetime="2025-07-11 02:00:00" \
--stop-datetime="2025-07-11 19:33:04" \
>> /backup/restore/restore_20250711.sql

▶︎ 데이터 복구

가장 먼저 전체 백업 데이터를 기준으로 film, film_actor, film_category, film_text 테이블을 복구한다.

[전체 백업 복구]

# mysql -u admin -p < /backup/mysqldump_20250711/database_full.sql

이후 전체 백업으로 복구 불가능한 데이터를 복구한다.

[Binary log 기반 복구]

# mysql -u admin -p sakila < /backup/restore/restore_20250711.sql 

MySQL에서 사용되는 기본적인 백업 유틸리티는 mysqldump이다. 하지만 mysqldump는 대용량 데이터 백업/복구에 있어 느린 속도가 발목을 잡는다.

이러한 mysqldump의 단점을 보완한 유틸리티가 XtraBackup이다.

✅ XtraBackup

XtraBackup은 Percona에서 개발된 오픈소스로 백업 도구이다.

mysqldump는 테이블 생성, 데이터 쿼리에 대한 SQL 생성문을 갖는 논리적 백업이라면 XtraBackup은 엔진 데이터를 그대로 복사하는 물리적 백업 방식이다.

• mysqldump는 create, insert 등의 SQL 쿼리문을 사용한 논리적 백업 방식이다.
• XtraBackup은 서버의 데이터를 그대로 복사해 오는 물리적 백업 방식이다.

이러한 XtraBackup은 MySQL 엔터프라이즈 라이센스에 포함된 백업 도구의 기능을 모두 제공할 뿐만 아니라 더 유용한 기능들도 제공한다.

XtraBackup의 백업 방식은 크게 전체 백업, 증분 백업, 개별(DB, TABLE) 백업, 압축(qpress) 백업, Encrypted 백업이 있다. 또한 stream을 지원하기 때문에 파이프(|)를 통하여 다른 프로그램의 표준 입력으로 리다이렉션이 가능하다. 이를 통해 상황에 맞는 복잡한 백업/복구 로직을 작성할 수 있다.

▶︎ XtraBackup 설치

MySQL 서버(CentOS 10, MySQL 8.4.2 CentOS 9, MySQL 8.0.41)에 맞는 XtraBackup 8.0 버전을 설치한다.

2025.07.05 기준 CentOS 10 버전에 맞는 XtraBackup 버전이 없어 CentOS 9를 설치 사용, 이에 맞게 임시로 MySQL 8.0.41 사용

• MySQL 5.x 이하일 때는 XtraBackup 2.x을 설치 사용 (XtraBackup 2.4 는 단종...)

• MySQL 8.0.x 이상일 때는 XtraBackup 8.0을 설치 사용

  • MySQL 8.4.x 이상인 경우 XtraBackup 8.4를 설치하여 사용하면 된다.

[XtraBackup 설치]

# Percona yum 저장소 설치
$ sudo dnf install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 저장소 활성화
$ sudo percona-release enable pxb-80

# Percona XtraBackup 설치
$ sudo dnf install percona-xtrabackup-80

# zstd 압축 알고리즘 설치 (선택 사항)
$ sudo dnf install zstd

[XtraBackup 설치 확인]

# 아래 명령어가 실패한 경우 설치가 완료되지 않았거나 잘못되었다는 뜻
$ xtrabackup --version

▶︎ XtraBackup 바이너리 유틸

Percona XtraBackup 8.0 기준으로 아래와 같은 바이너리 유틸리티를 제공한다.

• xtrabackup : 데이터베이스 인스턴스를 백업하는 기능
• xbcrypt : 백업 파일을 암호화하고 복호화하는 유틸리티
• xbstream : xbstream 형식으로 파일을 스트리밍하고 추출할 수 있는 유틸리티
• xbcloud : xbstream 아카이브의 전체 또는 일부를 클라우드에서 다운로드하고 업로드하는 데 사용되는 유틸리티

XtraBackup는 다양한 유틸을 제공하지만 가장 많이 사용하는 xtrabackup 유틸을 대표로 사용해볼 예정이다.

[xtrabackup 주요 옵션]

• --backup : 특정 디렉터리에 데이터베이스 백업
• --prepare : --backup으로 생성된 백업 데이터를 복구하는 모드(복구 모드로 전환)
• --copy-back : 백업된 데이터를 실제 디렉터리에 복구
  • 복구하려는 디렉터리에 데이터가 있는경우 운영 체제 오류 17과 함께 실패한다. 때문에 디렉터리를 비우고 실행하는 것을 권장
• --move-back : 백업된 데이터를 원래 위치로 복구+이동 (백업 데이터는 삭제)
  • 복구하려는 디렉터리에 데이터가 있는경우 운영 체제 오류 17과 함께 실패한다. 때문에 디렉터리를 비우고 실행하는 것을 권장
• --stats : 지정된 데이터 파일을 스캔하고 인덱스 통계 출력
• --target-dir : 백업 데이터를 저장할 디렉터리 지정 (디렉터리가 없는 경우 xtrabackup이 디렉터리를 생성)
• --no-lock : 백업하는 동안 테이블 락 없이 작업 진행 (InnoDB 에서만 사용 가능)

▶︎ xtrabackup을 통한 통 백업/복구

[데이터베이스 통 백업] 백업하는 동안 table lock 없이 백업을 진행하려면 --no-lock 옵션을 추가해야 한다. 단 이는 InnoDB에서만 사용 가능하다.

# 백업 대상 : /etc/my.cnf
# 백업 데이터 저장소 : /backup/xtrabackup_20250702
xtrabackup --backup \
--defaults-file=/etc/my.cnf \
--no-lock \
--target-dir=/backup/xtrabackup_20250702 \
--user=<DB 사용자명> \
--password=<DB 사용자 비밀번호> \

[데이터베이스 통 복구] 데이터베이스를 통 복구할 때는 데이터베이스에 데이터가 없어야 하며, MySQL 서비스를 종료해야 한다. (만약 서버에 데이터가 남아 있는 경우 에러가 발생하면서 복구에 실패한다)

# 백업 준비(복구 모드)
xtrabackup --prepare \
--target-dir=/backup/xtrabackup_20250702

# MySQL 서버 중지
systemctl stop mysqld

# MySQL 서버 데이터 이름변경을 통한 백업(혹시 모를 장애 대비)
mv /var/lib/mysql /var/lib/mysql_bak_$(date +%Y%m%d_%H%M)

# 데이터 복구
xtrabackup --copy-back \
--target-dir=/backup/xtrabackup_20250702

# 소유권 수정
chown -R mysql:mysql /var/lib/mysql

# MySQL 서버 시작
systemctl start mysqld

✅ XtraBackup vs mysqldump

sys, performance_schema, information_schema 제외 대략 2.45 GB의 데이터를 백업/복구하는 데 있어 아래와 같은 속도 차이가 있었다.

• mysqldump는 sys, performance_schema, information_schema 데이터베이스의 정보만을 백업한다. 즉, 완전한 백업이 아니다.

▶︎ mysqldump VS XtraBackup 백업 속도 측정

[mysqldump 백업]

mysqldump 명령어로 백업을 진행한 경우 총 33.810초가 소요되었음을 확인 할 수 있었다.

▶︎ mysqldump VS XtraBackup 복구 속도 측정

[mysqldump 복구]

mysqldump명령어로 복구을 진행한 경우 282.203(약 4분 42초)초가 소요되었음을 확인 할 수 있었다.

▶︎ mysqldump VS XtraBackup 비교표

MySQL 서버에서 sys, performance_schema, information_schema 데이터베이스를 제외한 약 2.45 GB의 데이터를 기준으로 아래와 같은 결과를 확인할 수 있었다.

백업의 경우 XtraBackup 방식이 약 3.75배 더 빠르며, 복구의 경우 XtraBackup 방식이 약 41.9배 더 빨랐다.

🔗 참고 사이트

🏍️ 우아한기술블로그_장애와 관련된 XtraBackup 적용기

📋 XtraBackup 공식 문서

🏀 케글_NBA 대용량 데이터

데이터를 주기적으로 백업하는 일은 DBA에게 있어 중요한 업무 루틴 중 하나라고 생각한다. (언제, 어떤 데이터의 복구 작업이 필요할지 알 수 없으므로 주기적으로 데이터를 백업할 필요가 있다)

이처럼 주기적인 데이터 백업을 수행할 때마다 DB 서버에 접속하여 백업 명령어를 일일이 수기로 작성하여 데이터를 백업해야 한다.

하지만 위 방법은 주기적으로 발생하는 반복 작업이기 때문에, 백업 스크립트를 작성한 뒤 스케줄러로 등록하는 방법이 가장 효율적인 방법이라 생각한다.

📋 계획

아래 두 과정을 거치면 자동 백업을 간단하게 적용/실행할 수 있다.

1. 백업 디렉터리 생성 1-1. 데이터 백업 디렉터리 1-2. 데이터 백업 로그 디렉터리 및 로그 파일

2. 백업 스크립트 작성

3. 리눅스 스케줄러 등록

🧑🏻‍💻 백업 디렉터리 생성

# 데이터 백업 디렉터리 생성
mkdir -p /backup/mysql/daily_full_backup
>
# 로그 디렉터리 생성
mkdir -p /backup/mysql/log
# 로그 파일 생성
touch /backup/mysql/log/backup.log

🧑🏻‍💻 백업 스크립트 작성

스크립트 파일의 경로는 상황에 따라(회사의 정책 및 규칙에 따라) 다르게 지정할 수 있다.

# 스크립트 생성 및 편집
vi /usr/local/bin/mysql_daily_backup.sh

[스크립트 내용]

• 2>> ${LOG_FILE} : 표준 에러 발생 시 로그파일(backup.log)끝에 에러 추가
• -mtime +7 : 수정된 지 7일 보다 오래된 파일
• -exec rm -f {} \; : rm명령어 실행
  • { } : find가 찾은 파일명
  • \; : -exec 옵션의 끝을 의미
  • -f : 강제 삭제

    #!/bin/bash
    #-------------------------------------------------------------------------------------------------------------------------------------------
    # 파 일 명 : /usr/local/bin/mysql_daily_backup.sh
    # 설   명 : 매일 모든 데이터베이스 백업, 7일 전 백업 파일은 자동 삭제
    # 작 성 자 : 김철수
    # 작 성 일 : 2025.07.04
    # 수 정 자 : -
    # 수 정 일 : -
    # 수 정 사 유: -
    #-------------------------------------------------------------------------------------------------------------------------------------------
    >
    #-------------------------------------------------------------------------------------------------------------------------------------------
    # 백업 변수 선언 START
    #-------------------------------------------------------------------------------------------------------------------------------------------
    >
    # MySQL 계정 정보
    MYSQL_USER="@@@@"
    MYSQL_PWD="@@@@"
    >
    # 날짜 변수
    TODAY=$(date +"%Y%m%d")
    >
    # 백업 위치
    BACKUP_DIR="/backup/mysql/daily_full_backup"
    >
    # 백업 로그 위치
    LOG_FILE="/backup/mysql/log/backup.log"
    >
    # 백업 파일명
    BACKUP_FILE="${BACKUP_DIR}/${TODAY}_full_backup.sql"
    >
    #-------------------------------------------------------------------------------------------------------------------------------------------
    # 백업 변수 선언 END
    #-------------------------------------------------------------------------------------------------------------------------------------------
    >
    >
    #-------------------------------------------------------------------------------------------------------------------------------------------
    # 백업 로직 START
    #-------------------------------------------------------------------------------------------------------------------------------------------
    >
    # mysqldump 백업 실행
    mysqldump -u ${MYSQL_USER} -p${MYSQL_PWD} --all-databases --routines --events --single-transaction --quick > ${BACKUP_FILE} 2>> ${LOG_FILE}
    >
    # 백업 성공 여부 기록
    if [ $? -eq 0 ]; then
    echo "$(date +"%Y-%m-%d %H:%M:%S") : Backup Success" >> ${LOG_FILE}
    else
    echo "$(date +"%Y-%m-%d %H:%M:%S") : Backup Failed" >> ${LOG_FILE}
    fi
    >
    # 7일 전 백업 파일 자동 삭제
    find ${BACKUP_DIR} -type f -name "*.sql" -mtime +7 -exec rm -f {} \;
    >
    #-------------------------------------------------------------------------------------------------------------------------------------------
    # 백업 로직 END
    #-------------------------------------------------------------------------------------------------------------------------------------------

👩🏻‍💻 백업 스크립트 스케줄러 등록

# 리눅스 스케줄러 등록 명령어
crontab -e

위 명령어 실행 후 스케줄러 등록 화면으로 진입할 수 있으며 상황에 맞는 스케줄러를 등록하면 된다.

# 매일 새벽 2시에 스케줄러 실행
0 2 * * * /usr/local/bin/mysql_daily_backup.sh

스케줄러가 정상적으로 등록 되었는지 확인이 필요할 때는 아래 명령어를 통해 확인한다.

crontab -l

🔎 결과

리눅스 crontab 스케줄러가 정상적으로 실행될 경우 로그 파일(/backup/mysql/log/backup.log)에 성공(Success) 또는 실패(Failed) 내용을 추가하여 기록된다.

문제 발생 시 해당 로그 기록을 확인하여 적절한 추가 작업을 진행하면 된다.

Users
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+

user_id는 이 테이블의 기본 키(고유 값을 갖는 열)입니다. 이 테이블에는 웹사이트에 가입한 사용자 정보가 포함되어 있습니다. 일부 이메일이 유효하지 않습니다.

유효한 이메일을 가진 사용자를 찾는 솔루션을 작성하세요.

유효한 이메일에는 접두사 이름과 도메인 where가 있습니다:

• 접두사 이름은 문자(대/소문자), 숫자, 밑줄 ‘_’, 마침표 ‘.’ 및/또는 대시 '-'를 포함할 수 있는 문자열입니다.
• 접두사 이름은 반드시 문자로 시작해야 합니다.
• 도메인은 '@leetcode.com'입니다.

결과 테이블을 임의의 순서로 반환합니다.

자세한 문제 내용은 사이트 참고...

❗️ 문제 풀이

binary : 비교를 바이트 단위로 비교 MySQL에서 대소문자 비교를 할 때는 binary를 사용해야 한다.

SELECT
    user_id,
    name,
    mail
FROM Users
WHERE 1=1
    and binary right(mail, 13) = '@leetcode.com'
    and mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com$'
;

🔗 Reference

https://leetcode.com/problems/find-users-with-valid-e-mails/

Products
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
+------------------+---------+

product_id는 이 테이블의 기본 키(고유 값을 갖는 열)입니다. 이 테이블에는 회사의 제품에 대한 데이터가 포함되어 있습니다.

Orders
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
+---------------+---------+

이 테이블에는 중복 행이 있을 수 있습니다. product_id는 Products 테이블의 외래 키(참조 열)입니다. unit은 order_date에서 주문한 제품 수입니다.

2020년 2월에 100개 이상 주문된 제품의 이름과 그 수량을 구하는 솔루션을 작성합니다.

결과 테이블을 임의의 순서로 반환합니다.

자세한 문제 내용은 사이트 참고...

❗️ 문제 풀이

select
    A.product_name
    , sum(B.unit) as unit
from Products A join Orders B
    on A.product_id = B.product_id
where 1=1
    and date_format(order_date, '%Y%m') = '202002'
group by A.product_name
having sum(B.unit) >= 100
;

🔗 Reference

https://leetcode.com/problems/list-the-products-ordered-in-a-period/description/?envType=study-plan-v2&envId=top-sql-50