MySQL中binlog备份脚本的方法(mysql备份工具)这样也行?

随心笔谈1年前 (2023)发布 admin
101 0

#!/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

© 版权声明

相关文章