xtrabackup使用-官方脚本

1、每天定时全量备份脚本

#!/bin/bash

#  0 0 * * * /path/to/daily_full_backup.sh

# Set server details (replace with your actual values)
SERVER_HOST="127.0.0.1" # MySQL地址
SERVER_USER="root" # MySQL用户
SERVER_PASSWORD="Password@2025" # MySQL密码
DATA_DIR="/var/lib/mysql" # MySQL数据存储路径
BACKUP_DIR="/backups/mysql/full" # 备份增量数据存储路径

# Get current date for filename
TODAY=$(date +%Y%m%d%H%M%S) # 年月日时分秒
FULL_BACKUP_FILENAME="${BACKUP_DIR}/${TODAY}" # 每天备份增量数据存储路径

mkdir -p ${FULL_BACKUP_FILENAME}

# Run xtrabackup with full backup command
xtrabackup --backup \
  --target-dir="${FULL_BACKUP_FILENAME}" \
  --datadir="${DATA_DIR}" \
  --user="${SERVER_USER}" \
  --password="${SERVER_PASSWORD}"

echo "Daily full backup completed at $(date)"

2、每小时差异备份备份脚本

差异备份就是备份自上次完全备份以来所有发生变化的数据。

#!/bin/bash

#  0 * * * * /path/to/your/hourly_incremental_backup.sh

# Set server details (same as daily script)
SERVER_HOST="127.0.0.1" # MySQL地址
SERVER_USER="root" # MySQL用户
SERVER_PASSWORD="Password@2025" # MySQL密码
DATA_DIR="/var/lib/mysql" # MySQL数据存储路径
BACKUP_DIR="/backups/mysql/incr" # 增量数据保存路径

# Get current date and hour for filename
TODAY=$(date +%Y-%m-%d) 
HOUR=$(date +%H%M%S) # Get current hour (00-23)

# TODAY和HOUR可以合并使用一个变量：$(date +%Y%m%d%H%M%S)可以使用这个，年月日时分秒

INCR_BACKUP_FILENAME="${BACKUP_DIR}/${TODAY}_${HOUR}" # 每小时增量数据保存路径

# Set the base directory for incremental backups (point to the latest full backup)
BASE_DIR="/backups/mysql/full/$(ls -tr /backups/mysql/full | tail -n 1)" # 获取最新的全量数据保存信息

mkdir -p ${INCR_BACKUP_FILENAME}

# Run xtrabackup with incremental backup command
xtrabackup --backup \
  --incremental-basedir="${BASE_DIR}" \
  --target-dir="${INCR_BACKUP_FILENAME}" \
  --user="${SERVER_USER}" \
  --password="${SERVER_PASSWORD}"

echo "Hourly incremental backup completed at $(date)"