利用shell备份mysql并将mysql上传到备份服务器上

#!/bin/bash
# description:	MySQL buckup shell script
# author:		Icyboy
# Email:		xupeng.js@gmail.com

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
export PATH

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

USER="xxx"             #用户名 
PASSWORD="xxx"   #密码 
HOST="localhost"
IGDB="information_schema mysql" #需要排除的sql
OPTIONS="-u$USER -p$PASSWORD -h$HOST --skip-opt --add-drop-table --create-options -q -e --set-charset --hex-blob --events --single-transaction --master-data=2"

BACKUP_DIR=/data/backup/        #备份文件存储路径 
LOGFILE=/data/backup/backup.log #日志文件路径
KEY=/home/ec2-user/.xp/key

REMOTE_USER="xxx"	
REMOTE_HOST="xx.xx.xx.xx"
REMOTE_DIR="/backup/db/"

DATE='GTP-'`date +%Y%m%d-%H%M`  #用日期格式作为文件名
ARCHIVE=$DATE.sql.tar.gz 

#判断备份文件存储目录是否存在，否则创建该目录 
if [ ! -d $BACKUP_DIR ] 
then
    mkdir -p "$BACKUP_DIR"
fi  

#开始备份之前，将备份信息头写入日记文件
echo "    ">> $LOGFILE 
echo "--------------------" >> $LOGFILE 
echo "BACKUP DATE:" $(date +"%y-%m-%d %H:%M:%S") >> $LOGFILE 
echo "-------------------" >> $LOGFILE  

#切换至备份目录 
cd $BACKUP_DIR
#查询所有的数据库
DBS="$($MYSQL -u $USER -h $HOST -p$PASSWORD -Bse 'show databases')"
for db in $DBS
do
    skipdb=-1
    if [ "$IGDB" != '' ]; then
        for i in $IGDB
        do
            [ "$db" == "$i" ] && skipdb=1 && break || :
        done
    fi
    
    if [ "$skipdb" == "-1" ]; then
        $MYSQLDUMP $OPTIONS $db > $db.sql
    fi
done
#判断数据库备份是否成功 
if [[ $? == 0 ]]
then 
    tar czvf $ARCHIVE *.sql >> $LOGFILE 2>&1 
    echo "[$ARCHIVE] Backup Successful!" >> $LOGFILE
    #删除原始备份文件,只需保留备份压缩包
    rm -f *.sql
    scp -i $KEY $BACKUP_DIR$ARCHIVE $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR >> $LOGFILE 2>&1
else
    echo "Database Backup Fail!" >> $LOGFILE 
fi
echo "Backup Process Done"
#删除3天以上的备份文件
find $BACKUP_DIR  -type f -mtime +2 -name "*.tar.gz" -exec rm -f {} \;