MySQL 8.0.21 使用xtrabackup增量备份

 

 

#!/bin/env bash

# create user xtrabackup@127.0.0.1 identified by 'cruces';
# grant backup_admin,process,reload,lock tables,replication client,replication slave on *.* to xtrabackup@127.0.0.1;
# flush privilges;

# Prepare
# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/incremental_1
# xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/incremental_2
# Restoring
# xtrabackup --copy-back --datadir=/backup/copy-back --target-dir=/data/mysql


base_dir=/backup
log_file=${base_dir}/xtrabackup.log
defaults_file=/etc/my.cnf
host=172.16.10.200
user=root
password=arbitrary0!
port=3306


function reorganize() {
        find $base_dir -maxdepth 1 ! -path $base_dir -mtime +9 -type d -exec rm -rf {} \;
}

function full_backup() {
        backup_dir=${base_dir}/`date +%F`
        if [ ! -d $backup_dir ];then
                mkdir $backup_dir
        fi
        xtrabackup --defaults-file=$defaults_file --backup --host=$host --user=$user --password=$password --port=$port --target-dir=$backup_dir/base &>> $log_file
        reorganize
}

function incremental_backup() {
        day_of_week=`date +%u`
        backup_dir=${base_dir}/`date -d 'last monday' +%F`
        target_dir=${backup_dir}/incremental_$((day_of_week-1))
        if [[ $day_of_week -eq 2 ]];then
                incremental_basedir=${backup_dir}/base
        else
                incremental_basedir=${backup_dir}/incremental_$((day_of_week-2))
                                if [ ! -d $incremental_basedir ];then
                                                incremental_basedir=${base_dir}/`date -d 'last monday' +%F`/base
                                fi
        fi

        if [[ -e $target_dir ]];then
                echo "############################################################################" >> $log_file
                echo `date +"%F %T %u %A %B %j"` $target_dir already exits!!!!!!!!!!>> $log_file
                echo "############################################################################" >> $log_file
                exit 0
        fi

        xtrabackup --defaults-file=$defaults_file --backup --user=$user --host=$host --password=$password --port=$port --target-dir=${target_dir} \
--incremental-basedir=${incremental_basedir} &>> $log_file
}

function alternate() {
                full_dir=${base_dir}/`date -d 'last monday' +%F`/base
                if [ ! -d ${full_dir} ];then
                mkdir -p $full_dir
                echo -e '\n\n\n\n' >> $log_file
                echo "############################################################################" >> $log_file
                echo `date +"%F %T %u %A %B %j"` A New Round backup begin!!!!!!!!!!>> $log_file
                echo "############################################################################" >> $log_file
                echo "############################################################################" >> $log_file
                echo `date +"%F %T %u %A %B %j"` full backup starting >> $log_file
                echo "############################################################################" >> $log_file
                xtrabackup --defaults-file=$defaults_file --backup --user=$user --host=$host --password=$password --port=$port --target-dir=$full_dir &>> $log_file
                reorganize
                exit 0
                fi

}

function main() {
        if [[ `date +%u` -eq 1 ]];then
                echo -e '\n\n\n\n' >> $log_file
                echo "############################################################################" >> $log_file
                echo `date +"%F %T %u %A %B %j"` A New Round backup begin!!!!!!!!!!>> $log_file
                echo "############################################################################" >> $log_file
                echo "############################################################################" >> $log_file
                echo `date +"%F %T %u %A %B %j"` full backup starting >> $log_file
                echo "############################################################################" >> $log_file
                full_backup
        else
                alternate
                echo -e '\n' >> $log_file
                echo "############################################################################" >> $log_file
                echo `date +"%F %T %u %A %B %j"` incremental backup starting >> $log_file
                echo "############################################################################" >> $log_file
                incremental_backup
        fi
}


main