Mysqldump备份脚本

两个小黄鹂

已于 2022-11-30 13:04:00 修改

阅读量370

点赞数

分类专栏：数据库 # MySQL 文章标签： mysql 数据库 linux

于 2022-11-30 13:03:28 首次发布

本文链接：https://blog.csdn.net/u014650965/article/details/128113231

版权

数据库同时被 2 个专栏收录

61 篇文章 1 订阅

订阅专栏

MySQL

14 篇文章 0 订阅

订阅专栏

#!/bin/bash
setEnv()
{
    fileName=`date "+%Y%m%d"`_allDB.gz
    backupFile=/data/backup/dump/$fileName
    logFile=/root/scripts/log/backup.log
    MYSQL_BACKUP_USER=root
    MYSQL_BACKUP_PWD=123456
    MYSQL_PORT=3306
    MYSQL_SOCKET=/tmp/mysql.sock
    MYSQL_BASE=/usr/local/mysql
    MYSQL_MYCNF=/etc/my.cnf
    BIN_LOG_PTAH=/data/mysql/log/binlog
    CURRENT_WEEK=`date +%w`
    CURRENT_DATE=`date +"%Y%m%d"`
    CURRENT_TIME=`date +"%Y%m%d%H%M"`
    BACKUP_BASE_PATH=/data/backup

}

backupDB()
{
    begin_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo "$begin_time DB start backup" >> $logFile
    /usr/local/mysql/bin/mysqldump --user=root --password=123456 --socket=/tmp/mysql.sock --single-transaction --master-data=2 --flush-logs -E -R --triggers --all-databases |gzip > $backupFile
    end_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo "$end_time DB backup finish" >> $logFile
    backupBinlog
    transferFile

}

transferFile()
{   begin_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo "$begin_time backupfile start transfer" >> $logFile
    scp $backupFile testdb:/data/historyBackup/dump/
    scp $BACKUP_BASE_PATH/binlogBackup/* testdb:/data/historyBackup/binlog/
    end_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo "$end_time  backupfile finish transfer" >> $logFile
}

clearFile()
{
    begin_time=`date "+%Y-%m-%d %H:%M:%S"`
    #echo -e "\n" >>$logFile
    echo "$begin_time clear file start" >> $logFile
    find /data/backup/dump -type f -name "20*" -mtime +1 -exec rm -rf {} \;
    find /data/backup/binlogBackup -type f -name "mysql-bin.0*" -mtime +7 -exec rm -rf {} \;
    find /data/backup/binlogBackup -type f -name "20*" -mtime +7 -exec rm -rf {} \;
    end_time=`date "+%Y-%m-%d %H:%M:%S"`
    echo "$end_time  clear file finish" >> $logFile
}

backupBinlog()
{
   CURRENT_BINGLOG=`more $BIN_LOG_PTAH/mysql-bin.index|sed -n '$p'|awk -F"/" '{print $NF}'`
   if [ ! -d $BACKUP_BASE_PATH/binlogBackup ];then
       mkdir -p $BACKUP_BASE_PATH/binlogBackup
       cp $BIN_LOG_PTAH/* $BACKUP_BASE_PATH/binlogBackup/
       touch $BACKUP_BASE_PATH/binlogBackup/binlogInfo.log
       echo "lastBinlog:$CURRENT_BINGLOG" > $BACKUP_BASE_PATH/binlogBackup/binlogInfo.log

   fi
   if [  -d $BACKUP_BASE_PATH/binlogBackup ];then
       LAST_BINLOG=`more $BACKUP_BASE_PATH/binlogBackup/binlogInfo.log |awk -F":" '{print $2}'`
       INFO=`sedFun ${LAST_BINLOG} ${CURRENT_BINGLOG} $BIN_LOG_PTAH/mysql-bin.index`
       #for i in `sed -n "/${LAST_BINLOG}/,/${CURRENT_BINGLOG}/p" $BIN_LOG_PTAH/mysql-bin.index`
       for i in $INFO
       do
           cp $i $BACKUP_BASE_PATH/binlogBackup/
       done
       cp $BIN_LOG_PTAH/mysql-bin.index $BACKUP_BASE_PATH/binlogBackup/`date +"%Y%m%d%H%M"`_mysql-bin.index
       echo "lastBinlog:$CURRENT_BINGLOG" > $BACKUP_BASE_PATH/binlogBackup/binlogInfo.log
   fi

}

sedFun()
{
 sed -n "/$1/,/$2/p" $3
}
setEnv
clearFile
backupDB
#transferFile