xtrabackup全备增备以及恢复脚本

1.全量备份脚本

[root@localhost mysql]# more xtrabackupfull.sh
#!/bin/bash
#远程备份IP地址
#back_ip="127.0.0.1"

#主机ip
#数据库信息
mysql_data_dir="/data/data"
mysql_user="root"
mysql_password="mysql"
mysql_host="127.0.0.1"
mysql_port=3306

#定义备份目录
backup_dir="/home/mysql/back_full"

echo "执行全量备份..."
# 获取当前时间戳
timestamp=$(date +'%Y%m%d%H%M%S')
back_dir="${backup_dir}/${timestamp}"
# 创建备份目录
mkdir -p "$back_dir"


#获取最新的全量备份目录
new_backup_dir=$(ls -1t "$backup_dir" | head -1)

#全量备份目录
full_dir="${backup_dir}/${new_backup_dir}"

#本地打包备份目录
#tar_backup="/home/mysql/full_mysqlbackup"
#mkdir -p $tar_backup
# 执行全量备份
xtrabackup --backup --target-dir="${back_dir}" --user="$mysql_user" --password="$mysql_password" --host="$mysql_host" --port="$mysql_port"

#echo "压缩最新的全量备份..."
#cd $full_dir
#tar -cvf "${tar_backup}/${timestamp}.tar.gz" $full_dir
#scp ${tar_backup}/${timestamp}.tar.gz $back_ip:$tar_backup




2.增备脚本

[root@localhost mysql]# more xtrabackupinc.sh
#!/bin/bash
# 远程备份IP地址
#back_ip="192.168.40.112"

# 数据库信息
mysql_user="root"
mysql_password="mysql"
mysql_host="127.0.0.1"
mysql_port=3306

# 定义增量备份目录
backup_dir="/home/mysql/back_inc"

# 获取上一次的增量备份目录
last_backup_dir=$(ls -1t "$backup_dir" | head -1)

# 如果没有上一次的增量备份目录，那么使用最新的全量备份目录
if [ -z "$last_backup_dir" ]; then
full_back_dir="/home/mysql/back_full/"
last_backup_dir=$(ls -1t "$full_back_dir" | head -1)
backup_type="full_inc"
else
backup_type="inc"
fi

# 获取当前时间戳
timestamp=$(date +'%Y%m%d%H%M%S')
back_dir="${backup_dir}/${timestamp}"

# 创建增量备份目录
mkdir -p "$back_dir"

# 本地打包备份目录
#tar_backup="/opt/${backup_type}_mysqlbackup"
#mkdir -p "$tar_backup"

# 根据备份类型设置备份目录
if [ "$backup_type" = "full_inc" ]; then
backup_basedir="$full_back_dir"
else
backup_basedir="$backup_dir"
fi

# 执行增量备份
xtrabackup --backup --target-dir="${back_dir}" --user="$mysql_user" --password="$mysql_password" --host="$mysql_host" --port="$mysql_port" --incremental-basedir="${backup
_basedir}/${last_backup_dir}"

#echo "压缩最新的${backup_type}备份..."
#cd $back_dir
#tar -cvf "${tar_backup}/${timestamp}.tar.gz" $back_dir
#scp ${tar_backup}/${timestamp}.tar.gz $back_ip:$tar_bakup


3.定时任务

0 22 * * 0 sh /root/xtrabackupfull.sh
0 22 * * 1-5 sh /root/xtrabackupinc.sh
50 21 * * 0 rm -rf /home/mysql/back_full/*
50 21 * * 0 rm -rf /home/mysql/back_inc/*



4.全量增量恢复步骤

4.1查看xtrabackup_checkpoints文件

全备开始lsn=0到lsn=28803598
[root@localhost 20240531135933]# pwd
/home/mysql/back_full/20240531135933
[root@localhost 20240531135933]# more xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 28803598
last_lsn = 28803598
flushed_lsn = 28803598
redo_memory = 0
redo_frames = 0

第一次增备 开始lsn=28802751到lsn=28802761
[root@localhost mysql]# cd back_inc/20240531135943/
[root@localhost 20240531135943]# more xtrabackup_checkpoints
backup_type = incremental
from_lsn = 28802751
to_lsn = 28802761
last_lsn = 28802761
flushed_lsn = 28802761
redo_memory = 0
redo_frames = 0

第二次增备 开始lsn=28802761=28803598
[root@localhost 20240531140220]# more xtrabackup_checkpoints
backup_type = incremental
from_lsn = 28802761
to_lsn = 28803598
last_lsn = 28803598
flushed_lsn = 28803598
redo_memory = 0
redo_frames = 0

4.2预备完整备份
xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/back_full/20240531135933

4.3预备增量备份

合并第一个增量备份到完整备份
xtrabackup --prepare --apply-log-only --target-dir=/home/mysql/back_full/20240531135933 --incremental-dir=/home/mysql/back_inc/20240531135943


合并第二个增量备份新的完整备份（注意：最后一次合并我没有加–apply-log-only

xtrabackup --prepare --target-dir=/home/mysql/back_full/20240531135933 --incremental-dir=/home/mysql/back_inc/20240531140220

注意：--apply-log-only 此参数的目的是未提交的事务不回滚，在存在多个增量备份时，准备阶段的最后一个增量备份时，不要加此参数

4.4停数据库备份目录
service mysqld stop

cd /data
cp -r data dababak 备份数据库目录

cd /data/data

rm -rf * 删除原来数据目录文件


4.5恢复最新的完整备份到数据库

xtrabackup --host=127.0.0.1 --user=root --password='mysql' --port=3306 --datadir=/data/data --copy-back --target-dir=/home/mysql/back_full/20240531135933

4.6修改恢复文件权限启动数据库

cd /data
chown -R mysql.mysql data

service mysqld start

恢复完成！


5.异机恢复

新服务器上完成同版本的数据库的安装，数据库目录也是/data/data

已经完成了4.1-4.3的步骤

将合并后的/home/mysql/back_full/20240531135933 全备文件拷贝到新服务器上

rsync -avrP /home/mysql/back_full/20240531135933 root@新服务器ip:/data/mysql/

修改权限

启动数据库

完成迁移！！！