MYSQL 백업 과 복구

이하는 사내에 Mysql DB를 사용함에 있어서, 백업시스템 부제 및 DB 유실 사고에 대한 대처 방법에 대해서 정리 했습니다.

Replication은 사용중에 있으나, 인위적으로 SQL Injection 공격등에 의해 데이터가 유실 되었을때는,

Binary log를 사용해서 복구를 하는 방법등이 있습니다. 

 

1. Mysql의 바이너리 로그

바이너리 로그에는 데이터를 지금 또는 나중에 업데이트하는 모든 명령문이 기록되어 있다.

명령문은 데이터 수정을 가리키는 “이벤트 (event)” 형태로 저장된다.

바이너리 로그는 각 명령문이 데이터를 업데이트하는 소비 시간 정보도 가지고 있다.

바이너리 로그에는 SELECT 또는 SHOW와 같이 데이터를 수정하지 않는 명령문은 기록되지 않는다.

만약에 여러분이 모든 명령문을 로그하고 싶다면 (예를 들면, 문제 쿼리를 구분하기 위해), 일반 쿼리 로그를 사용하기 바란다.

바이너리 로그의 주요 목적은 서버가 복원 작업을 하는 동안에 가능한 한 전체적으로 데이터 베이스를 업데이트할 수 있도록 하는 것인데,

그 이유는 바이너리 로그가 백업이 만들어진 후에 발생한 모든 업데이트를 가지고 있기 때문이다.

또한, 마스터 리플리케이션 서버에서는 슬레이브 서버에 보내져야 하는 명령문을 기록하기 위해 바이너리 로그를 사용하기도 한다. 

*바이너리 로그 설정 (/etc/my.cnf)

  - log-bin = /data/mysql/mysql-bin          # 주석처리 하지 않았다면, 로그는 지속적으로 쌓이게 됩니다.

  - binlog_cache_size = 2M               # binlog cache 사이즈

  - max_binlog_size = 50M               # bin로그 maximum 사이즈

  - expire_logs_days = 10                    # 로그 만료기간

바이너리 로그를 활성화 시킨 상태로 서버를 구동하면 약 1% 정도의 성능 저하가 발생한다.

하지만, 복원 작업과 리플리케이션을 설정할 수 있도록 해주는 바이너리 로그의 장점은 약간의 성능 저하 보다는 더 큰 이익을 준다.

서버를 --log-bin[=base_name] 옵션으로 시작하면, mysqld는 데이터를 업데이트하는 모든 SQL 명령어가 있는 로그 파일을 작성한다.

만약에 아무런 base_name 값도 주어지지 않는다면, 디폴트 이름은 –bin이 붙어 있는 호스트 머신 이름을 사용하게 된다.

만일 베이스 이름을 지정하기는 하였지만 절대 경로 이름 (absolute pathname) 형태가 아닌 경우에는,

서버는 파일을 데이터 디렉토리에 작성한다. 베이스 이름을 지정할 것을 권장한다.

로그 이름에 확장자를 준다고 하더라도 (예를 들면, --log-bin=base_name.extension), 그 확장자는 무시되고 제거가된다.

*바이너리 로그 베이스 생성 방식

mysqld는 바이너리 로그 베이스 이름에 숫자로 된 확장자를 부여한다.

그 숫자는 서버가 새로운 로그 파일을 생성할 때 마다 증가를 하기 때문에 파일의 생성 순서가 만들어 진다.

서버가 시작되거나 또는 로그를 플러시할 때 마다 서버는 새로운 바이너리 로그 파일을 생성한다.

또한, 현재의 로그 크기가 max_binlog_size에 근접할 때에도 새로운 바이너리 로그 파일을 만든다.

만약에 트랜젝션이 여러 개의 파일로 분리되지 않고 하나로 형성되기 때문에 어쩔 수 없이 큰 트랜젝션을 사용해야 하는 경우에는 바이너리 로그의 크기가 max_binlog_size 보다 커질 수는 있다.

*바이너리 로그 인덱스 파일 

(현재 서버에서는 log-bin-index 의 설정이 없다.)

사용된 바이너리 로그 파일을 관리하기 위해서,

mysqld는 사용된 모든 바이너리 로그 파일의 이름을 가지고 있는 바이너리 로그 인덱스 파일을 생성한다.

이 인덱스 파일은 바이너리 로그 파일과 동일한 베이스 이름을 디폴트로 가지게 되며, 확장자는 '.index'가 된다.

--log-bin-index[=file_name] 옵션을 사용하면 이 인덱스 파일의 이름을 변경할 수가 있다.

Mysqld가 동작 중일 경우에는 수동으로 이 파일을 수정할 수가 없다; 그렇게 하면 mysqld가 혼동을 하게 된다.

*바이너리 로그 파일 및 바이너리 로그 인덱스 파일 기록 방식

바이너리 로그 파일 및 바이너리 로그 인덱스 파일 기록 방식은 MyISAM 테이블에 기록하는 방식과 동일하게 처리가 된다.

RESET MASTER 명령문을 사용하면 모든 바이너리 로그 파일을 삭제할 수 있으며,

PURGE MASTER LOGS를 사용하면 로그 파일의 일부분만을 삭제할 수가 있다. 

바이너리 로그 포맷에는 백업 복구에 적용될 수 있는 몇 가지 제약 사항이 있다.

Section 6.3.1, “리플리케이션 기능 및 이슈 사항” 

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01_1&m_no=22416&cat1=753&cat2=804&cat3=948&lang=k

자 그럼 복구 해보자.

1. 복구할 DB 를 하나 생성합니다.

create database 복구DB default character set utf8 collate utf8_general_ci;

2. 복구할 DB 상에 백업해놓은 schema.sql 를 집어 넣는거죠.

[복원] mysql -uroot -p  복구DB < schema.sql

cf)[백업]  mysqldump -uroot -p 복구DB > backup.sql

3. BinaryLog를 SQL로 변환 

Binary format 으로 저장되어 있기에, 파일을 조회 및 이용하기 위해서는 아래와 같이 해야 함.

-s : 주석처리된 내용은 추출대상에서 제외합니다.

[복원] mysqlbinlog -s --database=대상DB명  test-bin.0000001 | more

[복원] mysqlbinlog -s --database=대상DB명 test-bin.0000001 |../bin/mysql

[복원] mysqlbinlog -disable-log-bin -s --database=대상DB명 test-bin.0000001 > restore.sql

[복원] mysqlbinlog -disable-log-bin -s --database=대상DB명 test-bin.0000002 >> restore.sql

[상태] tail -f restore.sql

우리는   mysql-bin.0000081 ~ 000000090 까지 있었다.

이중에서 우리는 대상DB명 Database를 살릴 것이고, 

이중에서 날짜는 11-24 16:53:21 ~ 11-26 17:50:30 사이가  유실된 기간입니다

[복원] mysqlbinlog  -d 대상DB명 mysql-bin.000081 > restore.sql

[복원] mysqlbinlog  -d 대상DB명 mysql-bin.000082 >> restore.sql

....

같은 SQL 파일로 추출하는게 급선무 였습니다. 

근데... 여기서 특정 날짜 부터 값을 찾아 올수 있을까요? 가 궁금증 .....있네요!!! 

http://www.mysqlkorea.com/sub.html?mcode=manual&amp;scode=01_1&amp;m_no=22824&amp;cat1=&amp;cat2=&amp;cat3=&amp;lang=k&amp;ver_name=5.1

--start-datetime : Read binary log from first event with timestamp equal to ro later than datatime argument. 

--stop-datetime : Stop reading binary log at first event with position equal to or greater than argument

주석 제거 하고, 시작 과 종료 해서 

mysqlbinlog  -s --start-datetime="2015-11-24 16:53:21"  --stop -datetime="2015-11-26 17:50:30"  -d 대상DB명 mysql-bin.000081 > restore.sql

하지만,  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 > restore.sql

그리고 밀어 넣기!!!!

mysql -uroot -p -f  복구DB < restore.sql

끝----> 끝은 무슨 끝... 이런 복구까지 가는 것은 정말 최악의 상황이다... ;;;

2. Mysql의 데이터 Dump

show variables;

set global expire_logs_days = 7;

 (1) 전체 백업

     mysqldump -uroot -p -A > all.sql

     mysqldump -a --all-databases -uroot -psecret > dump.sql

 (2)특정 DB 백업

     mysqldump -uroot -p 특정DB명 > 특정 DB명.sql

 (3) 특정 DB에 특정 테이블 백업

     mysqldump -uroot -p 특정DB명 특정Table명 > 특정DB명.특정Table.sql

쉽다....

3. mysql DB 자동 백업

자동 백업은 무슨;;; 스트립트 만들어서 crontab 설정하려함

아래는 1시간과 일간격으로 백업을 해 놓은 것이고, 아래 링크도 좋은 가이드 인듯

http://www.noupe.com/development/10-ways-to-automatically-manually-backup-mysql-database.html

쉘스트립트를 만들어 하겠습니다. 

스크립트 구성 방식중에 DB 리스트 얻는 방법은 

db_root_pw='비밀번호'

db_list=`echo "show databases;" | mysql -N -uroot -p"$db_root_pw"`
for db in $db_list ;do
  table_list=`echo "show tables" | mysql -N -uroot -p"$db_root_pw" $db`
  for table in $table_list ; do
    mysqldump -uroot -p"$db_root_pw" $db $table > $db.${table}.sql
  done

done

자 리스트는 구해 봤으니, 이제 전체 코딩 

autobackup.sh (1시간 간격, 24시간)

#!/bin/sh

DBHOST="localhost"

DBUSER="root"

DBPWD="비밀번호"

BACKUPDIR="/usr/local/mysqlbackup/"

OPTIONS="--skip-comments --default-character-set=utf8 --routines --single-transaction --quick"

DATE=`date +%Y%m%d-%H`

DELDATE=`date --date "1 days ago" +%Y%m%d-%H`

rm -rf ${BACKUPDIR}${DELDATE}

mkdir ${BACKUPDIR}${DATE}

RESULT=`/usr/bin/mysql -u$DBUSER -p$DBPWD -h$DBHOST -e "show databases" | grep -v Database`

for DB in $RESULT; do

  FNAME=${BACKUPDIR}${DATE}/${DB}${DATE}.sql

  /usr/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS $DB >> $FNAME

  echo "set foreign_key_checks=1;" >> $FNAME

  gzip --rsyncable $FNAME

done

autobackup.sh (1일 간격, 15일)

#!/bin/sh

DBHOST="localhost"

DBUSER="root"

DBPWD="비밀번호"

BACKUPDIR="/usr/local/mysqlbackup/ "

OPTIONS="--skip-comments --default-character-set=utf8 --routines --single-transaction --quick"

DATE=`date +%Y%m%d`

DELDATE=`date --date "5 days ago" +%Y%m%d`

rm -rf ${BACKUPDIR}${DELDATE}
mkdir ${BACKUPDIR}${DATE}

RESULT=`/usr/bin/mysql -u$DBUSER -p$DBPWD -h$DBHOST -e "show databases" | grep -v Database`

for DB in $RESULT; do

  FNAME=${BACKUPDIR}${DATE}/${DB}${DATE}.sql

  /usr/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS $DB >> $FNAME

  echo "set foreign_key_checks=1;" >> $FNAME
  gzip --rsyncable $FNAME

done

4. Crontab 설치 및 설정 

Cronttab(이하 크론탭)은 작업을 일정한 시간에 주기적으로 실행시키기 위해서 사용하는 task 스케쥴링 프로그램이다.

이와 비슷한 프로그램으로 at(1)와 anacron(1)이 있다.

at는 원하는 시간에 명령을 한번만실행 시키며, crontab과 같이 주기적으로 실행시키는 기능은 가지고 있지 않다.

anacron은 이름에서알 수 있듯이 crontab와 거의 동일하게 사용할 수 있다.

다른점은 시스템 다운이나 anacron 프로그램의 다운 등의 이유로 해당시간에 실행되어야할 프로그램이 실행되지 않았다면,

이를 확인해서 다시 실행시켜준다는 점이다.

설치 : yum install crontabs

서비스 시작 :   /usr/sbin/crond start

서비스 온 :  /sbin/chkconfig crond on

                 service crond start          

                /etc/init.d/cron start    

crontab list : crontab -l

                         cat /etc/crontab

crontab추가 : 

vi /etc/crontab

crontab추가 : 

crontab -e

crontab이 잘 돌아가나 볼까요 :  tail -100f /var/log/cron 

0  5  *   *  *  /bin/bash /usr/local/mysqlbackup/autobackup.sh     # 매일 새벽 5시에 실행

30 * * * *  /bin/bash /usr/local/mysqlbackup/autobackup.sh        # 매시 30분에 실행

*/10 * * * *  /bin/bash /usr/local/mysqlbackup/autobackup.sh        # 10분 마다  실행

/etc/init.d/crond restart

별표(*)는 all을 의미한다.
* * * * *  /bin/bash /usr/local/bin/myprog  # 매분 마다 myprog를 실행

시간을 특정할 수 있다.
30 * * * *  /bin/bash /usr/local/bin/myprog  # 매시 30분에 실행
5,10 * * * *  /bin/bash /usr/local/bin/myprog  # 매시 5,10분에 실행
5 1 * * 0  /bin/bash /usr/local/bin/myprog  # 매주 일요일 새벽 1시 5분에 실행

/를 이용해서 실행 간격을 조정할 수 있다.
*/5 * * * *  /bin/bash /usr/local/bin/myprog  # 5분 간격으로 실행
*/20 * * * *  /bin/bash /usr/local/bin/myprog  # 20분 간격으로 실행

-를 이용해서 범위를 지정할 수도 있다.
5-30 * * * *  /bin/bash /usr/local/bin/myprog  # 매시 5-30분에 분간격으로 실행
5 4-5 * * *  /bin/bash /usr/local/bin/myprog  # 새벽 4시5분, 5시 5분에 실행

-와 /를 조합할 수도 있다.

5-30/5 * * * *  /bin/bash /usr/local/bin/myprog  # 매시 5-30분에 5분간격으로 실행

이상!!!