轻松实现MySQL自动备份脚本

以下脚本实现每天自动备份，保存最近一周的所有全备份数据及上传至FTP备份，并且压缩减少磁盘空间，可以直接修改IP，密码等信息后，直接拿来用
有问题请回贴跟踪，欢迎拍砖！！！！

复制内容到剪贴板代码:
#!/bin/bash
# Written by Mpfei at 20090622
# Change your dbname of ucstarserver ,please define the following parameters
# Make sure the Script attribution is : 700
# Usage: #chmod 700 ucstarbackup.sh
# Modfify your database parameters #
##########################################################
DBUser=root
DBPasswd=qingqi
DBHost="192.168.1.160"
DBPort=3306
#Define this PATH ,for example:
#BackupPath=/home/backup
BackupPath=/home/backup
LogPath=/var/log/
LogFile=/var/log/mysqldb.log
#default mysql path
DBPath=/var/lib/mysql/
BackupMethod=mysqldump
#########################################################
#FTP server parameters,and make sure ftpserver remote directory exists
#For example: ucstar,wikidb,egroupware,discuz
FTPHost=192.168.1.160
FTPUser=username
FTPPasswd=123
Ftpath=/backup/
#########################################################

if [ -f $LogFile ]; then
echo "The logfile is $LogFile "
else
touch "$LogFile"
echo "Generate a new log file and locate $LogFile"
fi

echo "----------------------------------------------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "----------------------------------------------------------------------------------" >> $LogFile
#
#define a array of dbname and backup those database
declare -a DBName
DATABASE=("ucstar" "roller" "discuz" "wikidb" "egroupware")
#
for DBName in ${DATABASE[@]}; do
#
#Define file
NewFile="$DBName"db$(date +%y%m%d).tar.gz
DumpFile="$DBName"db$(date +%y%m%d).sql
OldFile="$DBName"db$(date +%y%m%d --date='7 days ago').tar.gz
#

echo "The latest backup file locate $BackupPath/$DBName/$NewFile"

cd "$BackupPath"
if [ -d $DBName ]; then
cd "$DBName"
else
mkdir -p "$DBName"
cd "$DBName"
echo "$DBName diretory no exists,mkdir the directory " >> $LogFile
echo "$DBName diretory no exists,mkdir the directory "
fi


if [ -f $NewFile ]; then
echo "[$BackupPath/$DBName/$NewFile]The latest Backup File is exists,Cancel Backup" >> $LogFile
echo "The latest backup file exists,Cancel!"
else
case $BackupMethod in
mysqldump)
if [ -z $DBPasswd ]; then
mysqldump -u "$DBUser" -h "$DBHost" -P "$DBPort" --opt "$DBName" > $DumpFile
else
mysqldump -u "$DBUser" -p"$DBPasswd" -h "$DBHost" -P "$DBPort" --opt "$DBName" > $DumpFile
fi
if [ ! -s $DumpFile ]; then
echo "-----Export $DBName new database file failure------------"
echo "Maybe $DBHost can't connect or $DBName database isn't exists"
echo "-----Export $DBName new database file failure------------" >> $LogFile
echo "Maybe $DBHost can't connect or $DBName database isn't exists" >> $LogFile
echo "Do nothing"
echo "Do nothing." >> $LogFile
else
tar -czpf "$NewFile" "$DumpFile" >> $LogFile 2>&1
echo "[$BackupPath/$DBName/$NewFile]Backup Success" >> $LogFile
echo "Export $DBName new database file Success!"
#Delete Old File
if [ -f $OldFile ]; then
rm -f "$OldFile" >> $LogFile 2>&1
echo "[$BackupPath/$DBName/$OldFile]Delete the old file $OldFile File Success" >> $LogFile
echo "Delete the old file $OldFile success!"
else
echo "[$BackupPath/$DBName]directory of the 7 days prior to the backup file does not exists " >> $LogFile
echo "7 days ago backup File is no exists"
fi
fi
rm "$DumpFile"
;;
mysqlhotcopy)
#rm -rf $DumpFile
#mkdir $DumpFile
if [ -z $DBPasswd ]; then
mysqlhotcopy -u $DBUser $DBName $DumpFile >> $LogFile
echo "Export new database file Success!"
else
mysqlhotcopy -u $DBUser -p$DBPasswd "$DBName" "$DumpFile" >> $LogFile
fi
;;
esac
fi

#Delete Old File
#if [ -f $OldFile ]; then
# rm -f "$OldFile" >> $LogFile 2>&1
# echo "[$BackupPath/$DBName/$OldFile]Delete $OldFile Success" >> $LogFile
# echo "Delete $OldFile success!"
# else
# echo "[$BackupPath/$DBName] directory of the 7 days prior to the backup file does not exists " >> $LogFile
# echo "7 days ago backup File is no exists!"
#fi

echo "Ready upload newfile to FTP server"
echo "Ready upload newfile to FTP server" >> $LogFile
#define a funcation of fput
if [ -f $NewFile ]; then
ftp -n<<!
open $FTPHost
user $FTPUser $FTPPasswd
binary
prompt off
cd $Ftpath
cd $DBName
pwd
mput $NewFile
delete $OldFile
close
bye
!
echo "Put newfile to Ftpserver $FTPHost success"
echo "Del old file $OldFile if Ftpserver $FTPHost exists"
echo "Put $NewFile newfile to Ftpserver $FTPHost success" >> $LogFile
echo "Del old file $OldFile if Ftpserver $FTPHost exists" >> $LogFile
else
echo "NewFile is not exists,cancel upload to ftpserver $FTPHost"
echo "NewFile is not exists,cancel upload to ftpserver $FTPHost" >> $LogFile
fi
#cd "$BackupPath"
done
echo "-------------------------------------------------------------------------------------------------------" >> $LogFile
exit 0