MySQL中binlog備份腳本分享

来源:https://www.cnblogs.com/kerrycode/archive/2023/04/19/17334793.html
-Advertisement-
Play Games

關於MySQL的二進位日誌（binlog），我們都知道二進位日誌（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關於二進位日誌（binlog)的備份,可以基於flush logs方式先切換binlog，然後拷貝&壓縮到到遠程伺服器或本地伺服器 ...


關於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
掃描上面二維碼關註我 如果你真心覺得文章寫得不錯，而且對你有所幫助，那就不妨幫忙“推薦"一下，您的“推薦”和”打賞“將是我最大的寫作動力！ 本文版權歸作者所有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接.
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 如何運行編譯.NetCore的源碼？
    背景 最近小編接到一個獲取網站請求數據的需求，要求抓取網站某個頁面請求的數據。我使用Google Chrome瀏覽器查看了一下請求鏈接的傳入參數，發現需要傳入一個Token值才能獲取數據。於是我在Chrome中登錄後，通過Postman請求成功，並將Token存儲到了Cookie中。然而問題又來了， ...
  • 記一次 .NET某醫療器械清洗系統 卡死分析
    一：背景 1. 講故事 前段時間協助訓練營里的一位朋友分析了一個程式卡死的問題，回過頭來看這個案例比較經典，這篇稍微整理一下供後來者少踩坑吧。 二：WinDbg 分析 1. 為什麼會卡死 因為是窗體程式，理所當然就是看主線程此時正在做什麼? 可以用 ~0s ; k 看一下便知。 0:000> k # ...
  • Linux 查看記憶體使用情況的幾種方法
    在運行 Linux 系統的過程中為了讓電腦或者伺服器以最佳水平運行，常常需要監控記憶體統計信息。今天我們就來看看都有那些方法可以查看記憶體信息。 ...
  • centos9 redis安裝報錯(實在無解使用方法)
    報錯如下 [root@centos bin]# ./redis-server ./redis-server: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such fi ...
  • VMware17安裝Windows10詳細記錄
    VMware17安裝Windows10詳細記錄 1. 前置準備 VMware軟體，這裡用的VMware17 Windows系統鏡像文件(.iso文件) Windows系統官方下載地址：Windows系統官方下載 I Tell You舊版站點：MSDN, 我告訴你 - 做一個安靜的工具站 (itell ...
  • 部署prometheus、grafana、alertmanager
    簡介：由於資源有限，本實驗用了兩台機器 監控端：部署prometheus、grafana、alertmanager 被監控端：node_exporter、mysqld_exporter 一. 部署promethus 1. 下載 https://prometheus.io/download/ 2. 解 ...
  • 設置Windows主機的瀏覽器為wls2的預設瀏覽器
    0.linux的目錄結構 1.用戶和用戶組的信息存儲 1.1. 用戶的基本信息文件/etc/passwd 1.1.1. 用戶名 1.1.2. 密碼 1.1.3. UID 1.1.4. GID 1.1.5. 註釋性描述 1.1.6. 宿主目錄 1.1.7. 預設shell 1.2. 用戶的密碼信息文件 ...
  • day02-2-商鋪查詢緩存
    功能02-商鋪查詢緩存 3.商鋪詳情緩存查詢 3.1什麼是緩存？ 緩存就是數據交換的緩衝區（稱作Cache），是存儲數據的臨時地方，一般讀寫性能較高。 緩存的作用： 降低後端負載 提高讀寫效率，降低響應時間 緩存的成本： 數據一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類