PITR 실습 설계: DROP·DELETE 직전으로 복구하고 데이터로 입증하기

백업을 적재하고 MySQL이 정상 기동했다는 사실만으로 복구 훈련이 끝나지는 않는다. 사고 DELETE가 제외되었는지, 그 직전의 정상 변경은 모두 남아 있는지, 감사 테이블까지 같은 업무 시점으로 돌아왔는지를 확인해야 한다. 특히 연속해서 여러 사고가 발생했다면 가장 나중에 발견한 DROP의 직전이 곧 정상 상태인 것은 아니다.

이 글은 앞서 다룬 전체 백업과 binlog 재실행 원리를 실제 훈련의 합격 기준으로 구체화한다. 한 원본에 정상 변경, 잘못된 DELETE, 후속 정상 변경, DROP을 순서대로 발생시키고, 두 개의 독립된 서버를 서로 다른 경계까지 복구한다. 목표는 명령어 암기가 아니라 복구 지점의 선택과 데이터 검증을 함께 설계하는 것이다.

검증 범위: MySQL Community Server 8.0.46의 폐기용 원본 1개와 복원 서버 2개에서 실제 논리 백업 생성, 두 binlog 파일 재실행, 전체 행 및 orders 테이블 정의 일치, GTID 포함·제외를 확인했다. 외부 네트워크를 차단한 컨테이너 실험이며 운영 서버 손실, 대용량 RTO, Aurora PITR을 시험한 것은 아니다. 본문의 좌표와 GTID는 이 실험에서 얻은 값으로 다른 서버에 재사용하면 안 된다. MySQL 8.4에서는 상태 조회 명칭 등 버전별 문법도 다시 확인한다.

1. 복구 목표를 시간 대신 상태와 경계로 정의한다

PITR의 입력은 일관된 기준 백업, 그 백업과 연결되는 로그 시작 좌표, 중간에 빠짐없는 binlog, 그리고 종료 경계다. 출력은 그 경계 이전에 완료된 변경을 반영한 데이터 상태다.

시각은 사고 후보를 찾는 단서다. 같은 초에 정상 트랜잭션과 사고 트랜잭션이 함께 기록될 수 있고, 사건 신고 시각은 실제 실행 시각보다 늦을 수 있다. 따라서 “09시 몇 분 직전”이라는 요구를 받으면 먼저 해당 로그를 조사하고 제외할 트랜잭션의 시작 위치로 목표를 확정한다. mysqlbinlog의 datetime 해석에는 실행 환경의 로컬 시간대도 영향을 주므로 사고 기록의 시간대와 함께 적어야 한다.

InnoDB의 일반 DML은 명시적인 트랜잭션 안에서 묶을 수 있다. 반면 일반 테이블의 DROP TABLE은 암묵적 커밋을 수반한다. MySQL 8.0의 atomic DDL이 장애 중 DDL의 일관성을 개선했다고 해서 사용자가 완료된 DROP을 ROLLBACK으로 취소할 수 있는 것은 아니다. DDL과 그 뒤에 별도로 기록한 감사 행을 하나의 롤백 단위로 가정하지 않는다.

2. 두 사고를 한 시간선에 배치한다

실습 데이터는 주문 금액 테이블 orders(id, amount)와 감사 테이블 audit(id, action)이다. 주문 번호와 금액은 결과 대조를 쉽게 하기 위한 합성 데이터다.

flowchart LR
    B["기준 백업<br/>주문 1:100, 2:200"] --> G["정상 변경<br/>주문 1:90, 2:210"]
    G --> A["복구 목표 A<br/>DELETE 시작 직전"]
    A --> D["사고 DELETE<br/>전체 주문 삭제"]
    D --> I["후속 정상 INSERT<br/>주문 3:300"]
    I --> C["복구 목표 B<br/>DROP 시작 직전"]
    C --> X["사고 DROP<br/>orders 제거"]

기준 상태를 만드는 다음 SQL은 비어 있는 격리 실습 인스턴스에서만 실행한다. drill 데이터베이스가 이미 존재하면 다른 이름을 정하고 모든 명령의 대상도 함께 변경한다. 운영 데이터베이스에서 실습을 시작하지 않는다.

CREATE DATABASE drill;
USE drill;
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE audit (
    id INT PRIMARY KEY,
    action VARCHAR(40) NOT NULL
) ENGINE=InnoDB;
INSERT INTO orders VALUES (1, 100), (2, 200);
INSERT INTO audit VALUES (1, 'baseline');
SELECT id, amount FROM orders ORDER BY id;

실행 결과(MySQL 8.0.x):

mysql> CREATE DATABASE drill;

Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE orders (
    ->     id INT PRIMARY KEY,
    ->     amount INT NOT NULL
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE audit (
    ->     id INT PRIMARY KEY,
    ->     action VARCHAR(40) NOT NULL
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO orders VALUES (1, 100), (2, 200);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO audit VALUES (1, 'baseline');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT id, amount FROM orders ORDER BY id;

+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
+----+--------+
2 rows in set (0.00 sec)

기준 백업 이후에 발생시킨 변경은 다음과 같다. 각 DML 단계는 명시적인 트랜잭션으로 커밋했다. 사고 동작은 폐기용 원본에서만 수행했다.

목표 A는 첫 데이터 손상 직전이므로 감사 1·2만 남아야 한다. 목표 B는 마지막 DROP만 제외하므로 감사 1·2·3·4가 남고, 주문은 3번만 존재해야 한다. B가 기술적으로 정확한 PITR 결과여도 업무적으로 정상인 복구 결과는 아닐 수 있다. 이 차이를 훈련의 핵심 판정 항목으로 삼는다.

3. 백업 좌표와 복구 자료를 먼저 고정한다

실험은 binary logging, ROW 형식, GTID를 활성화하고 transaction compression은 비활성화한 상태에서 수행했다. 압축 트랜잭션을 사용하는 운영 환경은 조사 시 이벤트 표현이 달라질 수 있으므로 동일한 도구 버전과 설정으로 다시 훈련한다.

아래 셸 명령은 실제 실험에 사용한 도구와 옵션을 호스트 접속 방식으로 표현한 환경 조정용 명령 형식이다. source_lab과 restore_lab은 사전에 안전하게 구성한 MySQL login path 이름이며 기본 제공 이름이 아니다. 실험에서는 외부 접속이 없는 컨테이너 내부 연결을 사용했다. 복원 login path가 원본이나 운영 서버를 가리키지 않는지 먼저 확인한다.

mysqldump --login-path=source_lab \
  --single-transaction --quick --source-data=2 \
  --set-gtid-purged=ON --routines --events --triggers \
  --no-tablespaces --databases drill > base.sql

--single-transaction은 InnoDB 데이터의 일관된 읽기를 사용하지만, 백업과 동시에 진행되는 DDL까지 무조건 안전하게 흡수하는 옵션은 아니다. 훈련에서는 스키마 변경을 중단하고 백업 완료 뒤에 사고를 발생시켰다. --source-data=2로 기록되는 좌표는 덤프의 주석에서 읽는다. MySQL 8.0.46 실험에서는 옵션 이름과 달리 주석이 다음처럼 CHANGE MASTER TO 명칭으로 기록되었다.

실제 덤프에서 발췌:

-- CHANGE MASTER TO MASTER_LOG_FILE='drill-bin.000004', MASTER_LOG_POS=197;

좌표는 명칭을 추정해서 만들지 않고 파일에서 직접 읽어 기록한다. 정상 변경을 첫 파일에 기록한 뒤 로그를 회전했고, 두 번째 파일에 DELETE·후속 INSERT·DROP을 기록했다. 사고가 끝난 뒤 다시 회전하여 실습 대상 두 파일을 닫고 보관했다.

이 실험의 덤프는 drill 데이터베이스를 위한 것이지 서버 전체 백업이 아니다. 특히 --set-gtid-purged=ON으로 기록되는 GTID 집합은 원본의 다른 데이터베이스 이력도 포함할 수 있다. 부분 데이터 덤프와 서버 전체 실행 이력을 동등하게 해석하거나, 기존 업무 이력이 있는 서버에 그대로 적재하면 안 된다. 훈련 대상은 원본 업무 이력이 없는 새 복원 서버로 제한했다.

4. 사고 SQL이 아니라 사고 트랜잭션의 시작을 찾는다

ROW binlog의 DELETE는 원래 SQL 문자열 대신 row event로 남을 수 있다. mysqlbinlog --base64-output=DECODE-ROWS -vv로 행 이벤트를 해석하면 대상 테이블과 삭제된 열 값을 조사할 수 있다. DDL은 Query event를 함께 확인한다.

mysqlbinlog --base64-output=DECODE-ROWS -vv \
  drill-bin.000004 drill-bin.000005 > inspect.txt

이 출력은 조사용이다. ### DELETE FROM ...는 재구성된 설명이며 그대로 실행할 복구 SQL이 아니다. 실제 재실행에는 기본 base64 출력을 사용한다.

실험의 inspect.txt에서 얻은 경계는 다음과 같다. GTID 번호는 원본 초기화 이력까지 반영하므로 1부터 시작한다고 가정하지 않는다.

두 위치 모두 해당 사고의 GTID 이벤트 시작 위치임을 대조했다. DELETE의 Table_map 또는 Delete_rows 이벤트 시작 지점이 아니다. DELETE와 같은 트랜잭션에 포함한 bad_delete 감사 행도 함께 제외되어야 하므로, 행 이벤트 중간에서 자르면 안 된다.

--stop-position은 지정 위치 이상에서 시작하는 이벤트를 읽기 전에 중단한다. 이번 실험처럼 GTID 이벤트의 시작 위치를 사용하면 사고 트랜잭션 전체를 적용 대상에서 제외할 수 있다. 반대로 사고의 COMMIT까지 적용한 뒤 멈추면 이미 사고가 반영된 상태다. 중간부터 이어붙인 불완전한 스트림을 오류 무시 옵션으로 강행하지 않는다.

시각 기반 옵션은 조사 범위를 줄이는 데 유용하지만 최종 적용 범위는 위치로 확정한다. 사고 직전에 끝나지 않은 다른 트랜잭션이나 후속 정상 거래가 있다면, 요구하는 업무 상태를 해당 경계로 표현할 수 있는지도 별도로 검토한다. 여기서 다루는 것은 로그의 연속된 접두 구간 복원이지 특정 거래만 골라 취소하는 기능이 아니다.

5. 새 복원 서버 두 곳에 서로 다른 종료 경계를 적용한다

먼저 각 복원 서버에 base.sql을 적재하고 주문 (1,100), (2,200)과 감사 baseline을 확인했다. 백업 적재만으로도 기대 데이터가 다르다면 로그를 적용하기 전에 중단해야 한다.

다음 명령은 목표 A용이다. 현재 디렉터리에는 보관한 두 binlog 파일이 있어야 한다. 파일 생성과 서버 적재를 분리하여 mysqlbinlog의 실패를 먼저 감지하고, 재실행 스트림을 한 MySQL 연결에 입력한다.

mysql --login-path=restore_lab < base.sql && \
mysqlbinlog --verify-binlog-checksum \
  --start-position=197 --stop-position=197 \
  drill-bin.000004 drill-bin.000005 > replay-before-delete.sql && \
mysql --login-path=restore_lab < replay-before-delete.sql

시작 위치와 종료 위치가 모두 197인 것은 빈 구간이라는 뜻이 아니다. 여러 파일을 한 번에 지정하면 start-position은 첫 파일에, stop-position은 마지막 파일에 적용된다. 여기서는 .000004:197부터 .000005:197 직전까지다. 중간 파일이 더 있다면 누락 없이 순서대로 나열한다. 숫자가 같아도 파일이 다르면 서로 다른 좌표다.

목표 B에는 별도의 새 복원 서버를 사용하여 같은 덤프를 적재하고 --stop-position=988로 스트림을 생성·적용했다. 목표 A 서버에 같은 덤프를 다시 무작정 덮어쓰거나, 이미 적용한 GTID를 삭제해서 실습을 이어가지 않았다.

각 명령은 종료 코드 0을 확인한다. --force로 SQL 오류를 무시하지 않는다. 파일 checksum 검사는 손상 감지에 도움이 되지만 파일 누락, 잘못된 백업 조합, 업무 데이터 불일치를 모두 증명해 주지는 않는다. 이 실험은 전체 보관 로그 구간을 사용했으며 --database로 복구 대상을 임의로 필터링하지 않았다. 교차 데이터베이스 변경과 이벤트 형식에 따른 필터 의미를 검토하지 않고 적용 범위를 줄이면 트랜잭션의 업무 일관성이 깨질 수 있다.

6. 복구 결과는 데이터와 GTID 두 축으로 판정한다

다음 표는 실제 두 복원 서버의 결과다. 주문과 감사 테이블은 PK 순서로 정렬한 모든 행과 모든 열을 미리 보관한 기대값과 비교했고, orders의 SHOW CREATE TABLE도 대조했다.

GTID 검사는 GTID_SUBSET(검사할_집합, @@GLOBAL.gtid_executed)의 결과를 실제로 대조했다. 정상 집합 포함과 사고 GTID 제외를 각각 검사해야 한다. 다만 GTID가 존재한다고 데이터가 반드시 정확한 것은 아니다. 필터링이나 빈 GTID 커밋 등으로 실행 이력과 실제 데이터가 달라질 수 있으므로 데이터 검사를 생략할 수 없다.

행 수와 합계만으로는 불충분하다

주문 1·2의 금액이 서로 뒤바뀌어도 행 수와 총액은 같을 수 있다. 다음은 그 오판을 재현하는 독립적인 검증 SQL이다. 실제 복원 테이블을 변경하지 않고 격리된 실습 DB에 비교용 일반 테이블 두 개를 생성한 뒤 정리한다. 같은 테이블을 한 문장에서 여러 번 참조하므로 MySQL의 TEMPORARY TABLE 재참조 제한을 피한다. 기대값은 반드시 사고 전에 별도로 확보한 자료에서 와야 하며, 사고 이후 원본을 그대로 정답으로 삼지 않는다.

CREATE TABLE expected_orders (
    id INT PRIMARY KEY,
    amount INT NOT NULL
);
CREATE TABLE recovered_orders LIKE expected_orders;
INSERT INTO expected_orders VALUES (1, 90), (2, 210);
INSERT INTO recovered_orders VALUES (1, 210), (2, 90);
SELECT
    (SELECT COUNT(*) FROM expected_orders) =
    (SELECT COUNT(*) FROM recovered_orders) AS same_count,
    (SELECT SUM(amount) FROM expected_orders) =
    (SELECT SUM(amount) FROM recovered_orders) AS same_total,
    NOT EXISTS (
        SELECT 1 FROM expected_orders e
        LEFT JOIN recovered_orders r ON r.id = e.id
        WHERE r.id IS NULL OR NOT (r.amount <=> e.amount)
    ) AND NOT EXISTS (
        SELECT 1 FROM recovered_orders r
        LEFT JOIN expected_orders e ON e.id = r.id
        WHERE e.id IS NULL
    ) AS all_rows_equal;
DROP TABLE recovered_orders, expected_orders;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE expected_orders (
    ->     id INT PRIMARY KEY,
    ->     amount INT NOT NULL
    -> );

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE recovered_orders LIKE expected_orders;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO expected_orders VALUES (1, 90), (2, 210);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO recovered_orders VALUES (1, 210), (2, 90);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->     (SELECT COUNT(*) FROM expected_orders) =
    ->     (SELECT COUNT(*) FROM recovered_orders) AS same_count,
    ->     (SELECT SUM(amount) FROM expected_orders) =
    ->     (SELECT SUM(amount) FROM recovered_orders) AS same_total,
    ->     NOT EXISTS (
    ->         SELECT 1 FROM expected_orders e
    ->         LEFT JOIN recovered_orders r ON r.id = e.id
    ->         WHERE r.id IS NULL OR NOT (r.amount <=> e.amount)
    ->     ) AND NOT EXISTS (
    ->         SELECT 1 FROM recovered_orders r
    ->         LEFT JOIN expected_orders e ON e.id = r.id
    ->         WHERE e.id IS NULL
    ->     ) AS all_rows_equal;

+------------+------------+----------------+
| same_count | same_total | all_rows_equal |
+------------+------------+----------------+
|          1 |          1 |              0 |
+------------+------------+----------------+
1 row in set (0.00 sec)

mysql> DROP TABLE recovered_orders, expected_orders;

Query OK, 0 rows affected (0.00 sec)

same_count=1, same_total=1인데도 all_rows_equal=0이어야 한다. 한 방향 비교만 하면 복원 테이블에 추가된 잘못된 행을 놓칠 수 있어 양방향 존재 검사를 사용한다. 실무 테이블에 적용할 때는 비교할 모든 열, NULL 처리, 문자 비교 규칙까지 정의한다. 작은 실습에서의 전 행 비교를 대용량 테이블에 그대로 적용하기 어렵다면 PK 범위별로 나누고 불일치 구간을 상세 대조한다. 표본 비교나 집계만 통과한 결과를 전 데이터 일치라고 보고하지 않는다.

7. 사고 이후 정상 거래를 어떻게 처리할지 따로 결정한다

목표 A는 삭제 피해를 없애지만 그 뒤의 정상 주문 3도 포함하지 않는다. PITR은 선택한 경계까지 되돌리는 작업이지 사고 트랜잭션만 제외하고 모든 후속 거래를 자동으로 보존하는 작업이 아니다.

사고 트랜잭션을 건너뛴 뒤 이후 로그를 계속 적용하면 된다고 단순화해서는 안 된다. 후속 변경은 삭제 이후의 데이터 상태를 전제로 수행되었을 수 있으며 키 충돌, 행 부재, 참조 무결성 문제 또는 더 조용한 업무 불일치를 일으킬 수 있다. 다음 선택지를 복구 승인자가 구분해야 한다.

• 전체 시점 복귀: 사고 직전 상태로 서비스 전체를 전환한다. 그 이후 정상 거래의 손실·재입력 정책과 사용자 통지가 필요하다.
• 격리 복원 후 선별 복원: 별도 서버에서 삭제 전 데이터를 추출하고 현재 운영 데이터와 대조해 복원한다. PK 충돌, 변경된 상태, 자식 행과 감사 이력을 검증하는 별도 데이터 정합성 작업이다.
• 후속 거래 재처리: 신뢰할 수 있는 외부 업무 원장이나 메시지에서 정상 거래를 재처리한다. 중복 처리 방지와 순서·의존성 검사가 필요하다.

어느 선택도 “PITR 명령이 성공했다”는 이유만으로 자동 승인하지 않는다. 복구 서버에는 배치·이벤트·외부 연동이 임의로 실행되지 않도록 통제하고, 승인 전까지 일반 쓰기와 애플리케이션 연결을 제한한다. 반입한 routine·event 정의가 새 서버에서 어떤 부수 효과를 낼지도 점검한다.

8. Aurora MySQL에서는 복원 경로를 분리한다

Aurora의 관리형 PITR은 사용자가 datadir에 binlog를 직접 재실행하는 절차가 아니다. 보존 기간 안의 특정 시각으로 새 DB 클러스터를 생성하고 그 결과를 검증한다. EarliestRestorableTime과 LatestRestorableTime으로 가능한 복원 범위를 확인해야 하며, 사고 시각이 보존 범위 밖이면 같은 방식의 복구를 가정할 수 없다.

AWS 문서상 복원된 클러스터에는 기본 파라미터 그룹이 연결될 수 있으므로 사용자 지정 그룹을 적용할지 명시적으로 확인한다. 보안 그룹, 인스턴스 생성·접속 준비, 이벤트 실행, 애플리케이션 엔드포인트 전환도 별도 점검 대상이다.

로컬 실험에서 확인한 .000005:197 같은 좌표를 Aurora 관리형 PITR API의 복원 시각으로 직접 전달할 수는 없다. 다만 정상 업무 행과 감사 행이 함께 남는가, 이전 DELETE가 반영된 시점을 실수로 고르지 않았는가라는 판정 방식은 동일하게 유효하다. Aurora에서는 복원한 클러스터의 데이터를 직접 검사하여 시각 선택이 업무 목표를 충족했는지 확인해야 한다. 이 글에는 실제 Aurora 복원 시간이나 실험 결과를 제시하지 않는다.

9. 훈련 합격 체크리스트

• 원본·복원 서버의 식별자와 접속 대상을 기록하고 운영 환경과 격리했다.
• 기준 덤프, 시작 좌표, 연속된 binlog, 도구 버전을 함께 보관했다.
• 사고 신고 시각과 로그 조사 시각의 시간대를 구분했다.
• 사고 row event뿐 아니라 그 앞의 트랜잭션 시작 경계를 확인했다.
• 백업만 적재한 결과와 각 PITR 목표의 결과를 단계별로 대조했다.
• 정상 변경은 포함되고 사고 GTID는 요구한 범위만큼 제외되는지 검사했다.
• 업무·감사 테이블의 전체 행·열, 필요한 객체 정의와 참조 관계를 검증했다.
• 행 수와 합계만 같다는 이유로 데이터 복원을 합격 처리하지 않았다.
• 사고 이후 정상 거래의 손실·선별 복원·재처리 정책을 결정했다.
• 복원 서버의 외부 연동과 쓰기를 통제하고 서비스 전환 승인 절차를 정했다.
• 데이터 준비부터 검증·전환까지 걸린 시간을 측정했다면 그 범위까지 함께 보고했다.

결론

PITR 실습은 백업 파일이 읽힌다는 사실을 확인하는 데서 끝나지 않는다. 종료 경계가 원하는 업무 상태를 뜻하는지와 복원 데이터가 그 상태에 실제로 일치하는지를 함께 입증해야 한다. 이번 실험에서 DROP 직전 복원은 정확히 성공했지만 앞선 DELETE의 피해까지 없애지는 못했다.

다음 단계는 이 작은 실험의 판정 항목을 실제 데이터 규모, 로그 보존 체계, 복원 소요 시간, 서비스 전환 절차로 확장하는 것이다. 백업 성공 여부와 복구 목표 달성 여부를 분리해 기록해야 반복 가능한 복구 운영 체계가 된다.

참고 문서

• MySQL 8.0: Using Backups for Point-in-Time Recovery — Event Positions
• MySQL 8.0: mysqlbinlog — Utility for Processing Binary Log Files
• Amazon Aurora: Restoring a DB cluster to a specified time