MySQL 중요 데이터 삭제 트러블슈팅

appti·2024년 2월 19일

트러블슈팅

목록 보기

2/3

서론

실제 사용자에게 서비스를 제공하는 중고 경매 거래 팀 프로젝트를 진행했었습니다.

사용자 유치를 위해 이벤트를 진행했었는데, 시간과 비용 상의 문제로 추가적으로 기능을 개발하거나 별도의 서버로 분리하지 않기로 결정했습니다.
기존 기능에 이벤트만을 위한 데이터를 추가한 뒤 이벤트가 끝나고 이벤트 데이터를 삭제하는 식으로 진행했습니다.

이벤트는 성공적이었고, 약 60명의 사용자를 약 80명대로 끌어올릴 수 있었습니다.

팀원 중 한 명이 이벤트가 끝난 뒤 관련 데이터만 모두 삭제하기로 했었습니다.
이후 각자 할 일을 하고 있었는데, 이벤트 데이터 삭제를 담당하게 된 분께 슬랙으로 연락이 왔습니다.

테스트용 DB를 초기화하는 것과 착각해 운영 DB에 있는 데이터를 일부 삭제했다는 연락이었습니다.
외래 키로 관리하고 있던 제약 조건을 무시하고 TRUNCATE를 수행했고, 테스트 DB가 아닌 운영 DB라는 것을 뒤늦게 깨달아 롤백을 하려고 했으나 데이터가 복구되지 않았다는 내용이었습니다.

문제 상황

회원 정보를 포함한 중요한 데이터가 모두 TRUNCATE로 날아간 상태였습니다.

특히 서비스를 이용하기 위해 회원 가입이 필수이다 보니 많은 테이블에서 회원을 외래 키로 참조하고 있었고, 회원 정보가 모두 날아가 외래 키로 관리하던 데이터 정합성이 모두 깨진 상황이었습니다.

DB Replication 등 데이터 백업을 적용했더라면 금방 복구할 수 있었겠지만 아쉽게도 프로젝트 규모도, 데이터도 적어 적용하지 않은 상황이었습니다.

차선책으로 MySQL의 빈 로그를 고려했습니다.

빈 로그

MySQL의 bin log, binary log는 데이터베이스 설정 변경에서부터 테이블 데이터 변경까지, 모든 변경과 관련된 이벤트를 기록합니다.
SELECT 등 변경하지 않는 이벤트는 기록하지 않으며, 완전히 수행된 이벤트 및 트랜잭션만 기록한다는 특징을 가지고 있습니다.

이는 상황과 설정만 맞는다면 특정 시점에 수행됐던, 완전히 적용된 변경 사항에 관한 쿼리를 확인할 수 있다는 것입니다.

적용할 수 있을지 판단

빈 로그는 DB를 변경시킨 이벤트(혹은 트랜잭션)을 모두 저장하기 때문에, 상황에 따라 빈 로그를 관리하고 삭제해야 할 필요성이 있습니다.

이와 관련된 MySQL의 옵션은 binlog_expire_logs_seconds으로, 얼마나 빈 로그를 유지시킬지에 대한 설정입니다.
(8.0 이전에는 expire_logs_days를 사용했고, 기본 값은 0으로 빈 로그를 삭제하지 않았습니다.)

기본 값은 30일을 기준으로 세팅되어 있습니다.

또한 DB의 구성 방식도 고려해야 합니다.

AWS RDS MySQL의 경우 binlog retention hours라는 옵션이 존재하는데, 기본 값은 NULL로 bin log를 저장하지 않습니다.

현재 프로젝트는 AWS RDS가 아닌 EC2에 직접 관리하고 있었으며, 초기 단계이기 때문이 빈 로그에 대한 유효 기간을 설정하기보다는 모니터링 후 메모리가 부족한 경우 수동으로 관리하기로 한 상황이었습니다.

실제 서비스를 시작한지 약 2주 정도가 지난 상황이었기 때문에 만약 AWS RDS를 사용했다면 심각한 문제가 발생했을 수도 있었습니다.

복구 전에 찍어놨던 빈 로그 스크린샷 입니다.

빈 로그의 위치는 /var/lib/mysql에 존재합니다.
grep 명령어를 통해 확인한 결과, 약 200개의 빈 로그가 서비스 시작 일자부터 존재하고 있음을 확인할 수 있었습니다.

복구 예제

실제 DB는 이미 복구했기 때문에, 도커를 사용해 상황을 간략화해서 복구하는 과정을 살펴보도록 하겠습니다.

macos에서 진행했습니다.

기본 세팅

docker run --platform linux/amd64 -v /Users/labtop/mysql_binlog:/var/lib/mysql --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 mysql:8.0.28

도커 컨테이너를 실행시켰습니다.
볼륨은 편의를 위해 설정해줬습니다.

CREATE DATABASE binlog_recovery;

USE binlog_recovery;

CREATE TABLE account (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
);

CREATE TABLE article (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30),
    account_id INT,
    FOREIGN KEY (account_id) REFERENCES account(id)
);

FLUSH LOGS;

INSERT INTO account (name) VALUES ('a');
INSERT INTO account (name) VALUES ('b');
INSERT INTO account (name) VALUES ('c');
INSERT INTO account (name) VALUES ('d');
INSERT INTO account (name) VALUES ('e');

INSERT INTO article (title, account_id) VALUES ('a_first_title', 1);
INSERT INTO article (title, account_id) VALUES ('a_second_title', 1);
INSERT INTO article (title, account_id) VALUES ('b_first_title', 2);
INSERT INTO article (title, account_id) VALUES ('c_first_title', 3);

기본적인 데이터 세팅이 끝났습니다.
현재 상황은 테이블 생성 이후 FLUSH LOGS;를 활용해 강제로 빈 로그를 생성한 상황입니다.
즉, 테이블 생성 이후 빈 로그가 생성되었고 이후 실행된 명령어는 아직 빈 로그가 생성되지 않은 상황입니다.

복구 진행

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE account;
SET FOREIGN_KEY_CHECKS = 1;

실수로 외래 키 제약조건을 무시하면서까지 account 데이터를 모두 삭제한 상황입니다.
이로 인해 삭제하지 않은 article 또한 데이터 정합성에 문제가 생겼습니다.

SHOW BINARY LOGS;

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3116922 | No        |
| binlog.000002 |      1009 | No        |
| binlog.000003 |      3209 | No        |
+---------------+-----------+-----------+

빈 로그가 얼마나 생성되었는지 확인합니다.
binlog_recovery라는 이름의 DB를 생성하고, 테이블 두 개를 생성한 쿼리까지는 FLUSH LOGS;를 통해 저장한 상황입니다.
아직 데이터를 추가하고 TRUNCATE로 삭제한 쿼리를 저장한 빈 로그는 없을 것이기 때문에 FLUSH LOGS;를 호출해 실행한 나머지 쿼리를 빈 로그로 저장합니다.

FLUSH LOGS;

SHOW BINARY LOGS;

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |   3116922 | No        |
| binlog.000002 |      1009 | No        |
| binlog.000003 |      3253 | No        |
| binlog.000004 |       157 | No        |
+---------------+-----------+-----------+

빈 로그 하나가 추가된 것을 확인할 수 있습니다.
이처럼 빈 로그는 특정 주기마다 저장되기 때문에 특정 시점으로 복구하기 위해서는 복구하고자 하는 시점에서 FLUSH LOGS;를 호출해 빈 로그를 생성해줘야 합니다.

ls | grep 'binlog'

binlog.000001
binlog.000002
binlog.000003
binlog.000004

볼륨에서 확인해보면 생성된 빈 로그를 확인할 수 있습니다.
다만 파일 이름에서 알 수 있다시피 빈 로그는 binary 형태로 제공되기 때문에 이를 후처리할 필요가 있습니다.

이럴 때 사용할 수 있는 유틸리티는 MySQL에서 기본적으로 제공해주는 mysqlbinlog 입니다.

mysqlbinlog binlog.000002 binlog.000003 binlog.000004 > binlog.sql

하나의 빈 로그를 sql로 변환할 수도 있고, *를 활용할 수도 있고, 위에처럼 여러 빈 로그를 직접 지정해줄 수도 있습니다.

binlog.000001의 경우 최초 MySQL 컨테이너 실행 시 설정을 세팅한 것이기 때문에 생략했습니다.

TRUNCATE라는 명령어로 찾다 보면 위 사진과 같이 TRUNCATE를 수행한 내용을 확인할 수 있습니다.

이 명령어를 주석처리하고 저장하면 지금까지 실행한 모든 쿼리를 재실행할 수 있습니다.

다만 주의사항이 있습니다.

이렇게 빈 로그에는 지금까지 수행했던 모든 변경사항이 저장되어 있습니다.

또한, 데이터의 일부만을 복구하고자 할 때 빈 로그에서 이미 존재하는 데이터를 다시 삽입하고자 하는 경우 에러가 발생할 수 있습니다.

mysql -u root -p -f < binlog.sql

그러므로 -f 옵션을 통해 에러가 발생하더라도 계속 sql을 실행하겠다는 옵션을 부여해야 합니다.

정상적으로 명령어가 실행되었습니다.

데이터도 정상적으로 복구되었음을 확인할 수 있습니다.

빈 로그 관리

프로젝트 규모가 적은 경우 신경 쓸 필요가 없겠고, 프로젝트 규모가 커서 DB Replication을 설정했다면 Replication 설정에 따르면 되겠지만 두 상황이 아닌 경우 빈 로그 관리를 고려할 필요가 있을 것입니다.

결국 웹 서비스에서 가장 중요한 것은 데이터이기 때문에 왠만하면 빈 로그를 삭제하지 않는 것이 좋겠지만, 만약 필요하다면 삭제할 필요도 있을 것입니다.

환경에 따라 binlog_expire_logs_seconds, max_binlog_size 옵션을 통해 유효 기간과 크기를 설정하고 수동 혹은 OS 스케줄러를 활용해 미리 복구할 수 있는 sql을 mysqlbinlog으로 생성해 별도의 공간에서 관리하는게 안전하다고 생각합니다.