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

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

**全量备份脚本：**

```bash
#!/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
```

**增量备份脚本：**

```bash
#!/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通知**

```bash
#!/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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://close.gitbook.io/yun-wei-bi-ji/centos/shell/ftpmysql-shu-ju-ku-de-quan-liang-bei-fen-he-zeng-liang-bei-fen-jiao-ben-shi-li.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.