#知识分享# #知识# 数据库数据是必须要进行备份的，而备份的目的是为了当出现意外情况的时候用于数据恢复。
数据备份又分为逻辑备份和物理备份，逻辑备份指的是通过sql的方式进行备份，如果数据量小，适合使用sql的方式进行备份，但是如果数据库稍微大一点，那么sql备份的方式就不适合了，因为随着数据量的增加，备份文件也随之增加，恢复的过程相当慢，慢的可能你都无法接受，所以就需要使用物理备份及使用物理备份恢复数据的方式。
Xtrabackup安装
1.1 搭建网络 yum源
# cd /etc/yum.repos.d/
# rm -rf *
# wget http://mirrors.163.com/.help/CentOS7-Base-163.repo
# yum clean all
# yum makecache 1.2 安装依赖组件libev
# yum install -y http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm 1.3 安装Percona yum存储库
# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y 1.4 开启tools存储库
# percona-release enable-only tools release 1.5 安装Percona XtraBackup
# yum install -y percona-xtrabackup-80 1.6 查看版本
# xtrabackup --version 1.7 安装压缩工具qpress
# yum -y install qpress Xtrabackup自动备份脚本
2.1 备份脚本
#!/bin/bash
# 此脚本只适用于mysql8版本, mysql5.5、5.6、5.7版本的会另外再发布一个
# crontab设置:(每天0点全备,其他时间每隔两小时一次增量)
# 0 0 * * * /home/backups/backup_func.sh full
# 0 2-22/2 * * * /home/backups/backup_func.sh incr
# 备份相关基础路径(必填)
BACKUPDIR=/backup/mysql
# 全量备份目录
Full=$BACKUPDIR/full
# 增量备份目录
Incr=$BACKUPDIR/incr
# 备份归档路径
Old=$BACKUPDIR/old
# 日志路径
baklog=$BACKUPDIR/backup.log
# 日期信息,用于区分当天备份用于归档
# TODAY=$(date +%Y%m%d%H%M)
YESTERDAY=$(date -d"yesterday" +%Y%m%d)
# Mysql实例相关信息(必填!!!)
MYSQL=/usr/local/mysql/bin/mysql
MYSQLADMIN=/usr/local/mysql/bin/mysqladmin
DB_HOST='localhost'
DB_PORT=3309
DB_USER='username'
DB_PASS='password'
DB_SOCK=/tmp/mysql.sock
DB_CONF=/etc/my.cnf
# 备份必备工具检查(压缩备份需要qpresss)
tools="percona-xtrabackup-80 qpress"
# Check packages before proceeding
for i in $tools; do
if ! [[ $(rpm -qa $i) =~ ${i} ]]; then
echo -e " Needed package $i not found.\n Pre check failed !!!"
exit 1
fi
done
# mysql 运行状态监测
if [ -z "$($MYSQLADMIN --host=$DB_HOST --socket=${DB_SOCK} --user=$DB_USER --password=$DB_PASS --port=$DB_PORT status | grep 'Uptime')" ]; then
echo -e "HALTED: MySQL does not appear to be running or incorrect username and password"
exit 1
fi
####################################################
#归档备份函数(全量时自动触发)
####################################################
function Xtr_tar_backup() {
# if [ ! -d "${Old}" ]; then
# mkdir ${Old}
# fi
for i in $Full $Incr $Old; do
if [ ! -d $i ]; then
mkdir -pv $i
fi
done
# 压缩上传前一天的备份
echo "压缩前一天的备份，移动到${old}"
cd $BACKUPDIR
tar -zcvf $YESTERDAY.tar.gz ./full/ ./incr/
#scp -P 8022 $YESTERDAY.tar.gz [email protected]:/data/backup/mysql/
mv $YESTERDAY.tar.gz $Old
if [ $? = 0 ]; then
rm -rf $Full $Incr
echo "Tar old backup succeed" | tee -a ${baklog} 2>&1
else
echo "Error with old backup." | tee -a ${baklog} 2>&1
fi
}
####################################################
#全量备份函数(手动触发)
####################################################
function Xtr_full_backup() {
if [ ! -d "${Full}" ]; then
mkdir ${Full}
fi
Xtr_tar_backup
# 第一步 创建本次的备份目录
FullBakTime=$(date +%Y%m%d-%H%M%S)
mkdir -p ${Full}/${FullBakTime}
FullBakDir=${Full}/${FullBakTime}
# 第二步 开始全量备份
echo -e "备份时间: ${FullBakTime}\n" | tee -a ${baklog} 2>&1
echo -e "本次全量备份目录为 ${FullBakDir}\n" | tee -a ${baklog} 2>&1
xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --port=$DB_PORT --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup --compress --compress-threads=4 --target-dir=${FullBakDir}
dirStorage=$(du -sh ${FullBakDir})
echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1
echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1
exit 0
}
####################################################
#增量备份函数(手动触发)
####################################################
function Xtr_incr_backup() {
# 第一步 获取上一次全量备份和增量备份信息
LATEST_INCR=$(find $Incr -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1)
LATEST_FULL=$(find $Full -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1)
if [ ! $LATEST_FULL]; then
echo "xtrabackup_info does not exist. Please make sure full backup exist."
exit 1
fi
echo "LATEST_INCR=$LATEST_INCR"
if [ ! -d "${Incr}" ]; then
mkdir ${Incr}
fi
# 判断上一次的备份路径,如果增量备份路径为空,则使用全量备份路径为--incremental-basedir
if [ ! $LATEST_INCR ]; then
CompliteLatestFullDir=$LATEST_FULL
else
CompliteLatestFullDir=$LATEST_INCR
fi
# 第二步 创建备份目录
IncrBakTime=$(date +%Y%m%d-%H%M%S)
mkdir -p ${Incr}/${IncrBakTime}
IncrBakDir=${Incr}/${IncrBakTime}
# 第三步 开始增量备份
echo -e "日期: ${IncrBakTime}\n" | tee -a ${baklog} 2>&1
echo -e "整点: ${Hour}\n" | tee -a ${baklog} 2>&1
echo -e "本次备份为基于上一次备份${CompliteLatestFullDir}的增量备份\n" | tee -a ${baklog} 2>&1
echo -e "本次增量备份目录为: ${IncrBakDir}\n" | tee -a ${baklog} 2>&1
xtrabackup --defaults-file=${DB_CONF} --host=${DB_HOST} --port=$DB_PORT --user=${DB_USER} --password=${DB_PASS} --socket=${DB_SOCK} --backup --compress --compress-threads=4 --parallel=4 --target-dir=${IncrBakDir} --incremental-basedir=${CompliteLatestFullDir}
dirStorage=$(du -sh ${IncrBakDir})
echo -e "本次备份数据 ${dirStorage}\n" | tee -a ${baklog} 2>&1
echo -e "备份完成...\n\n\n" | tee -a ${baklog} 2>&1
exit 0
}
####################################################
#主体备份函数
####################################################
function printInfo() {
echo "Your choice is $1"
}
case $1 in
"full")
echo "Your choice is $1"
Xtr_full_backup
;;
"incr")
echo "Your choice is $1"
Xtr_incr_backup
;;
*)
echo -e "No parameters specified!\nFor example:\n$0 full\n$0 incr"
;;
esac
exit 0
2.2 定时任务
1.每天0:30做一次全量备份, 之后每隔两小时做一次增量备份
30 0 * * * /scripts/mysql-backup.sh full
30 2-23/2 * * * /scripts/mysql-backup.sh incr
2.新增环境变量
# vim ~/.bash_profile
EDITOR=vi; export EDITOR
3.新增定时任务
# crontab -e 2.3 数据恢复
2.3.1 解压全量
# xtrabackup --decompress --target-dir=/backup/mysql/full/20210705-092359/ 2.3.2 解压增量
# xtrabackup --decompress --target-dir=/backup/mysql/incr/20210705-094753/ 2.3.3 数据准备
在同时存在全量和增量备份需要合并的情况下,准备备份时需要带上--apply-log-only参数，但是要注意在准备最后一个增量备份的时候，不需要加该参数。
# xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/20210705-092359/
# xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full/20210705-092359/ --incremental-dir=/backup/mysql/incr/20210705-094753/
# xtrabackup --prepare --target-dir=/backup/mysql/full/20210706-092359/ --incremental-dir=/backup/mysql/incr/20210706-094753/
注：以上操作会将所有的增量备份合并到全量备份中. 2.3.4 执行恢复数据
# xtrabackup --copy-back --target-dir=/backup/mysql/full/20210705-092359/ 2.3.5 修改数据文件权限
# chown mysql:mysql -R /MySQL_DATA 2.3.6 启动mysql并检查数据
# service mysqld start ![数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)