MySQL DB backup 정책

MySQL DB backup 정책

MySQL에서 데이터 복구 정책을 설계하는 것은 데이터 무결성과 가용성을 유지하는 데 중요합니다. 복구 정책을 수립하기 위해 주로 고려해야 할 요소는 데이터 백업 빈도, 백업 방법, 저장 위치, 데이터 손실 허용 한계(RPO), 복구 시간 목표(RTO) 등이 있습니다.

MySQL 데이터 복구 정책 구성 요소

1. 백업 전략 수립
   • 전체 백업(Full Backup): 데이터베이스의 전체 내용을 정기적으로 백업합니다. 일반적으로 일주일에 한 번 또는 한 달에 한 번 수행합니다.
   • 차등 백업(Differential Backup): 가장 최근의 전체 백업 이후 변경된 데이터만 백업합니다. 전체 백업 빈도가 낮을 때, 하루에 한 번 차등 백업을 수행하여 복구 시간을 단축할 수 있습니다.
   • 증분 백업(Incremental Backup): 마지막 백업 이후 변경된 데이터만 백업합니다. 데이터베이스 변경 사항이 자주 발생하는 경우 유용하며, 복구 시 전체 백업과 모든 증분 백업을 순차적으로 적용합니다.
   • 포인트 인 타임 복구(Point-in-Time Recovery): 트랜잭션 로그를 사용하여 특정 시점으로 복구할 수 있도록 설정합니다. MySQL에서는 바이너리 로그(Binary Log)를 사용하여 이를 수행할 수 있습니다.
2. 백업 도구와 방법
   • mysqldump: MySQL에서 제공하는 기본적인 백업 도구로, 데이터베이스를 SQL 덤프 파일로 저장합니다. 중소형 데이터베이스에 적합하며, 주로 전체 백업에 사용됩니다.
   • MySQL Enterprise Backup: MySQL의 엔터프라이즈 에디션에서 제공하는 백업 도구로, 대규모 데이터베이스에 최적화되어 있으며 빠른 백업 및 복구가 가능합니다.
   • Xtrabackup: Percona에서 제공하는 오픈 소스 백업 도구로, InnoDB와 XtraDB 스토리지 엔진을 사용하는 MySQL에 적합합니다. 온라인 백업을 지원하며, 대규모 데이터베이스의 백업 속도가 빠릅니다.
3. 백업 주기와 보관 정책
   • 백업 주기를 결정할 때 데이터 변경 빈도와 복구 시의 데이터 손실 허용 한계를 고려합니다.
   • 예: 전체 백업은 주 1회, 차등 백업은 주 2~3회, 증분 백업은 일일 수행.
   • 백업 데이터는 주기적으로 교체하며, 보관 기간은 보안 요구 사항과 규정에 맞게 설정합니다(예: 최소 3개월 또는 법적 요구에 맞춘 보관 기간).
4. 백업 데이터의 저장 위치
   • 온프레미스 저장: 로컬 서버 또는 네트워크 파일 스토리지(NFS)로 저장하여 접근성과 복구 속도가 빠릅니다.
   • 오프사이트 저장: 다른 물리적 위치에 백업을 저장하여, 재해 발생 시에도 복구할 수 있습니다.
   • 클라우드 스토리지: AWS S3, Google Cloud Storage, Azure Blob 등의 클라우드 서비스에 백업을 저장하여, 접근성과 안전성을 확보합니다.
5. 복구 프로세스 수립
   • 복구 시나리오 정의: 데이터베이스 손상, 전체 서버 장애, 특정 테이블 또는 레코드 손실 등 다양한 시나리오에 대해 대응 방법을 문서화합니다.
   • 복구 절차 테스트: 정기적으로 복구 절차를 테스트하여 예상한 대로 작동하는지, RTO와 RPO를 만족하는지 확인합니다.
   • 포인트 인 타임 복구 지원: 바이너리 로그를 사용하여 특정 시점으로 복구할 수 있도록 설정하고, 트랜잭션 손실을 최소화합니다.
6. 로그 관리
   • 바이너리 로그(Binary Log): 데이터베이스의 모든 변경 사항을 기록하여 포인트 인 타임 복구에 사용됩니다. 로그 보관 주기를 설정하고, 필요 시 외부 스토리지에 저장합니다.
   • 에러 로그(Error Log) 및 슬로우 쿼리 로그(Slow Query Log): 문제 진단 및 복구에 유용한 정보를 제공하며, 로그 보관 및 관리 정책을 설정합니다.
7. 모니터링과 알림
   • 데이터베이스 백업 실패, 로그 손상, 디스크 용량 부족 등의 문제를 모니터링하고, 문제가 발생하면 알림을 통해 신속히 대응할 수 있도록 합니다.
   • 백업 완료 및 검증 알림을 설정하여, 백업이 정상적으로 이루어졌는지 확인합니다.
8. 보안 관리
   • 백업 암호화: 백업 파일을 암호화하여, 데이터 유출 위험을 최소화합니다.
   • 접근 제어: 백업 파일과 로그에 대한 접근을 제한하여, 인증된 사용자만 복구를 수행할 수 있도록 합니다.

 

mysqldump 백업 및 복구

mysqldump를 사용하여 MySQL 백업을 설계할 때, 전체 백업과 증분 백업을 조합하여 데이터베이스의 가용성과 무결성을 유지하는 것이 중요합니다. 다음은 mysqldump를 활용한 백업 설계 예시입니다.

1. 백업 전략 수립

1) 전체 백업 (Full Backup)

• 주기: 일주일에 한 번 (예: 일요일 새벽)
• 백업 내용: 데이터베이스의 전체 데이터
• 백업 명령어: mysqldump를 사용하여 SQL 덤프 파일로 전체 데이터를 백업합니다.

 

2) 증분 백업 (Incremental Backup)

• 주기: 매일
• 백업 내용: 마지막 전체 또는 증분 백업 이후의 변경 사항
• 증분 방법: MySQL 바이너리 로그를 활용하여 증분 백업을 수행합니다. 바이너리 로그에는 모든 데이터 변경 사항이 기록되므로, 로그를 백업하고 복구 시 필요한 부분만 적용합니다.

 

2. 백업 스크립트 예시

아래는 mysqldump를 사용하여 MySQL 데이터베이스를 백업하는 스크립트 예시입니다. 이 스크립트는 전체 백업과 증분 백업을 자동으로 수행합니다.

#!/bin/bash

# 설정 변수
DB_USER="root"             # MySQL 사용자명
DB_PASS="password"         # MySQL 비밀번호
DB_NAME="database_name"    # 백업할 데이터베이스 이름
BACKUP_DIR="/path/to/backup" # 백업 파일 저장 경로
DATE=$(date +"%Y-%m-%d")   # 현재 날짜

# 전체 백업 수행 (주간 백업)
if [[ $(date +%u) -eq 7 ]]; then
    echo "전체 백업 시작: $DATE"
    mysqldump -u$DB_USER -p$DB_PASS --databases $DB_NAME > $BACKUP_DIR/full_backup_$DATE.sql
    echo "전체 백업 완료: $BACKUP_DIR/full_backup_$DATE.sql"
    # 바이너리 로그 초기화
    mysql -u$DB_USER -p$DB_PASS -e "RESET MASTER;"
else
    # 증분 백업 수행 (매일 바이너리 로그 백업)
    echo "증분 백업 시작: $DATE"
    BIN_LOGS=$(mysql -u$DB_USER -p$DB_PASS -e "SHOW BINARY LOGS;" | awk '{ print $1 }' | grep -v "Log_name")
    for LOG in $BIN_LOGS; do
        mysqlbinlog --read-from-remote-server --host=localhost --user=$DB_USER --password=$DB_PASS $LOG > $BACKUP_DIR/incremental_backup_$DATE_$LOG.sql
    done
    echo "증분 백업 완료"
fi

# 오래된 백업 파일 정리 (예: 30일 이상 된 파일 삭제)
find $BACKUP_DIR -type f -mtime +30 -exec rm {} \\;

3. 스케줄링 (크론 설정)

크론(cron)을 사용하여 정기적으로 이 스크립트를 실행하도록 설정합니다. 예를 들어, 매일 새벽 2시에 스크립트를 실행하려면 크론에 아래와 같이 추가합니다.

0 2 * * * /path/to/backup_script.sh >> /path/to/backup_log.txt 2>&1

4. 복구 절차

복구할 때는 전체 백업 파일을 복구한 뒤, 증분 백업 파일을 순서대로 적용합니다.

1. 전체 백업 복구:
2. mysql -u$DB_USER -p$DB_PASS $DB_NAME < /path/to/full_backup.sql
3. 증분 백업 파일 적용:모든 증분 백업 파일을 순서대로 적용하여, 데이터베이스를 복구합니다.
4. mysql -u$DB_USER -p$DB_PASS $DB_NAME < /path/to/incremental_backup_DATE_LOG.sql

5. 백업 파일의 암호화 및 저장 위치

백업 파일을 안전하게 보관하기 위해 아래 방법을 고려합니다.

• 암호화: gpg 또는 openssl을 사용하여 백업 파일을 암호화합니다.
• 외부 스토리지: 백업 파일을 클라우드 스토리지(AWS S3, Google Cloud Storage 등)에 업로드하여 재해 복구 대비를 강화합니다.

 

 

MySQL Enterprise Backup

MySQL Enterprise Backup(MEB)은 MySQL Enterprise Edition에서 제공하는 고급 백업 도구로, 데이터베이스를 효율적으로 백업하고 복구할 수 있는 다양한 기능을 제공합니다. MEB는 주로 대규모 데이터베이스와 고가용성 요구 사항이 있는 환경에서 사용되며, MySQL의 InnoDB 스토리지 엔진과 잘 통합되어 있어 데이터베이스의 백업과 복구 속도가 빠르고 안정적입니다.

MySQL Enterprise Backup의 주요 동작 원리

1. 백업 방식
   • 물리적 백업: MEB는 데이터 파일을 그대로 복사하는 방식으로, SQL 명령을 사용하는 mysqldump와 달리 실제 데이터 파일을 백업하기 때문에 속도가 빠르고 서버 자원 소모가 적습니다.
   • 온디스크(InnoDB) 데이터 구조 지원: 물리적 파일 수준에서 백업을 수행하므로, 특히 InnoDB와 같은 트랜잭션 스토리지 엔진과 잘 맞아 높은 성능을 보입니다.
   • 증분 백업 및 차등 백업: 마지막 전체 백업 이후 변경된 블록만 백업하는 증분 백업이나, 전체 백업 이후 변경된 모든 데이터를 백업하는 차등 백업을 지원하여, 백업 용량과 시간 효율성을 높입니다.
2. 온라인 백업 지원
   • MEB는 데이터베이스가 서비스 중인 상태에서도 백업을 수행할 수 있는 Hot Backup을 지원합니다. 이를 통해 데이터베이스 서비스 중단 없이 실시간 백업이 가능합니다.
   • InnoDB 스토리지 엔진을 사용하는 경우, 백업 중에도 쓰기 작업이 허용되며 백업을 시작할 때 스냅샷을 생성하여 일관성을 유지합니다.
3. 트랜잭션 무결성 보장
   • MySQL Enterprise Backup은 백업 중 트랜잭션 로그(redo log)를 통해 데이터베이스의 일관성을 유지합니다. 백업이 시작되면 스냅샷을 통해 특정 시점의 데이터를 캡처하고, 백업 도중 발생하는 변경 사항은 트랜잭션 로그에 기록하여 복구 시 적용할 수 있도록 합니다.
4. 데이터 압축 및 암호화
   • MEB는 데이터 압축을 지원하여 저장 공간을 절약하고 백업 성능을 향상시킵니다.
   • 백업 파일을 암호화하여 데이터 유출을 방지하고, 보안 요구 사항을 충족할 수 있습니다. 이는 민감한 데이터나 규제를 받는 데이터베이스 환경에서 유용합니다.
5. 복구 및 포인트 인 타임 복구
   • MEB는 전체 백업 파일을 사용한 전체 복구 외에도, 증분 복구를 통해 특정 시점으로 복구할 수 있는 포인트 인 타임 복구(Point-in-Time Recovery)를 지원합니다.
   • 바이너리 로그를 적용하여 특정 시점까지의 데이터 복구가 가능하므로, 데이터 손실을 최소화하면서 복구할 수 있습니다.
6. 백업 스토리지 및 멀티 스레드 지원
   • MEB는 병렬로 백업 및 복구 작업을 수행할 수 있는 멀티스레드 백업을 지원하여 대용량 데이터베이스에서도 빠르게 백업할 수 있습니다.
   • 백업 데이터는 로컬 저장소 외에도 네트워크 파일 시스템(NFS)이나 클라우드 스토리지에 저장할 수 있어 재해 복구 전략을 지원합니다.

MySQL Enterprise Backup 주요 명령어 예시

1. 전체 백업

mysqlbackup --user=root --password=password --host=localhost \\
--backup-dir=/path/to/backup/dir backup-and-apply-log

이 명령어는 MySQL 전체 데이터를 백업하고 apply-log 옵션을 사용하여 백업 로그를 바로 적용합니다.

1. 증분 백업

mysqlbackup --user=root --password=password --host=localhost \\
--incremental --start-lsn=123456 --backup-dir=/path/to/incremental_backup backup-and-apply-log

• -start-lsn을 사용하여 마지막 백업 이후 변경된 데이터만 백업합니다. LSN(Log Sequence Number)을 기준으로 증분 백업을 수행하며, apply-log로 로그를 적용하여 복구 준비를 완료합니다.

1. 복구

mysqlbackup --user=root --password=password --backup-dir=/path/to/backup/dir copy-back

copy-back 옵션을 사용하여 백업 파일을 데이터베이스 디렉터리로 복사하여 복구합니다.

MySQL Enterprise Backup 장점

• 고성능 백업 및 복구: 물리적 백업이기 때문에 백업 속도가 빠르며, 특히 대용량 데이터베이스에서 효율적입니다.
• 백업 파일 크기 절감: 압축 기능을 사용하여 백업 파일 크기를 줄일 수 있으며, 복구 속도를 높이는 데 도움이 됩니다.
• 온라인 백업 가능: 서비스 중단 없이 백업을 수행할 수 있어, 24/7 가용성을 유지해야 하는 시스템에 적합합니다.
• 트랜잭션 무결성 유지: InnoDB의 트랜잭션 기능과 연동하여 데이터 무결성을 유지하며, 정확한 복구를 제공합니다.

MySQL Enterprise Backup은 대규모 데이터베이스와 트랜잭션 무결성이 중요한 시스템에 적합하며, 특히 MySQL InnoDB를 사용하는 환경에서 안정적인 백업 및 복구 솔루션으로 활용됩니다.