LLM WikiAccess-protected knowledge portal
← 스터디 홈
4편 · 약 22분

백업·복구 실전: mysqldump, xtrabackup, binlog PITR

백업은 복구가 되었을 때만 백업이다

백업 전략을 세울 때 가장 많이 저지르는 실수는 백업이 돌고 있다는 것을 복구할 수 있다와 동일시하는 것이다. 실제 장애 상황에서 드러나는 문제는 다른 곳에 있다. binlog retention 기간이 PITR에 필요한 범위보다 짧았거나, xtrabackup prepare를 빠뜨려 데이터 파일이 일관성이 없었거나, 복구용 서버에 필요한 MySQL 버전이나 파라미터가 달라 import가 실패하거나, 마지막 복구 리허설을 언제 했는지 아무도 기억하지 못하는 경우다.

이번 장의 목표는 백업 명령어를 외우는 것이 아니다. DBA로서 어떤 장애 시나리오에서 어떤 도구로 어느 지점까지 복구할 수 있는지를 설계하고, 그 절차를 실제 운영에 연결하는 것이다.

백업 유형의 구분: 무엇을 선택할 것인가

MySQL 백업은 크게 두 축으로 분류한다.

논리 백업(Logical backup): 데이터를 SQL 문장이나 CSV 형식으로 덤프한다. mysqldump가 대표적이다. 어느 MySQL 버전이나 다른 RDBMS로도 이식할 수 있고, 특정 테이블이나 데이터베이스만 선택적으로 복구하기 쉽다. 단점은 대형 데이터베이스에서 덤프와 복원 시간이 매우 길다는 것이다. GB 단위 테이블은 시간 단위로 걸릴 수 있고, 그동안 쓰기 부하는 계속 쌓인다.

물리 백업(Physical backup): 데이터 파일, redo log, 데이터 딕셔너리를 그대로 복사한다. xtrabackup이 대표적이다. 파일 복사 기반이므로 대형 데이터베이스에서 빠르고, 복원도 파일 교체 수준이라 빠르다. 단, 같은 MySQL 버전과 InnoDB 포맷이 필요하고, 파일 단위가 아닌 테이블 단위 복구는 별도 절차가 필요하다.

스냅샷 백업: AWS EBS, GCP Persistent Disk 같은 블록 스토리지 스냅샷을 활용한다. 실질적인 백업 시간이 매우 짧고 RTO도 빠를 수 있지만, MySQL 데이터 일관성을 보장하려면 스냅샷 전에 FLUSH TABLES WITH READ LOCK이나 xtrabackup의 lock-free 방식을 함께 사용해야 한다.

논리 백업
mysqldump
이식성 높음
대형 DB 느림
테이블 선택 복구 가능
물리 백업
xtrabackup
대형 DB 빠름
동일 버전 필요
증분 백업 지원
스냅샷 백업
EBS / PD Snapshot
매우 빠른 RTO
MySQL lock 필요
블록 수준 복원
+ Binary Log
PITR 핵심
백업 이후 변경 추적
retention이 복구 범위 결정
백업 방식 비교와 PITR 연결 구조

핵심은 세 방식이 배타적이지 않다는 점이다. 실제 운영에서는 xtrabackup 전체 백업 + 증분 백업 + binary log PITR 조합이 RTO와 RPO를 동시에 관리하는 가장 일반적인 패턴이다.

mysqldump: 논리 백업의 기본과 주의점

mysqldump는 MySQL에 기본으로 포함된 논리 백업 도구다. 소규모 DB, 특정 테이블 마이그레이션, 개발 환경 복제에 자주 쓰이고, 덤프 파일이 사람이 읽을 수 있는 SQL이라는 점도 장점이다.

핵심 옵션

-- InnoDB 테이블의 일관된 논리 백업
mysqldump \
  --user=backup_user \
  --password \
  --host=127.0.0.1 \
  --single-transaction \
  --set-gtid-purged=ON \
  --master-data=2 \
  --routines \
  --triggers \
  --events \
  --databases mydb \
  > /backup/mydb_20260628.sql

--single-transaction: InnoDB 테이블에 대해 REPEATABLE READ 트랜잭션을 열고 일관된 스냅샷을 읽는다. 백업 중 다른 세션의 쓰기를 막지 않으므로 운영 부하를 최소화한다. 단, InnoDB가 아닌 테이블(MyISAM 등)은 이 보장에서 빠진다.

--master-data=2: 백업이 시작된 시점의 binary log 파일명과 position을 주석으로 기록한다. 이 값이 PITR 시작점이 된다. 값을 1로 하면 주석이 아닌 실행 가능한 CHANGE REPLICATION SOURCE TO 문으로 포함된다.

--set-gtid-purged=ON: GTID 기반 복제 환경에서 SET @@GLOBAL.gtid_purged 문을 덤프에 포함한다. 이 값으로 복원 후 replica를 붙이거나 PITR을 수행할 때 GTID 연속성을 유지할 수 있다.

mysqldump의 한계

  • 큰 테이블 하나를 덤프하면 그 시간 동안 해당 테이블의 데이터가 늘어나 undo segment가 계속 쌓인다.
  • 복원 시 INSERT 문 실행 속도가 느려 TB 규모 DB에서는 사실상 비현실적이다.
  • 덤프 중 FLUSH TABLES WITH READ LOCK을 거는 경우가 있어 MyISAM이 섞인 환경에서는 쓰기를 잠시 막을 수 있다.
  • 증분 백업 개념이 없어 매번 전체를 덤프해야 한다.

Percona XtraBackup: 물리 백업의 핵심 도구

Percona XtraBackup은 InnoDB의 redo log를 추적해 백업 중에도 쓰기를 허용하는 핫 백업을 제공한다. 백업이 진행되는 동안 MySQL은 계속 서비스하고, xtrabackup은 데이터 파일 복사와 병행해 변경된 redo log를 수집한다.

전체 백업(Full Backup)

-- 전체 백업 생성
xtrabackup \
  --backup \
  --user=xbackup_user \
  --password=*** \
  --host=127.0.0.1 \
  --target-dir=/backup/full_20260628

-- 준비(prepare): 복원 전 필수 단계
xtrabackup \
  --prepare \
  --target-dir=/backup/full_20260628

prepare 단계가 중요한 이유: --backup 직후의 데이터 파일은 InnoDB가 보기에 crash recovery 상태와 같다. prepare 과정에서 xtrabackup은 백업 중 수집한 redo log를 재적용해 데이터 파일을 일관된 상태로 만든다. prepare 없이 복원하면 MySQL이 시작되면서 crash recovery를 시도하는데, 백업된 redo log로는 완전히 복구할 수 없어 데이터 손상이 발생할 수 있다.

증분 백업(Incremental Backup)

xtrabackup은 InnoDB의 LSN(Log Sequence Number)을 기반으로 증분 백업을 지원한다. 마지막 전체 또는 증분 백업 이후 변경된 페이지만 복사하므로 시간과 공간을 절약할 수 있다.

-- 전체 백업 후 LSN 확인
cat /backup/full_20260628/xtrabackup_checkpoints
# backup_type = full-backuped
# from_lsn = 0
# to_lsn = 1234567890
# last_lsn = 1234567895

-- 증분 백업 (전체 백업 기준)
xtrabackup \
  --backup \
  --target-dir=/backup/inc_20260628_1 \
  --incremental-basedir=/backup/full_20260628

-- 전체 백업에 증분 적용 (복원 준비)
xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full_20260628

xtrabackup --prepare --apply-log-only \
  --target-dir=/backup/full_20260628 \
  --incremental-dir=/backup/inc_20260628_1

-- 마지막 증분은 --apply-log-only 없이 prepare
xtrabackup --prepare \
  --target-dir=/backup/full_20260628 \
  --incremental-dir=/backup/inc_20260628_2

--apply-log-only 옵션은 중간 증분 백업을 적용할 때 반드시 필요하다. 이 옵션 없이 prepare하면 redo log가 롤백까지 진행되어 이후 증분을 추가로 적용할 수 없게 된다.

복원(Restore)

-- MySQL 서비스 중지 후 데이터 디렉토리 비우기
systemctl stop mysql
rm -rf /var/lib/mysql/*

-- 준비된 백업 파일 복사
xtrabackup --copy-back \
  --target-dir=/backup/full_20260628

-- 권한 복구
chown -R mysql:mysql /var/lib/mysql

-- MySQL 시작
systemctl start mysql

Binary Log PITR: 특정 시점으로 복구하기

전체 백업만으로는 백업 이후의 변경을 복구할 수 없다. binary log가 그 간격을 채운다. PITR은 전체 또는 증분 백업으로 기반을 복원한 뒤 binary log를 원하는 시점까지 재적용하는 절차다.

Full Backup xtrabackup LSN + binlog pos Inc Backup 1 delta pages Inc Backup 2 delta pages 장애 발생 DROP TABLE 잘못된 UPDATE 복구 목표 시점 장애 직전 Binary Logs (연속 스트림) --stop-datetime ① 물리 복원 xtrabackup --copy-back inc1 + inc2 prepare ② binlog 적용 mysqlbinlog | mysql --stop-datetime 지정 ③ 검증 COUNT(*), checksum 앱 오류율, replica 재동기
MySQL 백업 + PITR 전체 흐름

mysqlbinlog를 이용한 PITR

백업을 복원한 뒤 binary log를 적용한다. xtrabackup_binlog_info 파일에서 백업 종료 시점의 binlog 파일명과 position을 확인하고 그 지점부터 재생한다.

-- 백업 종료 시점 확인
cat /backup/full_20260628/xtrabackup_binlog_info
# mysql-bin.000042  154298765  77d34f21-...

-- binlog에서 문제가 된 이벤트 찾기
mysqlbinlog \
  --start-position=154298765 \
  /var/lib/mysql/mysql-bin.000042 \
  /var/lib/mysql/mysql-bin.000043 \
  | grep -A5 "DROP TABLE orders"

-- 문제 이벤트 직전까지 적용 (시각 기준)
mysqlbinlog \
  --start-position=154298765 \
  --stop-datetime="2026-06-28 14:35:00" \
  /var/lib/mysql/mysql-bin.000042 \
  /var/lib/mysql/mysql-bin.000043 \
  | mysql -u root -p

-- 또는 position 기준
mysqlbinlog \
  --start-position=154298765 \
  --stop-position=187654320 \
  /var/lib/mysql/mysql-bin.000042 \
  | mysql -u root -p

GTID 환경에서의 주의사항: GTID 기반 복제 환경에서는 SET @@GLOBAL.gtid_purged가 덤프나 xtrabackup_binlog_info에 기록된다. PITR 후 replica를 재연결할 때 GTID 불일치가 발생할 수 있으므로 RESET MASTER 이후 gtid_purged를 명시적으로 설정하고 재동기하는 절차가 필요하다.

운영에서 실수하기 쉬운 지점

binary log retention

PITR의 실효 범위는 가장 오래된 보관 binary log에서 현재까지다. 기본 binlog_expire_logs_seconds는 2592000(30일)이지만, 디스크 압박으로 줄여 놓거나 수동으로 PURGE BINARY LOGS를 실행한 환경에서는 필요한 로그가 이미 삭제되어 있을 수 있다.

-- 현재 binlog 보존 설정 확인
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW BINARY LOGS;

-- 가장 오래된 보관 로그와 그 크기
SHOW BINARY LOGS\G

binary log는 외부 스토리지나 오브젝트 스토리지에도 별도 복제해 두는 것이 안전하다. 원본 MySQL 서버가 통째로 날아가는 시나리오에서는 해당 서버의 binary log도 사라지기 때문이다.

백업 소요 시간과 replica lag

xtrabackup은 백업 마지막에 짧은 lock을 잡는다. 운영 DB 직접 백업보다 replica에서 백업을 수행하면 primary에 주는 영향을 줄일 수 있다. 단, replica에서 백업 시 replica lag을 함께 모니터링해야 한다. 백업 중 lag이 크면 binlog position의 실제 시간 범위가 틀어질 수 있다.

복구 리허설

백업 검증의 가장 확실한 방법은 실제로 복구해 보는 것이다. 최소 분기 1회, 이상적으로는 월 1회 별도 환경에서 최신 백업 + binary log PITR로 완전 복원 후 다음을 확인한다.

  • SELECT COUNT(*) FROM 주요테이블 일치 여부
  • 애플리케이션 smoke test
  • 복구 소요 시간 측정 (RTO 달성 여부)
  • 복원된 데이터의 최종 timestamp가 RPO 내인지 확인

복구 리허설 결과는 runbook에 기록한다. 지난번에 됐으니 이번에도 된다는 가정은 운영 사고를 만드는 출발점이다.

백업 전략 설계 요약

항목mysqldumpxtrabackup스냅샷
백업 속도느림 (대형 DB)빠름매우 빠름
복원 속도느림빠름매우 빠름
이식성높음 (SQL)같은 버전 필요클라우드 의존
증분 지원없음있음 (LSN 기반)블록 수준
PITR 지원binlog 필요binlog 필요binlog 필요
선택 기준소규모, 이식, 특정 테이블 복구대형 DB, RTO 짧아야 할 때클라우드 환경, 빠른 RTO

운영에서 백업 전략은 RTO와 RPO 목표 → 백업 도구와 주기 결정 → retention 정책 → 복구 리허설 주기 순서로 설계한다. 백업 자체가 목적이 아니라 복구 가능성을 유지하는 것이 목적임을 항상 기억해야 한다.

References

  • MySQL 9.7 Reference Manual, "mysqldump — A Database Backup Program": https://dev.mysql.com/doc/refman/9.7/en/mysqldump.html
  • Percona XtraBackup Documentation, "Point-In-Time Recovery": https://docs.percona.com/percona-xtrabackup/8.0/point-in-time-recovery.html
  • Percona Blog, "Percona XtraBackup Point-In-Time Recovery for the Single Database": https://www.percona.com/blog/percona-xtrabackup-point-in-time-recovery-for-the-single-database/
  • MySQL 8.4 Reference Manual, "Using GTIDs for Failover and Scaleout": https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-failover.html
  • BinaryScripts, "MySQL Backup and Restore Strategies: Implementing Point-In-Time Recovery and Incremental Backups": https://binaryscripts.com/mysql/2025/05/27/mysql-backup-and-restore-strategies-implementing-point-in-time-recovery-and-incremental-backups.html
  • DCHost Blog, "mysqldump Or XtraBackup? The Friendly Guide To MySQL/MariaDB Backups And Point-in-Time Recovery": https://www.dchost.com/blog/en/mysqldump-or-xtrabackup-the-friendly-guide-to-mysql-mariadb-backups-and-point%E2%80%91in%E2%80%91time-recovery/
  • MySQL 9.7 Reference Manual, "Point-in-Time (Incremental) Recovery": https://dev.mysql.com/doc/refman/9.7/en/point-in-time-recovery.html