备份mysql数据库,并且自动通过FTP上传到服务器#set -x
#此脚本的主要用途是备份mysql服务器上的数据库。并且自动通过FTP上传到服务器。备份完后都会发送一封邮件。
echo -e "此脚本的主要用途是备份mysql服务器上的数据库.并且自动通过FTP上传到服务器。"
Host=172.16.1.110
pass=123456
name=root
DATE=`date +"%Y%m%d"`
WAN_DIR="/work/mysqlbackup/wan_dir"
ZENG_DIR="/work/mysqlbackup/zeng_dir"
ZENG_BACK="/work/mysqlbackup/backup"
DATA_DIR="/usr/local/mysql/data"
MYSQL_BIN="/usr/local/mysql/bin"
error_log="/work/mysqlbackup/backup_error_$DATE.log"
backup_log="/work/log/backup_$DATE.log"
gzdumpfile="$DATE.sql.tar.gz"
db="/work/log/backup_$DATE.txt"
cd $DATA_DIR
ls -l $DATA_DIR | grep "^d" | awk -F " " '{print $9}' >>$db
function wan() {
#检测完全备份目录是否存在,如果不存在就创建。
if [ -d $WAN_DIR ]
then
echo "完全备份目录存在" >>$backup_log
else
echo "完全备份目录不存在，开始创建......."
mkdir -p $WAN_DIR
fi
eMailFile="$WAN_DIR/mail.log"
email=kelly@r2games.net
echo " " > $eMailFile
echo "-----------------------" >> $eMailFile
echo "`date +"%y-%m-%d %H:%M:%S"`" >> $eMailFile
echo "-------------------------" >> $eMailFile
cd $WAN_DIR
#for dbname in $(cat $db)
#do
# mysqldump --flush-logs -u$name -p$pass --skip-lock-tables --quick $dbname > $dbname.sql
mysqldump --flush-logs -u$name -p$pass --skip-lock-tables --quick abds > abds.sql
if [ $? = 0 ]
then
find $ZENG_BACK -name "*.log" -mtime +32 -exec rm {} \; >/dev/null 2>&1
cd $WAN_DIR
tar -zcvf abds.$gzdumpfile abds.sql
echo "Backup MySQL succeed" >>$eMailFile
# mail -s "MySQL Backup" $email < $eMailFile
else
echo "Backup MySQL fail" >>$eMailFile
# mail -s "MySQL Backup fail" $email < $eMailFile
fi
#done
#完全备份后删除本地增量备份文件，只保留最近一个星期的增量备份文件
find $ZENG_BACK -name "*.sql.tar.gz" --mtime +7 -exec rm -rf {} \; >>$backup_log
#将备份好的上传到FTP服务器
cd $WAN_DIR
# for db_back in $(cat $db)
# do
# ftp -nv $Host <<EOF
# user wolf "wolf#123"
# put $db_back.$gzdumpfile
# quit
# EOF
# done
}
function zeng() {
/bin/mkdir -p $ZENG_DIR
eMailFile="$ZENG_DIR/mail.log"
email=kelly@r2games.net
echo " " > $eMailFile
echo "-----------------------" >> $eMailFile
echo "`date +'%y-%m-%d %H:%M:%S'`" >> $eMailFile
echo "-------------------------" >> $eMailFile
TIME=$(date "-d 10 day ago" +"%Y-%m-%d %H:%M:%S")
echo $TIME
StartTime=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S")
Start="--start-datetime"
#删除10天前的二进制文件
mysql -u$name -p$pass -e "purge master logs before '${TIME}'" && echo "delete 10 days before log" | tee -a $eMailFile
filename=`cat $DATA_DIR/localhost-bin.index | awk -F "/" '{print $2}'`
cd $ZENG_DIR
for i in $filename
do
echo "$StartTime start backup binlog" >> $eMailFile
#for db_name in $(cat $db)
#do
mysqlbinlog -u$name -pchenqibin -d abds $Start="$StartTime" $DATA_DIR/$i >>abds.$DATE.sql
if [ $? = 0 ]
then
cd $ZENG_DIR
tar -zcvf abds.$gzdumpfile abds.$DATE.sql
echo "Backup MySQL succeed" >>$eMailFile
# mail -s "MySQL Backup" $email < $eMailFile
else
echo "Backup MySQL fail" >>$eMailFile
# mail -s "MySQL Backup fail" $email < $eMailFile
fi
#done
done
find $ZENG_BACK -name "*.log" -name +32 -exec rm {} \; >/dev/null 2>&1
cd $ZENG_DIR
#删除上次备份的完整备份的文件
#find $WAN_DIR -name "*.tar.gz" -mtime 7 -exec rm -rf {} \;
#将备份好的上传到FTP服务器
#for db_back in $(cat $db)
#do
ftp -nv $Host <<-EOF
user llk "11111111"
bin
put abds.$gzdumpfile
bye
EOF
# done
}
backfile=`ls -l $WAN_DIR | wc -l`
if [ $backfile != 0 ]
then
echo "完整备份已经存在，现在进行增量备份"
sleep 1
zeng
else
echo "还没进行完整备份，现在进行完整备份"
sleep 3
wan
fi
