MySQL 데이터 복구하기

2023. 2. 25. 19:12

데이터 복구 과정에서 학습한 내용 기록

이번에 회사에서 AWS RDS로 DB 서버를 단계적으로 이전하는 과정에서 프로덕션 데이터가 소실되는 상황이 발생하였습니다. 다행히 바이너리 로그(binlog)를 보관하는 기간 내에 있어서 MySQL binlog를 활용하여 데이터를 복구할 수 있었습니다. 이때 학습한 내용을 기록 차원에서 남깁니다.

데이터 복구 과정

프로덕션 서버에서 binlog 확인
```
SHOW BINARY LOGS;
```
결과는 다음과 같았습니다:
+---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000015 | 724935 | | binlog.000016 | 733481 | +---------------+-----------+
먼저, 프로덕션 서버에 binlog가 존재하는지 확인하였습니다.

binlog 덤프 진행

mysqlbinlog --read-from-remote-server --host=<프로덕션 호스트> --raw --user=<계정> --password \
binlog.000015 binlog.000016

이 명령어는 지정한 binlog 파일들을 로컬 디렉토리로 가져옵니다.

프로덕션 호스트로부터 binlog를 로컬로 덤프하기 위해 mysqlbinlog 유틸리티를 사용하였습니다.
binlog에서 INSERT 및 UPDATE 추출
```
mysqlbinlog binlog.000015 binlog.000016 --database=<DB명> --base64-output=DECODE-ROWS -v > output.sql
```
- --database 옵션은 특정 데이터베이스만 대상으로 합니다.
- --base64-output=DECODE-ROWS와 -v 옵션은 binlog 이벤트를 사람이 읽을 수 있는 SQL 형식으로 변환합니다.
덤프한 binlog 파일에서 특정 데이터베이스의 DML 문을 추출하기 위해 다음과 같이 파싱하였습니다.
SQL 문 실행을 통한 데이터 복구
추출된 output.sql 파일에서 필요한 INSERT와 UPDATE 문을 확인하고, 이를 데이터베이스에 적용하여 데이터를 복구하였습니다.

향후 개선 사항

이번 복구 과정을 통해 몇 가지 개선할 점을 발견하였습니다.

자동화 도구 활용

python binlog2sql.py -h <프로덕션 호스트> -u <계정> -p<비밀번호> \
--start-file='binlog.000015' --stop-file='binlog.000016' \
--databases=<DB명> --output-file=output.sql

수동으로 binlog를 파싱하는 대신, binlog2sql과 같은 도구를 사용하면 더욱 효율적으로 복구 작업을 수행할 수 있습니다. 이 도구는 binlog를 직접 파싱하여 DDL과 DML 쿼리문을 생성해주므로, 수작업을 최소화할 수 있습니다.

Point-in-Time Recovery 활용

mysqlbinlog --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 23:59:59" \
binlog.000015 binlog.000016 | mysql -u <계정> -p<비밀번호> -h <데이터베이스 호스트> <DB명>

MySQL의 Point-in-Time Recovery 기능을 활용하면 특정 시점까지의 데이터를 복구할 수 있습니다. 이를 통해 binlog를 사용하여 원하는 시점까지 데이터를 복원할 수 있으며, 수동으로 SQL 문을 파싱하지 않아도 됩니다.

복구 진행 시 참고한 자료

MySQL 공식 문서 - mysqlbinlog
https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html
Amazon RDS에서 MySQL 바이너리 로그 액세스하기
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQL.Binarylog.html
Percona 블로그 - binlog2sql을 활용한 포인트 인 타임 복구
https://www.percona.com/blog/binlog2sql-binlog-to-raw-sql-conversion-and-point-in-time-recovery/

추가로 알아두면 좋은 점

binlog_format 설정 확인
```
SHOW VARIABLES LIKE 'binlog_format';
```
MySQL의 binlog_format 설정에 따라 binlog에 기록되는 내용이 달라집니다. 일반적으로 복구를 위해서는 ROW 또는 MIXED 포맷이 더 유용합니다.
binlog 보관 기간 설정
```
SET GLOBAL expire_logs_days = 7;
```
데이터 복구를 대비하여 binlog의 보관 기간(expire_logs_days)을 적절히 설정하는 것이 중요합니다.
정기적인 백업 수행
binlog를 이용한 복구는 최후의 수단일 수 있으므로, 정기적인 데이터베이스 백업을 통해 데이터 소실에 대비해야 합니다.

결론

이번 경험을 통해 binlog의 중요성과 이를 활용한 데이터 복구 방법에 대해 깊이 있게 이해하게 되었습니다. 향후에는 자동화 도구와 MySQL의 내장 기능을 적극 활용하여 더욱 효율적으로 데이터 복구를 진행할 수 있을 것으로 기대합니다.

Software Engineer Ethan