dev.syw

복제 토폴로지와 백업·모니터링으로 고가용 운영 환경을 구축한다.

운영·복제·백업과 모니터링

코드가 아무리 잘 최적화되어 있어도, 데이터베이스가 단일 장애점(Single Point of Failure)으로 운영된다면 언제든 서비스 전체가 중단될 수 있습니다. 운영 환경에서 MySQL을 안정적으로 유지하려면 복제(Replication)를 통한 고가용성 구성, 체계적인 백업 전략, 그리고 실시간 모니터링이 반드시 갖춰져야 합니다.

앞선 강들에서 인덱스·쿼리 최적화·트랜잭션·스키마 설계까지 다뤘다면, 이번 6강에서는 그 모든 노력을 실제 운영 환경에서 지속할 수 있도록 하는 인프라 레이어에 집중합니다. 장애가 발생했을 때 얼마나 빠르게 복구하느냐가 서비스 품질을 결정짓습니다.

학습 목표

  • **바이너리 로그(Binary Log)**의 구조와 비동기/반동기 복제의 차이를 설명할 수 있다.
  • GTID(Global Transaction Identifier) 기반 페일오버를 수행하고 복제 지연을 진단할 수 있다.
  • 논리 백업과 물리 백업의 장단점을 구분하고, 적절한 도구를 선택할 수 있다.
  • **Point-in-Time Recovery(PITR)**로 특정 시점까지 데이터를 복구할 수 있다.
  • Performance Schema와 주요 상태 변수로 서버를 모니터링하고 커넥션 풀을 튜닝할 수 있다.

바이너리 로그와 복제 동작 원리

바이너리 로그(Binary Log)란

바이너리 로그는 MySQL이 데이터를 변경하는 모든 이벤트를 기록하는 파일입니다. 복제와 Point-in-Time Recovery 모두 이 파일에 의존합니다. 바이너리 로그에는 세 가지 포맷이 있습니다.

포맷기록 방식장점단점
STATEMENTSQL 문 자체용량 작음비결정적 함수(NOW(), UUID()) 복제 불일치 가능
ROW변경된 행 데이터정확한 복제 보장대량 UPDATE 시 용량 폭증
MIXED자동 선택절충안간헐적 예측 어려움

실무에서는 ROW 포맷을 권장합니다. 특히 binlog_row_image=MINIMAL로 설정하면 변경된 컬럼만 기록해 용량을 절약할 수 있습니다.

-- 현재 바이너리 로그 포맷 확인
SHOW VARIABLES LIKE 'binlog_format';

-- 바이너리 로그 목록 확인
SHOW BINARY LOGS;

-- 특정 바이너리 로그 내용 확인
SHOW BINLOG EVENTS IN 'mysql-bin.000003' LIMIT 20;

# 셸에서 바이너리 로그 내용 확인 (가독성 있는 출력)
mysqlbinlog --base64-output=DECODE-ROWS --verbose \
  /var/lib/mysql/mysql-bin.000003 | head -100

비동기 복제 vs 반동기 복제

MySQL의 기본 복제 방식은 비동기(Asynchronous) 입니다. 소스(Primary)는 트랜잭션을 커밋한 뒤 레플리카(Replica)가 바이너리 로그를 수신했는지 확인하지 않고 즉시 클라이언트에 응답합니다.

[클라이언트]
    │ COMMIT
    ▼
[Primary]  ──바이너리 로그 전송──▶  [Replica]
    │                                   │
    └─▶ 즉시 응답 (OK)          (나중에 적용)

반동기(Semi-Synchronous) 복제는 최소 하나의 레플리카가 바이너리 로그를 수신(ACK)했을 때 클라이언트에 응답합니다. 데이터 유실 가능성이 크게 줄어들지만, 네트워크 지연이 쓰기 응답 시간에 직접 영향을 줍니다.

-- 소스에서 반동기 플러그인 활성화
INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = ON;
-- ACK를 기다리는 타임아웃(ms). 초과 시 비동기로 자동 전환
SET GLOBAL rpl_semi_sync_source_timeout = 10000;

-- 레플리카에서 반동기 플러그인 활성화
INSTALL PLUGIN rpl_semi_sync_replica SONAME 'semisync_replica.so';
SET GLOBAL rpl_semi_sync_replica_enabled = ON;

💡 TIP MySQL 8.0.26부터 master/slave 용어가 source/replica로 변경되었습니다. 구버전에서는 rpl_semi_sync_master_* / rpl_semi_sync_slave_* 변수명을 사용하세요.

복제 지연 진단과 GTID 기반 페일오버

복제 지연(Replication Lag) 진단

복제 지연은 소스에서 레플리카로 변경 사항이 반영되는 데 걸리는 시간입니다. 지연이 커지면 읽기 분산을 위한 레플리카에서 오래된 데이터를 읽거나, 페일오버 시 데이터 유실이 발생할 수 있습니다.

-- 레플리카에서 복제 상태 확인 (MySQL 8.0.22+)
SHOW REPLICA STATUS\G

-- 핵심 필드
-- Seconds_Behind_Source: 레플리카가 소스보다 몇 초 뒤처졌는지
-- Replica_IO_Running: 바이너리 로그 수신 스레드 상태 (Yes/No)
-- Replica_SQL_Running: SQL 실행 스레드 상태 (Yes/No)
-- Last_SQL_Error: 적용 중 발생한 마지막 오류

복제 지연의 주요 원인과 해결책은 다음과 같습니다.

원인증상해결책
단일 스레드 SQL 스레드Seconds_Behind_Source 지속 증가멀티 스레드 복제(replica_parallel_workers) 활성화
대용량 트랜잭션특정 시간대에 급격한 지연트랜잭션 분할, binlog_transaction_dependency_tracking=WRITESET
I/O 병목레플리카 디스크 사용률 높음레플리카 스토리지 업그레이드, 그룹 커밋 활용(binlog_group_commit_sync_delay) ※ sync_binlog=0은 크래시 시 트랜잭션 유실 위험이 있는 내구성 희생 설정이므로 데이터 유실을 감수할 수 있는 레플리카에 한해 신중히 사용
네트워크 대역폭 부족Slave_IO_Running 느림바이너리 로그 압축(binlog_transaction_compression=ON)
-- 멀티 스레드 복제 활성화 (레플리카 설정, my.cnf에도 반영)
SET GLOBAL replica_parallel_workers = 4;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
-- WRITESET 방식은 더 공격적인 병렬 처리 가능
SET GLOBAL binlog_transaction_dependency_tracking = 'WRITESET';

GTID 기반 복제와 페일오버

**GTID(Global Transaction Identifier)**는 소스에서 실행된 각 트랜잭션에 전역적으로 고유한 ID를 부여합니다. 형식은 source_uuid:transaction_id입니다.

GTID를 사용하면 페일오버 시 레플리카가 어느 트랜잭션까지 처리했는지 자동으로 파악할 수 있어, 수동으로 바이너리 로그 파일 이름과 위치를 지정할 필요가 없습니다.

# my.cnf (소스와 레플리카 모두 동일하게 설정)
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW
server_id = 1          # 각 서버마다 고유한 값

-- GTID 기반으로 복제 시작 (레플리카에서 실행)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.1.100',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'replicator',
  SOURCE_PASSWORD = 'StrongPass!',
  SOURCE_AUTO_POSITION = 1;  -- GTID 자동 포지셔닝 활성화

START REPLICA;

-- 현재 실행된 GTID 집합 확인
SELECT @@GLOBAL.gtid_executed;
-- 바이너리 로그에서 이미 삭제(purge)된 GTID 집합 확인 (gtid_executed의 부분집합)
SELECT @@GLOBAL.gtid_purged;
-- 수신했지만 아직 적용 안 된 GTID(릴레이 로그의 수신 집합) 확인
SELECT Received_transaction_set FROM performance_schema.replication_connection_status;

페일오버 절차 (소스 장애 시)

# 1. 가장 복제 지연이 적은 레플리카 선택
# 각 레플리카에서 실행
mysql -e "SHOW REPLICA STATUS\G" | grep Seconds_Behind_Source

# 2. 새 소스 후보 레플리카에서 복제 중지 및 쓰기 허용
mysql -e "STOP REPLICA;"
mysql -e "SET GLOBAL read_only = OFF;"

# 3. 나머지 레플리카들이 새 소스를 바라보도록 변경
mysql -e "
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '192.168.1.101',
  SOURCE_AUTO_POSITION = 1;
START REPLICA;
"

⚠️ 주의 프로덕션에서는 수동 페일오버 대신 MySQL InnoDB Cluster 또는 Orchestrator 같은 고가용성 솔루션을 사용하는 것을 권장합니다. 수동 페일오버는 실수로 스플릿 브레인(Split-Brain)을 유발할 수 있습니다.

논리 백업 vs 물리 백업

논리 백업: mysqldump와 mydumper

논리 백업은 데이터를 SQL 문으로 추출합니다. 복원 시 해당 SQL을 재실행하므로, 다른 MySQL 버전이나 다른 스토리지 엔진으로도 이전이 가능합니다.

mysqldump (MySQL 기본 제공)

# 단일 데이터베이스 백업
mysqldump -h 127.0.0.1 -u root -p \
  --single-transaction \        # InnoDB 일관성 스냅샷 (테이블 잠금 없음)
  --source-data=2 \             # 바이너리 로그 위치를 주석으로 기록 (8.0.26부터 --master-data 대체)
  --routines \                  # 저장 프로시저/함수 포함
  --triggers \                  # 트리거 포함
  --events \                    # 스케줄 이벤트 포함
  mydb > /backup/mydb_$(date +%Y%m%d).sql

# 복원
mysql -h 127.0.0.1 -u root -p mydb < /backup/mydb_20260608.sql

💡 TIP --master-data는 MySQL 8.0.26부터 deprecated되어 --source-data로 대체되었습니다. 구버전에서는 여전히 --master-data를 사용해야 하지만, 8.0.26 이상에서는 --source-data=2를 사용하세요. 두 옵션 모두 =2를 지정하면 바이너리 로그 위치를 주석(-- CHANGE REPLICATION SOURCE TO ...)으로 기록합니다.

mydumper (멀티 스레드 논리 백업 도구)

# 병렬 4 스레드로 백업 (대용량 DB에서 훨씬 빠름)
mydumper \
  --host=127.0.0.1 \
  --user=root \
  --password=StrongPass! \
  --database=mydb \
  --threads=4 \
  --compress \
  --outputdir=/backup/mydumper_$(date +%Y%m%d)

# 복원 (myloader)
myloader \
  --host=127.0.0.1 \
  --user=root \
  --password=StrongPass! \
  --database=mydb \
  --threads=4 \
  --directory=/backup/mydumper_20260608

물리 백업: XtraBackup

물리 백업은 InnoDB 데이터 파일을 직접 복사합니다. 대용량 DB에서 백업과 복원 속도가 논리 백업보다 훨씬 빠릅니다. Percona XtraBackup은 온라인 물리 백업을 지원하는 대표 도구입니다.

# 전체 백업 (Full Backup)
xtrabackup --backup \
  --user=root \
  --password=StrongPass! \
  --target-dir=/backup/full_$(date +%Y%m%d)

# 백업 적용 단계 (복원 전 반드시 필요)
xtrabackup --prepare \
  --target-dir=/backup/full_20260608

# 복원 (mysqld 중지 후 실행)
systemctl stop mysqld
xtrabackup --copy-back \
  --target-dir=/backup/full_20260608
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

증분 백업(Incremental Backup) — 전체 백업 이후 변경된 부분만 백업합니다.

# 1일차: 전체 백업
xtrabackup --backup \
  --target-dir=/backup/full_day1

# 2일차: 1일차 기준 증분 백업
xtrabackup --backup \
  --target-dir=/backup/inc_day2 \
  --incremental-basedir=/backup/full_day1

# 복원 시: 전체 백업 prepare → 증분 적용 → 최종 prepare
xtrabackup --prepare --apply-log-only --target-dir=/backup/full_day1
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full_day1 \
  --incremental-dir=/backup/inc_day2
xtrabackup --prepare --target-dir=/backup/full_day1
비교 항목mysqldumpmydumperXtraBackup
백업 속도느림중간 (병렬)빠름
복원 속도느림중간 (병렬)빠름
포터빌리티높음높음낮음 (동일 버전 권장)
증분 백업불가불가가능
용량큼 (텍스트)중간 (압축)작음 (압축)
운영 중 사용가능가능가능

Point-in-Time Recovery로 특정 시점 복구

PITR은 백업 시점 이후 발생한 바이너리 로그를 재적용해, 특정 시점까지의 데이터를 복구하는 기법입니다. 실수로 DELETE 또는 DROP TABLE을 실행했을 때 매우 유용합니다.

시나리오: 2026-06-08 14:30:00에 DELETE FROM orders WHERE id > 0을 실수로 실행했을 때, 14:29:59까지 데이터 복구.

# 1단계: 가장 최근 전체 백업으로 DB 복원
# (mysqldump 방식 예시)
mysql -u root -p < /backup/mydb_20260608_00.sql

# 2단계: 백업 이후의 바이너리 로그 목록 확인
mysqlbinlog --no-defaults \
  --start-datetime="2026-06-08 00:00:00" \
  --stop-datetime="2026-06-08 14:29:59" \
  /var/lib/mysql/mysql-bin.000010 \
  /var/lib/mysql/mysql-bin.000011 > /tmp/recovery.sql

# 3단계: 추출한 이벤트를 DB에 적용
mysql -u root -p mydb < /tmp/recovery.sql

-- GTID를 이용한 정밀 복구: 특정 트랜잭션 직전까지만 적용
-- 먼저 문제가 된 GTID 확인
SHOW BINLOG EVENTS IN 'mysql-bin.000011'\G
-- 예: 문제 트랜잭션 GTID = 'abc123:500'

# GTID 방식으로 499번 트랜잭션까지만 적용
mysqlbinlog --no-defaults \
  --include-gtids="abc123:1-499" \
  /var/lib/mysql/mysql-bin.000011 | mysql -u root -p mydb

⚠️ 주의 PITR을 위해서는 백업 시 --source-data=2 (mysqldump, 8.0.26 미만은 --master-data=2) 또는 XtraBackup의 xtrabackup_binlog_info 파일에 기록된 바이너리 로그 위치가 필요합니다. 백업 전략 수립 시 이 정보를 반드시 보존하세요.

💡 TIP mysqlbinlog에 여러 바이너리 로그 파일을 동시에 전달하면, 파일 경계를 넘는 트랜잭션도 올바르게 처리됩니다. 파일을 하나씩 따로 적용하면 분리된 트랜잭션 때문에 오류가 발생할 수 있습니다.

Performance Schema와 주요 상태 변수로 모니터링

핵심 상태 변수

-- 커넥션 관련 상태
SHOW GLOBAL STATUS LIKE 'Threads_%';
-- Threads_connected: 현재 연결된 스레드 수
-- Threads_running:   쿼리를 실행 중인 스레드 수 (보통 낮아야 정상)
-- Threads_created:   서버 시작 후 생성된 누적 스레드 수

-- 캐시 효율성
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
-- Innodb_buffer_pool_reads:      디스크에서 읽은 횟수
-- Innodb_buffer_pool_read_requests: 버퍼 풀에서 읽은 횟수
-- 히트율 = 1 - (reads / read_requests) → 0.99 이상이 이상적

-- 쿼리 캐시(8.0 제거됨) 대신 쿼리 통계 확인
SHOW GLOBAL STATUS LIKE 'Com_%';
-- Com_select, Com_insert, Com_update, Com_delete 비율로 워크로드 파악

-- InnoDB 잠금
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits:   잠금 대기 누적 횟수
-- Innodb_row_lock_time_avg: 평균 잠금 대기 시간(ms)

Performance Schema 활용

Performance Schema는 MySQL 내부에서 일어나는 이벤트를 실시간으로 수집하는 진단 도구입니다.

-- 가장 많이 실행되고 총 시간이 긴 쿼리 TOP 10
SELECT
  DIGEST_TEXT,
  COUNT_STAR          AS exec_count,
  SUM_TIMER_WAIT / 1e12 AS total_sec,
  AVG_TIMER_WAIT / 1e12 AS avg_sec,
  SUM_ROWS_EXAMINED   AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 현재 실행 중인 쿼리와 대기 이벤트 확인
SELECT
  t.PROCESSLIST_ID,
  t.PROCESSLIST_USER,
  t.PROCESSLIST_DB,
  t.PROCESSLIST_TIME,
  ews.EVENT_NAME AS wait_event,
  t.PROCESSLIST_INFO AS query
FROM performance_schema.threads t
JOIN performance_schema.events_waits_current ews
  ON t.THREAD_ID = ews.THREAD_ID
WHERE t.PROCESSLIST_COMMAND != 'Sleep'
ORDER BY t.PROCESSLIST_TIME DESC;

-- 테이블별 I/O 통계 (핫스팟 파악)
SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  SUM_TIMER_READ  / 1e12 AS read_sec,
  SUM_TIMER_WRITE / 1e12 AS write_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- sys 스키마를 활용하면 더 간편하게 확인 가능
-- 인덱스를 사용하지 않는 테이블 스캔 TOP 10
SELECT * FROM sys.schema_tables_with_full_table_scans
LIMIT 10;

-- 사용되지 않는 인덱스 목록
SELECT * FROM sys.schema_unused_indexes;

-- 중복되거나 불필요한 인덱스
SELECT * FROM sys.schema_redundant_indexes;

커넥션 풀·max_connections·서버 변수 튜닝

max_connections 설정

-- 현재 max_connections 확인
SHOW VARIABLES LIKE 'max_connections';

-- 현재까지 최대 동시 접속 수 (서버 재시작 이후 누적)
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

max_connections를 너무 크게 설정하면 메모리 소비가 폭증합니다. 각 커넥션은 스레드 스택(thread_stack), 정렬 버퍼(sort_buffer_size), 조인 버퍼(join_buffer_size) 등을 개별적으로 가집니다.

필요 메모리(대략) ≈ innodb_buffer_pool_size
                   + max_connections × (sort_buffer_size + join_buffer_size + ...)

# my.cnf 권장 설정 예시 (16GB RAM, OLTP 워크로드)
[mysqld]
max_connections        = 500
thread_cache_size      = 50       # 스레드 재사용으로 생성 비용 절감
wait_timeout           = 600      # 유휴 커넥션 600초 후 자동 종료
interactive_timeout    = 600

# 버퍼 크기 (너무 크게 설정 금지 — 커넥션당 할당됨)
sort_buffer_size       = 256K
join_buffer_size       = 256K
read_buffer_size       = 128K

# InnoDB 핵심 설정
innodb_buffer_pool_size        = 10G    # RAM의 60~70%
innodb_buffer_pool_instances   = 8      # 경험적 가이드: 각 인스턴스가 1GB 이상이 되도록 설정 (공식 규칙 아님)
innodb_log_file_size           = 1G
innodb_flush_log_at_trx_commit = 1      # 내구성 최우선 (ACID 보장)
sync_binlog                    = 1      # 바이너리 로그 동기화 (데이터 안전)

# 복제 설정
binlog_format                  = ROW
binlog_row_image               = MINIMAL
binlog_expire_logs_seconds     = 604800 # 오래된 바이너리 로그 자동 삭제(7일). expire_logs_days는 8.0에서 deprecated

애플리케이션 레벨 커넥션 풀 연동

-- 커넥션 풀 상태 모니터링 쿼리
SELECT
  user,
  host,
  db,
  command,
  time,
  state
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

-- Sleep 커넥션이 비정상적으로 많을 경우 (커넥션 풀 설정 문제)
SELECT command, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY command;

💡 TIP HikariCP(Java), pg-pool, Sequelize 등의 커넥션 풀에서 maximumPoolSize를 MySQL max_connections보다 반드시 작게 설정하세요. 풀 크기 × 인스턴스 수 < max_connections가 되도록 계획합니다. 또한 connectionTimeout과 MySQL wait_timeout이 충돌하지 않도록 풀의 idleTimeout을 wait_timeout보다 짧게 설정하세요.

장애 대응 체크리스트와 운영 자동화

장애 유형별 초기 진단

-- 1. 느린 쿼리 실시간 확인
SHOW PROCESSLIST;
-- 또는 (8.0+)
SELECT * FROM information_schema.PROCESSLIST
WHERE time > 30 ORDER BY time DESC;

-- 2. InnoDB 잠금 경합 확인
SELECT
  r.trx_id           AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query        AS waiting_query,
  b.trx_id           AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query        AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id;

-- 3. 장시간 실행 중인 트랜잭션 확인
SELECT
  trx_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_sec,
  trx_query,
  trx_rows_locked,
  trx_rows_modified
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY running_sec DESC;

운영 자동화 스크립트 예시

#!/bin/bash
# health_check.sh — 복제 지연 및 디스크 사용량 모니터링

MYSQL_USER="monitor"
MYSQL_PASS="MonitorPass!"
ALERT_THRESHOLD_SEC=30
DISK_THRESHOLD_PERCENT=85

# 복제 지연 확인
LAG=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e \
  "SHOW REPLICA STATUS\G" 2>/dev/null \
  | grep "Seconds_Behind_Source" | awk '{print $2}')

if [ "$LAG" = "NULL" ]; then
  echo "[ALERT] 복제가 중단되었습니다 (Replica SQL Thread 확인 필요)"
elif [ "$LAG" -gt "$ALERT_THRESHOLD_SEC" ]; then
  echo "[WARN] 복제 지연: ${LAG}초 (임계값: ${ALERT_THRESHOLD_SEC}초)"
fi

# 디스크 사용량 확인
DISK_USAGE=$(df /var/lib/mysql | awk 'NR==2 {print $5}' | tr -d '%')
if [ "$DISK_USAGE" -gt "$DISK_THRESHOLD_PERCENT" ]; then
  echo "[ALERT] 디스크 사용률 ${DISK_USAGE}% 초과 (임계값: ${DISK_THRESHOLD_PERCENT}%)"
fi

# 느린 쿼리 수 확인
SLOW_QUERIES=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e \
  "SHOW GLOBAL STATUS LIKE 'Slow_queries'" | awk '{print $2}')
echo "[INFO] 누적 슬로우 쿼리 수: $SLOW_QUERIES"

# cron 설정 예시 (crontab -e)
# 매 5분마다 상태 체크, 매일 새벽 2시에 전체 백업
*/5 * * * * /opt/scripts/health_check.sh >> /var/log/mysql_health.log 2>&1
0 2 * * * /opt/scripts/full_backup.sh >> /var/log/mysql_backup.log 2>&1

운영 자동화 도구 생태계

도구역할
Orchestrator복제 토폴로지 시각화 + 자동 페일오버
ProxySQL쿼리 라우팅, 커넥션 풀링, 읽기/쓰기 분리
pt-online-schema-change온라인 스키마 변경 (서비스 중단 없이)
pt-query-digest슬로우 쿼리 로그 분석 리포트
Prometheus + mysqld_exporter시계열 메트릭 수집
Grafana메트릭 시각화 대시보드

요약

  • **바이너리 로그(ROW 포맷)**는 복제와 PITR의 핵심 인프라이며, GTID를 활성화하면 페일오버와 복제 구성이 크게 단순해진다.
  • 반동기 복제는 최소 한 개의 레플리카가 로그를 수신한 뒤 커밋을 완료해 데이터 유실 위험을 낮추지만, 응답 지연이 늘 수 있다.
  • 논리 백업(mysqldump/mydumper)은 이식성이 높고, 물리 백업(XtraBackup)은 속도가 빠르며 증분 백업을 지원한다. 규모에 따라 병행 사용이 일반적이다.
  • PITR은 백업 + 바이너리 로그 재적용으로 완성되며, 백업 시 바이너리 로그 파일명과 위치를 반드시 기록해 두어야 한다.
  • Performance Schema와 sys 스키마를 활용하면 느린 쿼리, 핫스팟 테이블, 미사용 인덱스를 실시간으로 발견할 수 있다.
  • max_connections는 메모리 용량을 고려해 설정하고, 애플리케이션 커넥션 풀 크기와 MySQL wait_timeout을 함께 조율해야 커넥션 누수를 방지할 수 있다.

연습문제

  1. GTID 기반 복제 환경에서 소스 서버가 갑자기 다운되었습니다. 세 대의 레플리카 중 복제 지연이 가장 적은 레플리카를 새 소스로 승격시키는 SQL 명령어 시퀀스를 작성하세요.

  2. 프로덕션 DB에서 오늘 오후 3시에 DELETE FROM user_logs WHERE created_at < '2025-01-01'이 실수로 실행되었습니다. 새벽 1시 전체 백업(mysqldump, --source-data=2)과 바이너리 로그가 보존되어 있다고 가정하고, 오후 2시 59분 59초 기준으로 복구하는 절차를 bash 명령어로 작성하세요.

  3. 다음 Performance Schema 쿼리를 완성하여, 지난 1시간 내에 실행된 쿼리 중 평균 실행 시간이 1초를 초과하는 쿼리 다이제스트를 출력하세요. (events_statements_summary_by_digest 테이블 사용)

  4. 현재 서버 RAM이 32GB이고 max_connections=1000, sort_buffer_size=2M, join_buffer_size=2M으로 설정되어 있습니다. innodb_buffer_pool_size를 최대한 크게 유지하면서도 OOM(Out of Memory)을 방지하려면 어떻게 설정을 조정해야 하는지 계산 과정과 함께 설명하세요.

힌트 각 커넥션이 사용하는 메모리 총량 = (sort_buffer_size + join_buffer_size + 기타 세션 버퍼) × max_connections 를 먼저 계산하고, 남은 RAM에서 innodb_buffer_pool_size를 할당하세요.

💡 연습문제 풀이

불러오는 중…

함께 보면 좋은 자료

📑 치트시트

댓글 0

“MySQL 심화” 강좌에 대한 댓글입니다.

댓글을 작성하려면 로그인이 필요합니다.