MySQL--☀️这套【全网超细】万字数据库备份方案，老板看了直呼加薪❤️‍《⭐务必收藏⭐》-伙伴云

MySQL--☀️这套【全网超细】万字数据库备份方案，老板看了直呼加薪❤️‍《⭐务必收藏⭐》

网友投稿 849 2022-05-30

【实战背景】

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

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

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

==很细，建议先点⭐，预防走丢❤️‍，进入正文~==

@TOC

一、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--☀️这套【全网超细】万字数据库备份方案，老板看了直呼加薪❤️‍《⭐务必收藏⭐》

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增量备份命令

增备命令

参数说明：

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

==肝完了，兄弟们，冲冲冲！！！！！==

原创不易，觉得有用的小伙伴来个一键三连（++评论）+关注支持一下，非常感谢~

MySQL 数据库

标签：Mysql

MySQL高可用之MGR+Consul架构部署（mysql mgr配置）

849 2022-05-30

MySQL--☀️这套【全网超细】万字数据库备份方案，老板看了直呼加薪❤️‍《⭐务必收藏⭐》

230_mysql_binlog结构组成（mysql binlog详解）

MySQL 连接为什么挂死了？（mysql安装教程）

MySQL高可用之MGR+Consul架构部署（mysql mgr配置）

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

定制订单管理系统（为特定需求定制的订单管理系统）

连锁餐饮管理系统的功能有哪些？餐饮服务系统的构成及工

友情链接