Linux+nginx+MySQL+cacti网站服务器的搭建(6)

(六) crontab 定期备份数据库

备份脚本如下：

#salley zhao <salley@local.cn>

# 2011/3/23

#create for mysql database‘smmdb’bakup

#!/bin/sh

###bakup deriectory###

BACKUP="/data/backup/smmdb/local"

LOGFILE="$BACKUP/smm_backup.log"

### MySQL Setup ###

MUSER="zhaojuan"

MPASS="123456"

MHOST="192.168.15.7"

### Today + hour in 24h format ###

NOW=`date +%Y%m%d_%H%M`

DATENOW=`date +%Y/%m/%d' '%H:%M:%S`

### Binaries ###

TAR=`which tar`

GZIP=`which gzip`

MYSQL=`which mysql`

MYSQLDUMP=`which mysqldump`

CHMOD=`which chmod`

BACKSCRIPT="$MYSQLDUMP --add-drop-table --allow-keywords --default-character-set=utf8 --triggers --single-transaction --flush-logs -q -c -u $MUSER -h $MHOST -p$MPASS"

### Create hourly dir ###

#echo "$BACKUP"

test -f $BACKUP && rm -f $BACKUP

test -d $BACKUP || mkdir -p $BACKUP

#test -d $BACKUP mkdir

#test –e $TAR || echo "$TAR not exsit,please install it defore run this script!" && exit 1

#test –e $GZIP || echo "$GZIP not exsit,please install it defore run this script!" && exit 1

#test –e $MYSQL || echo "$MYSQL not exsit,please install it defore run this script!" && exit 1

#test –e $MYSQLDUMP || echo "$MYSQLDUMP not exsit,please install it defore run this script!" && exit 1

test -d $BACKUP/$NOW && rm -fr  $BACKUP/$NOW

mkdir $BACKUP/$NOW

#DBS="helpdesk smmdb activecollab bugtracker cbl smmdb_test"

DBS="mysql smmdb_test activecollab bugtracker helpdesk cbl "

for db in $DBS

do

### Create dir for each databases, backup tables in individual files ###

  test -d $BACKUP/$NOW/$db && rm -fr  $BACKUP/$db

  mkdir $BACKUP/$NOW/$db

  echo "######################################$db##################################" >> $LOGFILE

  for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`

  do

   # FILE=$BACKUP/$NOW/$db/$i.sql.gz

    echo $i

    $BACKSCRIPT $db $i --result-file=$BACKUP/$NOW/$db/$i.sql 2>> $LOGFILE

    #MDATE=`date +%m`

  done

### Append to log files  ###

    cd $BACKUP/$NOW

    $TAR -zcf $db.tar.gz $db 2>> $LOGFILE

    $CHMOD 0777 $db.tar.gz

    if [ "$?" == "0" ]; then

     rm -fr $db

    fi

  echo "$DATENOW DATABASE  $db  AS  $db-$NOW.tar.gz" >> $LOGFILE

done

cd $BACKUP/

$TAR -zcf $NOW.tar.gz $NOW 2>> $LOGFILE

$CHMOD 0777 $NOW.tar.gz

  if [ "$?" == "0" ]; then

    rm -fr $NOW

  fi

echo "$DATENOW DATABASE AS  $NOW.tar.gz  ok" >> $LOGFILE

exit 0