백업과 복구 — mysqldump, xtrabackup, 시점 복구 전략

백업의 핵심 목적:
1. 장애 복구: 서버 고장, 디스크 손상 시 데이터 복원
2. 실수 복구: 잘못된 쿼리 실행으로 인한 데이터 손실 복원
3. 마이그레이션: 서버 이전, 버전 업그레이드 시 데이터 이동

mysqldump 실행 흐름:

1. MySQL 서버에 접속
2. 대상 테이블에 SELECT 실행
3. 결과를 CREATE TABLE + INSERT INTO ... 형태의 SQL로 변환
4. 표준 출력으로 내보냄 (파일로 리다이렉션)

# 전체 데이터베이스 백업
mysqldump -u root -p \
  --all-databases \
  --single-transaction \    # InnoDB: 락 없이 일관된 스냅샷 (핵심 옵션)
  --routines \              # 스토어드 프로시저, 함수 포함
  --triggers \              # 트리거 포함
  --events \                # 이벤트 스케줄러 포함
  --source-data=2 \         # Binlog 포지션을 주석으로 기록 (PITR용)
  > full_backup.sql

# 특정 데이터베이스만 백업
mysqldump -u root -p \
  --single-transaction \
  --databases myapp_db \
  > myapp_backup.sql

# 특정 테이블만 백업
mysqldump -u root -p \
  --single-transaction \
  myapp_db orders users \
  > tables_backup.sql

# 전체 복원
mysql -u root -p < full_backup.sql

# 특정 데이터베이스 복원
mysql -u root -p myapp_db < myapp_backup.sql

xtrabackup 동작 흐름:

1. InnoDB 데이터 파일(.ibd)을 직접 복사
2. 복사 중 발생하는 변경은 Redo Log를 캡처하여 별도 저장
3. 복사 완료 후, 캡처된 Redo Log를 데이터 파일에 적용(prepare)
4. 일관된 상태의 백업 완성

# 1단계: 백업 (데이터 파일 복사 + Redo Log 캡처)
xtrabackup --backup \
  --user=root --password=secret \
  --target-dir=/backup/full/

# 2단계: 준비 (Redo Log 적용 → 일관된 상태 만들기)
xtrabackup --prepare \
  --target-dir=/backup/full/

# 3단계: 복원 (MySQL 데이터 디렉토리에 복사)
# 먼저 MySQL을 중지해야 합니다
systemctl stop mysqld

# 기존 데이터 디렉토리 비우기
rm -rf /var/lib/mysql/*

# 백업 파일 복사
xtrabackup --copy-back \
  --target-dir=/backup/full/

# 소유권 복원
chown -R mysql:mysql /var/lib/mysql

# MySQL 재시작
systemctl start mysqld

# 풀 백업 (일요일)
xtrabackup --backup \
  --user=root --password=secret \
  --target-dir=/backup/full/

# 증분 백업 1 (월요일) — 풀 백업 이후 변경분
xtrabackup --backup \
  --user=root --password=secret \
  --target-dir=/backup/inc1/ \
  --incremental-basedir=/backup/full/

# 증분 백업 2 (화요일) — 증분 1 이후 변경분
xtrabackup --backup \
  --user=root --password=secret \
  --target-dir=/backup/inc2/ \
  --incremental-basedir=/backup/inc1/

# 증분 백업 복원 순서
# 1. 풀 백업 prepare (redo만 적용, undo 롤백은 하지 않음)
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full/

# 2. 증분 1 적용
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full/ \
  --incremental-dir=/backup/inc1/

# 3. 증분 2 적용 (마지막 증분에서는 --apply-log-only 생략)
xtrabackup --prepare \
  --target-dir=/backup/full/ \
  --incremental-dir=/backup/inc2/

# 4. 복원
xtrabackup --copy-back --target-dir=/backup/full/

PITR 복구 흐름:

1. 가장 최근 전체 백업을 복원
2. 백업 시점 이후의 Binlog를 확인
3. mysqlbinlog로 원하는 시점까지의 이벤트를 추출
4. 추출된 SQL을 MySQL에 재실행

예시: 매일 새벽 3시 풀 백업, 오후 2시 30분에 사고 발생
→ 새벽 3시 백업 복원 + 3시~2시 29분 Binlog 재생

-- 현재 Binlog 파일과 포지션 확인
SHOW MASTER STATUS;
-- +------------------+----------+
-- | File             | Position |
-- +------------------+----------+
-- | mysql-bin.000015 |  1234567 |
-- +------------------+----------+

-- Binlog 이벤트 목록 확인
SHOW BINLOG EVENTS IN 'mysql-bin.000015' LIMIT 20;

# Binlog 내용을 사람이 읽을 수 있는 형태로 확인
mysqlbinlog --base64-output=DECODE-ROWS --verbose \
  /var/lib/mysql/mysql-bin.000015

# 특정 시간 범위의 이벤트만 추출하여 복원
mysqlbinlog \
  --start-datetime="2026-03-28 03:00:00" \  # 백업 시점 이후부터
  --stop-datetime="2026-03-28 14:29:00" \   # 사고 직전까지
  /var/lib/mysql/mysql-bin.000015 \
  | mysql -u root -p

# 특정 포지션 범위로도 가능
mysqlbinlog \
  --start-position=154 \
  --stop-position=1234567 \
  /var/lib/mysql/mysql-bin.000015 \
  | mysql -u root -p

# Binlog 파일이 여러 개인 경우 순서대로 나열
mysqlbinlog \
  --stop-datetime="2026-03-28 14:29:00" \
  mysql-bin.000013 mysql-bin.000014 mysql-bin.000015 \
  | mysql -u root -p

# 1. 먼저 새벽 3시 전체 백업을 복원
mysql -u root -p < /backup/full_20260328_0300.sql

# 2. 백업에 기록된 Binlog 포지션 확인
# mysqldump의 --source-data 옵션이 기록해둔 주석
head -30 /backup/full_20260328_0300.sql | grep "CHANGE MASTER"
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=4567;

# 3. 해당 포지션부터 사고 직전까지의 Binlog 적용
mysqlbinlog \
  --start-position=4567 \
  /var/lib/mysql/mysql-bin.000014 \
  --stop-datetime="2026-03-28 14:29:00" \
  /var/lib/mysql/mysql-bin.000015 \
  | mysql -u root -p

백업 전략 예시 (DB 크기: ~50GB)

┌──────────────────────────────────────────────────┐
│  일요일 02:00   풀 백업 (xtrabackup --backup)     │
│  월~토 02:00    증분 백업 (xtrabackup --incremental)│
│  상시           Binlog 실시간 기록                  │
│  매 6시간       Binlog를 원격 스토리지에 복사        │
└──────────────────────────────────────────────────┘

RPO: 최대 6시간 (Binlog 원격 복사 주기)
RTO: ~30분 (풀 백업 + 증분 적용 + Binlog 재생)

소규모 서비스라면 더 단순하게:

매일 새벽  mysqldump --single-transaction 풀 백업
상시      Binlog 활성화 + 7일 보관
주 1회    백업 파일을 S3 같은 원격 저장소에 업로드

보관 기간 예시:
- 일일 백업: 7일 보관
- 주간 풀 백업: 4주 보관
- 월간 풀 백업: 12개월 보관
- Binlog: 최소 7일 (다음 풀 백업까지는 반드시 보관)

# 주기적으로 복원 테스트 (별도 서버에서)
# 1. 백업 파일이 정상인지
mysql -u root -p test_restore_db < /backup/full_backup.sql
echo $?  # 0이면 성공

# 2. 데이터 건수 검증
mysql -u root -p -e "SELECT COUNT(*) FROM test_restore_db.orders;"

절대 하면 안 되는 것:
  ✗ 백업 파일을 MySQL 서버와 같은 디스크에 보관

올바른 방법:
  ✓ 별도 서버나 NAS에 저장
  ✓ S3, GCS 같은 클라우드 오브젝트 스토리지에 업로드
  ✓ 가능하면 다른 리전에도 복제

# 백업 스크립트 예시 — 실패 시 알림 발송
#!/bin/bash
BACKUP_DIR="/backup/$(date +%Y%m%d)"

xtrabackup --backup \
  --user=backup_user --password=secret \
  --target-dir="$BACKUP_DIR" 2>/tmp/backup.log

if [ $? -ne 0 ]; then
  # 슬랙이나 이메일로 알림
  curl -X POST -H 'Content-type: application/json' \
    --data '{"text":"[ALERT] MySQL 백업 실패!"}' \
    https://hooks.slack.com/services/YOUR/WEBHOOK/URL
  exit 1
fi

# 백업 크기 확인 (비정상적으로 작으면 경고)
SIZE=$(du -sm "$BACKUP_DIR" | awk '{print $1}')
if [ "$SIZE" -lt 100 ]; then
  curl -X POST -H 'Content-type: application/json' \
    --data "{\"text\":\"[WARN] 백업 크기가 비정상적으로 작습니다: ${SIZE}MB\"}" \
    https://hooks.slack.com/services/YOUR/WEBHOOK/URL
fi

echo "백업 완료: $BACKUP_DIR (${SIZE}MB)"

백업 3원칙:
1. 백업은 반드시 원격 저장소에 보관한다
2. 복원 테스트를 주기적으로 수행한다
3. 백업 성공/실패를 모니터링한다