Shell script to backup MySQL database(备份MySQL 脚本)

this is shell script to backup mysql database, using mysqldump to dump all databases into separate files, and encrypt backup file using zip password option, Generate a collective file using tar, ftp to ftp server, to send email to DBA .

#!/bin/bash
#
# Shell script to backup MySQL database with mysqldump
# dump all databases into separate files.
# Author: weejar
# create date: 2014-3-25
# email: weejar@gmail.com
# Copyright (C) 2014 anbob.com   
# Get date in yyyy-dd-mm format
TIMESTAMP=$(date +"%F")
# MYSQL_USER="root"
# MYSQL_PASSWORD="*******"
#  ZIP BACKUP FILE PASSWORD
ZIPPWD=888888
# backup file keep in local NUM times
NUM=3
# Linux bin path
MYSQL=/bin/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump
ZIP=/usr/bin/zip
FTP=/usr/bin/ftp
# Get HOST IP address
IPADR=`/sbin/ip addr show eth0|grep inet|awk '{print $2}'|awk -F/ '{print $1}'`
# Root directory where backup will be stored
BACKUP_DIR=/data/backup/sqldump
# Main directory where backup log will be store
LOGFILE=${BACKUP_DIR}/mysqldump_$TIMESTAMP.log
# Summary backup file
BACKUPFILE=${IPADR}_Mysqldump_${TIMESTAMP}.tar
# Ftp backup directory will be upload
FTPDIR="/g:/backup"  
# DBA email to  send logfile 
EMAIL=dba@anbob.com
# Create direcotry if $BACKUP_DIR doesn't exist.
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p $BACKUP_DIR
fi
# Redirect output to LOGFILE
exec 1> ${LOGFILE} 2>&1
echo  $(date +"%F %r")
#databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
# List need backup databases
databases=`$MYSQL  -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
for db in $databases; do
echo "backup $db is running..."
#$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$db.gz"
$MYSQLDUMP --force --opt  --databases $db | $ZIP -P $ZIPPWD  > ${BACKUP_DIR}/${IPADR}_${db}_${TIMESTAMP}_encrypt.zip
# Verify BACKUP 
if [ $? -ne 0 ]; then
       echo "backup $db fails!"         
else
       echo "backup $db completed!" 
fi
 echo  $(date +"%F %r")
 echo "********************************"
done
# Merge backup file
tar cvf ${BACKUP_DIR}/${BACKUPFILE} ${BACKUP_DIR}/${IPADR}_*_${TIMESTAMP}_encrypt.zip  ${LOGFILE}
# Remove expiry backup file
rm  ${BACKUP_DIR}/*.zip
ls -rt ${BACKUP_DIR}/${IPADR}_*mysqldump.tar|head -n -$NUM
echo 'deleting...'
ls -rt ${BACKUP_DIR}/${IPADR}_Mysqldump*.tar|head -n -$NUM|xargs rm -f
# Upload backup file to FTP Server ,also you should configuration ftp IP,USERNAME,PASSWORD Variable
$FTP -n <<EOF 
        open 192.168.212.110
        user ftpuser ftppassword
        cd $FTPDIR
        binary 
        lcd $BACKUP_DIR                
        put ${BACKUPFILE}
        bye
EOF
if [ $? -ne 0 ]; then
       echo "ftp upload backup file fails!"         
else
       echo "ftp upload backup file successful!" 
fi
 echo  $(date +"%F %r")
 echo "********************************"
# send email ,About how to send email on linux (http://www.anbob.com/archives/1753.html)
cat $LOGFILE | mail -s "${IPADR} backup info" $EMAIL
# end