概述
mysql数据库的备份方案,即要考虑到数据的完全性,也要考虑到备份所占用的空间,因此适用的备份方案是结合全量和增量进行备份。
在数据完全方便,备份时应能将本地备份数据上传到异地的备份服务器上,以确保数据的安全。一般情况下,可以在远程服务器上开启FTP服务,在本地生成备份文件中,通过FTP上传到远程的备份服务器中。
对于本地服务器及远程服务器都要及时删除历史的数据备份文件,以释放存储空间。如远程备份服务器保留90天的数据,而本地仅需保留10天或更短时间的备份文件。当然,可以根据实际情况进行历史数据长期的保留。
此外,当数据库空间可用容量下降到一定下限后,要能及时告警,以加大磁盘空间,以便造成应用数据占满磁盘空间造成系统停机的悲剧。
备份方案设计
(1) 周一到周六 每天晚上23:45进行增量备份,备份完成后刷新binlog,产生新日志文件;
(2) 周日晚上先进行增量备份,再进行全量备份,
全量备份到 ${baseDir}/full,增量到 ${baseDir}/daily
(3) 备份成功后,将备份文件上传到远程FTP服务器;
(4) 将本地大于30天的历史备份数据删除(当然这个可以定义成一个变量);
(5) 检查数据盘的剩余空间是否下降到设定的下限值,到达下限值时向管理员发送告警邮件。
(6) 程序执行会记录日志,日志文件位于:${baseDir}/log 目录下。
完整备份shell程序
dbbackup.sh的完整程序如下所示。
#!/bin/bash
##################################################################################
# [功能说明]
#==========================================================================
# 1.每天做增量数据备份,将binlog备份到${baseDir}/daily 目录下;
# 2.在周天备份${dbname}的全量数据,备份到${baseDir}/full 目录下;
# 3.备份完成后删除30天前的本地数据;
# 4.将当次的备份文件上传到FTP服务器中,FTP服务器地址为${ftpHost}
#
#
# [使用说明]
#==========================================================================
# 1.打开/etc/crontab/ 添加如下定时任务,每天晚上23:45分执行;
# 45 23 * * * root /root/bin/dbbackup.sh
# (请先通过service crond status 确保crond服务已经有启动。);
# 2.请根据环境设置好[参数列表]中的参数。
# 3.执行日志记录到${baseDir}/log/dbback.log日志文件中;
#==========================================================================
# [参数列表]
#==========================================================================
# 1. dbconf : 数据库配置文件地址
# 2. username : 数据库用户名;
# 3. password : 数据库密码;
# 4. dbname : 需备份的数据库
# 5. baseDir : 备份数据存储的基目录,全量数据存储于 ${baseDir}/full,增量数据
# 存储于${baseDir}/daily,日志文件位于 ${baseDir}/log.
# 6. ftpHost : FTP服务器地址
# 7. ftpUser : FTP用户名
# 8. ftpPw : FTP密码
#
# 9. toMail : 接收告警的邮箱
# 10.serverName :服务器名称,预警中会包括这个信息
# 11.diskPath : 数据盘的路径
# 12.minDiskSize :当磁盘剩余空间小于该值时发预警邮件,单位为G
#==========================================================================
# [注意事项]
# 1.请不要使用reset master 让binlog文件名重新从***001开始否则会导致新增量文件名
# 与历史文件重名,在执行增量备份时,如果发现daily目录有有重名的binlog文件,将忽略之。
#
# [版本信息]
# 作 者:itstamen
# 创建日期:2020-07-10
# 变更日志:
##################################################################################
# ******************需要设置的变量参数**************************************
#mysql配置文件地址
dbconf=/etc/my.cnf
#mysql数据库用户名
username="root"
#mysql数据库密码
password="root"
#需要备份的数据库
dbname="db1"
#备份文件存储目录
baseDir=/mnt/sdc/data/backup
#ftp服务器URL(如 ftp.yourhome.com或者IP)
ftpHost=xxx
#备份文件需要放置的FTP目录,如你希望将备份文件上传到FTP的
# aaa/bbb/ 目录下,则这里写 aaa/bbb/
ftpDir=xxx
#ftp用户
ftpUser=xxx
#ftp用户密码
ftpPw=xxx
# 数据库的数据盘目录,每次备份时会检测其剩余空间是否小于${minDiskSize}GB,
# 如果小于,则将发告警邮件到${toMail}邮箱中。
dataDiskPath=/dev/vdb1
#当剩余空间小于100G时每晚发预警邮件
minDiskSize=100
# 这个服务器的名称,如:上海xxx客户OA数据库服务器001
serverName=xxx
#数据盘容量小于设定的下限值时,发送告警邮箱
toMail=xxx
#*************************************************************************
# 备份目录
fullDir=${baseDir}/full
dailyDir=${baseDir}/daily
logDir=${baseDir}/log
logFile=${logDir}/dbback.log
ftpLogFile=${logDir}/ftp.log
# binlog的目录及文件
binDir=$(grep -E "^[^#]" $dbconf |grep datadir |awk -F'=' '{print $2}')
binFile=${binDir}/mysql-bin.index
# 本次备份的文件列表,多文件用逗号分隔,这些文件将需要上传到FPT服务器中
tempBakFiles = ""
## 日志输出函数
function mylog(){
echo [LOG]-$(date +"%Y-%m-%d %H:%M:%S") ${1} >> $logFile
}
## 拷贝binlog到daily目录
function increBackup(){
mylog "开始增量备份.."
#(1)拷贝binlog前先锁定表,以防备份期间数据的写入
mysql -u${username} -p${password} -e "flush tables with read lock;" 2>/dev/null
#(2)将缓存中的数据更改写到binlog中,并生产一个新的mysql-bin.00000*文件
mysqladmin -u${username} -p${password} flush-logs 2>/dev/null
counter=$(wc -l $binFile | awk '{print $1}')
nextNum=0
#(3)将除刚产生的binlog文件外的所有文件拷贝到daily备份目录中
for file in $(cat $binFile)
do
baseFile=$(basename $file)
nextNum=$(($nextNum + 1))
if [ $nextNum -eq $counter ]
then
mylog "忽略${baseFile}."
else
destFile=${dailyDir}/${baseFile}
if test ! -e $destFile
then
cp ${binDir}/${baseFile} $destFile
tempBakFiles=${tempBakFiles}" "$destFile
mylog "备份${baseFile}."
else
mylog "${baseFile}已存在,忽略备份."
fi
fi
done
#(4)备份完成后,释放锁
mysql -u${username} -p${password} -e "unlock tables;" 2>/dev/null
mylog "增量备份完成."
}
#将文件上传到FTP
#直接上传绝对路径的文件会报文件找不到
uploadFile()
{
ftp -i -n $ftpHost 21 2>${ftpLogFile} << _EOF_
user $ftpUser $ftpPw
binary
cd $ftpDir
prompt
lcd $1
put $2
bye
_EOF_
#统计前面FTP运行输出的错误日志记录行数
_fileName=$1/$2
logCount=$(cat ${ftpLogFile} | wc -l)
[ ${logCount} -eq 0 ] && mylog 上传成功:$2 || mylog 上传失败:$2
[ -f ${ftpLogFile} ] && rm ${ftpLogFile}
}
# 检查磁盘空间,如果小于下限值,则发送预警邮件
checkDiskSpace()
{
# 分析数据盘的
diskSize=$(df $dataDiskPath | grep / | awk '{print $4}')
diskSizeGB=$(($diskSize/1024/1024))
if [ $diskSizeGB -lt $minDiskSize ]
then
mailContent=${serverName}数据盘剩余空间为${diskSizeGB}GB,不足${minDiskSize},请及时扩容。
echo "${mailContent}" | mail -s "[预警]${serverName}数据盘空间不足" ${toMail}
fi
}
# 当目录不存在创建之
mkdir -p $fullDir
mkdir -p $dailyDir
mkdir -p $logDir
w=$(date +%w)
nowDate=$(date +%Y%m%d)
#是星期天,执行全量数据备份
if [ $w -eq 0 ]
then
mylog "开始全量备份.."
#定义全量备份文件名
dumpFile=${dbname}_${nowDate}.sql
#(1) 在全量备份前先执行增量备份
increBackup
#(2) 全量备份(对所有数据库备份,除了数据库goodthing里的village表)
mysqldump -u${username} -p${password} --databases ${dbname} \
--ignore-table ${dbname}.SYS_LOGON \
--ignore-table ${dbname}.SYS_LOGON_HIS \
--ignore-table ${dbname}.SYS_LOG_MENU \
--ignore-table ${dbname}.SYS_LOG_MENU_HIS \
--single-transaction > ${fullDir}/$dumpFile 2>/dev/null
# 备份文件压缩
cd $fullDir
gzip -f $dumpFile
tempBakFiles=${tempBakFiles}" "${dumpFile}.gz
mylog "备份文件:${dumpFile}.gz"
mylog "全量备份完成."
else
increBackup
fi
# 清除30天前的备份数据
find ${fullDir} -mtime +30 |xargs rm -f
find ${dailyDir} -mtime +31 |xargs rm -f
mylog "删除30天前的日志文件"
# 将今天备份文件上传到FTP服务器中
bakFileArr=($tempBakFiles)
for bakFile in ${bakFileArr[@]}
do
echo "upload file:[$bakFile]..."
tempFileDir=$(dirname $bakFile)
tempFileName=$(basename $bakFile)
uploadFile $tempFileDir $tempFileName
done
# 检查服务器剩余空间是否达到警界线,如果空间不足则发告警邮件
checkDiskSpace
exit 0
# END
相关配置
以上备份命令要正常运行,必须进行FTP及MAIL客户端的相关安装及配置。
FTP客户端安装
#查看是否安装 FTP客户端
yum list ftp*
#如果没有安装 ,则安装之
yum install ftp -y
MAIL客户端安装及配置
通过mailx使用远程smtp进行邮件发送,首先安装mailx
# 查看mail客户端软件有没有安装
yum list mail*
#如果没有安装,则安装之
yum install mailx
一般不建议搭建自己的邮件发送服务,直接通过qq,163等的第三方邮件发送服务即可。由于阿里云,华为云等云一般会禁止通过25端口进行邮件发送,但允许通过465
vim /etc/mail.rc
在文件末尾添加:
set from=yourmail@163.com
set smtp=smtps://smtp.163.com:465
set ssl-verify=ignore
set nss-config-dir=/root/.certs
set smtp-auth-user=yourmail@163.com
set smtp-auth-password=XXXXXXXX
set smtp-auth=login
此外,还需要获取163服务器的证书文件,以便SSL通讯加密:
echo -n | openssl s_client -connect smtp.163.com:465 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > ~/.certs/163.crt
certutil -A -n "GeoTrust Global CA" -t "C,," -d ~/.certs -i ~/.certs/163.crt
certutil -A -n "GeoTrust SSL CA" -t "C,," -d ~/.certs -i ~/.certs/163.crt
certutil -L -d /root/.certs
certutil -A -n "GeoTrust SSL CA - G3" -t "Pu,Pu,Pu" -d ~/.certs/ -i ~/.certs/163.crt
注意smtp-auth-password不是163邮箱的登录密码,而是授权密码,参见如下获取之:
163mailAuthPw.jpg
当然您也可以使用qq邮箱服务器来发送,具体可参见:https://www.cnblogs.com/ithomer/p/9907935.html
小结
这个mysql备份程序,可以通过参数设置,应用于不同的应用环境中,灵活性较高。还可以进行如下的优化:
(1) 将参数抽取到一个配置文件中,这样可以不更改程序,只设置配置文件的参数即可;
(2) 日志对各种错误的记录还不够全,如备份失败,邮件发送失败等;
(3) 在发生错误时,可以自动将错误日志邮件发送出来。