【實戰背景】

不管你是正在上學，還是已經作業了，想必多多少少都聽說過發生rm -rf、刪庫跑路的情況，主要指的就是被有意或無意的洗掉掉了資料庫的資料，而資料是整個業務最重要的價值體現，如果你的公司或者專案組沒有一個成熟的資料庫資料備份方案，一旦發生意外，我只能說年輕人，大意了吧，沒有備份資料庫！！！

資料庫備份方案根據實際需要進行定制，一般比較主流的方案是方案一：每周一次全量備份+每天一次增量備份，主要是為了節省MySQL資料庫服務器的空間（省錢），當然如果老板比較有錢，光是MySQL服務器的空間就有幾千個G，空間大，比較任性的，直接方案二：每天做一次全量備份也可以~

本文主要介紹基于Xtrabackup實作MySQL資料庫的全量備份+增量備份方案~

很細，建議先點?收藏，預防走丟???，進入正文~

一、Xtrabackup介紹

官方介紹檔案

簡單總結下來就是XtraBackup是Percona旗下的一款產品，支持MySQL資料庫的熱備份（在線不停機），并且是免費、開源，適用所有MySQL版本，非阻塞、緊密壓縮、高度安全~

心動就先點一下?收藏唄！ 哈哈哈，回歸正題，進入實戰演練~

二、Xtrabackup安裝

2.1 版本說明

（1）Percona XtraBackup 2.4版本，支持 MySQL 5.1 [1]、5.5、5.6 和 5.7服務器上的InnoDB資料庫備份，但不支持MySQL 8.0 資料庫備份，
（2）Percona XtraBackup8.0版本，僅支持MySQL 8.0服務器上存盤引擎為InnoDB的資料庫備份，但不支持在MySQL 8.0之前版本的資料庫備份~

Percona XtraBackup 8.0 官方檔案

Percona XtraBackup 2.4 官方檔案

2.2 資源準備

2.2.1 查看作業系統

查看服務器 lsb_release -a

我的作業系統資訊是Linux CentOS 7.7.1908 位數64，那么我后面我需要下載對應的相關安裝包~

2.2.2 查看MySQL版本號

方式一：Linux終端命令列
mysql -V

方式二：MySQL終端命令列
mysql> select version();

根據前面【版本說明】提到的MySQL5.7應該選擇的是Percona XtraBackup 2.4相關版本~

2.2.3 資源下載

這里我個人作業系統環境對應安裝的資源及版本如下：

• 安裝包 percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm
• 壓縮工具 qpress-11-linux-x64.tar

XtraBackup其他版本官方下載 https://www.percona.com/downloads/

XtraBackup 8.0
XtraBackup 2.4

（1）問題：
有同學問，你為啥下載的是rpm包，我選擇的卻是tar.gz安裝包？而且你為啥不直接在服務器通過wget +資源url在線下載呢？簡單又省事~
（2）回答：
首先下載什么包，這個跟你選擇的作業系統有關，選擇Linux-Generic是通用版本的，對應tar.gz包，我選擇的是CentOS7，對應rpm安裝包~
其次下載好rpm的安裝包后，再進行離線安裝這種方式，我個人覺得比較穩妥，因為實際生產環境很多都是內網，無法直接在線下載安裝，因此需要提前下載好離線安裝包，再上傳到服務器，進行安裝~

2.2.4 資源上傳

按實際需要，創建存放安裝資源的目錄~

mkdir -p /opt/mysql/xtrabackup
cd /opt/mysql/xtrabackup

2.3 開始安裝

2.3.1 安裝xtrabackup

先查看是否已安裝~

rpm -qa |grep xtrabackup

若沒有任何提示資訊，說明未安裝，再使用root用戶進行操作安裝~

cd /opt/mysql/xtrabackup
yum -y localinstall percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm

提示如下，表明安裝成功~

2.3.2 安裝libv

libv是xtrabackup的依賴包，如果使用yum命令已經正常安裝Xtrabackup，這步可以跳過~

但是有的小伙伴可能沒有使用yum命令安裝Xtrabackup：

yum -y localinstall percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm

而是使用了rpm命令進行安裝Xtrabackup：

rpm -ivh percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm

PS：yum和rpm最大的區別就是通過yum命令安裝缺失包時，會自動去加載需要的包，而通過rpm命令安裝缺失包時，不會自動去加載缺失的包，而是直接報錯~

所以當使用rpm命令安裝xtrabackup時，此時安裝xtrabackup需要依賴libev，如果libev沒安裝，則會安裝失敗，報錯關鍵資訊如下：
libev.so.4()(64bit) is needed by percona-xtrabackup-24-2.4.19-1.el7.x86_64

這種情況就需要下載對應版本的依賴包libev進行安裝，再繼續安裝xtrabackup~

（1）下載libev包
推薦比較靠譜的rpm包下載地址：http://rpm.pbone.net/

根據我的作業系統及作業系統版本號（CentOS 7.7.1908 位數x64）對應的libev的rpm安裝包版本是 libev-4.04-2.el5.x86_64.rpm~

另外兩個也挺靠譜的rpm包下載地址：
https://pkgs.org/search/?q=libev
https://rpmfind.net/linux/rpm2html/search.php?query=libev

（2）查看是否已安裝libev~

rpm -qa |grep libev

若沒有任何提示資訊，說明未安裝，再使用root用戶進行操作安裝~

cd /opt/mysql/xtrabackup
rpm -ivh libev-4.15-7.el7.x86_64.rpm

再使用rpm命令進行安裝Xtrabackup：

rpm -ivh percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm

安裝成功，提示如下資訊：

不過還是更推薦大家使用yum命令進行安裝Xtrabackup：

yum -y localinstall percona-xtrabackup-24-2.4.19-1.el7.x86_64.rpm

2.3.3 卸載重裝

如果想要重裝libev和xtrabackup，需要先卸載原來安裝的，不需要卸載這步可以忽略~

（1）查詢

rpm -qa |grep libev
rpm -qa |grep xtrabackup

（2）卸載

yum -y remove libev
yum -y remove percona-xtrabackup

（3）再次查詢

rpm -qa |grep libev
rpm -qa |grep xtrabackup

2.3.3 安裝qpress

需要使用root用戶操作安裝qpress，qpress主要用于xtrabackup備份資料庫時對資料檔案進行壓縮（壓縮檔案后綴.qp）以及還原資料庫時再對壓縮資料檔案（后綴.qp）進行解壓，大大節省存盤空間，當然如果老板非常有錢，服務器空間超大，不需要進行壓縮的可以忽略~

安裝程序

cd /opt/mysql/xtrabackup
tar -xvf qpress-11-linux-x64.tar
mv qpress /usr/bin
chmod 755 /usr/bin/qpress

三、MySQL開啟binlog日志

3.1 什么是binlog日志？

簡單點的說就是記錄了MySQL修改和添加的操作資訊，主要用于資料庫的主從復制以及備份還原，幾乎所有的第三方資料庫備份及恢復，都需要依賴于這個binlog日志實作，非常重要！！！
MySQL的binlog主要有STATEMENT、ROW和MIXED三種模式，比較主流的是ROW模式，建議大家有空可以了解了解~

3.2 如何開啟binlog日志？

修改MySQL的組態檔vim /etc/my.cnf，設定binlog為開啟：

#=====設定binlog方式1===========
log_bin=ON
log_bin_basename=/data/mysqllog/bin-log/mysql-bin
log_bin_index=/data/mysqllog/bin-log/mysql-bin.index

#=====設定binlog方式2===========
log_bin=/data/mysqllog/bin-log/mysql-bin

建議使用方式2，比較簡潔，相當于方式1的三行配置~

同樣vim /etc/my.cnf設定binlog模式（具體模式按實際需要進行設定）：

#設定binlog模式，ROW表示日志格式為記錄每行資料的變化
binlog_format=ROW

三、Xtrabackup備份及還原命令

3.1 xtrabackup全量備份命令

全備命令：

xtrabackup --defaults-file=/etc/my.cnf --backup --user=bkpuser --password=123456 --socket=/data/mysqldata/mysql.sock --compress --compress-thread --parallel=4 --throttle=400 --target-dir=/data/mysqlbackup/full

引數說明：

3.2 xtrabackup增量備份命令

增備命令

xtrabackup --defaults-file=/etc/my.cnf --backup --user=bkpuser --password=123456 --socket=/data/mysqldata/mysql.sock --compress --compress-thread --parallel=4 --throttle=400 --target-dir=/data/mysqlbackup/incr --incremental-basedir=/data/mysqlbackup/full

引數說明：

3.3 xtrabackup恢復備份命令

3.3.1 解壓資料

沒對資料進行壓縮的，這步可以直接跳過~
如果在備份（全備或增備）時使用了--compress 壓縮資料檔案，此時的資料檔案后綴為.qp，恢復資料庫資料時，需要先通過qpress進行解壓.qp得到正常的資料檔案，再洗掉原.qp壓縮檔案~

for j in $(find /data/mysqlbackup/full -name "*.qp"); do qpress -d $j $(dirname $j) && rm -rf $j; done;
for j in $(find /data/mysqlbackup/incr -name "*.qp"); do qpress -d $j $(dirname $j) && rm -rf $j; done;

3.3.2 預恢復備份

使用xtrabackup進行恢復資料庫資料之前，需要先進行預恢復備份~
假設資料庫備份方案是全量備份+增量備份，那么這個預恢復就分為兩步來完成~

第一步：先進行預恢復全量備份

xtrabackup --prepare --apply-log-only --target-dir=/data/mysqlbackup/full

第二步：再進行預恢復增量備份，合并當前預恢復備份到前一個備份（可能是全量或增量備份）目錄，如果有多個增量備份，依次完成所有增量備份的預恢復操作~

xtrabackup --prepare --target-dir=/data/mysqlbackup/full --incremental-dir=/data/mysqlbackup/incr

引數說明：

全量+增量預恢復備份，最核心的部分就是 --apply-log-only 引數，可以看下
增量備份官方檔案說明

簡單的總結就是預恢復備份時必須要指定 --apply-log-only 引數，用來防止未提交事務的回滾，當預恢復備份到最后一個備份版本時，需要去掉該引數~

3.3.3 恢復備份資料

恢復命令

xtrabackup --defaults-file=/etc/my.cnf  --copy-back --target-dir=/data/mysqlbackup/full

引數說明：

這里需要注意的是--target-dir指定恢復的備份目錄，是經過預恢復備份之后~

四、MySQL資料庫備份及還原（完整方案）

4.1 完整方案介紹

前面介紹了如何xtrabackup對MySQL進行全量備份、增量備份、預恢復備份、恢復備份的基本命令~
本文實際上實施整套MySQL資料庫備份及還原方案的話，需要通過Shell腳本+定時服務來實作~

（1）Shell腳本主要用來來撰寫Xtrabackup的基本命令~

（2）定時服務主要目的是定時每天執行Shell腳本，完成資料庫備份，定時服務方式很多，本文主要介紹Linux自帶的Crond定時服務，簡單易上手~
（3）本文采用周日全量備份+非周日增量備份的完整資料庫備份方案，需要注意的是資料庫的備份方案都是比較嚴謹的，一般都是定制化好的，該方案要保證每天只有一份備份，如果當天有多份備份，那又需要重新定制備份和恢復的Shell腳本了~

4.2 資料庫備份腳本

4.2.1 創建目錄

分別規劃以下幾個目錄：

• 腳本存放目錄：mkdir -p /data/mysqlbackup/tools
• 全量備份主目錄：mkdir -p /data/mysqlbackup/full
• 增量備份主目錄：mkdir -p /data/mysqlbackup/incr
• 備份日志主目錄：mkdir -p /data/mysqlbackup/logs

4.2.2 創建腳本

mysqlbackup.sh內容如下：

#!/bin/sh
#基于Xtrabackup的MySQL資料庫備份腳本，每周日全量備份，其他增量備份

#MySQL組態檔
mysqlConfig="/data/mysqldata/my.cnf"
#MySQL的sock檔案
mysqlSocket="/data/mysqldata/mysql.sock"
#資料庫用戶
dbUser="bkpuser"
#資料庫密碼
dbPassword="abc@123456"

#全量備份主目錄
fullBackupPath="/data/mysqlbackup/full"
#增量備份主目錄
incrBackupPath="/data/mysqlbackup/incr"
#備份日志主目錄及
logPath="/data/mysqlbackup/logs"
#備份日志檔案名
logfile="/data/mysqlbackup/logs/backup_$(date +%Y%m%d).log"

#資料庫備份保存天數
SAVE_DAYS=7

#清理備份保存天數之前的資料檔案
find $fullBackupPath -type d -mtime +$SAVE_DAYS |xargs rm -rf
find $incrBackupPath -type d -mtime +$SAVE_DAYS |xargs rm -rf

#格式化今天的日期(年月日)
todayFormat=$(date -d "today" +%Y%m%d)
#格式化昨天的日期(年月日)
yesterdayformat=$(date -d "yesterday" +%Y%m%d)
#格式化昨天的星期標識(0~6表示周日~周六)
todayWeekFlg=`date -d ${todayFormat} +%w`
#格式化昨天的日期標識(0~6表示周日~周六)
yesterdayWeekFlg=`date -d ${yesterdayformat} +%w`

#一、第一次備份
if [ ! -d $fullBackupPath/$todayFormat ] && [ ! -d $fullBackupPath/$yesterdayformat ] && [ ! -d $incrBackupPath/$todayFormat ] && [ ! -d $incrBackupPath/$yesterdayformat ];then
  #全量備份
  xtrabackup --defaults-file=$mysqlConfig --backup --user=$dbUser --password=$dbPassword --socket=$mysqlSocket --compress --target-dir=$fullBackupPath/$todayFormat 2>> ${logfile}
#二、不是第一次備份
else
  #2.1 今天是周日
  if [ $todayWeekFlg == "0" ];then
    #全量備份
    xtrabackup --defaults-file=$mysqlConfig --backup --user=$dbUser --password=$dbPassword --socket=$mysqlSocket --compress --target-dir=$fullBackupPath/$todayFormat 2>> ${logfile}
  #2.2 今天不是周日
  else
    #2.2.1 昨天是周榷訓昨天有全量備份
    if [ $yesterdayWeekFlg == "0" ] || [ -d $fullBackupPath/$yesterdayformat ];then
      #增量備份: 依賴于昨天的全量備份  
      xtrabackup --defaults-file=$mysqlConfig --backup --user=$dbUser --password=$dbPassword --socket=$mysqlSocket --compress --target-dir=$incrBackupPath/$todayFormat --incremental-basedir=$fullBackupPath/$yesterdayformat 2>> ${logfile}
    #2.2.1 昨天不是周日并且昨天也沒有全量備份
    else
      #增量備份: 依賴于昨天的增量備份
      xtrabackup --defaults-file=$mysqlConfig --backup --user=$dbUser --password=$dbPassword --socket=$mysqlSocket --compress --target-dir=$incrBackupPath/$todayFormat --incremental-basedir=$incrBackupPath/$yesterdayformat 2>> ${logfile}
    fi
  fi
fi
exit 0

4.2.3 執行腳本

（1）手動修改時間
生產環境，要通過后面設定的定時服務來執行Shell腳本~
不過實際開發環境時，一般需要馬上驗證腳本是否正確，這時可以通過修改Linux作業系統的日期，兩個間隔的日期分別手動執行一次備份腳本，模擬按日的資料庫備份方案，修改操作時間需謹慎！！！不能修改系統時間的可以當天手動執行一次備份腳本，隔天后再手動執行一次備份腳本進行驗證~

date
date -s  "2021-08-26 04:00:00"
date -s  "2021-08-27 04:00:00"

（2）同步網路時間（最后）
這步僅用于修改Linux作業系統時間后，分別在不同日期下執行完備份腳本后，再將Linux作業系統時間同步為互聯網的時間，沒修改系統時間的可以忽略~

yum install -y ntpdate
ntpdate 0.asia.pool.ntp.org
hwclock --systohc
或(指定具體時間)
date -s  "2021-08-27 04:00:00"
hwclock --systohc

（3）執行備份腳本

/bin/sh /data/mysqlbackup/tools/mysqlbackup.sh

（4）查看日志

tail -f /data/mysqlbackup/logs/backup_20210826.log

備份成功會提示如下：

如果給的權限不夠可能會報錯：
210826 21:02:01 Connecting to MySQL server host: localhost, user: bkpuser, password: set, port: 3306, socket: /data/mysqldata/mysql.sock
Using server version 5.7.29-log
Error: failed to execute query ‘SHOW ENGINE INNODB STATUS’: 1227 (42000) Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

說明執行備份的MySQL用戶權限不夠~

查看權限

mysql> select * from mysql.user where user = 'bkpuser' \G;

授權如下權限：

GRANT SELECT, INSERT, CREATE, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO `bkpuser`@`localhost`;
FLUSH PRIVILEGES;

實在不確定啥權限，可以授權所有權限給備份用戶也可以：

GRANT ALL PRIVILEGES ON  *.* TO `bkpuser`@`localhost`;
FLUSH PRIVILEGES;

4.2.4 配置定時服務

（1）定時服務作用？

配置定時服務主要目的是定時每天執行Shell備份腳本，完成資料庫備份，定時服務方式很多，本文主要介紹Linux自帶的Crond定時服務，簡單易上手~

（2）什么是Crond服務？

• crond是Linux作業系統自帶的定時服務，自帶守護行程，一般用于運行計劃任務如系統備份、日志分割及清理，
• crond服務適合在那些24x7不間斷運行的機器如服務器上運行的計劃任務，
• crond運算式
  示例1：0 0 * * * 每天整點執行
  示例2：0 4 * * * 每天4點整執行
  示例3：*/1 0 * * * 每隔一分鐘執行
  –引數說明
  crond運算式從左右分別表示分鐘(0-59)　小時(0-23)　日期(1-31)　月份(1-12)　星期(0-6,0代表星期天)
• 驗證crond配置
  配置當前用戶crond的執行命令/usr/bin/crontab -e
  編輯內容為： crond運算式 要執行的Command命令
• 實時查看cron執行日志（這里只有root用戶有權限查看~）
  tail -f /var/log/cron

（3）Crond常用命令

Linux通過Crond服務配置定時服務，需要確保Crond處于運行狀態~

查看啟動狀態命令
systemctl status crond.service

其他常用命令
systemctl start crond.service
systemctl restart crond.service
systemctl stop crond.service

關于Crond定時服務，也可以看下我的這篇博文學習
Linux-- 定時服務crond VS anacron和 crontab VS anacrontab區別對比

（4）定時執行備份腳本
使用啟動MySQL的Linux終端用戶進行操作，我這里是mysql用戶~
執行命令

/usr/bin/crontab -e

添加內容：

0 4 * * * /bin/sh /data/mysqlbackup/tools/mysqlbackup.sh

表示每天凌晨4點整，會自動執行基于Xtrabackup的MySQL資料庫全量備份+增量備份的Shell腳本~

4.3 資料庫恢復腳本

4.3.1 創建腳本

vim mysqlrecovery.sh添加內容：

#!/bin/sh
#基于Xtrabackup的MySQL資料庫恢復腳本,手動執行恢復

#全量備份主目錄
fullBackupPath="/data/mysqlbackup/full"
#增量備份主目錄
incrBackupPath="/data/mysqlbackup/incr"
#全量備份日期(最新)
fullBackupDate="20210826"
#增量備份日期(最新)
incrBackupDate="20210827"
#MySQL組態檔
mysqlConfig="/data/mysqldata/my.cnf"

#資料恢復日志檔案
logfile="/data/mysqlbackup/logs/recovery_$(date +%Y%m%d).log"

#格式化全量備份日期為秒數
fullBackupTime=`date -d "$fullBackupDate" +%s`
#格式化增量備份日期為秒數
incrBackupTime=`date -d "$incrBackupDate" +%s`
#計算兩個日期之間的天數差
diffTimes=$[$incrBackupTime - $fullBackupTime]
#天數差
diffDays=$[diffTimes/86400]

#解壓全量備份的壓縮檔案
for j in $(find $fullBackupPath/$fullBackupDate -name "*.qp"); do qpress -d $j $(dirname $j) && rm -rf $j; done;

#按天數差遍歷：從最新全量備份依次預恢復到最新增量備份
for((i=0;i<=diffDays;i++))
do
  #預恢復備份日期
  prepareRevoveryDate=`date -d "$fullBackupDate $i day" +%Y%m%d`
  #一、天數差為0
  if [ $diffDays == 0 ];then
    #預恢復最新全量備份,后面沒有增量備份,不需要指定--apply-log-only，回滾未提交事務
    xtrabackup --prepare --target-dir=$fullBackupPath/$fullBackupDate 2>> ${logfile}
  #二、天數差不為0
  else
    #2.1 預恢復備份日期等于全量備份日期
    if [ $prepareRevoveryDate == $fullBackupDate ];then
      #預恢復最新全量備份,后面還有增量備份,需指定--apply-log-only,防止回滾未提交事務
      xtrabackup --prepare --apply-log-only --target-dir=$fullBackupPath/$fullBackupDate 2>> ${logfile}
    #2.2 預恢復備份日期不等于全量備份日期
    else
      #解壓預恢復增量備份
      for j in $(find $incrBackupPath/$prepareRevoveryDate -name "*.qp"); do qpress -d $j $(dirname $j) && rm -rf $j; done;
      #2.2.1 最新增量備份
      if [ i == $diffDays ];then
        #預恢復最新增量備份,不需要指定--apply-log-only，回滾未提交事務
        xtrabackup --prepare --target-dir=$fullBackupPath/$fullBackupDate --incremental-dir=$incrBackupPath/$prepareRevoveryDate 2>> ${logfile}
      #2.2.2 非最新增量備份
      else
        #預恢復非最新增量備份,需指定--apply-log-only,防止回滾未提交事務
        xtrabackup --prepare --apply-log-only --target-dir=$fullBackupPath/$fullBackupDate --incremental-dir=$incrBackupPath/$prepareRevoveryDate 2>> ${logfile}
      fi
    fi
  fi
done

#xtrabackup恢復預恢復完成的全量備份資料
xtrabackup --defaults-file=$mysqlConfig --copy-back --target-dir=$fullBackupPath/$fullBackupDate 2>> ${logfile}

#退出程式
exit 0

4.3.2 刪庫跑路（模擬）

手動備份資料目錄（預防恢復失敗）

cd /data/mysqldata
tar -czvf 3306_$(date +%Y%m%d)_$(date +%H%M%S)2.tar.gz 3306

刪庫跑路（模擬）~

cd /data/mysqldata
rm -rf 3306

4.3.3 手動執行恢復腳本

查看MySQL行程

ps -ef|grep mysql

如果存在行程，需要先停止mysql

service mysql stop

或者

kill -9 行程號

執行恢復腳本

/bin/sh /data/mysqlbackup/tools/mysqlrecovery.sh

查看恢復日志

tail -f /data/mysqlbackup/logs/recovery_$(date +%Y%m%d).log

啟動mysql
如圖所示，表示恢復成功~

最后連接MySQL查看全量備份和增量備份恢復是否完整~
$ mysql -uroot -p

肝完了，兄弟們，沖沖沖！！！！！

原創不易，覺得有用的小伙伴來個一鍵三連（點贊+收藏+評論 ）+關注支持一下，非常感謝~