centos7 xtrabackup mysql（8）增量备份 脚本（2）

centos7 xtrabackup mysql（8）增量备份 脚本（2）

Shell 基本运算符
https://www.runoob.com/linux/linux-shell-basic-operators.html

https://blog.csdn.net/weixin_30823001/article/details/96417008

https://blog.csdn.net/weixin_35985795/article/details/113379060

https://blog.csdn.net/qq_42768234/article/details/133277583

测试用脚本

cat script_xtrabackup.sh

#!/bin/sh
. /etc/profile
user=root
password=1234aA~1
backup_dir=/data/
gzip_dir=$backup_dir
log_dir=$backup_dir/log

# 全备是在一周的第几天
full_backup_week_day=1
error_log=

# 备份日期

backup_date=`date +%Y%m%d`


# 备份时间

backup_time=`date +%H-%M-%S`

# 备份时的周几

backup_week_day=`date +%u`

mkdir -p $backup_dir

mkdir -p $log_dir


log_file=${backup_dir}/log/backup.log
log_x_file=${backup_dir}/log/backup_xtra.log


x_file_size=`ls -l $log_x_file | awk '{print $5}'`

num_x_file_size=$((x_file_size))
echo $num_x_file_size

if [ $num_x_file_size -gt 9000000 ];then
    echo "" > $log_x_file
fi


echo $backup_date
echo $backup_time
echo $backup_week_day

let delta=backup_week_day-full_backup_week_day

echo $delta
echo ""
echo ""
echo '------------------  start  ---------------------'${backup_date} ${backup_time} >> $log_file

if [ "$delta" -eq 0 ]
then
    # 全量备份
    echo '全量备份 delta=$delta' >> ${log_file}

    mkdir -p $backup_dir/backup$backup_date

    if [ -e "$backup_dir/backup$backup_date/full_backup" ];then
        echo "full_backup exist " >> ${log_file}

        exit 0
    fi

    xtrabackup --backup --target-dir=$backup_dir/backup$backup_date/full_backup --user=$user --password=$password --socket=/opt/datadir/mysql/mysql.sock >> ${log_x_file} 2>&1

elif [ "$delta" -eq 1 ]
then
    echo ""   >> ${log_file}
    echo ""   >> ${log_file}
    echo "第一次增量备份 delta=$delta" >> ${log_file}
    date_last=$(date -d "-1 day" '+%Y%m%d')
    echo $date_last >> ${log_file}

    if [ -e "$backup_dir/backup${date_last}/inc_1_backup" ];then
        echo "inc_1_backup exist " >> ${log_file}

        exit 0
    fi

    if [ ! -e "$backup_dir/backup${date_last}/full_backup" ];then
        echo "full_backup not exist " >> ${log_file}

        exit 0
    fi



    xtrabackup --backup --target-dir=$backup_dir/backup${date_last}/inc_1_backup --incremental-basedir=$backup_dir/backup${date_last}/full_backup --user=root --password=1234aA~1 --socket=/opt/datadir/mysql/mysql.sock  >> ${log_x_file} 2>&1

elif [ "$delta" -gt 0 ]
then
    echo ""   >> ${log_file}
    echo ""   >> ${log_file}
    echo "delta大于0 增量备份 delta=$delta" >> ${log_file}
    date_last=$(date -d "-${delta} day" '+%Y%m%d')
    echo $date_last  >> ${log_file}

    if [ -e "$backup_dir/backup${date_last}/inc_${delta}_backup" ];then
        echo "inc_${delta}_backup exist " >> ${log_file}

        exit 0
    fi


    if [ ! -e "$backup_dir/backup${date_last}/inc_$((delta-1))_backup" ];then
        echo "$backup_dir/backup${date_last}/inc_$((delta-1))_backup"
        echo "inc_$((delta-1))_backup not exist " >> ${log_file}

        exit 0
    fi



    xtrabackup --backup --target-dir=$backup_dir/backup${date_last}/inc_${delta}_backup --incremental-basedir=$backup_dir/backup${date_last}/inc_$((delta-1))_backup --user=root --password=1234aA~1 --socket=/opt/datadir/mysql/mysql.sock  >> ${log_x_file} 2>&1
else
    echo ""   >> ${log_file}
    echo ""   >> ${log_file}
    echo "delta小于0 增量备份 delta=$delta" >> ${log_file}
    let num=7+$delta
    echo 'num ='${num} >> ${log_file}
    date_last=$(date -d "-${num} day" '+%Y%m%d')
    echo $date_last >> ${log_file}
    echo $((num-1)) >> ${log_file}

    if [ -e "$backup_dir/backup${date_last}/inc_${num}_backup" ];then
        echo "inc_${num}_backup exist " >> ${log_file}

        exit 0
    fi


    if [ ! -e "$backup_dir/backup${date_last}/inc_$((num-1))_backup" ];then
        echo "inc_$((num-1))_backup not exist " >> ${log_file}

        exit 0
    fi


    xtrabackup --backup --target-dir=$backup_dir/backup${date_last}/inc_${num}_backup --incremental-basedir=$backup_dir/backup${date_last}/inc_$((num-1))_backup --user=root --password=1234aA~1 --socket=/opt/datadir/mysql/mysql.sock  >> ${log_x_file} 2>&1
fi





#sudo xtrabackup --backup --target-dir=/data//backup20240729/inc_3_backup --incremental-basedir=/data//backup20240729/inc_1_backup --user=root --password=1234aA~1 --socket=/opt/datadir/mysql/mysql.sock

恢复提示语句 脚本

cat create_restore_file.sh

#!/bin/sh

mysql_dir=/opt/datadir/
backup_dir=/data/

date2="20080203"
date_store=`date -d "$date2" +%s`
echo $date_store
file_store=''
for file in $backup_dir/*
do
    echo $file
    if [ -d "$file" ];then
        echo "$file is a dir"
        dir_name=${file##*/}
        echo "file name = $dir_name"
        if [[ $dir_name == backup* ]];then
            echo "dir_name= $dir_name"
            substring=${dir_name:6:8}
            echo "substring=$substring"
            date1=`date -d "$substring" +%s`
            echo "$date1"
            if [ $date_store -lt $date1 ];then
               date_store=$date1
               echo "hello $date_store"
               file_store=$file
               echo "$file_store"
            fi
        fi
    fi
done

num='0'
for file in $file_store/*
do
    echo $file
    if [ -d "$file" ];then
        dir_name=${file##*/}
        echo "file name = $dir_name"
        if [[ $dir_name == inc* ]];then
            echo "dir_name= $dir_name"
            substring=${dir_name:4:1}
            echo "substring=$substring"
            if [ $num -lt $substring ];then
               num=$substring
            fi
        fi
    fi
done

echo "num= $num"
echo "sudo systemctl stop mysqld" > ~/restore_sql.txt
echo "sudo cp -r ${mysql_dir}/mysql ${mysql_dir}/bak_mysql" >> ~/restore_sql.txt
echo "sudo rm -rf ${mysql_dir}/mysql/*" >> ~/restore_sql.txt

echo "sudo xtrabackup --prepare --apply-log-only --target-dir=$file_store/full_backup" >> ~/restore_sql.txt


cnt=$((num))
echo "cnt = $cnt"
if [ $num -gt '0' ];then
    for((i=1;i<$cnt;i++));
    do
        echo "xtrabackup --prepare --apply-log-only --target-dir=$file_store/full_backup --incremental-dir=$file_store/inc_${i}_backup" >> ~/restore_sql.txt
    done
    echo "xtrabackup --prepare  --target-dir=$file_store/full_backup --incremental-dir=$file_store/inc_${cnt}_backup" >> ~/restore_sql.txt

fi

echo "sudo xtrabackup --copy-back  --target-dir=$file_store/full_backup" >> ~/restore_sql.txt

echo "sudo chown -R mysql.mysql ${mysql_dir}/mysql/*" >> ~/restore_sql.txt

echo "sudo systemctl restart mysqld" >> ~/restore_sql.txt