Mysql 全量增量备份教程及脚本（三）

mysql周日全量备份其他六天增量备份

脚本内容如下

#!/bin/bash

# 备份策略(可以根据需求调整备份策略)

# 周日全量备份

# 周一、周二、周三、周四、周五、周六增量备份

# 全量备份目录

Full=/data/backup/fullbak

# 增量备份目录

Incr=/data/backup/incbak

# MySQL相关配置信息

DB_HOST='localhost'

DB_PORT=3306

DB_USER='root'

DB_PASS='123'

DB_SOCK=/var/lib/mysql/mysql.sock

DB_CONF=/etc/my.cnf

# 备份的日志文件

baklog=/data/backup/back.log

# 备份开始

echo -e "\n\n" | tee -a ${baklog} 2>&1

echo -e "备份开始...\n" | tee -a ${baklog} 2>&1

# 判断当前日期是周几

Week=`date +%w`

case ${Week} in

	0)

		# 判断今日为周日则进行全量备份

		# 判断主目录是否存在，不存在则创建

		if [ ! -d "${Full}" ];then

			mkdir ${Full}

		fi

		# 第一步 创建本次的备份目录

		FullBakTime=`date +%Y%m%d-%H%M%S`
                
	       #!/bin/bash
	       OFDAY="`date "+%u"`"
	       STEPOFDAY="`expr $OFDAY + 1`"
	       STEPOFDAY_END="`expr $OFDAY + 7`"
	       date_befor_weekday="`date -d  '-'$STEPOFDAY_END' day' "+%Y%m%d"`"
	       date_befor_weekday_end="`date -d '-'$STEPOFDAY' day' "+%Y%m%d"`"
	       startDay="$date_befor_weekday"
       endDay="$date_befor_weekday_end"
       cd /data/backup/;
       mkdir bak/$date_befor_weekday
       while [ $startDay != $endDay ]
       do
	 #  echo ${startDay};
            startDay=`date -d " day  ${startDay}" +%Y%m%d` #关键步骤，获取第二天的时间
            for i in  ${startDay} 
        	do
	        cp -r incbak/$i* bak/$date_befor_weekday 
	      	done
	done

		 cp -r fullbak/$date_befor_weekday*_0 bak/$date_befor_weekday
	 	 tar -zcvf tar/$date_befor_weekday.bak.tar.gz bak/$date_befor_weekday/*

		mkdir -p ${Full}/${FullBakTime}_${Week}

		FullBakDir=${Full}/${FullBakTime}_${Week}

		# 第二步 开始全量备份

		echo -e "日期: ${FullBakTime}\n" | tee -a ${baklog} 2>&1

		echo -e "星期: ${Week}\n" | tee -a ${baklog} 2>&1

		echo -e "本次全量备份目录为 ${FullBakDir}\n" | tee -a ${baklog} 2>&1
		
		echo -e "已将 $date_befor_weekday - $date_befor_weekday_end 文件打包\n" | tee -a ${baklog} 2>&1

#		xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup   --target-dir=${FullBakDir} 
                xtrabackup --user=${DB_USER} --password=${DB_PASS} --backup   --target-dir=${FullBakDir} 

		dirStorage=`du -sh ${FullBakDir}`

		echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1

		echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1

		exit 0

		;;

	1|2|3|4|5|6)

		# 判断今日为周一、周二、周三、周四、周五、周六时，进行增量备份

		case ${Week} in

			1)

				# 基于上一次全量备份的增量备份

				# 判断主目录是否存在，不存在则创建

				if [ ! -d "${Incr}" ];then

					mkdir ${Incr}

				fi

				# 第一步 获取上一次全量备份

				LatestFullDir=`ls -lth ${Full}|grep -v '总用量'|grep -v 'total'|head -1|awk -F '[ ]+' '{print $NF}'`

				CompliteLatestFullDir=${Full}/${LatestFullDir}

				# 第二步 创建备份目录

				IncrBakTime=`date +%Y%m%d-%H%M%S`

				mkdir -p ${Incr}/${IncrBakTime}_${Week}

				IncrBakDir=${Incr}/${IncrBakTime}_${Week}

				# 第三步 开始增量备份

				echo -e "日期: ${IncrBakTime}\n" | tee -a ${baklog} 2>&1

				echo -e "星期: ${Week}\n" | tee -a ${baklog} 2>&1

				echo -e "本次备份为基于上一次全量备份${CompliteLatestFullDir}的增量备份\n" | tee -a ${baklog} 2>&1

				echo -e "本次增量备份目录为: ${IncrBakDir}\n" | tee -a ${baklog} 2>&1

#				xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup  --target-dir=${IncrBakDir} --incremental-basedir=${CompliteLatestFullDir}
                                xtrabackup --user=${DB_USER} --password=${DB_PASS} --backup  --target-dir=${IncrBakDir} --incremental-basedir=${CompliteLatestFullDir}

				dirStorage=`du -sh ${IncrBakDir}`

				echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1

				echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1

				exit 0

				;;

			2|3|4|5|6)

				# 基于上一次增量备份的增量备份

				# 判断主目录是否存在，不存在则创建

				if [ ! -d "${Incr}" ];then

					mkdir ${Incr}

				fi

				# 第一步 获取上一次增量备份

				LatestIncrDir=`ls -lth ${Incr}|grep -v '总用量' |grep -v 'total'|head -1|awk -F '[ ]+' '{print $NF}'`

				CompliteLatestIncrDir=${Incr}/${LatestIncrDir}

				# 第二步 创建备份目录

				IncrBakTime=`date +%Y%m%d-%H%M%S`

				mkdir -p ${Incr}/${IncrBakTime}_${Week}

				IncrBakDir=${Incr}/${IncrBakTime}_${Week}

				# 第三步 开始增量备份

				echo -e "日期: ${IncrBakTime}\n" | tee -a ${baklog} 2>&1

				echo -e "星期: ${Week}\n" | tee -a ${baklog} 2>&1

				echo -e "本次备份为基于上一次增量备份${CompliteLatestIncrDir}的增量备份\n" | tee -a ${baklog} 2>&1

				echo -e "本次增量备份目录为: ${IncrBakDir}\n" | tee -a ${baklog} 2>&1

#				xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup  --target-dir=${IncrBakDir} --incremental-basedir=${CompliteLatestIncrDir}
                                xtrabackup --user=${DB_USER} --password=${DB_PASS} --backup --target-dir=${IncrBakDir} --incremental-basedir=${CompliteLatestIncrDir} 

				dirStorage=`du -sh ${IncrBakDir}`

				echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1

				echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1

				exit 0

				;;

			*)

				echo -e "Error\n"

				exit 1

				;;

			esac

			;;

		*)

			echo -e "Error\n"

			exit 1

			;;

		esac

将脚本存放到 /data/backup目录下，起名为 db_backup.sh

加到计划任务中，每天凌晨3点执行备份脚本

crontab -e
0 3 * * * cd /data/backup; /usr/bin/bash db_backup.sh

以上内容根据自己实际情况进行修改。

其修改地方只有 MySQL相关配置信息 与 路径

如果不懂，请联系博主