Binary Log(binlog) 사용하기

Binary Log(binlog) 사용하기

binlog(쉽게 빈로그라 읽고 쓰자..)는 서버내에서 발생되는 모든 변경내역이 기록되는 파일이다.

PIT(시점복구)와 리플리케이션에 필수 요소이며 이 2가지 기능을 쓰지 않는다면 굳이 활성화 할 필요는 없다.

빈로그를 쓰는것 자체가 미세하지만 IO 부담을 가중시키기 때문이다.

빈로그 활성화 하기

빈로그를 활성화 할 수 있는 방법은 2가지다.

1. 서버 구동시에 --log-bin 옵션을 준다.

[root@localhost]# mysqld_safe --log-bin=systemv-bin-log &

--log-bin=VALUE 과 같은 형식으로 사용할수 있는데 VALUE에는 파일경로/파일명을 쓸수 있다.

VALUE값을 생략하면 data_dir 디렉토리에 hostname-bin 이라는 이름으로 생성된다.

(라고 모든 MySQL 공식 도큐먼테이션에서 밝히지만 5.5버전 이후에서 테스트 했을시 mysqld-bin 으로 생성되더라.

hostname 변경시 발생될 수 있는 변수 상황을 피하기 위함이라 생각한다.) 

2. my.cnf 에 작성

my.cnf의 [mysqld] 섹션에 log_bin 옵션을 설정해준다.

log_bin=VALUE 형식으로 작성할수 있으며 VALUE에 대한 내용은 위에 구동시 내용과 동일하다. 

어떤방식으로 활성화를 하든 생성되는 빈로그에는 시퀀셜한 넘버링이 자동으로 붙게된다. (mysqld-bin.000001)

빈로그 활성화 여부는 show 로 확인 가능하다.

mysql> show variables like 'log_bin';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

1 row in set (0.00 sec)

show 로 확인되는 log_bin 값과 my.cnf 에 작성하는 log_bin 은 전혀 다르므로 헷갈리면 안된다.

show 의 log_bin 은 현재 빈로그의 활성화 유무만을 보여주는 변수값일 뿐이고 

my.cnf 에 작성되는 log_bin 은 빈로그를 활성화 시키면서 경로까지 지정해주는 설정 옵션이다.

어떤 방식으로 활성화 해도 결과는 동일하지만 서버 구동시마다 잊지 않고 옵션을 넣어 주는게 힘들기 때문에 

my.cnf 설정법이 주로 쓰인다.

추가 설정 옵션

빈로그 사용시 기본적으로 같이 쓰는 옵션이 몇개 더 있으니 넘어가듯 살펴보자.

- log-bin=bin-log

data_dir에 bin-log.000001 같은 형식으로 빈로그를 남기고...

 

- binlog_format=row

빈로그 데이터 포맷을 row 형식으로 지정하고...

- binlog-do-db=test-db

test-db 라는 DB에 대한 내용을 남기는데...

- max_binlog_size=256M

빈로그 파일이 256M가 넘어가면 bin-log.000002 로 자동 로테이트 하면서 ...

- expire_logs_days=4  

4일 이상 오래된 빈로그는 삭제하도록 한다.

빈로그 포맷

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 방식을

사용해야 한다.

PIT(Point In Time)복구

보통 사용량이 낮은 새벽시간에 디비백업을 하는데 그 이후 장애가 발생했다면 백업과 장애발생 시점 사이의

데이터를 잃을수 밖에 없다. 그래서 백업된 데이터를 활용해 1차로 복구하고 빈로그를 통해 PIT 복구를 추가로

진행하게 된다.  

xtrabackup 을 이용해 백업된 데이터를 먼저 복구하고

innobackupex --apply-log /backup_data

innobackupex --copy-back /backup_data

백업된 데이터의 마지막 포지션이 빈로그와 겹치는 파일부터 포지션을 지정해 먼저 적용하고

mysqlbinlog --start-position=222222 mysqld-bin.000003 | mysql -u root -p

그 이후 부터 현재까지 생성된 빈로그도 전부 적용시키면 PIT 복구까지 완료된다.

mysqlbinlog mysqld-bin.000004 | mysql -u root -p

mysqlbinlog mysqld-bin.000005 | mysql -u root -p

mysqlbinlog mysqld-bin.000006 | mysql -u root -p

* start-position 대신 start-datetime 을 쓸수도 있으나 동일한 시간대에 실행된 원치않은 다른 쿼리나 이벤트가

재실행 될수도 있기 때문에 position을 쓰는걸 권장한다.

빈로그 원격저장

PIT복구등에 반드시 필요한 빈로그이지만 예전에는 로컬에만 저장되었기 때문에 디스크나 서버자체에 장애가 생기게 되면

복구하는데 어려움이 많았다. 하지만 MySQL5.6 이상, MariaDB 10.0 이상부터 빈로그를 원격에 저장할 수 있는 기능이

생겼다. 물론 이전에도 rsync나 scp, ftp 등을 이용해 원격에서도 백업할수 있었지만 지금 쓰고 있는 빈로그는 백업할

수가 없었다. 맥스사이즈를 넘어서거나 DB재가동 등으로 스위칭 된 빈로그만 백업이 가능했기때문에 스위칭되는 간격동안의

데이터는 유실될수밖에 없다는 단점이 있었다.

새로 추가된 원격저장은 지원버전 이상의 mysqlbinlog(예전부터 빈로그를 관리하던 커맨드에 옵션만 추가됨)를 사용해 

실행할 수 있다. 기존에 사용하던 실행파일을 그대로 쓰는건 좋은거 같다.

실행문을 보기 전에 기본적으로 필요한 옵션 몇가지만 정리해보자.

--read-from-remote-server 

빈로그를 원격에서 저장하기 위한 핵심옵션으로 이 옵션이 사용되면 당연히 --host(DB주소), --user(접속계정),

--password(패스워드), --port(접속포트) 등의 옵션도 함께 사용해야한다.

--raw 

이 옵션이 없으면 원격서버에서 빈로그를 텍스트 형태로 가져오게 된다. 일반적으로 실서버에 있는 빈로그 그대로를

백업하기 원하기 때문에 이 옵션을 반드시 쓰도록 한다.

--stop-never

빈로그가 존재하는 실서버의 디비가 내려가거나 커넥션이 끊어지지 않는한 용량이 커지건 파일이 바뀌건 죽지않고

계속 빈로그를 가져오게 된다.

--stop-never-slave-server-id

stop-never 옵션을 쓰게 되면 백업서버가 리플리케이션의 슬레이브처럼 동작하게 된다. 리플리케이션 내에서는

server_id가 중복될수 없기때문에 해당 옵션으로 server_id를 지정해주는 것이다. 물론 생략도 가능하며

생략하면 65535로 셋팅된다.

--result-file

가져온 빈로그 파일명에 Prefix를 붙여준다. 

--result-file=orig-binlog- 라면 orig-binlog-mysqld-bin.000001 처럼 저장된다.

원본과 동일하게 저장하고자 하면 잴끝에 "/" 를 포함해 디렉토리명까지만 지정해주면 된다.

--result-file=/bin_backup_dir/

--to-last-log

이 옵션을 쓰면 지정된 빈로그 뿐만 아니라 그 이후에 생성된 모든 빈로그를 가지고 온다. --stop-never 옵션을 쓰면

자동으로 활성화되는 옵션이므로 굳이 지정해 쓸 일은 없다.

위 옵션들을 참고해 실행문을 만들어 보면 아래와 같다.

mysqlbinlog --read-from-remote-server --raw --stop-never --host=192.168.0.100 \

--user=root --password=pwpw mysqld-bin.000001

스크립트

원격 빈로그백업의 단점이 원서버가 재시작되거나 다른이유로 커넥션이 한번 끊기면 자동 재접속을 하지 않는다는 것이다.

게다가 실행문 마지막에 백업할 빈로그 파일명을 하나 지정해줘야하는데 그때마다 파일명을 확인하는게 굉장히 번거롭다.

그래서 스크립트로 만들어 쓰는게 좋다.

---

#!/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