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