文章摘要
这篇文章介绍了用于MySQL二进制日志备份的Shell脚本。该脚本的主要功能包括：
1. 调用mysql_binlog_backup.sh脚本获取二进制日志备份文件
2. 自动删除保留天数（默认为7天）之前的过时备份文件
3. 删除指定类型日志文件（默认为mysql_binlog.*）的错误日志
4. 检查MySQL服务进程数，若运行中则退出当前脚本
5. 对本地备份和远程备份分别处理，并通过邮件通知备份失败情况
脚本支持本地和远程备份，从MySQL的二进制文件索引获取最小的备份文件。文章还提到脚本版本历史，从V1.0到V1.2的改进，包括修复Bug和优化功能。脚本使用Shell命令执行任务，并通过管道处理日志文件。
#!/bin/sh
#########################################################################################
#?#
#?This?script?is?used?for?mysql?binlog?backup.#
#?#
#?#######################################################################################
#?#
#?ScriptName:mysql_binlog_backup_job.sh?#
#?Author:潇湘隐者?#
#?CerateDate:2017-04-14?#
#?Description?:?#
#—————————————————————————————#
#?作业中调用此脚本，然后此脚本去调用mysql_binlog_backup.sh执行#
#?MySQL的二进制日志备份(将MySQL的二进制日志备份到NAS存储或备份存#
#?储上)，此脚本还会判断mysqlbinlog是否在一直在备份二进制日志，#
#?如果是的话，则退出当前脚本。如果mysqlbinlog已经由于服务器重?#
#?启等原因退出了，则会重新调用mysql_binlog_backup.sh#
#***************************************************************************************#
#?VersionModified?DateDescription?#
#***************************************************************************************#
#?V.1.02016-06-20?create?the?script?for?mysql?binlog?backp#
#?V.1.12016-07-26?fix?some?bug#
#?V.1.22023-04-14?$FIRST_BINLOG从MySQL中获取，即使远程备份也不用手工#
#?设定,本地备份也可以这种方式，本地备份默认从?#
#mysql?binlog?index?file读取#
#########################################################################################
#mysql?binlog备份文件的保留天数
KEEPY_DAYS=7
FIRST_BINLOG=”
LOG_DATE=$(date?+%Y_%m_%d_%H_%M_%S)
BACKUP_DATE=$(date?+%Y_%m_%d_%H_%M_%S)
LOCAL_BACKUP_DIR=/dbbackup/mysql_backup/db_backup/binlog_backup
#MYSQL_BINLOG_INDEX=/data/bin_logs/mysql_binlog.index
MYSQL_CMD=/opt/mysql/mysql8.0/bin/mysql
BACKUP_LOG_PATH=/dbbackup/mysql_backup/logs
ERROR_LOG=${BACKUP_LOG_PATH}/binlog_backup_error_${BACKUP_DATE}.log
FILE_TYPE=”mysql_binlog.*”
SQL_TEXT=’show?binary?logs’
MAIL_TO=”xxxx@xxx.com.cn”
MAIL_FROM=”xxxx@xxx.com.cn”
MYSQL_LOGIN_PATH=server1_dbbackup
error()
{
?echo?”$1″?1>&2
?echo?”$1″?>>?${ERROR_LOG}
?echo?”$1″?|?mailx?-s?”The?binlog?backup?on?the?server?`hostname`?failed?,please?check?the?log!”?-r?${MAIL_FROM}?${MAIL_TO}
?exit?1
}
##目录不存在则创建目录
if?[?!?-d?$BACKUP_LOG_PATH?];then
?mkdir?-p?$BACKUP_LOG_PATH
fi
if?[?!?-x?/bin/mailx?];then
error?”{LOG_DATE}:mailx?did?not?exists!”?
fi
if?[?!?-x?$MYSQL_CMD?];then
?error?”{LOG_DATE}:?mysql?client?did?not?exists!”?
fi
#SQL_RESULT=`mysql?-h${REMOTE_HOST}?-P${PORT}?-u${USER_NAME}?-p${PASSWORD}?${DATABASE_NAME}?-Bse?”${SQL_TEXT}”`
SQL_RESULT=`$MYSQL_CMD?–login-path=${MYSQL_LOGIN_PATH}?-Bse?”${SQL_TEXT}”`
FIRST_BINLOG=`echo?${SQL_RESULT}?|?awk?'{print?$1}’`
echo?$FIRST_BINLOG
if?[?!?$FIRST_BINLOG?];then
?error?”${LOG_DATE}:?please?check?the?mysql?binlog”
fi
##create?local_backup_dir?if?this?folder?is?not?exists
if?[?!?-d?${LOCAL_BACKUP_DIR}?];then
mkdir?-p?${LOCAL_BACKUP_DIR}
fi
if?[?!?-e?${MYSQL_BINLOG_INDEX}?];then
error?”${LOG_DATE}:mysql?binlog?index?file?did?not?exists,?please?check?it!”?
fi
#删除KEEPY_DAYS天之前的binlog备份文件
find?${LOCAL_BACKUP_DIR}?-name?”${FILE_TYPE}”?-type?f?-mtime?+$KEEPY_DAYS?-delete
#删除30天前的错误日志
find?${BACKUP_LOG_PATH}?-name?”binlog_backup_error*.log”?-mtime?30?-delete
process_num=$(ps?-ef?|?grep?-w?mysqlbinlog?|?grep?-v?grep?|wc?-l)
if?[?${process_num}?-ge?1?];then
?exit?1?
else
#如果是在本机备份binlog到NAS存储或备份存储上，从二进制文件的索引获取当前MySQL数据库最小的binlog文件
#如果是远程备份二进制日志(binlog)的话，则使用下面注释的脚本获取
#FIRST_BINLOG=$(head?-1?${MYSQL_BINLOG_INDEX})
#FIRST_BINLOG=$(find?${LOCAL_BACKUP_DIR}?-name?”mysql_binlog.*”-printf?”%p\t%C@\n”?|?sort?-k2?-g?|head?-1?|?awk?'{print?$1}’?|?awk?-F?”/”?'{print?$NF}’)?
echo?${FIRST_BINLOG}
nohup?sh?/dbbackup/mysql_backup/scripts/mysql_binlog_backup.sh?${FIRST_BINLOG}?${LOCAL_BACKUP_DIR}?${FILE_TYPE}?&
fi