瀟湘隱者

關(guān)于MySQL的二進(jìn)制日志（binlog），我們都知道二進(jìn)制日志（binlog）非常重要，尤其當(dāng)你需要point to point災(zāi)難恢復(fù)的時(shí)侯，所以我們要對(duì)其進(jìn)行備份。關(guān)于二進(jìn)制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠(yuǎn)程服務(wù)器或本地服務(wù)器的其他存儲(chǔ)上，例如掛載的NAS存儲(chǔ)，也可以使用mysqlbinlog實(shí)現(xiàn)binlog的備份，可以實(shí)現(xiàn)MySQL二進(jìn)制日志（binlog)的本地備份或遠(yuǎn)程備份。最后將MySQL二進(jìn)制日志（binlog)的備份文件收到磁帶。各個(gè)公司的備份策略或備份工具有所不同，這里不做展開(kāi)，我們主要考慮MySQL二進(jìn)制日志（binlog)備份方案/策略盡可能盡善盡美，在一些極端情況下少丟失數(shù)據(jù)。例如，第一種方式，這種備份方式都是周期性的，如果在某個(gè)周期中，遇到一些極端情況，例如服務(wù)器宕機(jī)了，硬盤損壞了，就可能導(dǎo)致這段時(shí)間的binlog丟失了。而且這個(gè)周期時(shí)間太長(zhǎng)，二進(jìn)制日志（binlog）丟失的風(fēng)險(xiǎn)就越大，如果這個(gè)周期太短，頻繁切換binlog也不好。所以還是使用mysqlbinlog來(lái)備份二進(jìn)制日志（binlog)，這里主要介紹一下我寫的MySQL二進(jìn)制日志的備份腳本，這個(gè)腳本參考了如何遠(yuǎn)程備份MySQL binlog^[1]中的腳本，但是在其基礎(chǔ)上做了很多改進(jìn)和完善：

1. 參考資料中的腳本由于使用了循環(huán)操作，不適合在作業(yè)中調(diào)用。一般需要手工執(zhí)行腳本，讓其在后臺(tái)運(yùn)行。遇到服務(wù)器重啟或其他異常情況，此腳本可能出現(xiàn)未能執(zhí)行的情況。
2. 增加了mysql_binlog_backup_job.sh腳本，作業(yè)會(huì)定期調(diào)用此腳本，此腳本會(huì)判斷mysqlbinlog是否還在執(zhí)行二進(jìn)制日志備份。如果遇到了數(shù)據(jù)庫(kù)服務(wù)器重啟等情況，也不會(huì)擔(dān)心MySQL的二進(jìn)制日志（binlog）的備份停掉的問(wèn)題。
3. 不用手工指定第一個(gè)binlog文件參數(shù)，采用從數(shù)據(jù)庫(kù)讀取binlog的值.如果是在本地服務(wù)器執(zhí)行binlog的備份，還可以從二進(jìn)制日志索引文件中獲取(參考腳本注釋部分)
4. 使用mysql_config_editor配置賬號(hào)密碼，避免在腳本中使用數(shù)據(jù)庫(kù)用戶的明文密碼。
5. 郵件告警處理。

在使用腳本前，必須配置mailx，創(chuàng)建數(shù)據(jù)庫(kù)連接賬號(hào)

create user bkuser@'xxx.xxx.xxx.xxx' identified by "******";
grant  replication client on *.* to bkuser@'%';
grant  replication slave on *.* to bkuser@'%';

這個(gè)根據(jù)實(shí)際情況調(diào)整，例如我就是使用Xtrabackup的賬號(hào)來(lái)備份MySQL的binlog。 另外，如果在MySQL數(shù)據(jù)庫(kù)服務(wù)器本機(jī)備份binlog，那么就在本機(jī)安全加密登錄，如果是在遠(yuǎn)程服務(wù)器備份binlog的話，就在遠(yuǎn)程服務(wù)器配置

$ mysql_config_editor set  --login-path=server1_dbbackup -h xxx.xxx.xxx.xxx -ubkuser -p -P 3306
Enter password:

mysql_binlog_backup_job.sh腳本

#!/bin/sh

#########################################################################################
#                                                                                       #
# This script is used for mysql binlog backup.                                          #
#                                                                                       #
# #######################################################################################
#                                                                                       #
# ScriptName            :    mysql_binlog_backup_job.sh                                 #
# Author                :    瀟湘隱者                                                     #
# CerateDate            :    2017-04-14                                                 #
# Description           :                                                               #
#---------------------------------------------------------------------------------------#
#                         作業(yè)中調(diào)用此腳本，然后此腳本去調(diào)用mysql_binlog_backup.sh執(zhí)行      #
#                         MySQL的二進(jìn)制日志備份(將MySQL的二進(jìn)制日志備份到NAS存儲(chǔ)或備份存#
#                         儲(chǔ)上)，此腳本還會(huì)判斷mysqlbinlog是否在一直在備份二進(jìn)制日志，  #
#                         如果是的話，則退出當(dāng)前腳本。如果mysqlbinlog已經(jīng)由于服務(wù)器重   #
#                         啟等原因退出了，則會(huì)重新調(diào)用mysql_binlog_backup.sh            #
#***************************************************************************************#
# Version        Modified Date            Description                                   #
#***************************************************************************************#
# V.1.0          2016-06-20     create the script for mysql binlog backp                #
# V.1.1          2016-07-26     fix some bug                                            #
# V.1.2          2023-04-14     $FIRST_BINLOG從MySQL中獲取，即使遠(yuǎn)程備份也不用手工      #
#                               設(shè)定,本地備份也可以這種方式，本地備份默認(rèn)從             #
#                                mysql binlog index file讀取                            #
#########################################################################################


#mysql binlog備份文件的保留天數(shù)
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
}

##目錄不存在則創(chuàng)建目錄
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天前的錯(cuò)誤日志
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

  #如果是在本機(jī)備份binlog到NAS存儲(chǔ)或備份存儲(chǔ)上，從二進(jìn)制文件的索引獲取當(dāng)前MySQL數(shù)據(jù)庫(kù)最小的binlog文件
  #如果是遠(yuǎn)程備份二進(jìn)制日志(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

mysql_binlog_backup.sh腳本

#!/bin/sh

#########################################################################################
#                                                                                       #
# This script is used for mysql binlog local or remote backup.                          #
#                                                                                       #
# #######################################################################################
#                                                                                       #
# ScriptName            :    mysql_binlog_backup.sh                                     #
# Author                :    Kerry                                                      #
# CerateDate            :    2017-04-14                                                 #
# Description           :                                                               #
#---------------------------------------------------------------------------------------#
#                         此腳本參考了http://www.rzrgm.cn/ivictor/p/5502240.html    #
#                         的腳本，在它的基礎(chǔ)上做了一些改進(jìn)，例如,ivitcor中腳本備份binlog#
#                         如果服務(wù)器重啟了，則必須手動(dòng)執(zhí)行腳本.......                   #
#***************************************************************************************#
# Version        Modified Date            Description                                   #
#***************************************************************************************#
# V.1.0          2016-06-20     create the script for mysql binlog backp                #
# V.1.1          2016-07-26     fix some bug                                            #
#########################################################################################

BACKUP_BIN=/opt/mysql/mysql8.0/bin/mysqlbinlog
BACKUP_LOG_PATH=/dbbackup/mysql_backup/logs
LOG_DATE=$(date +%Y_%m_%d_%H_%M_%S)
BACKUP_LOG=${BACKUP_LOG_PATH}/binlog_backup.log
ERROR_LOG=${BACKUP_LOG_PATH}/binlog_backup_error_${LOG_DATE}.log
#復(fù)制二進(jìn)制日志的主機(jī)，可以遠(yuǎn)程MySQL數(shù)據(jù)庫(kù)也可以是本機(jī)
MYSQL_LOGIN_PATH=server1_dbbackup
#time to wait before reconnecting after failure
SLEEP_SECONDS=10
MAIL_TO="xxx@xxx.com.cn"
MAIL_FROM="xxx@xxx.com.cn"

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
}


##目錄不存在則創(chuàng)建目錄
if [ ! -d $BACKUP_LOG_PATH ];then
       mkdir -p $BACKUP_LOG_PATH
fi


if [ "$#" -ne 3];then   
    error "${LOG_DATE}:you must input 3 arguments" 
fi

if [ ! $1 ];then
    error "${LOG_DATE}:first_binlog arguments is null" 
else
    FIRST_BINLOG=$1
fi

if [ ! $2 ];then
    error "${LOG_DATE}:local_backup_dir arguments is null" 
else
    LOCAL_BACKUP_DIR=$2
fi

if [ ! $3 ];then
    error "${LOG_DATE}:file_type arguments is null" 
else
    FILE_TYPE=$3
fi

##檢查mysqlbinlog二進(jìn)制文件是否存在
if [ ! -x ${BACKUP_BIN} ];then
       error "${LOG_DATE}:mysqlbinlog did not exists, please check it!" 
fi


       
cd ${LOCAL_BACKUP_DIR}

## 運(yùn)行while循環(huán)，連接斷開(kāi)后等待指定時(shí)間，重新連接
while :
do
  #如果當(dāng)前備份二進(jìn)制日志目錄為空，則使用MySQL實(shí)例最小的二進(jìn)制日志文件名
  if [ `ls -A "${LOCAL_BACKUP_DIR}" |wc -l` -eq 0 ];then
     LAST_BINLOG_FILE=${FIRST_BINLOG}
  else
     #LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} | grep -v backuplog |tail -n 1 |awk '{print $9}'`
     #echo ${LOCAL_BACKUP_DIR}
     #echo ${FILE_TYPE}
     #取mysqlbinlog備份的最后一個(gè)binlog文件名
     LAST_BINLOG_FILE=`find ${LOCAL_BACKUP_DIR} -name "${FILE_TYPE}"  -printf "%p\t%C@\n" | sort -k2 -g |tail -1 | awk '{print $1}' | awk -F "/" '{print $NF}'`
  fi
  #${BACKUP_BIN} --login-path=${MYSQL_LOGIN_PATH} --read-from-remote-server --raw --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT}  ${LAST_BINLOG_FILE}
  ${BACKUP_BIN} --login-path=${MYSQL_LOGIN_PATH} --read-from-remote-server --raw --stop-never   ${LAST_BINLOG_FILE}
  echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog停止，返回代碼：$?" | tee -a ${BACKUP_LOG}
  echo "${SLEEP_SECONDS}秒后再次連接并繼續(xù)備份" | tee -a ${BACKUP_LOG}  
  
  
  sleep ${SLEEP_SECONDS}
done

配置作業(yè)

*/10 * * * * /dbbackup/mysql_backup/scripts/mysql_binlog_backup_job.sh >> /dbbackup/mysql_backup/logs/mysql_binlog_back.log 2>&1

參考資料