MySQL DB Backup

출처 : http://wyseburn.tistory.com/222
출처2: http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=54465&sca=DBMS&page=6

**MySql DB 별 백업 쉘스크립트(메모)**

매일 디렉토리 생성 후 데이타베이스별로 백업 후 압축
15일이 지난 백업분 제거

<code>

#!/bin/sh

DBHOST="호스트"
DBUSER="계정"
DBPWD="비번"

BACKUPDIR="/root/backup/"
OPTIONS="--skip-comments --default-character-set=utf8 --routines --single-transaction --quick"

DATE=`date +%Y%m%d`
DELDATE=`date --date "15 day ago" +%Y%m%d`

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

RESULT=`/usr/local/mysql/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/local/mysql/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS $DB >> $FNAME
  echo "set foreign_key_checks=1;" >> $FNAME
  gzip --rsyncable $FNAME

done

</code>

**[DBMS] mysql 백업(Backup) 및 복구(Recover)**

Mysql 백업
우선 Mysql 이 /usr/local/mysql/에 설치되어있으며 Mysql configure시
--localstatedir=/usr/local/mysql/data 옵션을 주고 설치했다고 가정하겠다.
백업 방법으로는 크게 2가지가 있다.
Mysql DB 데이터 화일을 직접 백업하는 경우와 mysqldump 문을 이용하여 sql문을 백업받는방법이 있다

1. 데이터 화일 직접백업
보통의 경우 Mysql 을 사용하는 Type 이 InnoDB와 Myisam 이 있다.
두 Type 의 차이는 여러가지가 있지만 여기서 언급하는것은 데이터 화일 백업에 관련된것이므로
데이터 화일의 위치를 언급하겠다.

nnodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

InnoDB 의 경우 일반적으로 위의 /etc/my.cnf 에서 설정된 것과 같이
ibdata1, ibdata2 과 같은 화일에 index 및 파일데이터가 저장된다. 그리고 DB 및 테이블 정보는
/usr/local/mysql/data/DB명/테이블명.frm 과 같은 구조로 이루어져 있다.
그러므로 ibdata1 와 같은 화일과 /usr/local/mysql/data/DB명/테이블명.frm 화일들을 복사하면 백업하면된다.

Myisam 인 경우는 좀더 직관적이다.
/usr/local/mysql/data/DB명 에 있는 화일을 통째로 백업받으면 된다.
특정 DB의 특정 테이블만 백업받을 경우에는..

/usr/local/mysql/data/DB명/테이블명.frm # 테이블 구조
/usr/local/mysql/data/DB명/테이블명.MYD # Myisam Type 테이블의 DATA
/usr/local/mysql/data/DB명/테이블명.MYI # Myisam Type 테이블의 index

위의 3개 화일만 다운받으면 된다.
물론 data 디렉토리를 통째로 백업받을수도 있다.

다만 이런 직접적으로 파일을 백업받는 방법은...
Mysql 버젼이 달라졌을때 문제가 될수도 있으니...
가급적이면 mysqldump 를 이용하는것이 좋다.


2. mysqldump를 이용한 Backup
가장 널리 이용하는 방법이고 가장 확실한 방법이다.
다만 DB 데이터가 큰 경우 Backup 시간이 많이 걸릴수 있기 때문에
보통 크론등을 이용하여 주기적으로 새벽시간을 이용하여 백업받는다.

사용방법은 다음과 같다.
/usr/local/mysql/bin/mysqldump -uroot -pPassword [백업옵션] [환경옵션] > /BackUp/Mysql/Backup.sql
/BackUp/Mysql/ <== 임의로 정한 백업화일 위치이다.

[백업옵션] 의 내용은 아래의 4가지 형태만 알아도 될듯하다.
옵션들에 주의 해주면 다양한 형태로 백업을 받을수 있다.
--all-databases # mysql DB 전체를 백업다는것을 의미한다.
--databases DB1 DB2 DB3 # mysql 에서 특정 DB만 백업받는 것을 의미한다.
DB1 # DB1 이라는 DB 만 백업받을때 사용한다.
DB1 table1 # DB1 이라는 DB의 table1 이라는 테이블만 백업받을때 사용한다.

[환경옵션]은 백업시에 어떤환경으로 백업을 할것인가에 대한 옵션이다.
--default-character-set=utf8 # 지정된 캐릭터셋을 기본으로함
--set-charset # 기본지정된 캐릭터셋(default-character-set)을 SET NAMES default-character-set로 설정
--opt # 메모리에 로드하지 않고 바로 화일로 덤프
--create-options # create문 백업시에 테이블 설정을 포함함.
--compatible=DB # 백업sql이 특정 db에 호환되도록 함 예) mysql40, mysql41, oracle, mssql
--extended-insert=FALSE # insert 문을 한줄씩 만든다
--result-file=file # 지정된 file 로 바로 넣음.. "> /BackUp/Mysql/Backup.sql"  과 같은 의미
--triggers # 트리거 덤프
--no-create-db # DB 생성정보를 뺌
--no-create-info # 테이블 생성정보를 뺌
--no-data # 테이블의 데이터를 뺌
이외에도 많은 옵션이 있지만 거의 사용할일이 없어서 나도 잘모른다.
공부하고 싶으신분들은 mysqlkorea.com 이나 mysql.com 에서 찾아보시길..


복구(Recover)
복구는 상당히 간단하다.
/usr/local/mysql/bin/mysql -uroot -pPassword < /BackUp/Mysql/Backup.sql
위의 방법은 전체복구이며 단위 DB가 생성되어 있는상태에서 db별 백업은 다음과 같이 한다.
/usr/local/mysql/bin/mysql -uroot -pPassword DB명< /BackUp/Mysql/Backup.sql


대용량DB 복구를 위한 백업
팁으로 대용량의 Table 을 덤프 받아서 입력할때...
입력속도 때문에 문제가 생기는 경우가 있다.
아마도 대부분이 InnoDB라서 그런 문제가 생기지 싶다.
그럴때는 Table Type 을 InnoDB에서 Myisam 으로 바꾸고 Insert 그리고 다 입력된 다음..
Table Type을 다시 InnoDB로 바꾸는게 빠르다.
그래도 속도가 느리다면 Mysql 에서 LOAD DATA INFILE 을 실행하여.. CSV 같은 데이터를 입력받는게
가장 빠르다.

다음은 하나의 DB(DB에 포함된 모든 테이블 정보)에 관한 DDL문 하나와 각 테이블별 CSV 데이터를 만드는
방법이다. 아래의 명령을 실행시키기 이전에 우선 /백업디렉토리 설정을 먼저해야한다.
갑자기 왠 백업디렉토리 설정이냐라고 물을수 있는데.. CSV 로 만들경우 mysql 이라는 유저권한으로
파일들이 생성되기 때문에 mysql 이 쓰기권한이 있어야한다.

/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --no-data > /백업디렉토리/DB.sql
위의 dump 명령은 db 구조만 백업받는것이다.
/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --no-create-info --tab=/백업디렉토리 --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"'

여기서 주의해야 할것은 각 테이블별로 "--tab=/백업디렉토리" 정의된 곳에 table명.txt 화일로 CSV 화일이
생성된다는 것이다. 만약 --no-create-info 옵션을 주지 않는경우에는 "--tab=/백업디렉토리"에 정의된 대로
해당디렉토리에 테이블 DDL 문장이 테이블명.sql 로 각각 생성이된다.

각테이블별로 DDL문과 CSV 화일을 백업받고 싶다면..
/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --tab=/백업디렉토리 --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"'
이와 같이 하면된다.

만들어진 테이블DDL문과 CSV 데이터로 복구할때는 아래와 같이..
/usr/local/mysql/bin/mysql -uroot -pPassword < /백업디렉토리/DB.sql
/usr/local/mysql/bin/mysql -uroot -pPassword DB명
mysql> load data infile '/백업디렉토리/테이블.txt' into table 테이블명 fields terminated by ',' enclosed by '"' lines terminated by '\r\n'; 

참고1
--tab 옵션을 사용해서 CSV 형태로 백업할때는 --all-database 와 같은 옵션과 같이 사용할수없다.
--tab 옵션은 하나의 DB 이하에서만 사용이 가능하다.
참고2
--fields-terminated-by=',' # 필드구분자
--fields-enclosed-by='"' # 필드를 특정기호로 감싸는것
--lines-terminated-by='\r\n' # 라인구분자(테이블 데이터의 Row 구분자)