利用 xtrabackup 工具实现增量备份 mysql(附脚本)

1、安装percona源

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

2、安装xtrabackup

yum install percona-xtrabackup-24 -y            #2.4之前版本可能不支持mysql5.7

3、创建备份文件夹

mkdir /data/backup/mysqlbak

4、编写备份脚本

vim mysqldump.sh

#!/bin/sh
base_dir="/data/backup/mysqlbak/"
log_file="/tmp/Backup.log"
increse_dir=`grep "Backup created in directory" $log_file | awk -F "'" {'print$2'}`
dir_name=`grep "Backup created in directory" $log_file | awk -F "'" {'print$2'} | awk -F "/" {'print$5'}`
increse_dir_path=`grep "Backup created in directory" $log_file | awk -F "'" {'print$2'} | awk -F '/' '{for(i=1;i<=4;i++)printf $i"/"; printf "\n"}'`
fullbackup_exist=`ls $base_dir | wc -l`
if [ $fullbackup_exist = 0 -a "$1" != "full_backup" ];then
		echo "you must make the fullbackup first! please usage: $0 full_backup"
		exit 88;
fi
full_backup() {
	innobackupex --user=root --password=password $base_dir
}
increase_backup() {
	innobackupex --user=root --password=password --incremental-basedir=$increse_dir --incremental $base_dir
	cd $increse_dir_path
	tar -zcvf ${dir_name}.tar.gz $dir_name
	mv $increse_dir /tmp/
}
case "$1" in
	full_backup)
		full_backup > $log_file 2>& 1
		;;
	increase_backup)
		increase_backup > $log_file 2>& 1
		;;
	*)
		echo "usage: $0 {full_backup|increase_backup}"
		;;
esac

5、执行第一次全量备份

./mysqldump.sh full_backup

6、创建计划任务，进行日常增量备份

crontab -e

01 00 * * * sh /root/mysqldump.sh increase_backup

7、制作还原脚本

#!/bin/sh
base_dir="/data/backup/"
tar_files=`find $base_dir -name "*.tar.gz"`
recover_mysql() {

for tar_file in $tar_files

do

cd $base_dir

tar -zxvf $tar_file

rm -f $tar_file

done

full_dir=ls -lt $base_dir | tail -1 | awk {'print$9'}

increase_dirs=ls -lrt $base_dir | grep "root" | awk {'print$9'}

innobackupex --apply-log --redo-only $bas{e}_{d}ir${full_dir}

for increase_dir in $increase_dirs

do

if [ "$increas{e}_{d}ir"!="$full_dir" ];then

innobackupex --apply-log --redo-only --incremental $bas{e}_{d}ir${full_dir} --incremental-dir=$bas{e}_{d}ir${increase_dir}

fi

done

innobackupex --copy-back $bas{e}_{d}ir${full_dir}

}
recover_mysql > /tmp/recover_mysql.log 2>& 1