주진 이
안녕하세요 오늘은 예기치 못한 데이터 망실 또는 장애 발생 시 기존에 백업해놓은 데이터가 없을 경우, mysql bin-log 복구 방법에 대해서 설명해보도록 하겠습니다.
binary log 란?
: bin log는 DDL문과 DML 문을 통해 mysql data가 변경되는 경우 변경된 이벤트를 적용하여 기록하는 로그 파일이다.
binary log 의 용도
1. 리플리케이션 구성 시 사용
mysql replication 구성 시 마스터 서버에서 bin-log가 생성되고, 슬레이브 서버에서 마스터 서버에 접속하여 마스터 서버의 bin-log를 읽어와 슬레이브 서버와 동기화 시킨다.
2. 데이터 복구 시 사용
서버를 운영함에 있어서 장애 발생 또는 관리자의 실수로 데이터가 망실될 때, bin-log를 통해서 데이터를 복구할 수 있다.
※ bin-log 활성화 시, mysql에서 bin-log 생성 및 제어 작업을 진행하기 때문에 db 성능이 약간 느려질 수 있음. 그렇지만 bin-log는 장애 발생 시 데이터를 복구하기 위해 활성화해서 사용하는것이 좋다.
bin 로그 경로 확인 및 설정
아래와 같은 설정이 없다면, bin-log 파일에 관한 설정을 추가해주어야 파일이 생성됩니다.
1. my.cnf 파일에서 확인
# vi /etc/my.cnf
log-bin = mysql-log // bin-log 로그 경로
binlog_cache_size = 2M // bin-log cache 사이즈
max_binlog_size = 1G // bin-log 최대 사이즈 (ex. 1G로 설정 시, bin-log 1G 용량이 다 차면, 로테이션되어 다음 bin-log 파일에 로그가 쌓임)
expire_logs_days = 14 // bin-log 보관 기간. 일(day) 단위이다.
==> 설정파일 수정 후 재시작해주어야 설정 내용이 적용됩니다.
2. mysql 접속하여 확인
mysql > use mysql;
mysql > show binary logs;
bin-log 삭제
1. bin-log 경로에서 직접 삭제
# rm -rf /usr/local/mysql/data/binlog.000001
2. mysql 접속하여 삭제
mysql > purge master logs to ‘binlog.000001’;
bin-log 보관 기간 확인
버전에 따라 조회 방법이 다르나, mysql 8.x 버전부터는 binlog_expire_logs_seconds 사용한다.
보관 기간은 second(초) 단위이다.
mysql > show global variables like ‘binlog_expire%’;
mysql > show global variables like ‘expire_logs_days%’;
백업을 주기적으로 하지 않는다면 해당 설정을 넣지 않고 최대한 예전 빈로그로 데이터 손실을 최소화하며 복구할 수 있다.
하지만 데이터베이스 용량이 큰데 해당 설정을 넣지 않았다면, 로그가 계속해서 쌓여 용량이 꽉 차는 문제가 발생할 수 있음
bin-log 파일을 이용한 백업 방법
1. 데이터베이스 전체 복구
백업을 원하는 가장 최근 빈로그 파일을 sql파일로 변환하여 추출
# mysqlbinlog /usr/local/mysql/data/binlog.000001 > all_backup.sql
bin log의 경우 DDL문과 DML문을 모두 포함하기 때문에, mysql 설치 후 사용한 DDL, DML 문이 모두 기록되어 있다.
그러므로, bin-log.sql 파일 자체에 DB, TABLE 생성 명령어가 존재한다면, DB를 모두 초기화 한 후 넣어주어야한다. (이건 직접 sql 파일을 열어서 확인하는것이 빠름)
mysql 소스 설치와 패키지 설치에 따라 DB 초기화 명령어가 다르다.
[소스의 경우]
# /usr/local/mysql/bin/mysql_install_db
[패키지의 경우]
# mysql_install_db
만약, 생성한 bin-log.sql 파일에 DB와 TABLE 생성 명령어가 없다면, 기존에 존재하던 데이터베이스들을 생성 후 넣어주어야한다.
예를 들어 초기화하기 전, 기존에 존재하던 데이터베이스가 TEST1, TEST2, TEST3 이라고 가정하면, 데이터베이스 생성 명령어를 통해 DB 생성 후, 복구해야 정상적으로 복구된다.
mysql > create database TEST1;
mysql > create database TEST2;
mysql > create database TEST3;
mysql > flush privileges;
추출한 sql 파일 밀어넣기
방법1) mysql 명령어 통해 밀어넣기 (밀어넣는동안 에러가 뜨면 작업이 중단되면서 에러문구가 뜬다.)
# mysql -uroot -p < all_backup.sql
방법2) mysql 접속하여 밀어넣기 (밀어넣는동안, 쿼리가 들어가는것을 실시간으로 볼 수 있다.)
mysql > \.all_backup.sql
mysql 재시작
# /etc/init.d/mysqld restart
2. 특정 데이터베이스 복구
-d 옵션을 사용하여 bin-log.sql 파일 생성 후 복구한다.
# mysqlbinlog /usr/local/mysql/data/binlog.000001 -d TEST1 > TEST1.sql
생성한 sql 파일을 밀어넣는 방법은 위에 기재한것과 동일.
언제나 장애에 대비하여 최신 백업본을 가지고 있는 것이 중요하지만, 만약의 경우에 백업본이 존재하지 않는다면 binlog를 이용하여 복구를 시도해보는 방법도 있다.
하지만 혹시 모르니 bin-log 파일로 복구를 시도하기 전 기존 데이터도 백업을 진행해놓고 작업하는것을 추천한다.
더 추가적인 내용을 공부하고 싶으신 분은 mysql 공식 홈페이지 문서를 참조하시면 됩니다.
이번 글에서는 mysql bin-log 복구 방법에 대해서 알아보았습니다.
다음에 또 유익한 포스팅을 가지고 찾아오겠습니다!