技術文章 » MySQL中binlog備份指令碼分享

MySQL中binlog備份指令碼分享

2023-04-20 06:00:58

關於MySQL的二進位制紀錄檔（binlog），我們都知道二進位制紀錄檔（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關於二進位制紀錄檔（binlog)的備份,可以基於flush logs方式先切換binlog，然後拷貝&壓縮到到遠端伺服器或本地伺服器的其他儲存上，例如掛載的NAS儲存，也可以使用mysqlbinlog實現binlog的備份，可以實現MySQL二進位制紀錄檔（binlog)的本地備份或遠端備份。最後將MySQL二進位制紀錄檔（binlog)的備份檔案收到磁帶。各個公司的備份策略或備份工具有所不同，這裡不做展開，我們主要考慮MySQL二進位制紀錄檔（binlog)備份方案/策略儘可能盡善盡美，在一些極端情況下少丟失資料。例如，第一種方式，這種備份方式都是週期性的，如果在某個週期中，遇到一些極端情況，例如伺服器宕機了，硬碟損壞了，就可能導致這段時間的binlog丟失了。而且這個週期時間太長，二進位制紀錄檔（binlog）丟失的風險就越大，如果這個週期太短，頻繁切換binlog也不好。所以還是使用mysqlbinlog來備份二進位制紀錄檔（binlog)，這裡主要介紹一下我寫的MySQL二進位制紀錄檔的備份指令碼，這個指令碼參考了如何遠端備份MySQL binlog[1]中的指令碼，但是在其基礎上做了很多改進和完善：

  1. 參考資料中的指令碼由於使用了迴圈操作，不適合在作業中呼叫。一般需要手工執行指令碼，讓其在後臺執行。遇到伺服器重啟或其他異常情況，此指令碼可能出現未能執行的情況。
  2. 增加了mysql_binlog_backup_job.sh指令碼，作業會定期呼叫此指令碼，此指令碼會判斷mysqlbinlog是否還在執行二進位制紀錄檔備份。如果遇到了資料庫伺服器重啟等情況，也不會擔心MySQL的二進位制紀錄檔（binlog）的備份停掉的問題。
  3. 不用手工指定第一個binlog檔案引數，採用從資料庫讀取binlog的值.如果是在本地伺服器執行binlog的備份，還可以從二進位制紀錄檔索引檔案中獲取(參考指令碼註釋部分)
  4. 使用mysql_config_editor設定賬號密碼，避免在指令碼中使用資料庫使用者的明文密碼。
  5. 郵件告警處理。

在使用指令碼前，必須設定mailx，建立資料庫連線賬號

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

這個根據實際情況調整，例如我就是使用Xtrabackup的賬號來備份MySQL的binlog。 另外，如果在MySQL資料庫伺服器本機備份binlog，那麼就在本機安全加密登入，如果是在遠端伺服器備份binlog的話，就在遠端伺服器設定

$ 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           :                                                               #
#---------------------------------------------------------------------------------------#
#                         作業中呼叫此指令碼，然後此指令碼去呼叫mysql_binlog_backup.sh執行      #
#                         MySQL的二進位制紀錄檔備份(將MySQL的二進位制紀錄檔備份到NAS儲存或備份存#
#                         儲上)，此指令碼還會判斷mysqlbinlog是否在一直在備份二進位制紀錄檔，  #
#                         如果是的話，則退出當前指令碼。如果mysqlbinlog已經由於伺服器重   #
#                         啟等原因退出了，則會重新呼叫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中獲取，即使遠端備份也不用手工      #
#                               設定,本地備份也可以這種方式，本地備份預設從             #
#                                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="[email protected]"
MAIL_FROM="[email protected]"
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

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           :                                                               #
#---------------------------------------------------------------------------------------#
#                         此指令碼參考了https://www.cnblogs.com/ivictor/p/5502240.html    #
#                         的指令碼，在它的基礎上做了一些改進，例如,ivitcor中指令碼備份binlog#
#                         如果伺服器重啟了，則必須手動執行指令碼.......                   #
#***************************************************************************************#
# 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
#複製二進位制紀錄檔的主機，可以遠端MySQL資料庫也可以是本機
MYSQL_LOGIN_PATH=server1_dbbackup
#time to wait before reconnecting after failure
SLEEP_SECONDS=10
MAIL_TO="[email protected]"
MAIL_FROM="[email protected]"

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 [ "$#" -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二進位制檔案是否存在
if [ ! -x ${BACKUP_BIN} ];then
       error "${LOG_DATE}:mysqlbinlog did not exists, please check it!" 
fi


       
cd ${LOCAL_BACKUP_DIR}

## 執行while迴圈，連線斷開後等待指定時間，重新連線
while :
do
  #如果當前備份二進位制紀錄檔目錄為空，則使用MySQL範例最小的二進位制紀錄檔檔名
  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備份的最後一個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}秒後再次連線並繼續備份" | tee -a ${BACKUP_LOG}  
  
  
  sleep ${SLEEP_SECONDS}
done

設定作業

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

參考資料

[1]

: https://www.cnblogs.com/ivictor/p/5502240.html