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

copy_left 2019-06-23

展开全文

概述

1、增加备份日志输出

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

3、定期清理过期备份

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

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 “\033[41;37m /home/scripts/log_function.sh is not exist. \033[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

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

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 “\033[32m $@ sucessed. \033[0m”elselog_error “$@ failed!”echo -e “\033[41;37m $@ failed. \033[0m”exitfi}

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