逻辑备份和物理备份各有优劣，一般来说，物理备份恢复速度比较快，占用空间比较大，逻辑备份速度比较慢，占用空间比较小。
一. 前言
逻辑备份和物理备份各有优劣，一般来说，物理备份恢复速度比较快，占用空间比较大，逻辑备份速度比较慢，占用空间比较小。逻辑备份的恢复成本高。
二. 逻辑备份
逻辑备份是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。
1)mysqldump mysqldump是采用SQL级别的备份机制，他将数据表导成SQL脚本文件，是最常用的逻辑备份方法。
三. 物理备份
物理备份就是备份数据文件了，比较形象点就是cp下数据文件，但真正备份的时候自然不是的cp这么简单。主要是使用 xtrabackup 工具是一个用来备份 MySQL数据库的开源工具。
四.备份脚本展示
1、逻辑备份
#！/bin/bash
#备份保留天数,建议保留三天
days=3
#备份时间
time=`date +%Y%m%d%H%M%S`
#备份保存路径
backup_dir=/tmp/backup
#备份工具
tool=mysqldump
#用户名
username=root
#密码
password="123456"
#端口
port="3306"
#是否采用--all-databases备份所有数据库,是填写Y,否填其他
bak_all=N
#将要备份的数据库,填写将要备份的数据库名
database_arr=(database1 database2 ... databaseN)
#备份命令可根据实际情况自行添加参数
#如果文件夹不存在则创建
if [ ! -d $backup_dir/mysqlbak_$time ];
then
mkdir -p $backup_dir/mysqlbak_$time;
fi
if [ $bak_all == "Y" ];
then
$tool -u$username -p$password -P$port --all-databases |gzip > $backup_dir/mysqlbak_$time/mysqlbak_all_$time.sql.gz;
else
i=0
while [ $i -lt "${#database_arr[*]}" ]
do
database=${database_arr[$i]}
$tool -u$username -p$password -P$port $database |gzip > $backup_dir/mysqlbak_$time/mysqlbak_${database}_${time}.sql.gz;
let i+=1
done
fi
#删除三天前的备份
find $backup_dir -maxdepth 1 -type d -mtime +$days -name 'mysqlbak*' -exec rm -rf {} \;
2.物理备份
（1）安装xtrabackup 工具，不同的数据库版本对应不同的工具版本，如mysql8.0.27对应percona-xtrabackup-8.0.27。
#!/bin/bash
#################################################
#Version : v1.0.0
#Author : zhouhuanhuan
#Time : 2023-07-19
#Auto HotBackup For MySQL #
#################################################
#autohotbackup tools
#备份根目录
BackupRoot=/backup
#全量备份根目录
FullBackupRoot=${BackupRoot}/full
#增量备份根目录
IncrementalBackupRoot=${BackupRoot}/incre
#xtrabackup 备份时log
Log=/data/log/autohotbackup_$(date +%Y-%m-%d).log
#现在星期几
Week=$(date +%w)
Hour=$(date +%H)
#全量备份时间，按照星期定义,周一到周日依次1,2,3,4,5,6,7
FullBackupWeek=(1 2 3 4 5)
#是否开启binlog备份,ON / OFF
BinlogBackup=ON
#全量备份时间,
#例子, 周一 10-11 点响应 ，周二 15-16 点
FullBackupTime=(1:10 2:15)
FullBackupTime=(1:04 2:04 3:04 4:04 5:04 6:04 0:04)
#增量备份时间,小时
IncrementalBackupIntervalTime=2
#过期时间，超过这个时间将删除,天
DeadLine=4
#MySQL信息
readonly MysqlAddress="localhost"
readonly MysqlPort="3306"
readonly MysqlUser="root"
readonly MysqlPassword="123123"
readonly MysqlSocket=/tmp/mysql.sock
readonly MysqlConfigFile=/etc/my.cnf
#Innobackupex
#使用内存
readonly UseMemory=1000M
#Innobackupex
#其他选项
readonly InnobackupexConfigure="--backup"
#SCP，传输已备份文件到备份服务器
#readonly BackupServerAddress=192.168.10.105
#readonly BackupServerPort=22
#readonly BackupServerUser=root
#readonly BackupServerDir=/backup
#模块开关
#备份文件到另一台服务器,ON/on代表打开，OFF/off代表关闭
#readonly IncrementalBackup=ON
#readonly TarZip=ON
#readonly ScpFile=ON
#readonly BinlogBackup=OFF
#readonly DeleteFile=ON
#pigz 多线程压缩工具是否启用，可能会消耗更多的CPU资源
#readonly UsePigz=YES
#bin Path
readonly INNOBACKUPEX=$(which xtrabackup 2>/dev/null || echo "xtrabackup")
readonly MYSQLADMIN=$(which mysqladmin 2>/dev/null || echo "mysqladmin")
readonly MYSQL=$(which mysql 2>/dev/null || echo "mysql")
readonly MKDIR=$(which mkdir 2>/dev/null || echo "mkdir")
readonly FIND=$(which find 2>/dev/null || echo "find")
readonly TAR=$(which tar 2>/dev/null || echo "tar")
readonly SCP=$(which scp 2>/dev/null || echo "scp")
readonly PIGZ=$(which pigz 2>/dev/null || echo "pigz")
echo "*************** $(date +%c): Xtrabackup 进行MySQL热备份 ***************"
echo " "
#主函数
function Main_Fun() {
echo " "
echo "*************** $(date +%c): Xtrabackup 进行MySQL热备份 ***************"
echo ""
Delete_File
Backup_Policy
Tar_Gzip
Scp_File
#Delete_File
}
#判断mysql可以连接
function Mysql_Alive() {
local TestConnectMySQL=$(mysqladmin -h"${MysqlAddress}" -P"${MysqlPort}" -u"${MysqlUser}" -p"${MysqlPassword}" ping 2>/dev/null | grep -c alive)
if [ "${TestConnectMySQL}" = 1 ];then
echo "连接 MySQL($MysqlAddress) 成功,开始备份 !"
else
echo "连接 MySQL($MysqlAddress) 失败,退出"
exit 1
fi
}
#全量备份
function Full_Backup() {
#判断备份目录是否存在
echo "*** 进行全量备份 ***"
[ -d ${FullBackupRoot} ] || ${MKDIR} ${FullBackupRoot} -p
#[ $? == 0 ] || echo "创建 ${BackupRoot} 失败,退出! " ; exit 1
if [ $? != 0 ];then
echo "创建 ${FullBackupRoot} 失败,退出! "
exit 1
fi
#判断mysql可活可连接
Mysql_Alive
#进行备份命令
${INNOBACKUPEX} --user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --target-dir="${FullBackupRoot}" --socket="${MysqlSocket}"
#进行判断
[ $? == 0 ] && echo "全量备份成功" || echo "全量备份失败!"
BackupType="FULL"
}
#基于全量进行增量备份
function Base_Full_Backup() {
if [ "${IncrementalBackup}" == "ON" -o "${IncrementalBackup}" == "on" ];then
echo "*** 尝试基于全量增量备份 "
elif [ "${IncrementalBackup}" == "OFF" -o "${IncrementalBackup}" == "off" ];then
echo " 增量备份开关已关闭 "
return
else
echo "增量备份开关配置错误,错误配置 IncrementalBackup= ${IncrementalBackup} ! "
return
fi
#echo " 尝试基于全量增量备份 ***"
#获取上次全量备份目录名,根据全名查找，如果找不到，则破例进行全量备份
local LatestFullBackup=$(${FIND} ${FullBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
if [ ! "${LatestFullBackup}" ];then
echo "在 ${FullBackupRoot} 下面没有找到全量备份,将进行全量备份 !"
sleep 2
#Full_Backup
return
#exit
fi
local LatestFullBackupRoot=${FullBackupRoot}/${LatestFullBackup}
local IncrementalBasedirRoot=${LatestFullBackupRoot}
#判断mysql可活可连接
#Mysql_Alive
echo "本次(基于全量)--incremental-basedir= ${IncrementalBasedirRoot}"
echo " "
sleep 3
#判断增量备份目录是否存在
[ -d ${IncrementalBackupRoot} ] || ${MKDIR} ${IncrementalBackupRoot} -p
#判断mysql可活可连接
#Mysql_Alive
#进行备份操作
${INNOBACKUPEX} --defaults-file="${MysqlConfigFile}" --use-memory="${UseMemory}" --host="${MysqlAddress}" --user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --incremental --incremental-basedir="${IncrementalBasedirRoot}" ${IncrementalBackupRoot}
#判断是否成功备份
[ $? == 0 ] && echo "基于 ${IncrementalBasedirRoot} 全量的增量备份成功 " || echo "基于 ${IncrementalBasedirRoot} 全量的增量备份失败!"
BackupType="INCREMENTAL"
}
#基于增量进行增量备份
function Base_Incremental_Backup() {
if [ "${IncrementalBackup}" == "ON" -o "${IncrementalBackup}" == "on" ];then
echo "*** 尝试基于全量增量备份 "
elif [ "${IncrementalBackup}" == "OFF" -o "${IncrementalBackup}" == "off" ];then
echo " 增量备份开关已关闭 "
return
else
echo "增量备份开关配置错误,错误配置 IncrementalBackup= ${IncrementalBackup} ! "
return
fi
#echo " 尝试基于增量的增量备份 ***"
local LatestIncrementalBackup=$(${FIND} ${IncrementalBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
if [ ! "$LatestIncrementalBackup" ];then
echo "在 ${IncrementalBackupRoot} 没找到增量备份,将运行基于全量备份模块 !"
sleep 2
#Base_Full_Backup
return
#exit
fi
local LatestIncrementalBackupRoot=${IncrementalBackupRoot}/${LatestIncrementalBackup}
local IncrementalBasedirRoot=${LatestIncrementalBackupRoot}
#判断mysql可活可连接
#Mysql_Alive
echo "本次(基于增量)--incremental-basedir= ${IncrementalBasedirRoot} "
echo " "
sleep 3
#开始操作备份
${INNOBACKUPEX} --defaults-file="${MysqlConfigFile}" --use-memory="${UseMemory}" --host="${MysqlAddress}" --user="${MysqlUser}" --password="${MysqlPassword}" "${InnobackupexConfigure}" --incremental --incremental-basedir="${IncrementalBasedirRoot}" ${IncrementalBackupRoot}
#判断是否成功备份
[ $? == 0 ] && echo "基于 ${IncrementalBasedirRoot} 增量的增量备份成功 " || echo "基于 ${IncrementalBasedirRoot} 增量的增量备份失败!"
BackupType="INCREMENTAL"
}
#打包加压模块，主要进行整理文件
function Tar_Gzip() {
#echo ${BackupType}
[ ! "${BackupType}" ] && return
#判断是否打开打包加压开关
if [ "${TarZip}" == "ON" -o "${TarZip}" == "on" ];then
echo "******************* 进行打包加压操作 ***********************"
elif [ "${TarZip}" == "OFF" -o "${TarZip}" == "off" ];then
echo "打包加压开关为 off 状态 !"
return
else
echo "打包加压开关配置错误,错误配置 TarZip= ${TarZip} !"
return
fi
#echo " "
#echo "进行打包加压操作 !"
sleep 3
if [ "${BackupType}" == "FULL" ];then
local LatestFullBackup=$(${FIND} ${FullBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
echo " "
cd "${FullBackupRoot}"
# 判断是否启用pigz工具多线程压缩，默认不启用
if [ "${UsePigz}" == "YES" -o "${UsePigz}" == "yes" ];then
echo "使用 ${PIGZ} 进行多线程压缩 ${LatestFullBackup}"
${TAR} -icvf - ${LatestFullBackup} --remove-file | ${PIGZ} > "${LatestFullBackup}".tar.gz
else
${TAR} -izcf "${LatestFullBackup}".tar.gz "${LatestFullBackup}" --remove-file
fi
[ $? == 0 ] && echo "在 ${FullBackupRoot} 下面已对 ${LatestFullBackup} 打包加压成 ${LatestFullBackup}.tar.gz !"
TarZipFile=${FullBackupRoot}/${LatestFullBackup}.tar.gz
elif [ "${BackupType}" == "INCREMENTAL" ];then
local LatestIncrementalBackup=$(${FIND} ${IncrementalBackupRoot} -mindepth 1 -maxdepth 1 -type d -printf "%P\n" 2>/dev/null | sort -nr | head -1)
echo " "
cd "${IncrementalBackupRoot}"
# 判断是否启用pigz工具多线程压缩,默认不启用
if [ "${UsePigz}" == "YES" -o "${UsePigz}" == "yes" ];then
echo "使用 ${PIGZ} 进行多线程压缩 ${LatestFullBackup}"
${TAR} -icvf - ${LatestIncrementalBackup} --remove-file | ${PIGZ} > "${LatestIncrementalBackup}".tar.gz
else
${TAR} -izcf "${LatestIncrementalBackup}".tar.gz "${LatestIncrementalBackup}" --remove-file
fi
[ $? == 0 ] && echo "在 ${IncrementalBackupRoot} 下面已对 ${LatestIncrementalBackup} 打包加压成 ${LatestIncrementalBackup}.tar.gz !"
TarZipFile=${IncrementalBackupRoot}/${LatestIncrementalBackup}.tar.gz
fi
}
#时间控制，主要进行控制备份策略
function Backup_Policy() {
local NOW=$(date +%c)
echo "******************************* ${NOW} *****************************"
#FullBackupTime
for BackupTime in ${FullBackupTime[]}
do
#echo ${BackupTime}
FullBackupWeek=${BackupTime%%:}
if [ "${FullBackupWeek}" == "${Week}" ];then
#判断是否全量备份
local FullBackupHour=${BackupTime##:}
if [ "${FullBackupHour}" == "${Hour}" ];then
echo " 进行全量备份 "
sleep 3
#Full_Backup
return
fi
#判断需不需要基于全量备份
local HourNow=$((IncrementalBackupIntervalTime + FullBackupHour))
if [ "${Hour}" == "${HourNow}" ];then
#进行基于全量的增量备份
echo " 尝试基于全量增量备份 ***"
sleep 3
#Base_Full_Backup
return
fi
fi
done
#判断需不需要基于全量备份
#HourNow=$((IncrementalBackupIntervalTime + FullBackupHour))
echo "*** 尝试基于增量的增量备份 ***"
sleep 3
#Base_Incremental_Backup
return
}
#进行同步备份文件,只是同步已经打包加压成 .tar.gz 的文件
function Scp_File() {
#间接获取是否执行innobackupex
[ ! "${BackupType}" ] && return
#判断打包压缩模块是否打开，打开备份模块才生效
[ "${TarZip}" == "ON" -o "${TarZip}" == "on" ] || return
#判断是否打开备份开关
if [ "${ScpFile}" == "ON" -o "${ScpFile}" == "on" ];then
echo "******************* 将使用SCP命令传输备份数据库文件 ***********************"
elif [ "${ScpFile}" == "OFF" -o "${ScpFile}" == "off" ];then
echo " "
echo "备份状态为 off,没进行传备份文件 ${TarZipFile} 到服务器 ${BackupServerAddress} !"
return
else
echo " "
echo "备份配置错误,错误配置 ScpFile= ${ScpFile}"
return
fi
#echo "******************* 将使用SCP命令传输备份数据库文件 ***********************"
${SCP} -P "${BackupServerPort}" "${TarZipFile}" "${BackupServerUser}"@${BackupServerAddress}:${BackupServerDir}
#判断是成功备份
[ "$?" == "0" ] && echo "备份文件 ${TarZipFile} 到服务器 ${BackupServerAddress} 的 ${BackupServerDir} 成功 !" || echo "备份文件 ${TarZipFile} 到服务器失败 !"
}
#删除备份文件，删除备份策略
function Delete_File() {
echo ""
if [ "${DeleteFile}" == "ON" -o "${DeleteFile}" == "on" ];then
echo "*** 查询并删除过期文件 ***"
elif [ "${DeleteFile}" == "OFF" -o "${DeleteFile}" == "off" ];then
echo "删除文件状态为关闭 !"
return
else
echo "删除模块配置错误,错误配置 DeleteFile=${DeleteFile} "
return
fi
#整理全量备份的目录
echo "*** 查询全量备份目录 ${FullBackupRoot} ***"
local FindResult=$(${FIND} ${FullBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} 2>/dev/null)
if [ "${FindResult}" ];then
echo "将在目录 ${FullBackupRoot} 删除一下文件: "
echo "${FindResult}"
#进行删除文件
sleep 5
${FIND} ${FullBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
else
echo "在目录 ${FullBackupRoot} 下面没有可删除过期( ${DeadLine} 天)文件 !"
fi
#整理增量备份的目录
echo "*** 查询增量备份目录 ${IncrementalBackupRoot} ***"
local FindResult=$(${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} 2>/dev/null)
if [ "${FindResult}" ];then
echo "将在目录 ${IncrementalBackupRoot} 删除一下文件: "
echo "${FindResult}"
#进行删除文件
${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
sleep 5
${FIND} ${IncrementalBackupRoot}/*.tar.gz -type f -mtime +${DeadLine} -exec rm -rf {} \;
else
echo "在目录 ${IncrementalBackupRoot} 下面没有可删除过期( ${DeadLine} 天)文件 !"
fi
}
#Backup_Policy
Main_Fun >> ${Log} 2>&1
#Base_Incremental_Backup
#Base_Full_Backup
#Incremental_Backup
#Full_Backup
