利用 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 ${base_dir}${full_dir}
for increase_dir in $increase_dirs
do
if ["$increase_dir" != "$full_dir"];then
innobackupex --apply-log --redo-only --incremental ${base_dir}${full_dir} --incremental-dir=${base_dir}${increase_dir}
fi
done
innobackupex --copy-back ${base_dir}${full_dir}
}
recover_mysql > /tmp/recover_mysql.log 2>& 1