mysql5.7数据库mysqldump和XBK备份和恢复

MySQL备份与恢复

一 、数据备份介绍

1.1 为何要备份

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.

硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)

须知在生产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据没了那可就毁了，生产环境中最重要的应该就是数据了。所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据
。

ps：

这里是引用

在之前的章节里我们介绍过innodb存储引擎有自动的数据恢复功能，在执行一条写操作并且commit成功时innodb存储引擎会将新数据其写入redo log，此时如果数据库挂掉，重启后仍然可以依据redo log来恢复尚未执行完毕的数据，这跟本节讲的数据备份与恢复是两回事，试问，如果你一张表的数据不小心删掉了，redo log能帮你恢复吗，no，它只能帮你完成你尚未完成的操作，而对于你已经完成的操作，它帮不了你。所以不要混淆，为了防止数据意外丢失，我们还是应该做好定期的数据备份工作。
删除线格式

1.2 备份什么

我们要备份什么?

一般情况下, 我们需要备份的数据分为以下几种

数据
二进制日志, InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件

1.3 备份的类型

1）冷备、温备、热备

按照备份时数据库的运行状态，可以分为三种

1）冷备：停库、停服务来备份
即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

2）温备：不停库、不停服务来备份，会(锁表)阻止用户的写入 
即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作 

3）热备（建议）：不停库、不停服务来备份，也不会(锁表)阻止用户的写入
即当数据库进行备份时, 数据库的读写操作均不是受影响 

MySQL中进行不同类型的备份还要考虑存储引擎是否支持

MyISAM
热备 ×
温备 √
冷备 √
InnoDB
热备 √
温备 √
冷备 √

2）物理与逻辑

按照备份的内容分，可以分为两种

1、物理备份：直接将底层物理文件备份
2、逻辑备份：通过特定的工具从数据库中导出sql语句或者数据，可能会丢失数据精度
3）全量、差异、增量

按照每次备份的数据量，可以分为

全量备份/完全备份（Full Backup）：备份整个数据集( 即整个数据库 )
部分备份：备份部分数据集(例如: 只备份一个表的变化)

而部分备份又分为：差异备份和增量备份两种

1、差异备份（Differential Backup）

每次备份时，都是基于第一次完全备份的内容，只备份有差异的数据(新增的、修改的、删除的)，例如

第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第四次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第五次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
。。。

2、增量备份（Incremental Backup ）

每次备份时，都是基于上一次备份的内容（注意是上一次，而不是第一次），只备份有差异的数据(新增的、修改的、删除的)，所以增量备份的结果是一条链，例如

第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第二次备份内容的差异
第四次备份：以当前时间节点的数据为基础，备份与第三次备份内容的差异
第五次备份：以当前时间节点的数据为基础，备份与第四次备份内容的差异
。。。

针对上述三种备份方案，如何恢复数据呢

1、全量备份的数据恢复

只需找出指定时间点的那一个备份文件即可，即只需要找到一个文件即可

2、差异备份的数据恢复

需要先恢复第一次备份的结果，然后再恢复最近一次差异备份的结果，即需要找到两个文件

3、增量备份的数据恢复

需要先恢复第一次备份的结果，然后再依次恢复每次增量备份，直到恢复到当前位置，即需要找到一条备份链

综上，对比三种备份方案
1、占用空间：全量 > 差异 > 增量
2、恢复数据过程的复杂程度：增量 > 差异 > 全量

1.4 备份的工具

备份工具 备份速度 恢复速度 便捷性 适用存储引擎 支持的备份类型 功能 应用场景
此外，如果考虑到增量备份，还需要结合binlog日志（binlog只属于增量恢复），需要用到工具mysqlbinlog，相当于逻辑备份的一种

二 设计备份策略

2.1 备份策略设计的参考值

备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略

能够容忍丢失多少数据
恢复数据需要多长时间
需要恢复哪一些数据

2.2 三种备份策略及应用场景

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种

直接cp,tar复制数据库文件
mysqldump+复制BIN LOGS
lvm2快照+复制BIN LOGS
xtrabackup+复制BIN LOGS**
以上的几种解决方案分别针对于不同的场景

如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

三 备份实战

3.1 使用cp进行备份

备份步骤

1、向所有表施加读锁

FLUSH TABLES WITH READ LOCK;  

#2、备份数据文件

mkdir /egon_bak
cp -a /var/lib/mysql/* /egon_bak

模拟数据丢失并恢复

数据丢失

rm -rf /var/lib/mysql/*

恢复数据

cp -a /egon_bak/* /var/lib/mysql

重启服务

systemctl restart mysql

3.2 使用mysqldump+复制BINARY LOGS备份

mysqldump命令

```sql
#==========语法
mysqldump  -h 服务器  -u用户名  -p密码  选项与参数 > 备份文件.sql

===选项与参数
1、-A/--all-databases          所有库
2、-B/--databases bbs db1 db2  多个数据库
3、db1                          数据库名
4、db1 t1 t2              db1数据库的表t1、t2
5、-F                           备份的同时刷新binlog
6、-R 备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
7、--triggers 备份触发器数据（现在都是开发写触发器）
8、-E/--events 备份事件调度器
9、-d 仅表结构
10、-t 仅数据
11、--master-data=1  备份文件中 change master语句是没有注释的，默认为1
用于已经制作好了主从，现在想扩展一个从库的时候使用
如此备份，扩展添加从库时导入备份文件后
便不需要再加mater_pos了
change matser to
master_host='10.0.0.111'
master_user='rep'
master_password=123
master_log_pos=120
master_log_file='master-bin.000001'
12、--master-data=2  备份文件中 change master语句是被注释的 
 
13、--lock-all-tables 备份过程中所有表从头锁到尾，简单粗暴
在mysqldump导出的整个过程中以read方式锁住数据库中所有表，类似 flush tables with read lock 的全局锁），
这是一个全局读锁，只允许读不允许写，以此保证数据一致性。
比如当前数据库有如下schema：
information_schema（不会导出）
mysql
performance_schema（不会导出）
sys（不会导出）
test
test1
test2
那么我们在使用mysqldump导出时：
mysqldump --lock-all-tables --set-gtid-purged=on -AER > test.sql
指定--lock-all-tables参数，那么从一开始就对整个mysql实例加global read lock锁。
这整个全局读锁会一直持续到导出结束。
所以在这个过程中，数据库实际严格处于read only状态。
所以导出的数据库在数据一致性上是被严格保证的，也就是数据是一致性的。
由于这个参数会将数据库置于read only状态（也相当于不可使用状态），所以默认不加该参数。
这相当于脱机备份的感觉，所以生产数据库的备份策略上，也很少使用该参数。

该参数本身默认off,但使用该参数的话，也会自动将 --single-transaction 及 --lock-tables 参数置于 off 状态,他们是互斥的。

对于支持事务的表例如InnoDB和BDB，推荐使用--single-transaction选项，因为它根本不需要锁定表

14、--single-transaction： 快照备份 （搭配--master-data可以做到热备）
保证各个表具有数据一致性快照。
指定 --single-transaction 参数，那么导出过程中只能保证每个表的数据一致性（利用多版本特性实现，目前只能针对InnoDB事务表）。
比如有一个大表，mysqldump对该表的导出需要1分钟，那么在这1分钟的过程中，该表时可以被正常访问的。
（正常访问包括增删改查，但是alter table等对表结构发生更改的语句要被挂起。）
mysqldump能够保证从开始对该表进行导出，一直到对该表的导出结束，该表的数据都是开始的一致性数据快照状态。
所以该参数明显不能保证各个表之间的数据一致性（特别是外键约束的父表和子表之间）。
但是该参数能够让数据库处于可使用（就是应用感觉数据库可用）状态，相当于联机备份，所以被经常使用。
该参数默认off。

15、--lock-tables：如果是备份所有库，那么备份到某个库时只锁某个库，其他库可写，而--lock-all-tables是从始自终都全都锁定

保证各个schema具有数据一致性快照。
指定 --lock-tables 参数，那么在导出过程中能够保证各个schema的数据一致性。
比如导出 cms 库（该库有155张表）时：
mysqldump --lock-tables --set-gtid-purged=off -ER -B cms>test.sql
从命令开始，就对 cms 库的155张表加类似 lock table xxx read 的读锁。
这会导致在导出整个cms库的过程中，cms库实际上整体处于read only状态。
但是如果我们指定如下命令：
mysqldump --lock-tables --set-gtid-purged=on -AER >test.sql
来导出全部mysql库，那么当导出cms库的过程中，其他 schema 实际上是可以被正常访问的。
这个正常访问就是可以接受所有合法的sql语句。
所以该参数只能保证各个schema自己的数据一致性快照。
该参数默认on。

#==========完整语句 
mysqldump -uroot -pEgon@123 -A -E -R --triggers --master-data=2 --single-transaction > /backup/full.sql

#====文件太大时可以压缩 gzip ，但是gzip不属于mysql独有的命令，可以利用管道
mysqldump -uroot -pEgon@123 -A -E -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full$(date +%F).sql.gz

#====导出时压缩了，导入时需要解压，可以使用zcat命令，很方便
zcat /backup/full$(date +%F).sql.gz | mysql -uroot -p123

储备知识：binlog内容很多，如何定位到某个固定的点

```sql
===> 1、grep过滤

===> 2、检查事件：依据End_log_pos的提示，来确定某一个事件的起始位置与结束位置
mysql> show binlog events in 'mybinlog.000001'; 
如果事件很多，可以分段查看
mysql> show binlog events in 'mybinlog.000001' limit 0,30; 
mysql> show binlog events in 'mybinlog.000001' limit 30,30; 
mysql> show binlog events in 'mybinlog.000001' limit 60,30; 

===> 3、利用mysqlbinlog命令
生产中很多库，只有一个库的表被删除，我不可能把所有的库都导出来筛选，因为那样子binlog内容很多，辨别复杂度高，我们可以利用

[root@egon mysql]# mysqlbinlog -d db1 --start-position=123 --stop-position=154 mybinlog.000001 --base64-output=decode-rows -vvv | grep -v 'SET'

参数解释：

1）-d 参数接库名
mysqlbinlog -d database --base64-output=decode-rows -vvv mysql-bin.000002
2）--base64-output  显示模式
3）-vvv   显示详细信息
备份：

1、先打开binlog日志

vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/var/lib/mysql/mybinlog
binlog_format='row' #(row,statement,mixed)    
binlog_rows_query_log_events=on
max_binlog_size=100M

2、登录数据库，插入测试数据

mysql> create database db3;
mysql> use db3;
mysql> create table t1(id int);
mysql> insert t1 values(1),(2),(3);

3、在命令行执行命令，进行全量备份

[root@egon mysql]# mysqldump -uroot -pEgon@123 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz

4、在命令行执行命令，刷新binlog，便于日后查找

[root@egon mysql]# mysql -uroot -pEgon@123 -e "flush logs"

5、登录数据库，再插入一些数据，模拟增量，这些数据写入了新的binlog

mysql> use db3;
mysql> insert t1 values(4),(5),(6);
模拟数据损坏恢复

模拟数据丢失

mysql> drop database db1;

恢复数据

1、mysql数据导入时，临时关闭binlog，不要将恢复数据的写操作也记入

mysql> set sql_log_bin=0;

2、先恢复全量

mysql> source /tmp/full.sql

如果是压缩包呢，那就这么做

mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pEgon@123

3、再恢复增量

导出：注意导出binlog时不要加选项--base64-output
[root@egon mysql]# mysqlbinlog mybinlog.000002 --stop-position=531 > /tmp/last_bin.log
导入
mysql> source /tmp/last_bin.log

4、开启二进制日志

mysql> SET sql_log_bin=ON;
测试在线热备份

可以先准备一个存储过程，一直保持写入操作，然后验证热备

1. 准备库与表

create database if not exists db1;
use db1;
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

2. 创建存储过程，每隔3秒插入一条

delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'egon','male',concat('egon',i,'@oldboy'));
     select concat('egon',i,'_ok') as name,sleep(3);
  set i=i+1;
    end while;
END$$ #$$结束
delimiter ;

3. 查看存储过程

show create procedure auto_insert1\G 
备份：

1、先打开binlog日志

略

2、登录数据库，执行存储过程

mysql> use db1;
mysql> call auto_insert1();

若想杀死存储过程
mysql> show processlist; -- 查出id
mysql> kill id号;

3、在命令行执行下述命令，进行全量备份

[root@egon mysql]# mysqldump -uroot -pEgon@123 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz

4、全量备份完毕后的一段时间里，数据依然插入，写入了mybinlog.000001中

然后我们在命令行刷新binlog，产生了新的mybinlog.000002

[root@egon mysql]# mysql -uroot -pEgon@123 -e "flush logs"

5、此时数据依然在插入，但都写入了最新的mybinlog.000002中，所以需要知道的是，增量的数据在mysqlbinlog.000001与mybinlog.000002中都有

我们登录数据库，杀掉存储过程，观察到最新的数据插到了id=55的行
mysql> show processlist; -- 查出id
mysql> kill id号;
删除数据

drop database db1;
恢复数据

登录数据库，先恢复全量

mysql> set sql_log_bin=0;
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pEgon@123
mysql> select * from db1.s1; -- 查看恢复到了id=28，剩下的去增量里恢复

在命令行导出mybinlog.000001中的增量，然后登录库进行恢复

查找位置，发现@1=29即第一列等于29，即id=29的下一个position是10275

mysql> show binlog events in 'mybinlog.000001';  
[root@egon mysql]# mysqlbinlog mybinlog.000001 --start-position=10038  --stop-position=11340 --base64-output=decode-rows -vvv | grep -v 'SET' | less

在命令行中执行导出

[root@egon mysql]# mysqlbinlog mybinlog.000001 --start-position=10275 > /tmp/1.sql

在库内执行导入,发现恢复到了39

mysql> source /tmp/1.sql  -- 最好是在库内恢复，因为sql_log_bin=0，导入操作不会记录
mysql> select * from db1.s1

;

在命令行导出mybinlog.000002中的增量，然后登录库进行恢复

上面恢复到了id=39，我们接着找id=40的进行恢复，查找位置
发现@1=40的position是432
发现@1=55的position是6464
mysql> show binlog events in 'mybinlog.000002'; 
[root@egon mysql]# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002|grep -v 'SET'|grep -C20 -w '@1=40'
[root@egon mysql]# mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002|grep -v 'SET'|grep -C20 -w '@1=55'


导出
[root@egon mysql]# mysqlbinlog mybinlog.000002 --start-position=432 --stop-position=6464> /tmp/2.sql

在库内执行导入,发现恢复到了55
mysql> source /tmp/2.sql  
mysql> select * from db1.s1;

开启binlog

mysql> SET sql_log_bin=ON; 
问题：能否利用binlog做全量恢复

可以，但直接使用binlog做全量恢复，成本很高，我们只用起来做增量恢复。

正确的方案是：全备+binlog增量
每天或者每周全备一次，全备之后，那个位置点之前的binlog全都可以删除，不可能一年有上百个binlog的库都导出来筛选，因为那样子binlog内容很多，辨别复杂度高，我们可以利用

3.4 物理备份之Xtrabackup

（1）介绍

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

备份过程快速、可靠；
备份过程不会打断正在执行的事务；
能够基于压缩等功能节约磁盘空间和流量；
自动实现备份检验；
还原速度快；
使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启

（2）安装

版本选择

mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本

mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本，xtrabackup8.0也只支持mysql8.0以上的版本

比如，接触过一些金融行业，mysql版本还是多采用mysql 5.7，当然oracle官方对于mysql 8.0的开发支持力度日益加大，新功能新特性迭代不止。生产环境采用mysql 8.0的版本比例会日益增加。

安装方式一

安装yum仓库

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

安装XtraBackup命令

yum install percona-xtrabackup-24 -y

安装方式二

#下载epel源

wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-7.repo

#安装依赖

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#下载Xtrabackup

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

安装

yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
安装完后会生成命令

xtrabackup      以前使用该命令
innobackupex    现在使用该命令

innobackupex是xtrabackup的前端配置工具，使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.

（3）Xtrabackup 备份方式（物理备份）

1.对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。

2.对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。

3.备份时读取配置文件/etc/my.cnf

（4）Xtrabackup全量备份

#1、创建备份目录，会把mysql的datadir中的内容备份到改目录中
mkdir /backup

#2、全备
#2.1 在本地执行下述命令，输入登录数据的本地账号与密码
#2.2 指定备份目录为/backup下的full目录
innobackupex --user=root --password=123 /backup/full

#3、查看：默认会在备份目录下生成一个以时间戳命名的文件夹
[root@localhost ~]# cd /backup/full/
[root@localhost full]# ls
2021-07-16_16-09-47
[root@localhost full]# ls 2021-07-16_16-09-47/ #备份目录
。。。
[root@localhost full]# ls /var/lib/mysql # 数据目录
。。。在这里插入代码片

4、去掉时间戳，让备份数据直接放在备份目录下

我们在写备份脚本和恢复脚本，恢复的时候必须指定上一次备份的目录，如果备份目录带着时间戳，该时间戳我们很难在脚本中确定，无为了让脚本编写更加方便，我们可以使用选项–no-timestamp去掉时间戳，让备份内容直接放置于我们指定的目录下（ps：金融公司喜欢每天全备，每小时增备，如果备份目录带着时间戳，看似合理，但确实会很让头疼）

[root@localhost full]# rm -rf 2021-07-16_17-45-53/
[root@localhost full]# innobackupex --user=root --password=123 --no-timestamp /backup/full

补充：关于备份目录下新增的文件说明，可用cat命令查看

xtrabackup_checkpoints 存储系统版本号，增备的时候会用到
xtrabackup_info 存储UUID，数据库是由自己的UUID的，如果相同，做主从会有问题
xtrabackup_logfile 就是redo

（5）Xtrabackup增量备份

#一 基于上一次备份进行增量,参数说明:
--incremental：开启增量备份功能
--incremental-basedir：上一次备份的路径
 
#二 加上上一次命令
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/xtra
 
#三 判断数据备份是否衔接
cat /backup/full/xtrabackup_checkpoints
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 1808756
    last_lsn = 1808756
    compact = 0
    recover_binlog_info = 0
    flushed_lsn = 1808756
 
cat /backup/xtra/xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 1808756  # 值应该与全被的to_lsn一致
    to_lsn = 1808756
    last_lsn = 1808756
    compact = 0
    recover_binlog_info = 0
    flushed_lsn = 1808756
更多参数详见：https://www.cnblogs.com/linhaifeng/articles/15021166.html

（6）企业实战：Xtrabackup + Binlog恢复

mysql配置文件：数据目录与binlog放在不同的文件夹下

[mysqld]
datadir=/var/lib/mysql
default-storage-engine=innodb
innodb_file_per_table=1
server_id=1
log-bin=/data/binlog/mybinlog  
binlog_format='row' #(row,statement,mixed)    
binlog_rows_query_log_events=on
max_binlog_size=100M

为binlog日志创建目录

mkdir -p /data/binlog/
chown -R mysql.mysql /data/

启动mysql

systemctl restart mysql

模拟数据

create database full charset utf8mb4;
use full;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

进行周日的全备

1、事先创建好备份目录

[root@db01 backup]# rm -rf /backup 
[root@db01 backup]# mkdir /backup   

2、全备

[root@db01 backup]# innobackupex --user=root --password=123 --no-timestamp  --parallel=5  /backup/full

模拟周一的数据变化

create database inc1 charset utf8mb4;
use inc1;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

进行周一的增量备份

innobackupex  --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 

检查本次备份的LSN

[root@localhost backup]# cat /backup/full/xtrabackup_checkpoints **#查看全备的lsn**
backup_type = full-backuped
from_lsn = 0
to_lsn = 1817002
last_lsn = 1817002
compact = 0
recover_binlog_info = 0
flushed_lsn = 1817002
[root@localhost backup]# cat /backup/inc1/xtrabackup_checkpoints #from_lsn数值要等与全备的last_lsn的值。
backup_type = incremental
from_lsn = 1817002
to_lsn = 1825905
last_lsn = 1825905
compact = 0
recover_binlog_info = 0
flushed_lsn = 1825905
[root@localhost backup]# 

模拟周二数据变化

create database inc2 charset utf8mb4;
use inc2;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

周二的增量

innobackupex   --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 

周三的数据变化

create database inc3 charset utf8mb4;
use inc3;
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

模拟上午10点数据库崩溃

systemctl stop mysql # pkill -9 mysqld
\rm -rf /var/lib/mysql/*

恢复思路

1. 停业务,挂维护页
2. 查找可用备份并处理备份:full+inc1+inc2 
3. 找到binlog中: inc2 到 故障时间点的binlog
4. 恢复全备+增量+binlog
5. 验证数据
6. 起业务,撤维护页

恢复前的准备 [rml_read_more]：

所有增量必须要按顺序合并到全备当中才能用于恢复

#(1) 整理full，--use-memory越大效率越高，但是不要超过内存大小，超过则报错
innobackupex --apply-log --use-memory=3G --redo-only  /backup/full

--apply-log：该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

#(2) 合并inc1到full,并整理备份
innobackupex --apply-log --use-memory=3G --redo-only  --incremental-dir=/backup/inc1 /backup/full 

#(3) 合并后对比inc1与full的LSN号:last_lsn保持一致
cat /backup/full/xtrabackup_checkpoints 
cat /backup/inc1/xtrabackup_checkpoints 

#(4) 合并inc2到full,并整理备份 (合并最后一个增量备份时不要加--redo-only)
innobackupex --apply-log --use-memory=3G --incremental-dir=/backup/inc2 /backup/full 

#(5) 合并后对比inc2与full的LSN号:last_lsn保持一致
cat /backup/full/xtrabackup_checkpoints 
cat /backup/inc2/xtrabackup_checkpoints 

#(6) 最后一次整理ful
innobackupex --use-memory=3G --apply-log  /backup/full

#(7) 截取二进制日志


起点

cat /backup/inc2/xtrabackup_binlog_info
输出内容如下，显示日志的初始位置
mybinlog.000002    2125 
查看详细日志信息，找到上面查到的起始位置。
mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002 

导出数据
mysqlbinlog /data/binlog/mybinlog.000002  --start-position=2125 > /backup/binlog.sql

恢复备份的数据

cp -a  /backup/full/* /var/lib/mysql
chown -R mysql.mysql /var/lib/mysql
systemctl start mysql
mysql -uroot -p123
> set sql_log_bin=0;  #停止数据库再次写入binlog日志
> source /backup/binlog.sql

验证

select * from full.t1;
select * from inc1.t1;
select * from inc2.t1;
select * from inc3.t1;

四 自动备份脚本

4.1 备份计划

1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

4.2 备份脚本

备份脚本：

[root@egon ~]# vim /mysql_back.sh
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
        mkdir -p /backup
fi

备份并截断日志

mysqldump -u${user} -p${pass} --events -R --triggers --master-data=2 --single-transaction --all-databases > ${back_dir}/${back_file}

mysql -u${user} -p${pass} -e 'flush logs'

只保留最近一周的备份

cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

4.3 手动测试

手动测试：
chmod a+x /mysql_back.sh
chattr +i /mysql_back.sh
bash /mysql_back.sh

4.4 配置计划任务

配置cron：
[root@egon ~]# crontab -l
0 2 * * * /mysql_back.sh

五 企业案例

5.1 背景

1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本

3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？

5.2 处理故障思路

1.停业务避免数据的二次伤害
2.找一个临时的库，恢复前一天的全备
3.截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4.测试可用性和完整性
5.开启业务前的两种方式
1）直接使用临时库顶替原生产库，前端应用割接到新
2）将误删除的表单独导出，然后导入到原生产环境
6.对外开放业务

5.3 故障模拟

1）准备初始数据

#刷新binlog使内容更清晰
flush logs;
#查看当前使用的binlog
show master status;

#准备测试库与数据
create database dbtest;
use dbtest;
create table t1(id int,name varchar(16));
insert t1 values
(1,"egon1"),
(2,"egon2"),
(3,"egon3");

create table t2 select * from t1;

2）全备

[root@db01 ~]# mkdir /backup
[root@db01 ~]# mysqldump -uroot -pEgon@123 -A -R --triggers --master-data=2 --single-transaction |gzip > /backup/full.sql.gz  # 通常备份

文件应该带时间，此处略

3）模拟23:00到10:00的操作

use dbtest;
create table t3 select * from t1;
select * from t3;
update t1 set name="EGON" where id=2;
delete from t2 where id>2;

4）模拟10：00删库操作

#删库、跑路
drop database dbtest;

5.4 恢复数据

1）先停生产库,避免数据二次伤害

[root@db01 ~]# systemctl stop mysql

2）准备新库，在新库中完成数据恢复操作后再更新给生成库

3）通过binlog找到23:00到第二天10:00之间新增的数据

1.找到结束位置点：
mysql> show master status;
mysql> show binlog events in 'mybinlog.000002';
[root@localhost backup]# cd /var/lib/mysql
[root@localhost mysql]# mysqlbinlog --base64-output=decode-rows -vvv --start-datetime="2021-07-16 02:00:00" mybinlog.000002 

3.取出位置点之间新增的数据

[root@db01 ~]# mysqlbinlog --start-position=694 --stop-position=1249 mybinlog.000002 > /backup/xin.sql 

4）将前一天的全备数据和新增的数据拷贝到新数据库

scp /backup/full.sql.gz  172.16.1.52:/tmp/  
scp /backup/xin.sql  172.16.1.52:/tmp/

5）将前一天的全备与增量恢复到新库

mysql> set sql_log_bin=0;
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -pEgon@123

mysql> source /tmp/xin.sql;

6）查看表和数据验证数据完整

mysql> use dbtest;
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;

7）恢复生产环境提供服务

1.将恢复的表导出，导入到生产库（如果核心业务表很小）

1）导出指定表

 [root@db02 mysql]# mysqldump dbtest t1 t2 t3 > /tmp/test.sql

2）将sql传输到生产库

 [root@db02 mysql]# scp /tmp/test.sql 172.16.1.51:/tmp/

3）指定库导入表

 [root@db01 data]# mysql backup < /tmp/test.sql

2.应用服务修改数据库配置连接到新库（如果核心业务表很大）