[MySQL] (백업/복구4️⃣) PITR(Point In Time Recovery) 원리와 설정 실습

MySQL 백업 전략에서 가장 강력한 복구 기능 중 하나가 PITR(Point-In-Time Recovery)다.
PITR은 이름 그대로 특정 시점까지 데이터베이스 상태를 되돌리는 복구 기법이다.
데이터를 완전히 날렸을 때 단순 전체 복원만으로는 복구할 수 없는 상황을 대비해, PITR은 실무에서 매우 중요하게 활용된다.

✅ PITR이란 무엇인가?

PITR(Point-In-Time Recovery)는 풀백업(mysqldump 등) 이후, 특정 시점까지의 변경 이력을 반영하여 데이터 상태를 복구하는 방법이다.
Binary Log에 기록된 DML(INSERT, UPDATE, DELETE) 쿼리를 활용해 정해진 시간 이전의 데이터 상태로 되돌릴 수 있다.

예를 들어, 실수로 DELETE FROM user를 실행한 경우,
삭제 직전 시간까지 복원하면 데이터 유실을 방지할 수 있다.

반응형

🧱 PITR을 위한 준비 요소

PITR을 수행하기 위해서는 다음 세 가지가 반드시 필요하다.

1. 풀백업 파일 (mysqldump 등으로 생성)
2. Binary Log 활성화 및 이력 보관
3. 복구 대상 시점 (시간)

이 세 가지가 모두 준비되어야만 시점 복구가 가능하다.

🔧 Binary Log 활성화 및 설정 확인

먼저 my.cnf 또는 my.ini에서 Binary Log 설정이 되어 있어야 한다.

[mysqld]
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

활성화 여부 확인

SHOW VARIABLES LIKE 'log_bin';

현재 기록 중인 로그 파일 확인

SHOW MASTER STATUS;

📤 mysqldump로 Full Backup 생성

PITR은 Full Backup → Binary Log 적용의 순서로 복원하기 때문에 백업이 먼저 수행되어야 한다.

mysqldump -u root -p --single-transaction --routines --events mydb > mydb_full.sql

파일명에 백업 시간을 붙여두면 나중에 편하다.

mydb_full_20250703_1200.sql

🔍 Binary Log 내 시간 추적

로그 분석 도구: mysqlbinlog

예를 들어 특정 시점의 Binary Log를 살펴보려면 다음처럼 실행한다.

mysqlbinlog --start-datetime="2025-07-03 12:00:00" --stop-datetime="2025-07-03 14:00:00" /var/lib/mysql/mysql-bin.000012 > pitr_diff.sql

옵션 설명

• --start-datetime: 변경 추적을 시작할 시간
• --stop-datetime: 변경 반영을 멈출 시간
• mysql-bin.000012: 해당 시간 구간을 포함하는 로그 파일

추출된 SQL은 수정 없이 그대로 적용 가능하다.

반응형

🔁 복구 실행 순서 정리

1. mysqldump 백업 파일로 DB 초기화

mysql -u root -p < mydb_full_20250703_1200.sql

2. Binary Log로 변경 이력 반영

mysql -u root -p < pitr_diff.sql

3. 복구 완료 후, 검증

• 레코드 수 확인
• 삭제된 데이터 존재 여부 확인
• 이중 반영이 없는지 트랜잭션 확인

⚠️ 실무 PITR에서 자주 발생하는 문제와 대처법

1. Binary Log가 삭제되었거나 남아있지 않음
   → expire_logs_days 값을 길게 설정하거나 별도로 복사 보관 필요
2. 서버 시간과 실제 기준 시간 차이
   → SELECT NOW();로 정확한 시간 기준 확인 필요
3. 복구 대상 로그 파일 추정 실패
   → SHOW BINARY LOGS;로 로그 목록 확인 후 필요한 파일만 추출
4. GTID 충돌
   → mysqlbinlog 실행 시 --set-gtid-purged=OFF 옵션 추가로 회피 가능

🧪 실습 예제 요약

1. 2025-07-03 12시 00분에 mysqldump로 백업
2. 13시 43분에 실수로 user 테이블 전부 삭제
3. 12:00~13:43까지의 Binary Log 추출
4. 복원 시, 백업 파일 적용 후, 추출한 binlog SQL만 실행
5. user 테이블 삭제 이전 상태로 완전 복구 성공

🔐 복구도 전략이다

많은 기업들이 "백업은 잘 하고 있다"고 말하지만, 정작 복구 실습을 제대로 해본 적 없는 경우가 많다.
PITR은 백업 전략 중에서도 실제 장애 상황에서 가장 유연하고 실용적인 복구 전략이다.
반드시 백업 + 로그 이력을 함께 운영하고, 복구 실습을 통해 숙련도를 쌓아야 한다.

✅ 정리

• PITR은 정확한 시점으로 데이터베이스를 되돌릴 수 있는 복구 전략이다.
• Full Backup과 Binary Log가 함께 있어야 한다.
• mysqlbinlog 도구를 사용해 원하는 시간 구간의 변경 사항만 추출 가능하다.
• 복원 순서는 백업 → 로그 SQL 반영이다.
• 실무에서는 반드시 자동화, 로그 보존 정책, 테스트 복구 환경이 병행되어야 한다.

🔗 공식 문서 참고
MySQL 8.0 Reference Manual - Point-in-Time Recovery