mysql xtrabackup备份两周，每周日做一次全备，每天做增量备份和恢复到两周内任何一天的脚本

1. 全备份脚本（每周日执行）：

#!/bin/bash

# 设置备份目录和日志文件目录
BACKUP_DIR=/data/backups
LOG_DIR=/data/logs

# 创建备份目录和日志文件目录
mkdir -p $BACKUP_DIR
mkdir -p $LOG_DIR

# 设置备份文件名和日志文件名
BACKUP_FILE=$BACKUP_DIR/$(date +%Y%m%d)_full_backup.xbstream
LOG_FILE=$LOG_DIR/$(date +%Y%m%d)_full_backup.log

# 执行全备份
xtrabackup --backup \
--target-dir=$BACKUP_DIR \
--stream=xbstream \
--compress \
--compress-threads=4 \
--parallel=4 \
--compress-chunk-size=64K \
2>$LOG_FILE | gzip > $BACKUP_FILE.gz

# 打印备份完成信息
echo "Full backup complete: $BACKUP_FILE.gz"

2. 增量备份脚本（每天执行）：

#!/bin/bash

# 设置备份目录和日志文件目录
BACKUP_DIR=/data/backups
LOG_DIR=/data/logs

# 创建备份目录和日志文件目录
mkdir -p $BACKUP_DIR
mkdir -p $LOG_DIR

# 设置备份文件名和日志文件名
BACKUP_FILE=$BACKUP_DIR/$(date +%Y%m%d)_incr_backup.xbstream
LOG_FILE=$LOG_DIR/$(date +%Y%m%d)_incr_backup.log

# 执行增量备份
xtrabackup --backup \
--target-dir=$BACKUP_DIR \
--stream=xbstream \
--compress \
--compress-threads=4 \
--parallel=4 \
--compress-chunk-size=64K \
--incremental-basedir=$(ls -td $BACKUP_DIR/*_full_backup.xbstream | head -1) \
2>$LOG_FILE | gzip > $BACKUP_FILE.gz

# 打印备份完成信息
echo "Incremental backup complete: $BACKUP_FILE.gz"

3. 恢复脚本（用于恢复到两周内任何一天）：

#!/bin/bash

# 设置备份目录和日志文件目录
BACKUP_DIR=/data/backups
LOG_DIR=/data/logs

# 创建备份目录和日志文件目录
mkdir -p $BACKUP_DIR
mkdir -p $LOG_DIR

# 设置恢复日期
RECOVERY_DATE=$1

# 获取最近的全备份和增量备份
LATEST_FULL_BACKUP=$(ls -td $BACKUP_DIR/*_full_backup.xbstream | head -1)
INCREMENTAL_BACKUPS=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -B 1 $RECOVERY_DATE | head -n 1)

# 打印恢复信息
echo "Recovering to $RECOVERY_DATE"
echo "Latest full backup: $LATEST_FULL_BACKUP"
echo "Incremental backups: $INCREMENTAL_BACKUPS"

# 执行恢复
xtrabackup --prepare \
--apply-log-only \
--target-dir=$BACKUP_DIR \
--incremental-dir=$INCREMENTAL_BACKUPS \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 1 $INCREMENTAL_BACKUPS | tail -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 2 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 3 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 4 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 5 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 6 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 7 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 8 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 9 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 10 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 11 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 12 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 13 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 14 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--incremental-dir=$(ls -td $BACKUP_DIR/*_incr_backup.xbstream | grep -A 15 $INCREMENTAL_BACKUPS | tail -n 1 | head -n 1) \
--target-dir=$BACKUP_DIR \
2>$LOG_DIR/recovery.log

# 打印恢复完成信息
echo "Recovery complete"