当前位置：首页 > news >正文

[mysql] 定时任务-全备+差备mysql数据库+邮件通知

news 2025/1/15 6:23:30

初学shell与数据库备份，记录学习，写的很糟糕，但是也算是小有成就感

crontab定时任务

[root@localhost backup_db]# crontab  -l
*/30 * * * * /bin/bash /root/shell/diff_backup.sh
## 30分钟执行一次差异备份
0 3 * * * /bin/bash /root/shell/full_backup.sh
## 凌晨三点执行全量备份#两shell脚本  一个python程序
[root@localhost shell]# pwd
/root/shell
[root@localhost shell]# ll
total 12
-rw-r--r--. 1 root root 2384 Jan 13 23:32 diff_backup.sh
-rw-r--r--. 1 root root 1033 Jan 13 23:36 email_post.py
-rw-r--r--. 1 root root 2610 Jan 13 23:32 full_backup.sh

full_backup.sh

全量备份的脚本

#!/bin/bash# 定义日志文件
log_file="/var/log/backup_db.log"# 获取当前时间
ctime=$(date +%F_%H%M%S)
back_name="full_backup_${ctime}"
back_dir="/backup_db"echo "-------------------${ctime} 全备脚本开始运行----------------------"
echo "-------------------${ctime} 全备脚本开始运行----------------------">> ${log_file}# 输出备份目录路径
echo "${back_dir}/${back_name}"# 创建备份目录（如果不存在）
mkdir -p "${back_dir}"# 备份数据库
mariabackup --backup --target-dir="${back_dir}/${back_name}" -uroot -p123456 > 2&>1# 获取上一条命令的执行状态
status=$?# 根据备份状态记录日志
if [ ${status} -eq 0 ]; thenecho "${ctime} 本地备份成功：${back_dir}/${back_name}" >> ${log_file}#exit 0
elseecho "${ctime} 本地备份失败，状态码：${status}" >> ${log_file}exit 100
fi# 将数据打包
tar -zcf ${back_dir}/${back_name}.tar.gz  ${back_dir}/${back_name}status=$?if [ ${status} -eq 0 ]; thenecho "${ctime} 本地备份打包成功：${back_dir}/${back_name}" >> ${log_file}#exit 0
elseecho "${ctime} 本地备份打包失败，状态码：${status}" >> ${log_file}exit 101
fi#备份到异机
remote_host="root@192.168.100.100"
remote_dir="/dbbackup/"
scp ${back_dir}/${back_name}.tar.gz $remote_host:${remote_dir}status=$?if [ ${status} -eq 0 ]; thenecho "${ctime} 异机备份传输成功：${back_dir}/${back_name}" >> ${log_file}#exit 0
elseecho "${ctime} 异机备份传输失败，状态码：${status}" >> ${log_file}exit 102
fi#清除本地>30的数据
#find ${back_dir} -mtime +30 -type f -name ".tar.gz" -exec rm -rf {} \;#检查异机数据完整性# #cut -d "" -f 1
local_md5=$(md5sum ${back_dir}/${back_name}.tar.gz |cut -d " " -f 1)  #awk '{print $1}'
remote_md5=$(ssh ${remote_host} md5sum /dbbackup/${back_name}.tar.gz |cut -d " " -f 1)echo $local_md5
echo $remote_md5if [ "${local_md5}" = "${remote_md5}" ];thenecho -e "${ctime} hash校验成功！\n本地：${local_md5}\n远程：${remote_md5}" >> ${log_file}
elseecho "${ctime} hash校验失败！，状态码：${status}" >> ${log_file}exit 103
fiecho "-------------------${ctime} 全备脚本结束运行----------------------"  >> ${log_file}echo -e "\n" >> ${log_file}#将本次保存的文件名存入文件夹
echo "${back_name}" > ${back_dir}/TAGlog_info=$(tail -n 10  /var/log/backup_db.log)/usr/bin/python3 /root/shell/email_post.py "$log_info"echo "-------------------${ctime} 全备脚本结束运行----------------------"

diff_backup.sh

差异备份的脚本

#!/bin/bash#定义本地的变量
back_dir="/backup_db"
nowTime=$(date +%F_%H%M%S)
log_file=/var/log/backup_db.log#定义备份服务器的变量
remote_server="root@192.168.100.100"
remote_dir="/dbbackup"#开始脚本运行
echo "-------------------${nowTime} 差备脚本开始运行----------------------"
echo "-------------------${nowTime} 差备脚本开始运行----------------------" >> ${log_file} # 获取当前全备名称
full_back_name=$(cat ${back_dir}/TAG)
cmd_status=$?echo "$full_back_name"
echo "${back_dir}/${full_back_name}_incr_${nowTime}"#开始差备 命名规则  全备名称_incr_nowTime
mariabackup  -uroot -p123456 --backup --target-dir=${back_dir}/${full_back_name}_incr_${nowTime} --incremental-basedir=${back_dir}/${full_back_name} > 2&>1
cmd_status=$?if [ ${cmd_status} -eq 0 ];thenecho "${nowTime}  本地差备执行成功！" >> ${log_file} 
elseecho "${nowTime}  本地差备执行失败！状态码：${cmd_status}" >> ${log_file}exit 100
fi#压缩打包差备文件
tar zcf ${back_dir}/${full_back_name}_incr_${nowTime}.tar.gz ${back_dir}/${full_back_name}_incr_${nowTime} > 2&>1
cmd_status=$?if [ ${cmd_status} -eq 0 ];thenecho "${nowTime}  本地差备打包成功！" >> ${log_file} 
elseecho "${nowTime}  本地差备打包失败！状态码：${cmd_status}" >> ${log_file}exit 101
fi#scp到备份服务器，验证hash值
scp  ${back_dir}/${full_back_name}_incr_${nowTime}.tar.gz ${remote_server}:${remote_dir}local_md5str=$(md5sum ${back_dir}/${full_back_name}_incr_${nowTime}.tar.gz |awk '{print $1}')
remote_md5str=$(ssh ${remote_server} md5sum ${remote_dir}/${full_back_name}_incr_${nowTime}.tar.gz |awk '{print $1}')
if [ "${local_md5str}" = "${remote_md5str}" ];thenecho "${nowTime}  差备文件完整性验证成功！本地${local_md5str} 远程${remote_md5str} " >> ${log_file} 
elseecho "${nowTime}  差备文件完整性验证不通过！本地${local_md5str} 远程${remote_md5str} 状态码：${cmd_status}" >> ${log_file}exit 102
fiecho "-------------------${nowTime} 差备脚本结束运行----------------------" >> ${log_file}echo  "" >> ${log_file}
#结束log_info=$(tail -n 6  /var/log/backup_db.log)/usr/bin/python3 /root/shell/email_post.py "$log_info"echo "-------------------${nowTime} 差备脚本结束运行----------------------"

email_post.py

发送邮件给dba，其中包含本次运行的日志文件

#!/usr/bin/python3
import smtplib
import sys
from email.mime.text import MIMEText
from email.header import Header
from email.utils import formataddrSMTP_SERVER="smtp.qq.com"
SMTP_PORT=465
SEND_EMAIL="xxxx"
PASSWORD="xxx"target_email="xxx"#获取传入的log_info 信息
if len(sys.argv) > 1:log_info = sys.argv[1]
else:log_info = "脚本未传入数据！！！"# 邮件内容
mail_content = log_info
message = MIMEText(mail_content, 'plain', 'utf-8')
message['From'] = formataddr((str(Header("server：192.168.100.101", 'utf-8')), SEND_EMAIL))
message['To'] = formataddr((str(Header("dba", 'utf-8')), target_email))
message['Subject'] = Header("mysql服务器备份", 'utf-8')try:smtpObj = smtplib.SMTP_SSL("smtp.qq.com", 465)smtpObj.login(SEND_EMAIL, PASSWORD)smtpObj.sendmail(SEND_EMAIL, [target_email], message.as_string())smtpObj.quit()print("邮件发送成功")
except smtplib.SMTPException as e:print("邮件发送失败：", e)