mysql備份指令

#!/bin/bash

MyUSER="root"     # USERNAME
MyPASS="123456"       # PASSWORD
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%Y%m%d%H%M")"

#keep backup data amount
Num=30                      

# Backup Dest directory, change this if you have someother location
DEST="/var/local/"

# Main directory where backup will be stored
MBD="$DEST/sqlbackup-$NOW"

# Get hostname
HOST="$(hostname)"


# File to store current backup file
FILE=""
# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST


 #################  keep the data amount    ####################################

  if [ `ls $DEST |sort|wc -l` -gt $Num ];then
          while [ `ls $DEST |sort|wc -l` -gt $Num ]
          do
           echo "deling"
           Dir=`ls $DEST|sort -r| tail -1`
           rm -rf $DEST$Dir
          done
      fi



# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
    for i in $IGGY
    do
        [ "$db" == "$i" ] && skipdb=1 || :
    done
    fi

    if [ "$skipdb" == "-1" ] ; then
    FILE="$MBD/$db.$HOST.$NOW.sql"
    # do all inone job in pipe,
    # connect to mysql using mysqldump for select mysql database
    # and pipe it out to gz file in backup dir :)
        #$MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS  --default-character-set=latin1 --add-drop-table --add-locks -B  $db  > $FILE
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS   --add-drop-table --add-locks -B  $db  > $FILE
    fi
done


#######################copy binlog ########################
BinLog=`find /var/lib/mysql/ -name '*bin.0*'  | cut -d '/' -f5 | grep -v 'relay' | sort | tail -n2`
mysqldir="/var/lib/mysql"    #mysql data direction
#bckupDB=`find /var/lib/mysql/ -type d  | cut -d '/' -f5`  #DB name which you want to backup
 #################  backup the DB          ######################################
#      echo "backup db data ................."
#      cd $mysqldir
#      cp -rf $BinLog  $MBD
#      cp -rf  $backupDB $MBD
   ################ tar the DB backup files  ######################################
#      cd $DEST
#      tar zcf $NOW.tar.gz $MBD