mysql数据库完整备份方案

概述

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) 在发生错误时，可以自动将错误日志邮件发送出来。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,047评论 6赞 492
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,807评论 3赞 386
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,501评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,839评论 1赞 285
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,951评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,117评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,188评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,929评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,372评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,679评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,837评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,536评论 4赞 335
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,168评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,886评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,129评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,665评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,739评论 2赞 351