MySQL 테이블별 백업

#!/bin/sh

DBHOST="localhost"

DBUSER=""

DBPWD=""

BACKUPDIR="/root/backup/"

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

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

DELDATE=`date --date "30 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`

#특정 데이타베이스 백업시(DB명 띄어쓰기로 구분해서 나열)

RESULT="mysql information_schema db명"

for DB in $RESULT; do

  mkdir ${BACKUPDIR}${DATE}/${DB}

  TABLELIST=`/usr/local/mysql/bin/mysql -u$DBUSER -p$DBPWD -h$DBHOST $DB -e "show tables" | grep -v Tables_in_$DB`

  for TABLE in $TABLELIST; do

    FNAME_DESC=${BACKUPDIR}${DATE}/${DB}/${TABLE}_desc_${DATE}.sql

    FNAME_DATA=${BACKUPDIR}${DATE}/${DB}/${TABLE}_data_${DATE}.sql

    /usr/local/mysql/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS --no-data $DB $TABLE >> $FNAME_DESC

    /usr/local/mysql/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS --no-create-db $DB $TABLE >> $FNAME_DATA

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

    gzip --rsyncable $FNAME_DATA

  done

done