mysql 备份脚本_超详细的mysql数据库自动备份脚本（附日志结果输出）值得收藏...

概述

1、增加备份日志输出

2、增加备份是否有效判断

3、定期清理过期备份

01

备份脚本内容

存放位置：/home/scripts/mysql_backup.sh

#!/bin/bash#this is mysqldump auto full backup script#Copyright by hwb#date 2019-06-04backup_path='/data/backup'user='root'passwd='fswl@1234'dbname='fsl_prod'host='localhost'dbname2='diaocha_db'today=`date +'%Y%m%d-%H%M%S'`sqlname=$dbname$today.sqlsqlname2=$dbname2$today.sqlif [ -e /home/scripts/log_function.sh ]thensource /home/scripts/log_function.shelseecho -e “33[41;37m /home/scripts/log_function.sh is not exist. 33[0m”exit 1fibackup(){log_correct '开始执行备份脚本,删除14天过期备份'#backupmysqldump -h$host -u$user -p$passwd $dbname >$backup_path/$sqlnamemysqldump -h$host -u$user -p$passwd $dbname >$backup_path/$sqlname2}delete(){#delete expired 14 daysfind /data/backup -mtime +14 -type f -name '*.sql' -exec rm -f {} ;}size(){cd /data/backupdd=`du -sh $sqlname`dd2=`du -sh $sqlname2`if [ -s ./$sqlname ] ; then log_correct 'fsl_prod备份正常' log_correct $ddelse log_error 'fsl_prod备份失败'fiif [ -s ./$sqlname2 ] ; then log_correct 'diaocha_db备份正常' log_correct $dd2else log_error 'diaocha_db备份失败'fi}backupdeletesize

02

日志脚本

存放位置：/home/scripts/log_function.sh#!/bin/bash#log function#Copyright by hwb#date 2019-06-04####log_correct函数打印正确的输出到日志文件function log_correct () {DATE=`date +'%Y%m%d-%H%M%S'` ####显示打印日志的时间USER=$(whoami) ####那个用户在操作echo “${DATE} ${USER} execute $0 [INFO] $@” >>/home/scripts/log_info.log ######($0脚本本身，$@将参数作为整体传输调用)}#log_error打印shell脚本中错误的输出到日志文件function log_error (){DATE=`date +'%Y%m%d-%H%M%S'`USER=$(whoami)echo “${DATE} ${USER} execute $0 [INFO] $@” >>/home/scripts/log_error.log ######($0脚本本身，$@将参数作为整体传输调用)}####fn_log函数 通过if判断执行命令的操作是否正确，并打印出相应的操作输出function fn_log (){if [ $? -eq 0 ]thenlog_correct “$@ sucessed!”echo -e “33[32m $@ sucessed. 33[0m”elselog_error “$@ failed!”echo -e “33[41;37m $@ failed. 33[0m”exitfi}

03

备份输出

输出结果如下：

备份成功：

备份失败：

到这里就基本满足需求，也就不继续做优化了。

脚本其实是写逻辑，只要逻辑通了，一般都可以满足需求，这个脚本大家也可以继续深入优化，时间比较赶，就不继续了。