【实战背景】
不管你是正在上学,还是已经工作了,想必多多少少都听说过发生
rm -rf
、删库跑路
的情况,主要指的就是被有意
或无意
的删除掉了数据库的数据,而数据是整个业务最重要的价值体现,如果你的公司或者项目组没有一个成熟的数据库数据备份方案,一旦发生意外,我只能说年轻人,大意了吧,没有备份数据库!!!
数据库备份方案根据实际需要进行定制,一般比较主流的方案是
方案一:每周一次全量备份+每天一次增量备份
,主要是为了节省MySQL数据库服务器的空间(省钱),当然如果老板比较有钱,光是MySQL服务器的空间就有几千个G,空间大,比较任性的,直接方案二:每天做一次全量备份
也可以~
本文主要介绍基于Xtrabackup实现MySQL数据库的全量备份+增量备份方案~
很细,建议先点⭐收藏,预防走丢❤️,进入正文~
目录
一、Xtrabackup介绍二、Xtrabackup安装2.1 版本说明2.2 资源准备2.2.1 查看操作系统2.2.2 查看MySQL版本号2.2.3 资源下载2.2.4 资源上传2.3 开始安装2.3.1 安装xtrabackup2.3.2 安装libv2.3.3 卸载重装2.3.3 安装qpress三、MySQL开启binlog日志3.1 什么是binlog日志?3.2 如何开启binlog日志?三、Xtrabackup备份及还原命令3.1 xtrabackup全量备份命令3.2 xtrabackup增量备份命令3.3 xtrabackup恢复备份命令3.3.1 解压数据3.3.2 预恢复备份3.3.3 恢复备份数据四、MySQL数据库备份及还原(完整方案)4.1 完整方案介绍4.2 数据库备份脚本4.2.1 创建目录4.2.2 创建脚本4.2.3 执行脚本4.2.4 配置定时服务4.3 数据库恢复脚本4.3.1 创建脚本4.3.2 删库跑路(模拟)4.3.3 手动执行恢复脚本一、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其他版本官方下载/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/xtrabackupcd /opt/mysql/xtrabackup
2.3 开始安装
2.3.1 安装xtrabackup
先查看是否已安装~
rpm -qa |grep xtrabackup
若没有任何提示信息,说明未安装,再使用root
用户进行操作安装~
cd /opt/mysql/xtrabackupyum -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 bypercona-xtrabackup-24-2.4.19-1.el7.x86_64
这种情况就需要下载对应版本的依赖包libev
进行安装,再继续安装xtrabackup
~
(1)下载libev包
推荐比较靠谱的rpm包下载地址:/
根据我的操作系统及操作系统版本号(CentOS 7.7.1908 位数x64)
对应的libev
的rpm
安装包版本是 libev-4.04-2.el5.x86_64.rpm~
另外两个也挺靠谱的rpm包下载地址:
/search/?q=libev
/linux/rpm2html/search.php?query=libev
(2)查看是否已安装libev~
rpm -qa |grep libev
若没有任何提示信息,说明未安装,再使用root
用户进行操作安装~
cd /opt/mysql/xtrabackuprpm -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 libevrpm -qa |grep xtrabackup
(2)卸载
yum -y remove libevyum -y remove percona-xtrabackup
(3)再次查询
rpm -qa |grep libevrpm -qa |grep xtrabackup
2.3.3 安装qpress
需要使用
root
用户操作安装qpress
,qpress
主要用于xtrabackup
备份数据库时对数据文件进行压缩(压缩文件后缀.qp
)以及还原数据库时再对压缩数据文件(后缀.qp
)进行解压,大大节省存储空间,当然如果老板非常有钱,服务器空间超大,不需要进行压缩的可以忽略~
安装过程
cd /opt/mysql/xtrabackuptar -xvf qpress-11-linux-x64.tarmv qpress /usr/binchmod 755 /usr/bin/qpress
三、MySQL开启binlog日志
3.1 什么是binlog日志?
简单点的说就是记录了MySQL修改和添加的操作信息,主要用于数据库的主从复制以及备份还原,几乎所有的第三方数据库备份及恢复,都需要依赖于这个
binlog
日志实现,非常重要!!!MySQL的binlog主要有
STATEMENT
、ROW
和MIXED
三种模式,比较主流的是ROW模式,建议大家有空可以了解了解~
3.2 如何开启binlog日志?
修改MySQL的配置文件vim /etc/f
,设置binlog为开启:
#=====设置binlog方式1===========log_bin=ONlog_bin_basename=/data/mysqllog/bin-log/mysql-binlog_bin_index=/data/mysqllog/bin-log/mysql-bin.index#=====设置binlog方式2===========log_bin=/data/mysqllog/bin-log/mysql-bin
建议使用方式2
,比较简洁,相当于方式1
的三行配置~
同样vim /etc/f
设置binlog模式(具体模式按实际需要进行设置):
#设置binlog模式,ROW表示日志格式为记录每行数据的变化binlog_format=ROW
三、Xtrabackup备份及还原命令
3.1 xtrabackup全量备份命令
全备命令:
xtrabackup --defaults-file=/etc/f --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/f --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/f --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/f"#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 -rffind $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}fififiexit 0
4.2.3 执行脚本
(1)手动修改时间
生产环境,要通过后面设置的定时服务来执行Shell脚本~
不过实际开发环境时,一般需要马上验证脚本是否正确,这时可以通过修改Linux操作系统的日期,两个间隔的日期分别手动执行一次备份脚本,模拟按日
的数据库备份方案,修改操作时间需谨慎!!!不能修改系统时间的可以当天手动执行一次备份脚本,隔天后再手动执行一次备份脚本进行验证~
datedate -s "-08-26 04:00:00"date -s "-08-27 04:00:00"
(2)同步网络时间(最后)
这步仅用于修改Linux操作系统时间后,分别在不同日期下执行完备份脚本后,再将Linux操作系统时间同步为互联网的时间,没修改系统时间的可以忽略~
yum install -y ntpdatentpdate 0.asia.hwclock --systohc或(指定具体时间)date -s "-08-27 04:00:00"hwclock --systohc
(3)执行备份脚本
/bin/sh /data/mysqlbackup/tools/mysqlbackup.sh
(4)查看日志
tail -f /data/mysqlbackup/logs/backup_0826.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="0826"#增量备份日期(最新)incrBackupDate="0827"#MySQL配置文件mysqlConfig="/data/mysqldata/f"#数据恢复日志文件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`#一、天数差为0if [ $diffDays == 0 ];then#预恢复最新全量备份,后面没有增量备份,不需要指定--apply-log-only,回滚未提交事务xtrabackup --prepare --target-dir=$fullBackupPath/$fullBackupDate 2>> ${logfile}#二、天数差不为0else#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}fififidone#xtrabackup恢复预恢复完成的全量备份数据xtrabackup --defaults-file=$mysqlConfig --copy-back --target-dir=$fullBackupPath/$fullBackupDate 2>> ${logfile}#退出程序exit 0
4.3.2 删库跑路(模拟)
手动备份数据目录(预防恢复失败)
cd /data/mysqldatatar -czvf 3306_$(date +%Y%m%d)_$(date +%H%M%S)2.tar.gz 3306
删库跑路(模拟)~
cd /data/mysqldatarm -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
肝完了,兄弟们,冲冲冲!!!!!
原创不易,觉得有用的小伙伴来个一键三连(点赞+收藏+评论 )+关注
支持一下,非常感谢~