MySQL 바이너리 로그파일을 이용한 데이터 복구

1. 개요

바이너리 로그는 데이터베이스에 대한 모든 변경 사항의 레코드를 포함합니다. CREATE, ALTER, INSERT,
UPDATE, DELETE 와 같이 데이터 변경과 관련된 모든 기록이 저장됩니다. SELECT 나 SHOW 와 같은 데이터
에 영향을 주지 않는 질의의 경우 기록되지 않습니다. 바이너리 로그의 내용을 기반으로 하여 마스터 서버에서
슬레이브 서버로 데이터를 전송하고 복제(replication)할 수 있으며, 데이터를 복원하는 데 사용하기도 합니다.
DB 설치 직후부터 바이너리 로그가 온전히 남아 있다면 DB 복구에 문제가 없지만, 일부만 남아 있다면 제한
적으로 복구할 수 있습니다.
바이너리 형태로 저장되기 때문에 일반 편집기로는 내용 확인은 불가능합니다. mysql 에서는 바이너리 로그
를 볼 수 있도록 mysqlbinlog 라는 유틸리티를 제공합니다.
본 매뉴얼에서는 MariaDB 10.2.8 버전을 이용하였으며, 해당 버전에 포함되어있는 mysqlbinlog 유틸리티
의 버전은 3.3 입니다.

2. 바이너리 로그 활성화

기본적으로 my.cnf에 log-bin 부분은 활성화되어 있으나, MySQL 버전에 따라 활성화되어 있지 않은 경우
도 있습니다. 그럴 경우 해당 항목을 추가한 후, DB 를 재시작하면 됩니다. 용량 및 관리 문제로
expire_logs_days 라는 항목을 추가해주는 경우도 있는데, 백업을 주기적으로 하지 않는다면 사용하지 않는 것
이 복구하는데 있어서는 좋습니다.

 

# vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin
expire_logs_days = 7

3. 바이너리 로그 확인

mysql 데이터 폴더에서 ls 를 이용하여 확인하거나, mysql 에서 show 명령어를 이용하여 확인 가능합니다.

 

# ll /usr/local/mysql/data

 

MariaDB [(none)]> SHOW BINARY LOGS;

4. mysqlbinlog 유틸리티 사용법

   1) 기본 사용법
       기본적으로 원하는 바이너리 로그를 mysqlbinlog 유틸리티를 이용하여 불러오면 됩니다. 필요하면 다른 필터 명령어를 

       묶어서 사용할 수도 있습니다. 또한 별도의 파일로도 추출할 수 있다.

 

       # mysqlbinlog /usr/local/mariadb/data/mysql-bin.000001 > 1.sql

          → mysql-bin.000001 에 기록된 내용 을 1.sql 이란 파일로 저장

 

   2) 주요 옵션
       mysqlbinlog 유틸리티는 옵션을 넣어서 원하는 방식으로 binlog 를 추출할 수 있습니다. 많은 옵션이 있지만, 

       주로 사용하는 옵션은 다음과 같다.

--database=[DB 이름]	-d	특정 데이터베이스의 내용만 출력
--short-form	-s	일반 쿼리만 출력. 모든 정보를 출력하기 원하지 않을 때
--start-datetime="YYYY-MM-DD hh:mm:ss"		지정한 시간부터 binlog 추출
--stop-datetime="YYYY-MM-DD hh:mm:ss"		지정한 시간까지 binlog 추출

 

 

5. 바이너리 로그 파일을 이용한 복구

   1) 모든 DB 복구

       바이너리 로그가 최초 DB 가 실행되던 시점부터 정상적으로 기록되어 있을 경우 사용할 수 있다. 매일 mysqldump 유틸리티를

       이용하여 DB 를 백업하지 않아도 복구가 가능합니다. 바이너리 로그를 OS 영역이 아닌 다른 디스크에 주기적으로 백업을 한다면

       OS 디스크가 망가져도 백업된 시점까지는 복구가 가능하다.
       여기에서는 OS 및 MySQL 설치 후 복구한다고 가정하였습니다.       

 

# mysqlbinlog /backup/binlog/mysql-bin.0* > all.sql // 별도의 디스크에 저장된 바이너리 로그 추출
# mysql -uroot -p < all.sql  // 추출한 파일을 이용하여 내용 밀어넣기
# /etc/init.d/mariadb restart // 적용을 위해 DB 재시작

 

2) 특정 DB 복구

 

        특정 DB 만 정상적으로 작동하지 않으면서 DB 백업본이 따로 없을 때 사용할 수 있습니다. 시간이 한참 지난 후 DB 가 정상적으

       로 작동되지 않는다는 것을 확인하여 언제까지 정상작동했는지는 모르는 상황이라고 가정한다.

       

<사전작업>

MariaDB [(none)]> CREATE DATABASE hong;

MariaDB [(none)]> USE hong;

MariaDB [hong]> CREATE TABLE TEST1 ( num int(10) not null, name varchar(10) not null );

MariaDB [hong]> CREATE TABLE TEST2 ( num int(10) not null, name varchar(10) not null );

MariaDB [hong]> DROP TABLE TEST1;

 

<시나리오>

hong이라는 DB를 생성하고 TEST1, TEST2 테이블을 생성했는데, 실수로 TEST1테이블을 삭제해서 복구해야 하는 상황

        이 경우 바이너리 로그에서 해당 DB 만 추출한 후, 문제를 야기한 쿼리를 파악해야 합니다. 문제가 되는 쿼리를 주석처리하여 

       복구시 해당 쿼리가 들어가지 않도록 설정해주면 된다. 여기에서는 -s 옵션을 줘서 쿼리를 쉽게 파악할 수 있도록 하였다.

 

 

# mysqlbinlog -d hong -s mysql-bin.0* > hong.sql  // hong 라는 DB 추출

 

# vi hong.sql  // 추출한 파일을 열어 문제가 되는 쿼리 주석처리

 

# mysql -u root -p < hong.sql  // 수정한 파일을 DB에 밀어넣기

 

오류1)

이미 hong이라는 DB가 있어서 DB생성하는 쿼리도 주석처리

 

# mysql -u root -p < hong.sql  // 수정한 파일을 DB에 밀어넣기

 

# mysql -u root -p

Enter password:

 

MariaDB [(none)]> USE hong;
MariaDB [hong]> SHOW TABLES;

 

복구된 것을 확인 할 수 있다.

 

 

3) 특정 시점의 특정 DB 복구

       특정 DB 가 정상적으로 작동했던 시점을 알고 있거나 문제가 발생한 시점을 알고 있을 때 사용합니다. 매일 mysqldump 로 

       백업을 하고 있지만, 문제가 발생한 시점이 다를 경우 다음과 같은 방법을 이용하면 데이터 망 손실을 최소화시킬 수 있다.           

       여기에서는 1 차적으로 기존의 백업본으로 DB 복구를 한 후, 바이너리 로그를 가지고 추가적으로 복구를 진행하였다. 

       백업은 매일 2 시에 진행되며, 11 시까지는 정상적으로 작동했다고 가정한다.

 

# mysql -uroot -p textcube < 20210323_hong.sql   // 백업된 DB 를 이용하여 1 차 복구

// 3 월 23 일 2 시부터 11 시까지 textcube DB 추출
# mysqlbinlog --start-datetime="2021-03-23 02:00:00" --stop-datetime="2021-03-23 11:00:00" -d hong mysql-bin.0* > hong.sql

# mysql -u root -p < hong.sql // 추출한 파일 DB 에 밀어넣기