실수로 날린 데이터를 바이너리 로그를 활용하여 복구하는 방법에 대해 알아보겠습니다.
참고로 아래 예제는 한개의 바이너리 로그 파일 안에 모든 쿼리가(insert, truncate) 다 들어있다는 가정하에 진행됩니다.
Drop, Turncate, Delete, Update 등 데이터에 변화를 줄 수 있는 쿼리는 꼭!! 트랜잭션 을 걸고 실행하길 바랍니다.
✅ 바이너리 로그
Mysql에는 바이너리 로그라는 개념이 있다.
DB에 대한 모든 변경 사항의 레코드를 포함하는 로그 파일로 SELECT, SHOW 와 같이 데이터에 영향을 주지 않는 쿼리를 제외하곤 모두 기록된다.
위 바이너리 로그를 기반으로 데이터베이스 레플리케이션이 가능하다. 바이너라 로그를 읽어 레플리카에 릴레이 로그로 저장한 다음 복제를 진행한다.
Truncate, Drop 등 명령어를 잘못날려 데이터가 날라간 경우에도 바이너리 로그를 이용하여 복구할 수 있다.
📌 활성화 여부 확인
바이러니 로그가 활성화 되어있는지 확인해봐야 한다.
활성화가 안되어 있다면 다른 복구 전략을 고려해봐야 한다…
1
SHOW VARIABLES LIKE 'log_bin';
📌 파일 목록 확인
가장 밑에 있는 파일이 현재 로그가 쌓이고 있는 파일이다.
1
SHOW BINARY LOGS;
📌 파일 위치 확인
1
SHOW VARIABLES LIKE 'log_bin_basename';
✅ 시나리오 설정
1
2
3
INSERT INTO `testDB`.test(text) VALUES ('text1');
INSERT INTO `testDB`.test(text) VALUES ('text2');
TRUNCATE TABLE `testDB`.test;
위와 같이 데이터를 넣어주고 있는데 실수로 TRUNCATE 명령어로 데이터를 날린 상황이다.
여기서 주의깊게 봐야할건 한 바이너리 파일 안에 백업에 필요한 INSERT 쿼리가 다 있는 것이다.
📌 바이너리 파일 FLUSH
아래 명령어는 현재 작성중인 로그 파일을 닫고 새로운 바이너리 로그파일에 열어 명령어를 저장하는 것이다.
명령어를 실행하면 파일이 한개 더 생성되는 것을 확인 할 수 있을 것이다.
1
FLUSH LOGS;
📌 Mysqlbinlog
Mysqlbinlog 유틸리티는 로그파일을 텍스트 파일로 변환할 수 있게 지원해주는 기능이다.
MySQL에서 기본으로 제공되므로 바로 명령어를 사용할 수 있다.
바이너리 파일을 직접적으로 읽을 수 없으므로 .sql 파일로 변환해야 한다.
파일 저장 경로는 위에서 확인했으므로 아래 명령어를 파일 위치에 맞게 수정해주면 된다.
1
2
3
4
5
6
7
8
9
# 하나의 로그 파일만 변환하기
sudo mysqlbinlog /usr/local/mysql/data/binlog.000026 > binlog.sql
# 여러개 지정하여 단일 파일로 변환하기
sudo mysqlbinlog /usr/local/mysql/data/binlog.000025 /usr/local/mysql/data/binlog.000026 > binlog.sql
# 와일드카드를 이용하여 단일 파일로 변환하기
mysqlbinlog /usr/local/mysql/data/binlog.0* > binlog.sql
✅ 복구하기
파일을 vim으로 열어 TRUNCATE 쿼리를 주석처리 한 후 로그에 저장된 쿼리를 다시 실행해 주면 된다.
📌 TRUNCATE 쿼리 주석처리
1
vi binlog.sql
📌 Mysql에 쿼리 실행
1
2
# -f: force 실행(여러가지 충돌이 날 수 있어 강제 처리)
mysql -u root -p -f testDB < binlog.sql
데이터를 조회하면 정상적으로 복구된 것을 확인할 수 있다.
마치며
역시 백업은 중요한 것 같다. 위 예제는 간단한 예제이지만 실무에서는 더욱 복잡하게 얽혀있을 것이다.
유실된 데이터를 복구하는 것도 중요하지만, 데이터를 실수로 날리는 것을 방지하는 것도 중요한 것 같다.