Ftp-Mysql数据库的全量备份和增量备份脚本实例

这个脚本仅仅适合小型数据库，不适合中大型数据库。毕竟是逻辑备份方式，数据库过大导入会比较慢，建议采用innobackupex方式备份，包括我自己服务器也是采用的innobackupex。

全量备份脚本：

#!/bin/bash 
#bak mysql all
TIME=`date +%Y%m%d_%H%M%S`
LOGFILE="/home/mysqlbak/mysql_full_bak_time.log"
MYSQL_BAKDIR="/home/mysqlbak/full$TIME"
USER=root
PASSWORD=xxxxx
[ ! -d ${MYSQL_BAKDIR}  ] &&  mkdir -p ${MYSQL_BAKDIR}
[ ! -e  ${LOGFILE} ] && touch  ${LOGFILE}
DATABASE=`mysqlshow  -u${USER}  -p${PASSWORD}  |  grep  -Ev "Data|info|per|sys" | grep  -v + | awk '{print  $2}'`
for bakmysqlname in ${DATABASE}
do
    cd ${MYSQL_BAKDIR}
    mysqldump  -u${USER}  -p${PASSWORD}  ${bakmysqlname}  --skip-lock-tables > ${bakmysqlname}.sql
    tar zcf ${bakmysqlname}.tar.gz   ${bakmysqlname}.sql --remove-files &> /dev/null
done
ret=$?
FINISHTIME=`date +%Y%m%d_%H%M%S`
 
[ $ret -eq 0 ] && echo  "Mysql fullbakup  finished, time: ${FINISHTIME}">>  ${LOGFILE} || echo "Mysql fullbakup failed,please check time:${FINISHTIME}" >>  $LOGFILE

增量备份脚本：

#!/bin/bash
#bak incr for mysql
#2018.8.4
TIME=`date +%Y%m%d_%H%M%S`
DATADIR=/data/mysql
LOGFILE="/home/mysqlbak/mysql_incr__bak_time.log"
USER=root
PASSWORD=xxxxxx
MYSQL_BAKDIR=/home/mysqlbak/incr$TIME
[ ! -d $MYSQL_BAKDIR ] && mkdir -p $MYSQL_BAKDIR
[ ! -e  $LOGFILE ] && touch  $LOGFILE
mysqladmin   -u$USER -p$PASSWORD  flush-logs
TOTAL=`ls $DATADIR/mysql-bin.*  |wc -l`
TOTAL=`expr $TOTAL - 2`
for filetemp  in `ls $DATADIR/mysql-bin.*|head -n $TOTAL`
do
    incrfilename=`basename $filetemp`
    cp  $filetemp   $MYSQL_BAKDIR/$incrfilename
    tar zcf $MYSQL_BAKDIR/$incrfilename.tar.gz  $MYSQL_BAKDIR/$incrfilename --remove-files &> /dev/null
done
ret=$?
FINISHTIME=`date +%Y%m%d_%H%M%S`
[ $ret -eq 0 ] && echo  " Mysql incr bak finished,time: $FINISHTIME">> $LOGFILE || echo  " Mysql incr bak failed,please check.time: $FINISHTIME">> $LOGFILE

全量备份+FTP上传+Telem通知

#!/bin/bash 
#bak mysql all

############################# 公共部分    ########################
# 数据库相关
TIME=`date +%Y%m%d_%H%M%S`
LOGFILE="/home/mysqlbak/mysql_full_bak_time.log"
MYSQL_BAKDIR="/home/mysqlbak/full$TIME"
USER=root
PASSWORD=123456

# Ftp 相关
FTP_IP=IP
FTP_PORT=port
FTP_USER=ftpUser
FTP_PASSWORD=ftpPasswd
SRCDIR=/home/mysqlbak
DESTDIR=ftpDir

# telegram 通知
URL=https://api.telegram.org/bot713248881:AAFljNaEs-uBRJJP2ZOGP4hbbsbbsbbs_bbs/sendMessage
CHAT_ID=-1001233551438
#############################  全局备份   ########################

[ ! -d $MYSQL_BAKDIR  ] &&  mkdir -p $MYSQL_BAKDIR
[ ! -e  $LOGFILE ] && touch  $LOGFILE
DATABASE=`/www/server/mysql/bin/mysqlshow  -u${USER}  -p${PASSWORD}  |  grep  -Ev "Data|info|per|sys" | grep  -v + | awk '{print  $2}'`
for bakmysqlname in ${DATABASE}
do
    cd ${MYSQL_BAKDIR}
    mysqldump  -u${USER}  -p${PASSWORD}  ${bakmysqlname}  --skip-lock-tables > ${bakmysqlname}.sql
    tar zcf ${bakmysqlname}.tar.gz   ${bakmysqlname}.sql --remove-files &> /dev/null
done
ret=$?
FINISHTIME=`date +%Y%m%d_%H%M%S`
 
[ $ret -eq 0 ] && echo  "Mysql fullbakup  finished, time: ${FINISHTIME}">>  ${LOGFILE} || echo "Mysql fullbakup failed,please check time:${FINISHTIME}" >>  $LOGFILE


##############################  FTP 上传   #######################

function UpFtp() {
    cd ${SRCDIR} && tar zcf db_all_full${TIME}.tar.gz full${TIME} --remove-files &> /dev/null
     
    # 判断打包的文件是否存在
    if [ $? -eq 0 ];then
        echo ''
    else
        curl -X POST -d "chat_id=${CHAT_ID},&text=${DESTDIR} DB Backup Failed" ${URL}
        exit 1
    fi

ftp -ivn <<EOF
open ${FTP_IP} ${FTP_PORT}
user ${FTP_USER} ${FTP_PASSWORD}
binary
mkdir ${DESTDIR}
cd ${DESTDIR}
lcd ${SRCDIR}
put db_all_full${TIME}.tar.gz
quit
EOF

     if [ $? -eq 0 ];then
          echo "Mysql fullbakup Uploads files to romote FTP server successful." >> ${LOGFILE}
          curl -X POST -d "chat_id=${CHAT_ID},&text=${DESTDIR} DB Upload FTP Successful" ${URL}
          # 上传成功后删除3天前的文件
          cd ${SRCDIR} && find . -name 'db_all_full202*' -type f -mtime +3 -exec rm {} \;
     else
          echo "Mysql fullbakup Upload files failed, pls check." >> ${LOGFILE}
          curl -X POST -d "chat_id=${CHAT_ID},&text=${DESTDIR} DB Upload FTP Failed" ${URL}

     fi

}

[ $ret -eq 0 ] && UpFtp