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 으로 변경할 수 있지만 리플리케이션일 경우에는 마스터/슬레이브를 내리고 변경하는게 좋다.

빈로그 포맷 형식에는 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
===============================================================================

스크립트

원격 빈로그백업의 단점이 원서버가 재시작되거나 다른이유로 커넥션이 한번 끊기면 자동 재접속을 하지 않는다는 것이다. 게다가 실행문 마지막에 백업할 빈로그 파일명을 하나 지정해줘야하는데 그때마다 파일명을 확인하는게 굉장히 번거롭다. 그래서 스크립트로 만들어 쓰는게 좋다.
===============================================================================
#!/bin/bash

mysqlbinlog="/sbin/mysqlbinlog"
mysql_server="192.168.0.49"
server_name="db_server01"
backup_dir="/data1/binlog_backup/$server_name"

user="root"
password='dbpassword'

mkdir -p $backup_dir

while [ : ] ;
do
last_file=`ls -1 $backup_dir | grep -v orig | tail -n 1`
if [ -z "$last_file" ] ; then
last_file=`echo "show binary logs" | mysql -Ns -u $user -p$password -h $mysql_server | awk '{if(NR==1)print $1}'`
else
now=`date +"%s"`
file_size=`stat -c%s $backup_dir/$last_file`

if [ "$file_size" -gt "0" ] ; then
mv $backup_dir/$last_file $backup_dir/$last_file.orig_$now
fi
touch $backup_dir/$last_file
fi
$mysqlbinlog --raw --read-from-remote-server --stop-never --host=$mysql_server --user=$user --password=$password --result-file=$backup_dir/ $last_file
sleep 60
done
===============================================================================