mysql全量备份和增量备份脚本

打篮球两年半

已于 2023-10-13 10:45:34 修改

阅读量2.8k

点赞数 3

分类专栏：常用shell分享文章标签： linux 定时任务 nginx

于 2021-11-19 00:18:23 首次发布

本文链接：https://blog.csdn.net/qq_39677803/article/details/121413107

版权

常用shell分享专栏收录该内容

14 篇文章 1 订阅

订阅专栏

一、全量备份 ,每周一次

vim   MYSQL_FULLBAK.sh

#!/bin/bash
#set -x
############################################################
if [ $# -ne 2 ];then 
	echo "Usage:依次输入参数: <ROOTPASSWD> <BAKDIR> ";
	exit 1
fi

if ! ps -ef | grep -v "grep"|grep -q "mysqld ";then
	echo "未检测到mysql进程"
	exit 1
fi
############################################################
# 入参
PASSWD=$1
BAKDIR=$2
############################################################
# PORT && HOST && MYSQLHOME && BAKDIR
PORT=$(netstat -lanp | grep LISTEN | grep "mysqld"|awk -F ":" 'NR==1{print $2}'|awk '{print $1}')
MYSQL_HOME=$(ps -ef | grep -v "grep"|grep "mysqld "|tr -s ' ' '\n'|grep "\-\-basedir"|awk -F "=" '{print $NF}')
PATH=$MYSQL_HOME/bin:$PATH

localnetcard=$(route -n|grep UG|head -n 1|awk '{print $NF}')
if [ ! "$localnetcard" == "" ];then
    HOST=$(ip -4 addr show $localnetcard|awk -F '[/ ]' '{for(i=1;i<=NF;i++){if($i~"[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}"){print $i;break}}}'|awk 'NR==1{print $0}')
fi

if [ ! -d $BAKDIR ];then
	mkdir -p $BAKDIR
	chown -Rf mysql:mysql  $BAKDIR
fi
############################################################
# 主从
LANG=C
MYSQL_CONN="mysql -uroot -p$PASSWD  -h$HOST -P$PORT -s -e"
MHOST=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Host:")
if [ "X$MHOST" != "X" ];then
    echo "current is Slave, got Master Info..." 
    MPORT=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Port:")
    if [ "X$MPORT" != "X" ]; then
        HOST=$(echo $MHOST | awk '{ print $2 }')
        PORT=$(echo $MPORT | awk '{ print $2 }')
        MYSQL_CONN="mysql -uroot -p$PWD -h$HOST -P$PORT -s -e"
    fi
fi
############################################################
# 备份时间
TIME=$(date +"%Y%m%d%H%M%S")
# 备份文件名
DUMPFILE=FULL_$TIME.sql
GZDUMPFILE=FULL_$TIME.sql.tgz
############################################################
# 开始备份
cd $BAKDIR
LAST=$(date +"%Y年%m月%d日 %H:%M:%S")
mysqldump -uroot -p$PASSWD  -h$HOST -P$PORT --quick --events --all-databases  >$DUMPFILE 2>&1
if [ $? -eq 0 ];then
	tar -czf $GZDUMPFILE $DUMPFILE 2>&1
	rm -f $DUMPFILE 
	echo "***********$GZDUMPFILE******************"         
	echo "***********${LAST}全量备份成功******************"  
else
	rm -f $DUMPFILE
	echo "***********${LAST}全量备份失败******************"  
	exit 1
fi
############################################################
# 清理备份,保留1个月
COUNT=$(ls -lrt *.tgz | wc -l)
if [ "$COUNT" -gt 4 ];then
	FILE=$(ls -lrt  *.tgz |awk 'NR==1{print $NF}')
	rm -f $FILE
fi

# 删除增量
#if [ -d "/data/backup/increment" ];then
	#rm -f /data/backup/increment/* 
#fi

二、增量备份，每天一次

vim  MYSQL_INCREMENT_BAK.sh

#!/bin/bash
#set -x
############################################################
if [ $# -ne 2 ];then 
    echo "Usage:依次输入参数: <ROOTPASSWD> <BAKDIR>";
    exit 1
fi

if [ "$UID" != 0 ];then
    echo "must be root"
    exit 1
fi 

if ! ps -ef | grep -v "grep"|grep -q "mysqld ";then
    echo "未检测到mysql进程"
    exit 1
fi
############################################################
# 入参
PASSWD=$1
BAKDIR=$2
############################################################
# PORT && HOST && MYSQLHOME && BAKDIR
PORT=$(netstat -lanp | grep LISTEN | grep "mysqld"|awk -F ":" 'NR==1{print $4}')
MYSQL_HOME=$(ps -ef | grep -v "grep"|grep "mysqld "|tr -s ' ' '\n'|grep "\-\-basedir"|awk -F "=" '{print $NF}')
PATH=$MYSQL_HOME/bin:$PATH

localnetcard=$(route -n|grep UG|head -n 1|awk '{print $NF}')
if [ ! "$localnetcard" == "" ];then
    HOST=$(ip -4 addr show $localnetcard|awk -F '[/ ]' '{for(i=1;i<=NF;i++){if($i~"[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}"){print $i;break}}}'|awk 'NR==1{print $0}')
fi

if [ ! -d $BAKDIR ];then
    mkdir -p $BAKDIR
    chown -Rf mysql:mysql  $BAKDIR
fi
############################################################
# 主从
LANG=C
MYSQL_CONN="mysql -uroot -p$PASSWD  -h$HOST -P$PORT -s -e"
MHOST=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Host:")
if [ "X$MHOST" != "X" ];then
    echo "current is Slave, got Master Info..." 
    MPORT=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Port:")
    if [ "X$MPORT" != "X" ]; then
        HOST=$(echo $MHOST | awk '{ print $2 }')
        PORT=$(echo $MPORT | awk '{ print $2 }')
        MYSQL_CONN="mysql -uroot -p$PASSWD -h$HOST -P$PORT -s -e"
    fi
fi
############################################################
if $MYSQL_CONN "show variables like 'log_%';" |egrep -i -q "log_bin[[:space:]]ON";then
    # binlog生成目录
    BINDIR=$($MYSQL_CONN "show variables like 'log_%';" |egrep -i "log_bin_basename"|awk '{print $NF}'|awk -F "mysql-bin|" '{print $1}')   
    # binlog索引
    BINFILE=$($MYSQL_CONN "show variables like 'log_%';" |egrep -i "log_bin_index"|awk '{print $NF}')
    TIME=$(date +"%Y年%m月%d日 %H:%M:%S")
    # 产生新的mysql-bin.00000*文件
    mysqladmin -uroot -p$PASSWD  -h$HOST -P$PORT flush-logs
    if [ $? -ne 0 ];then
        echo "***********${TIME}增量备份失败******************"  
        exit 1
    fi
else
    echo "mysql 未开启binlog日志"
    exit 1
fi
############################################################
COUNT=$(wc -l $BINFILE |awk '{print $1}')
i=0
cat $BINFILE|while read line;do   
    BINNAME=$(basename $line)
    i=$(expr $i + 1)
    if [ $i -ne $COUNT ];then
        dest=$BAKDIR/$BINNAME
        if [ ! -e $dest ];then
            cp $BINDIR/$BINNAME $BAKDIR
            if [ $? -eq 0 ];then
                echo "***********${TIME} $BINNAME增量备份成功******************"  
            else
                echo "***********${TIME} $BINNAME增量备份失败******************"  
                exit 1
            fi   
        fi

    fi
done

三、赋权(可选)

chmod +x  /data/backup/*.sh

四、定时任务（可选）

crontab  -e
#每个星期日凌晨23:00执行完全备份脚本
0 23 * * 0   sh  /data/backup/MYSQL_FULLBAK.sh /back1 123456 >/dev/null 2>&1
#周一到周六凌晨23:00做增量备份
0 23 * * 1-6 sh /data/backup/MYSQL_INCREMENT_BAK.sh /back2 123456>/dev/null 2>&1