mysql如何存储文件怎么打开 mysql存储文件方案

转载

mob64ca1403528a 2023-09-06 22:32:14

文章标签 mysql如何存储文件怎么打开 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

# 变更日志：

##################################################################################
# ******************需要设置的变量参数**************************************
#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邮箱服务器来发送，具体可参见：

小结

这个mysql备份程序，可以通过参数设置，应用于不同的应用环境中，灵活性较高。还可以进行如下的优化：

(1) 将参数抽取到一个配置文件中，这样可以不更改程序，只设置配置文件的参数即可；

(2) 日志对各种错误的记录还不够全，如备份失败，邮件发送失败等；

(3) 在发生错误时，可以自动将错误日志邮件发送出来。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。