이번 시간에는 실수로 WHERE 없이 지운 주문을 전체 백업과 Binlog로 되살리는 PITR을 직접 해 보며 배워가는 시간입니다.
예를 들어서 오후 3시 18분, 운영 DB에서 쿼리를 하나 실행했다고 해볼게요. WHERE 절이 빠진 DELETE였어요. 주문 테이블이 통째로 비었습니다.
다행히 새벽 0시에 받아 둔 전체 백업이 있어요. 백업을 복원하면 새벽 0시의 데이터가 돌아옵니다. 하지만 그 뒤 15시간 동안 들어온 주문과 환불 내역은 돌아오지 않죠.
이렇게 원하는 시점의 데이터를 다시 만드는 방법을 PITR(Point-In-Time Recovery, 특정 시점 복구)이라고 합니다.
MySQL은 데이터를 바꾼 트랜잭션을 커밋 순서대로 Binlog(바이너리 로그) 파일에 남겨요. PITR은 전체 백업을 복원한 뒤, 이 Binlog를 원하는 지점까지 다시 적용하는 방식입니다.
실습 준비
실습에서는 새벽 0시까지 기다릴 수 없으니, 백업을 받고 몇 분 뒤에 사고를 재현해 볼게요.
Docker로 MySQL 8.4를 두 대 띄웁니다. 한 대는 사고가 나는 운영 DB 역할을 하고, 다른 한 대는 복구 작업에 써요.
복구에는 mysqlbinlog라는 도구가 필요해요. 그런데 공식 MySQL Docker 이미지에는 이 도구가 들어 있지 않습니다.
그래서 macOS에 Homebrew로 MySQL 8.4 클라이언트를 설치하고, 컨테이너 밖에서 접속해 작업하는 방식으로 가볼게요.
brew install mysql-client@8.4
export PATH="/opt/homebrew/opt/mysql-client@8.4/bin:$PATH"
mysqlbinlog --version
mysqlbinlog Ver 8.4.11 for macos15.7 on arm64 (Homebrew)
운영 DB 역할의 pitr-source는 3307 포트로, 복구용 pitr-restore는 3308 포트로 띄울게요. 시간대는 한국 시간으로 맞춥니다.
비밀번호를 매번 입력하지 않도록 MYSQL_PWD 환경 변수도 지정해요. 이 방식은 실습에서만 쓰고, 운영 환경에서는 쓰지 마세요.
docker run -d --name pitr-source -p 3307:3306 -e MYSQL_ROOT_PASSWORD=root -e TZ=Asia/Seoul mysql:8.4
docker run -d --name pitr-restore -p 3308:3306 -e MYSQL_ROOT_PASSWORD=root -e TZ=Asia/Seoul mysql:8.4
export MYSQL_PWD=root
컨테이너가 뜨는 데 몇십 초가 걸려요. 접속이 되면 Binlog 설정부터 확인해 볼게요.
mysql -h127.0.0.1 -P3307 -uroot -e "SHOW VARIABLES WHERE Variable_name IN ('log_bin', 'binlog_format', 'binlog_expire_logs_seconds', 'sync_binlog');"
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
| binlog_format | ROW |
| log_bin | ON |
| sync_binlog | 1 |
+----------------------------+---------+
출력을 보면 MySQL 8.4는 Binlog가 기본으로 켜져 있고, 변경된 행을 그대로 기록하는 ROW 형식을 써요.
실습 1: 사고 직전까지 복구하기
주문 데이터 만들고 백업하기
주문 테이블을 만들고 주문 세 건을 넣어 주세요.
mysql -h127.0.0.1 -P3307 -uroot -e "
CREATE DATABASE shop;
CREATE TABLE shop.orders (id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(20), amount INT, status VARCHAR(10));
INSERT INTO shop.orders (customer, amount, status) VALUES ('kim', 32000, 'paid'), ('lee', 15000, 'paid'), ('park', 48000, 'paid');"
이 상태에서 전체 백업을 받을게요.
mysqldump -h127.0.0.1 -P3307 -uroot --single-transaction --source-data=2 --databases shop > full.sql
grep "SOURCE_LOG" full.sql
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=955;
옵션 두 개만 짚고 갈게요.
--single-transaction: 테이블을 잠그지 않고, 한 시점의 데이터를 일관되게 백업해요.--source-data=2: 백업 시점의 Binlog 파일 이름과 위치를 주석으로 남겨요.
위 출력은 이 백업이 binlog.000002 파일의 955 위치까지 반영했다는 뜻이에요. 복구할 때는 바로 이 위치부터 Binlog를 읽기 시작합니다.
사고 일으키기
이제 서비스가 돌아가는 상황을 만들어 볼게요. 새 주문을 받고, 환불을 처리하고, 또 주문을 받습니다.
mysql -h127.0.0.1 -P3307 -uroot -e "INSERT INTO shop.orders (customer, amount, status) VALUES ('choi', 21000, 'paid');"
mysql -h127.0.0.1 -P3307 -uroot -e "UPDATE shop.orders SET status = 'refunded' WHERE customer = 'lee';"
mysql -h127.0.0.1 -P3307 -uroot -e "INSERT INTO shop.orders (customer, amount, status) VALUES ('jung', 9000, 'paid');"
그리고 오후 3시 18분, 새 주문이 들어온 바로 그 순간에 WHERE 없는 DELETE가 실행돼요.
mysql -h127.0.0.1 -P3307 -uroot -e "INSERT INTO shop.orders (customer, amount, status) VALUES ('yoon', 54000, 'paid'); DELETE FROM shop.orders;"
사고를 알아채기 전에도 서비스는 돌아가고 있으니, 주문이 한 건 더 들어와요.
mysql -h127.0.0.1 -P3307 -uroot -e "INSERT INTO shop.orders (customer, amount, status) VALUES ('han', 27000, 'paid');"
mysql -h127.0.0.1 -P3307 -uroot -e "SELECT * FROM shop.orders;"
+----+----------+--------+--------+
| id | customer | amount | status |
+----+----------+--------+--------+
| 7 | han | 27000 | paid |
+----+----------+--------+--------+
주문 여섯 건이 사라지고, 사고 뒤에 들어온 한 건만 남았어요.
DELETE가 기록된 위치 찾기
먼저 mysqlbinlog로 백업 위치 이후의 Binlog를 읽어 파일로 저장할게요. 이때 쓰는 옵션은 다음과 같습니다.
--read-from-remote-server: 서버에 접속해 Binlog를 가져와요. 컨테이너 안의 파일을 꺼낼 필요가 없습니다.--base64-output=decode-rows -v: ROW 형식으로 기록된 변경을 사람이 읽을 수 있는 SQL 형태로 풀어 줘요.--start-position: 이 위치부터 읽어요. 백업에 남은 위치인 955를 넣습니다.
mysqlbinlog --read-from-remote-server -h127.0.0.1 -P3307 -uroot \
--base64-output=decode-rows -v --start-position=955 binlog.000002 > decoded.txt
decoded.txt를 열어 보시면 이벤트마다 # at 1254처럼 위치가 찍혀 있어요. 그리고 Anonymous_GTID 이벤트가 나오는 곳에서 트랜잭션이 하나씩 시작됩니다.
파일이 수백 줄이라 그대로 읽기는 어려워요. 그래서 트랜잭션마다 시작 위치와 시간, 안에서 실행된 변경만 뽑아 볼게요.
awk '/^# at /{pos=$3} /Anonymous_GTID/{print "트랜잭션 시작", pos, $2} /^### (INSERT|UPDATE|DELETE)/{print " " $2}' decoded.txt | uniq -c
1 트랜잭션 시작 955 15:10:00
1 INSERT
1 트랜잭션 시작 1254 15:12:01
1 UPDATE
1 트랜잭션 시작 1584 15:17:40
1 INSERT
1 트랜잭션 시작 1883 15:18:05
1 INSERT
1 트랜잭션 시작 2182 15:18:05
6 DELETE
1 트랜잭션 시작 2578 15:20:00
1 INSERT
맨 앞 숫자는 같은 줄이 연달아 몇 번 나왔는지를 뜻해요. 출력을 트랜잭션별로 풀어 보면 이렇습니다.
- 955에서 시작하는 트랜잭션: 15:10:00, choi의 주문
- 1254에서 시작하는 트랜잭션: 15:12:01, lee의 환불 처리
- 1584에서 시작하는 트랜잭션: 15:17:40, jung의 주문
- 1883에서 시작하는 트랜잭션: 15:18:05, yoon의 주문
- 2182에서 시작하는 트랜잭션: 15:18:05, 주문 여섯 건을 지운 DELETE
- 2578에서 시작하는 트랜잭션: 15:20:00, han의 주문
그럼 어디서 멈춰야 할까요? 멈출 위치는 ### DELETE FROM이 찍힌 줄이 아니라, DELETE가 속한 트랜잭션이 시작되는 위치여야 해요. 트랜잭션 중간에서 멈추면 절반만 적용된 트랜잭션이 남거든요.
이번 예시에서는 DELETE 트랜잭션이 시작되는 2182입니다.
복구용 인스턴스에 복원하기
운영 DB가 아니라 복구용 인스턴스에 먼저 백업을 복원해 주세요.
mysql -h127.0.0.1 -P3308 -uroot < full.sql
이제 백업 위치부터 DELETE 직전 위치까지 Binlog를 다시 적용해 볼게요.
--start-position: 백업 위치인 955를 넣어요.--stop-position: DELETE 트랜잭션이 시작되는 위치인 2182를 넣어요. 이 위치에 닿으면 읽기를 멈추니, DELETE 트랜잭션은 적용되지 않습니다.
mysqlbinlog --read-from-remote-server -h127.0.0.1 -P3307 -uroot \
--start-position=955 --stop-position=2182 binlog.000002 | mysql -h127.0.0.1 -P3308 -uroot
mysql -h127.0.0.1 -P3308 -uroot -e "SELECT * FROM shop.orders;"
+----+----------+--------+----------+
| id | customer | amount | status |
+----+----------+--------+----------+
| 1 | kim | 32000 | paid |
| 2 | lee | 15000 | refunded |
| 3 | park | 48000 | paid |
| 4 | choi | 21000 | paid |
| 5 | jung | 9000 | paid |
| 6 | yoon | 54000 | paid |
+----+----------+--------+----------+
사고 직전의 주문 여섯 건이 모두 돌아왔어요. lee의 환불 상태도, DELETE와 같은 순간에 들어온 yoon의 주문도 그대로입니다.
실습 2: 사고 뒤에 들어온 주문은 어떻게 할까요
그런데 여기서 끝내면 사고 뒤에 들어온 han의 주문은 빠져요. 실제 서비스라면 사고를 발견할 때까지 몇 분, 길게는 몇 시간 동안 정상 주문이 쌓이죠. 이 주문을 버릴 수는 없습니다.
방법은 간단해요. DELETE 트랜잭션만 건너뛰고, 그다음 트랜잭션부터 이어서 다시 적용하면 됩니다. DELETE 트랜잭션은 2182에서 시작해 2578에서 끝나니, 2578부터 다시 읽을게요.
mysqlbinlog --read-from-remote-server -h127.0.0.1 -P3307 -uroot \
--start-position=2578 binlog.000002 | mysql -h127.0.0.1 -P3308 -uroot
mysql -h127.0.0.1 -P3308 -uroot -e "SELECT * FROM shop.orders;"
+----+----------+--------+----------+
| id | customer | amount | status |
+----+----------+--------+----------+
| 1 | kim | 32000 | paid |
| 2 | lee | 15000 | refunded |
| 3 | park | 48000 | paid |
| 4 | choi | 21000 | paid |
| 5 | jung | 9000 | paid |
| 6 | yoon | 54000 | paid |
| 7 | han | 27000 | paid |
+----+----------+--------+----------+
주문 일곱 건이 모두 복구됐고, 잘못된 DELETE 하나만 빠졌어요.
정리
범위: mysql:8.4 컨테이너 두 대(운영 역할, 복구용), Homebrew mysql-client@8.4의 mysqlbinlog 8.4.11, 주문 테이블 하나.
mysqldump --single-transaction --source-data=2로 받은 백업에는binlog.000002의 955 위치가 주석으로 남았어요. 복구는 이 위치부터 Binlog를 읽어 시작하세요.- 백업을 복원하고 955부터 DELETE 트랜잭션이 시작되는 2182까지 다시 적용하자 사고 직전의 주문 여섯 건이 환불 상태까지 돌아왔습니다. 멈출 위치는
### DELETE FROM줄이 아니라 DELETE가 속한 트랜잭션의 시작 위치로 잡아 주세요. - DELETE 트랜잭션이 끝나는 2578부터 이어서 다시 적용하자 사고 뒤에 들어온 han의 주문까지 일곱 건이 모두 돌아왔어요. 사고 뒤의 정상 변경은 사고 트랜잭션만 건너뛰고 이어서 다시 적용하세요.
- 이 글에 싣지 않은 추가 측정에서는 yoon의 주문과 DELETE가 같은 초(15:18:05)에 기록돼, 위치 대신 시간으로 멈추는
--stop-datetime은 15:18:05에서 yoon의 주문을 빠뜨렸고 15:18:06에서는 DELETE까지 적용해 0건이 됐습니다. 시간은 살펴볼 구간을 좁히는 데만 쓰고, 멈추는 지점은 Binlog 위치로 정해 주세요. - 이 실습은 백업 복원과 Binlog 적용을 모두 복구용 인스턴스
pitr-restore에서 하고 결과를 SELECT로 확인했습니다. 운영 DB에 바로 복원하거나 다시 적용하지 마세요.