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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類