【实战背景】

不管你是正在上学,还是已经工作了,想必多多少少都听说过发生rm -rf删库跑路的情况,主要指的就是被有意无意的删除掉了数据库的数据,而数据是整个业务最重要的价值体现,如果你的公司或者项目组没有一个成熟的数据库数据备份方案,一旦发生意外,我只能说年轻人,大意了吧,没有备份数据库!!!

数据库备份方案根据实际需要进行定制,一般比较主流的方案是方案一:每周一次全量备份+每天一次增量备份,主要是为了节省MySQL数据库服务器的空间(省钱),当然如果老板比较有钱,光是MySQL服务器的空间就有几千个G,空间大,比较任性的,直接方案二:每天做一次全量备份也可以~

本文主要介绍基于Xtrabackup实现MySQL数据库的全量备份+增量备份方案~

很细,建议先点⭐收藏,预防走丢❤️‍,进入正文~

一、Xtrabackup介绍

官方介绍文档

简单总结下来就是XtraBackupPercona旗下的一款产品,支持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 资源下载

这里我个人操作系统环境对应安装的资源及版本如下:

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

libvxtrabackup的依赖包,如果使用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:yumrpm最大的区别就是通过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)对应的libevrpm安装包版本是 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 卸载重装

如果想要重装libevxtrabackup,需要先卸载原来安装的,不需要卸载这步可以忽略~

(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用户操作安装qpressqpress主要用于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主要有STATEMENTROWMIXED三种模式,比较主流的是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

参数说明:

参数 说明
--defaults-file mysql配置文件全路径
--backup 表示备份操作
--user 有权限的备份用户
--password 备份用户密码
--socket mysql的lock文件
--compress 使用qpress压缩数据文件,文件后缀.qp
--compress-thread 压缩文件线程数(不清楚就不写该参数,使用默认的)
--parallel 并发数(不清楚就不写该参数,使用默认的
--throttle 每秒IO操作的次数(不清楚就不写该参数,使用默认的
--target-dir 备份数据目录

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

参数说明:

参数 说明
--defaults-file mysql配置文件全路径
--backup 表示备份操作
--user 有权限的备份用户
--password 备份用户密码
--socket mysql的lock文件
--compress 使用qpress压缩数据文件,文件后缀.qp
--compress-thread 压缩文件线程数(不清楚就不写该参数,使用默认的)
--parallel 并发数(不清楚就不写该参数,使用默认的
--throttle 每秒IO操作的次数(不清楚就不写该参数,使用默认的
--target-dir 备份数据目录
--incremental-basedir 备份要基于前面哪个基础备份目录

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

参数说明:

参数 说明
--prepare 表示预恢复
--apply-log-only 指定该参数时,表示防止未提交事务的回滚
--target-dir 预恢复目标目录
--incremental-dir 预恢复备份基于上一个备份的目录(全量或增量备份)

全量+增量预恢复备份,最核心的部分就是 --apply-log-only 参数,可以看下
增量备份官方文档说明

简单的总结就是预恢复备份时必须要指定 --apply-log-only 参数,用来防止未提交事务的回滚,当预恢复备份到最后一个备份版本时,需要去掉该参数~

3.3.3 恢复备份数据

恢复命令

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

参数说明:

参数 说明
--defaults-file mysql配置文件全路径
--copy-back 表示恢复操作
--target-dir 要恢复数据的备份目录

这里需要注意的是--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
#基于XtrabackupMySQL数据库备份脚本,每周日全量备份,其他增量备份

#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;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


肝完了,兄弟们,冲冲冲!!!!!

原创不易,觉得有用的小伙伴来个一键三连(点赞+收藏+评论 )+关注支持一下,非常感谢~

原文链接:https://blog.csdn.net/JustinQin/article/details/119910821原文链接:

最后修改日期:2021年9月7日

留言

撰写回覆或留言