优点:热备份,不影响业务,增量备份,远程备份。

目的:自动打包备份到远程10.0.0.111备份服务器


好用帮点赞!谢谢小伙伴的支持。


前提:

1.安装xtrabackup

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

yum install percona-xtrabackup-22


2.创建目录

mkdir -p /data/backup

mkdir -p /data/backuptar/


3.添加mysql公钥到备份服务器(10.0.0.111),能免密码远程登录。

4.创建备份用户

GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'bkuser'@'localhost' IDENTIFIED BY 'passwd';

FLUSH PRIVILEGES;


------------------------

脚本:

#!/bin/bash

backdir=/data/backup

backupbin=/usr/bin

begintime=`date +"%Y-%m-%d %H:%M:%S"`

format_time=`date +"%Y-%m-%d_%H_%M_%S"` 

time_cost=$backdir/xtrabackup_time.txt

week=`date +%w`

user_name=bkuser

password="passwd"

file_cnf=/etc/my.cnf.d/wsrep.cnf

backtar=/data/backuptar/

remote_host="10.0.0.111"

remote_dir=/data/mysqlbak_remote_23

remote_user="wanzi"

out_log=$backdir/xtrabackup_log_$format_time


  

if [ -d "$backdir/rec5" ];then

        echo "开始打包5次的备份"

        cd $backdir

        tar -zcf lastweek.gz ./*

        mv lastweek.gz $backtar/$format_time.lastweek.gz

        echo "完成5次备份打包"

        cd $backtar

        rm -rf `ls |grep -v "$format_time.lastweek.gz"`

        echo "删除本地上次备份完成"


        echo "开始删除远程备份....."

        ssh $remote_user@$remote_host "cd $remote_dir;rm -rf ./*"

        echo "删除远程多余备份完成"

        echo "开始拷贝本地备份到远程服务器..."

        scp $backtar/$format_time.lastweek.gz $remote_host:/$remote_dir

        echo "远程备份完成"

        rm -rf $backdir

        mkdir $backdir

fi


#fullbackup   

if [ ! -d "$backdir/fullbackup" ];then

        echo "在$begintime开始全量备份" >>$time_cost

        $backupbin/innobackupex --defaults-file=$file_cnf --user=$user_name --password=$password --no-timestamp --slave-info  $backdir/fullbackup 1>$out_log 2>&1

        echo "完成全备"

elif [ ! -d "$backdir/rec0" ];then   

        echo "#####start 0 incremental backup at $BEGINTIME to directory rec0" >>$time_cost   

        $backupbin/innobackupex --defaults-file=$file_cnf  --no-timestamp --user=$user_name --password=$password --slave-info  --incremental --incremental-basedir=$bac

kdir/fullbackup $backdir/rec0 1> $out_log 2>&1

        echo "完成第0次增量备份"

        

elif [ ! -d "$backdir/rec1" ];then  

        echo "#####start 1 incremental backup at $BEGINTIME to directory rec1" >>$time_cost  

        $backupbin/innobackupex --defaults-file=$file_cnf  --no-timestamp --user=$user_name --password=$password --slave-info  --incremental --incremental-basedir=$bac

kdir/rec0 $backdir/rec1 1> $out_log 2>&1

        echo "完成第1次增量备份"

            

elif [ ! -d "$backdir/rec2" ];then  

        echo "#####start 2 incremental backup at $BEGINTIME to directory rec2" >>$time_cost  

        $backupbin/innobackupex --defaults-file=$file_cnf  --no-timestamp --user=$user_name --password=$password --slave-info  --incremental --incremental-basedir=$bac

kdir/rec1 $backdir/rec2 1> $out_log 2>&1  

        echo "完成第2次增量备份"

         

elif [ ! -d "$backdir/rec3" ];then  

        echo "#####start 3 incremental backup at $BEGINTIME to directory rec3" >>$time_cost  

        $backupbin/innobackupex --defaults-file=$file_cnf  --no-timestamp --user=$user_name --password=$password --slave-info  --incremental --incremental-basedir=$bac

kdir/rec2 $backdir/rec3 1> $out_log 2>&1 

        echo "完成第3次增量备份"

         

elif [ ! -d "$backdir/rec4" ];then  

        echo "#####start 4 incremental backup at $BEGINTIME to directory rec4" >>$time_cost  

        $backupbin/innobackupex --defaults-file=$file_cnf  --no-timestamp --user=$user_name --password=$password --slave-info  --incremental --incremental-basedir=$bac

kdir/rec3 $backdir/rec4 1> $out_log 2>&1

        echo "完成第4次增量备份"

         

elif [ ! -d "$backdir/rec5" ];then  

        echo "#####start 5 incremental backup at $BEGINTIME to directory rec5" >>$time_cost  

        $backupbin/innobackupex --defaults-file=$file_cnf  --no-timestamp --user=$user_name --password=$password --slave-info  --incremental --incremental-basedir=$bac

kdir/rec4 $backdir/rec5 1> $out_log 2>&1  

         echo "完成第5次增量备份"

 fi  

 ENDTIME=`date +"%Y-%m-%d %H:%M:%S"`  

 begin_data=`date -d  "$BEGINTIME" +%s`  

 end_data=`date -d  "$ENDTIME" +%s`  

 spendtime=`expr $end_data - $begin_data`  

 echo "it takes $spendtime sec for packing the data directory" >>$time_cost  


echo "备份结束......"`date`