xtrabackup备份与恢复

                                                                xtrabackup及主从同步

1.从库备份：

!/bin/bash

设置数据库连接参数

DB_USER="oper_dc"
DB_PASSWORD="operzADF2020qwer"
DB_INSTANCE=3306
BACKUP_DIR="/data/backup/xtrabackup/${DB_INSTANCE}"
base_dir=$(cd $(dirname $0);pwd)
datetime=date +'%Y-%m-%d'
export PATH=${base_dir}/bin:$PATH

创建备份目录

if [ ! -d $BACKUP_DIR ]; then
mkdir -p $BACKUP_DIR
fi
BACKUP_FILE="$BACKUP_DIR/backup-${DB_INSTANCE}-${datetime}.tar.gz"

open --compress=lz4

if recovery please use --decompress --remove-original decompress before prepare

xtrabackup --defaults-file=/data/mysqldata/${DB_INSTANCE}/etc/my.cnf --user=$DB_USER --password=$DB_PASSWORD --compress=lz4 --backup --target-dir=$BACKUP_DIR/full_backup_${datetime} > $BACKUP_DIR/backup-${datetime}.log 2>&1
if grep -q "completed OK" "$BACKUP_DIR/backup-${datetime}.log"
then
echo "BACUP SUCCESS BEGIN COMPRESS"
tar -zcvf $BACKUP_FILE $BACKUP_DIR/full_backup_${datetime} > /dev/null 2>&1
if [ $? -eq 0 ]
then
rm -rf $BACKUP_DIR/full_backup_${datetime}
fi
else
echo "BACUP failed please check backup and try again "
fi

删除7天前的备份文件

find $BACKUP_DIR -type f -name "backup-*" -mtime +1 -exec rm {} ;

2.解压
nohup tar -xvf backup-3306-2025-02-06.tar.gz &

3.解压lz4文件
里面有大量.lz4的文件需要解压一下
xtrabackup --decompress --remove-original --target-dir=/data/backup/2024-05-23/

4.应用日志
应用日志：（如果不是增量备份，不需要做这一步，否则会导致数据没有undo回滚引发数据不一致问题）
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/2024-05-23/

5.rsync拷贝
拷贝备份文件到从库
[root@localhost ~]# rsync -va /data/backup/2024-05-23 从库IP:/data/