MySQL 바이너리 로그 관리

Introduction

MySQL 바이너리 로그(binlog)는 데이터베이스 복제와 복구에 필수적인 컴포넌트이지만, 적절하지 않은 관리는 디스크 공간 부족과 성능 저하를 초래할 수 있습니다. 특히 프로덕션 환경에서는 바이너리 로그로 인해 디스크가 가득 차서 데이터베이스가 중단되는 치명적인 문제가 발생할 수 있습니다. 이 글에서는 바이너리 로그의 동작 원리부터 효과적인 관리 전략까지 다루겠습니다.

Environment

# MySQL 버전 확인
mysql --version

# 현재 바이너리 로그 설정 확인
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin%';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'expire_logs_days';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_binlog_size';"

-- 현재 활성화된 바이너리 로그 파일 목록
SHOW BINARY LOGS;

-- 바이너리 로그 상세 정보
SHOW MASTER STATUS;

Problem

대규모 트랜잭션이 빈번한 시스템에서 바이너리 로그가 예상보다 빠르게 증가했습니다:

# 디스크 사용량 확인
df -h /var/lib/mysql

# 결과
# Filesystem      Size  Used Avail Use% Mounted on
# /dev/sda1       100G   95G    5G  95% /

# 바이너리 로그 디렉토리 크기 확인
du -sh /var/lib/mysql/binlog.*
# binlog.000451  2.1G
# binlog.000452  2.3G
# binlog.000453  1.8G
# ... 총 45GB

에러 로그에서 관련 경고:

[Warning] Disk is 95% full. Waiting for someone to free some space...
[ERROR] Could not open log file
[Note] mysqld: Ready for connections

Analysis

바이너리 로그 문제의 근본 원인을 분석했습니다:

# 바이너리 로그 만료 설정 확인
mysql -u root -p -e "SHOW VARIABLES LIKE '%expire%';"
# expire_logs_days = 0  <-- 만료 설정이 비활성화됨
# binlog_expire_logs_seconds = 0

# 바이너리 로그 포맷 확인
mysql -u root -p -e "SHOW VARIABLES LIKE 'binlog_format';"
# binlog_format = ROW  <-- ROW 포맷은 더 많은 공간 사용

-- 바이너리 로그 이벤트 분석
SHOW BINLOG EVENTS IN 'binlog.000451' LIMIT 10;

-- 특정 시간대의 바이너리 로그 생성량 확인
mysqlbinlog --start-datetime="2025-02-27 00:00:00" \
            --stop-datetime="2025-02-27 23:59:59" \
            binlog.000451 | wc -l

Solution

1단계: 즉각적인 디스크 공간 확보

# 오래된 바이너리 로그 수동 삭제
mysql -u root -p -e "PURGE BINARY LOGS BEFORE '2025-02-20 00:00:00';"

# 또는 특정 파일까지 삭제
mysql -u root -p -e "PURGE BINARY LOGS TO 'binlog.000440';"

2단계: 자동 만료 설정

-- 만료 기간 설정 (7일)
SET GLOBAL expire_logs_days = 7;

-- 또는 초 단위 설정 (MySQL 8.0+)
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- my.cnf에 영구 설정
-- [mysqld]
-- expire_logs_days = 7
-- max_binlog_size = 100M

3단계: 바이너리 로그 크기 제한

# my.cnf 설정
[mysqld]
max_binlog_size = 100M
binlog_cache_size = 32K
sync_binlog = 1

4단계: 모니터링 스크립트 구현

#!/bin/bash
# binlog_monitor.sh

MYSQL_USER="monitor"
MYSQL_PASS="secure_password"
WARN_THRESHOLD=80
CRITICAL_THRESHOLD=90

DISK_USAGE=$(df /var/lib/mysql | awk 'NR==2 {print $5}' | tr -d '%')

if [ $DISK_USAGE -gt $CRITICAL_THRESHOLD ]; then
    echo "CRITICAL: Disk usage is ${DISK_USAGE}%"
    mysql -u $MYSQL_USER -p$MYSQL_PASS -e \
        "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);"
elif [ $DISK_USAGE -gt $WARN_THRESHOLD ]; then
    echo "WARNING: Disk usage is ${DISK_USAGE}%"
    mysql -u $MYSQL_USER -p$MYSQL_PASS -e \
        "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 5 DAY);"
fi

# 크론에 등록
# crontab -e
0 */6 * * * /opt/scripts/binlog_monitor.sh >> /var/log/binlog_monitor.log 2>&1

Lessons Learned

사전 설정의 중요성: expire_logs_days는 반드시 설정해야 하며, 기본값인 0은 위험합니다
모니터링 필수: 디스크 사용량과 바이너리 로그 생성 속도를 지속적으로 모니터링해야 합니다
RIGHT 포맷 고려: 단순 복제 목적이면 STATEMENT 포맷이 공간 효율적입니다
자동 정리 스크립트: 프로덕션 환경에서는 자동 정리 메커니즘을 반드시 구현해야 합니다
복구 전략 수립: 바이너리 로그 삭제 전 백업 전략을 반드시 수립해야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

MySQL 바이너리 로그 관리: 디스크 공간 및 성능 최적화