mysql异地增量备份工具_利用 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