bin log

bin log 이란

• MySQL 의 빈로그 혹은 바이너리 로그는 MySQL 서버 인스턴스의 데이터 변경사항들에 대한 정보를 포함하는 로그 파일의 세트이다.

• 여기에는 에러코드, 바이너리 로그 자체에 대한 메타데이터 등 다양한 데이터가 같이 포함되게 된다.

• 기본적으로 Transaction Commit 시에 기록되어지며, 데이터 변경 순서를 보장한다는 특징이 있다.

• 주로 복제(Replication) 및 복구(Recovery)를 목적으로 binary log 가 사용되어지며, 복제 시에는 Secondary Node 가 Primary Node 로부터 binlog 데이터를 전달받아서 로깅하게 된다. (그리고 전달받아 로깅하는 이 로그를 릴레이 로그 라고 한다)

MySQL 에서 제공하는 바이너리 로그에는 3가지 종류가 있다.

(1) Statement-based logging

• Insert, Update, Delete 에 대한 SQL 문들이 포함된다.
• Statement base 로 복제를 수행 시 Statement-Based Replication (SBR) 이라고 한다.
• 이 방식에서는 로그 파일에 적은 데이터가 기록되며, 로그 파일에 필요한 저장 공간이 줄어드는 장점이 있다.
• 백업에 대한 복구는 Replay 처럼 수행되며 빠르게 복원이 수행될 수 있다.
• 다만 로그 기반으로 복원 시 제약이 조금 있는데, 예를들어 RAND(), LOAD_FILE, UUID() 등과 같이 Deterministic 하지 않은 동작에 대해서는 정확히 복제가 안된다고 보면 된다. (당연히 RAND() 를 복원해서 나온 두 번의 결과가 같을리는 만무하다!)

(2) Row-based logging

• 이 방식은 각 행에 대한 변화를 기록한다.
• Row-based logging 을 이용해서 Primary → Secondary 로 복제를 수행할 수 있고, 이를 Row-Based Replication(RBR)이라 한다
• RBL 의 경우 각 행의 변경 사항을 이진 로그에 기록하므로 로그 파일의 크기가 매우 빠르게 증가할 수 있으며 지연이 발생할 수도 있다.
• 또한, 임시 테이블의 경우 RBL 기반으로 복제되지 않으므로 임시 테이블 관련 구문은 Statement base 로 기록되어야 한다.

(3) 혼합형

• MySQL 5.1 이상부터 Row-based logging 과 함께 혼합형태가 지원되어진다.
• 기본적으로는 Statement based logging 을 사용하지만, 스토리지 엔진 및 특정 명령문에 따라 로그가 자동으로 Row based logging 으로 기록되어진다.

mysqlbinlog 유틸리티를 사용하면 바이너리 로그에 대한 내용을 쉽게 조회해볼 수 있다.

바이너리 로그와 헷갈리는 개념이 MySQL 의 Transaction log (트랜잭션 로그) 라는 개념이다.

binlog 는 데이터베이스에 기록 및 업데이트한 이력(History)을 로깅하는 개념이고, 이를 기반으로 상태 복원에 대해 핵심적으로 사용될 수 있는 Incremental Backup 을 지원한다.

반면 transaction log(redo log) 는 트랜잭션에 대한 처리, 롤백, Crash Recovery, Point In Time Recovery 등을 위한 용도로 사용되어지게 된다.

MySQL 에서 트랜잭션은 InnoDB 스토리지 엔진만 사용하므로, MyISAM과 같은 스토리지 엔진에서는 binlog 만 사용하게 된다.

Point In Time Recovery 가 트랜잭션 로그를 통해서 지원되므로, 어떤 데이터베이스 솔루션 등을 쓴다라고 할 때에는 Transaction Log 에 대한 지원을 살펴볼 필요가 있겠다.

DB 서버 밖에서 설정 (my.cnf)

DB 설정파일에서 설정

vi /etc/my.cnf

server-id=000001 # mysql서버id
user=mysql # mysqld유저
log-bin=mysql-bin # binlog파일명
datadir=/data/mysql # binlog 등 출력할 디렉토리
binlog_format=MIXED # binlog포맷 ////(0:MIXED, 1:STATEMENT, 2:ROW) ★
socket=/tmp/mysql.sock # UNIX도메인 소켓
binlog_cache_size = 2M #바이너리 로그 캐시 사이즈
max_binlog_size = 512M #바이너리 로그 최대 사이즈
expire_logs_days = 10 #보관 기간(만료기간)

bin log 포맷 형식 설명

MySQL 5.7.6 까지는 기본값이 statement 이고 그 이후 버전부터는 row 가 기본값이다. (예외적으로 클러스터는 mix) 빈로그 포맷은 동적변수이므로 DB가 가동중일때도 set 으로 변경할 수 있지만 리플리케이션일 경우에는 마스터/슬레이브를 내리고 변경하는게 좋다.

binlog 포맷 형식에는 3가지가 있는데 상황에 맞게 적절하게 써야한다.

• statement

  • 쿼리문으로 기록되기 때문에 용량을 적게 차지하고 버전특성을 타지 않는다.
  • 하지만 복구시 일관된 데이터에 대한 보장이 적으며(sysdate()와 now()의 다른 결과등..)
  • 쿼리기반이기 때문에 복구(동기화)시 굉장히 느려질 수 있다.

• row

  • statement 형식과 반대 개념으로 쿼리문이 아닌 변경된 데이터 기반으로 기록된다.
  • 장단점 역시 statement 과 정반대로 용량이 커지고 복구시 일관성을 보장받을수 있으며 빠른 복구(동기화)가 가능하다.

• mixed

  • statement 와 row 방식의 장점을 취합한 형태로 기본적으로
  • statement 방식을 취하나 필요에 따라(일관성 보장등..) row 방식으로 기록한다.
  • 데이터 일관성을 위해 mixed 나 row 방식을 선택하는게 백번 맘 편하다.

주의

isolation level이 read-committed 일 경우 현재 트랜젝션이 종료되지 않았더라도 다른 트랜젝션에서 동일한 데이터의 commit 이 일어나면 현재 트랜젝션에서도 변경된 값이 보이게 된다. 이런 환경에서 statement 방식을 사용하게 되면 트랜젝션 단위로 순서대로 로깅하기 때문에 복구나 슬레이브동기화시 원하는 바와 달리 다른 결과가 나타날수 있다. 그렇기 때문에 read-committed 에서는 row 방식이나 이런 상황에서 자동으로 row 형태로 변경해주는 mixed 방식을 사용해야 한다.

DB 재시작

$ /etc/init.d/mysqld restart
$ systemctl restart mysql
$ systemctl restart mysqld
$ systemctl restart mariadb
$ systemctl restart MariaDB

DB 서버 안에서 bin log 확인

mysql -u root -p

binary log 파일 목록 확인
mysql> show binary logs;

binary log 캐시 크기 확인
mysql> show variables like 'binlog_cache_size';

binary log 최대 크기 확인
mysql> show variables like 'max_binlog_size';

binary log 보관기간 확인
mysql> show variables like 'expire_logs_days';

bin log 관리

1. 서버밖 my.cnf 설정변경

expire_logs_days = 10 #보관 기간(만료기간) 변경
설정파일 변경 후 재시작

2. DB 서버 안 (MySQL실행 안에서)

mysql> show variables like '%expire%'; # 현재 만료기간 확인
mysql> set global expire_logs_days=7; # 만료기간 7일로 변경

binary log 삭제

mysql> show binary logs;
mysql > purge master logs to 'mysql-bin.******';

예시

mysql> purge master logs to 'mysql-bin.000016'; 지정된 binary 를 제외한 "이전"의 바이너리로그 파일 모두 삭제
(mysql-bin.000016 이전 로그들은 모두 삭제함.)

bin log를 이용한 데이터 복구

일반 쿼리로 변경하는 방법(서버밖 서버접속시 현재디렉토리에 기준이다.)
mysqlbinlog mysql-binlog.00001 > backup1.sql

특정 데이터베이스의 특정 날짜시간 동안의 이용한 특정 시간대 복구

time-based recovery 응용
mysqlbinlog --database=DB이름 --start-date="시작날짜" --stop-date="종료날짜" "mysql bin로그 경로" > binlog1.sql

예시)
mysqlbinlog --database=funshop_db --start-datetime="20140101 00:00:00" --stop-datetime="20140101 23:59:59" mysql-binlog.00001 > backup1.sql

특정 데이터베이스의 지정된 포지션 사이의 bin로그 텍스트로 변환

mysqlbinlog --database=DB이름 --start-position=1 --stop-position=100000 "mysql bin로그 경로" > binlog1.sql

mysqlbinlog 옵션

--database (-d) : 지정한 데이터베이스에대한 쿼리만 추출한다
--start-datetime, --stop-datetime : 복구를 시작할날짜, 복구할 마지막 날짜를 지정한다
--short-form (-s) : 주석처리된 내용은 추출대상에서 제외한다

binlog 백업 진행시 mysqlbinlog: unknown variable 'default-character-set=utf8
-> my.cnf 설정 에 default-character-set=utf8 옵션이 추가된 상태에서 mysqlbinlog 를 실행할경우 발생되는 에러
--no-defaults 라는 옵션을 사용하여, 기본 디폴트 옵션을 제외시켜서 mysqlbinlog 를 실행하면 된다.

mysqlbinlog --no-defaults -s --start-datetime="2015-11-24 16:53:21" --stop-datetime="2015-11-26 17:50:30" -d 대상DB명 mysql-bin.000081 > restore1.sql
또는
mysqlbinlog --no-defaults --database=[DB명] ./mysql-bin.0* > .[DB명].sql

참고 자료

• https://93it-serverengineer.co.kr/86