day08-03-备份恢复之物理备份(XBK)

1. 上节回顾
   mysqldump 核心参数
   -A
   -B
   库 表

--master-data=2
(1) 以注释的形式记录二进制日志信息
(2) 自动开启锁表的功能

--single-transaction
针对InnoDB进行快照备份
-R
-E
--triggers

--set-gtid-purged=AUTO/ON/OFF
默认是auto
主从复制,忽略此参数
普通备份,可以OFF

-F flush logs;

---

物理备份 Xtrabackup （Percona-Xtrabackup)

安装依赖包：

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum list|grep xtrabackup

# Mysql 8.0版本之前，使用Percona-Xtrabackup-2.4.x版本
yum install percona-xtrabackup-24

# Mysql 8.0版本，使用Percona-Xtrabackup-8.x版本

下载软件并安装

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

https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

软件包安装后共有四个可执行文件

usr
├── bin
│   ├── innobackupex
│   ├── xbcrypt
│   ├── xbstream
│   └── xtrabackup

innobackupex 使用

备份核心理念

1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件
2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata(undo表空间）1,ibd（数据行）,frm（数据结构).
   并且将备份过程中产生,新的数据变化的部分ib_logfile(redo)一起备份走
3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复.

xtrabackup的备份原理

.assets\2016667-20210511170032022-889254913.png)

其中最主要的是 innobackupex 和 xtrabackup，前者是一个 perl 脚本，后者是 C/C++ 编译的二进制。由于C和perl之间没有很好的互通协议，PXC通过创建文件来协调两者的工作。

在版本2.2（包含）之前，xtrabackup是用来备份InnoDB表的，不能备份非InnoDB表；innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 mysqld server 发送命令进行交互，如加读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。简单来说，innobackupex 在 xtrabackup 之上做了一层封装。

在版本2.3开始，innobackupex 功能全部集成到 xtrabackup 里面，只有一个 binary，另外为了使用上的兼容考虑，innobackupex 作为 xtrabackup 的一个软链。

在版本8.0，innobackupex 命令被移除

备份执行过程*********

1.innobackupex 在启动后，会先 fork 一个进程，启动 xtrabackup进程，然后就等待 xtrabackup 备份完 ibd 数据文件；
2.xtrabackup 在备份 InnoDB 相关数据时，是有2种线程的 ，
	1种是 redo 拷贝线程，负责拷贝 redo 文件，
	1种是 ibd 拷贝线程，负责拷贝 ibd 文件；
	redo 拷贝线程只有一个，在 ibd 拷贝线程之前启动，在 ibd 线程结束后结束。
	xtrabackup 进程开始执行后，先启动 redo 拷贝线程，从最新的 checkpoint 点开始顺序拷贝 redo 日志；然后再启动 ibd 数据拷贝线程，在 xtrabackup 拷贝 ibd 过程中，innobackupex 进程一直处于等待状态（等待文件被创建）。
3.xtrabackup 拷贝完成idb后，通知 innobackupex（通过创建文件），同时自己进入等待（redo 线程仍然继续拷贝）;
4.innobackupex 收到 xtrabackup 通知后，执行FLUSH TABLES WITH READ LOCK (FTWRL)，取得一致性位点，然后开始备份非 InnoDB 文件（包括 frm、MYD、MYI、CSV、opt、par等）。拷贝非 InnoDB 文件过程中，因为数据库处于全局只读状态，如果在业务的主库备份的话，要特别小心，非 InnoDB 表（主要是MyISAM）比较多的话整库只读时间就会比较长，这个影响一定要评估到。
5.当 innobackupex 拷贝完所有非 InnoDB 表文件后，通知 xtrabackup（通过删文件） ，同时自己进入等待（等待另一个文件被创建）；
6.xtrabackup 收到 innobackupex 备份完非 InnoDB 通知后，就停止 redo 拷贝线程，然后通知 innobackupex redo log 拷贝完成（通过创建文件）；
7.innobackupex 收到 redo 备份完成通知后，就开始解锁，执行 UNLOCK TABLES；
8.最后 innobackupex 和 xtrabackup 进程各自完成收尾工作，如资源的释放、写备份元数据信息等，innobackupex 等待 xtrabackup 子进程结束后退出

在上面描述的文件拷贝，都是备份进程直接通过操作系统读取数据文件的，只在执行 SQL 命令时和数据库有交互，基本不影响数据库的运行，在备份非 InnoDB 时会有一段时间只读（如果没有MyISAM表的话，只读时间在几秒左右），在备份 InnoDB 数据文件时，对数据库完全没有影响，是真正的热备。

InnoDB 和非 InnoDB 文件的备份都是通过拷贝文件来做的，但是实现的方式不同，前者是以page为粒度做的(xtrabackup)，后者是 cp 或者 tar 命令(innobackupex)，xtrabackup 在读取每个page时会校验 checksum 值，保证数据块是一致的，而 innobackupex 在 cp MyISAM 文件时已经做了flush（FTWRL），磁盘上的文件也是完整的，所以最终备份集里的数据文件都是写入完整的。

增量备份

PXB 是支持增量备份的，但是只能对 InnoDB 做增量，InnoDB 每个 page 有个 LSN 号，LSN 是全局递增的，page 被更改时会记录当前的 LSN 号，page中的 LSN 越大，说明当前page越新（最近被更新）。每次备份会记录当前备份到的LSN（xtrabackup_checkpoints 文件中），增量备份就是只拷贝LSN大于上次备份的page，比上次备份小的跳过，每个 ibd 文件最终备份出来的是增量 delta 文件。

MyISAM 是没有增量的机制的，每次增量备份都是全部拷贝的。增量备份过程和全量备份一样，只是在 ibd 文件拷贝上有不同。

恢复过程

如果看恢复备份集的日志，会发现和 mysqld 启动时非常相似，其实备份集的恢复就是类似 mysqld crash后，做一次 crash recover。

恢复的目的是把备份集中的数据恢复到一个一致性位点，所谓一致就是指原数据库某一时间点各引擎数据的状态，比如 MyISAM 中的数据对应的是 15:00 时间点的，InnoDB 中的数据对应的是 15:20 的，这种状态的数据就是不一致的。PXB 备份集对应的一致点，就是备份时FTWRL的时间点，恢复出来的数据，就对应原数据库FTWRL时的状态。

因为备份时 FTWRL 后，数据库是处于只读的，非 InnoDB 数据是在持有全局读锁情况下拷贝的，所以非 InnoDB 数据本身就对应 FTWRL 时间点；InnoDB 的 ibd 文件拷贝是在 FTWRL 前做的，拷贝出来的不同 ibd 文件最后更新时间点是不一样的，这种状态的 ibd 文件是不能直接用的，但是 redo log 是从备份开始一直持续拷贝的，最后的 redo 日志点是在持有 FTWRL 后取得的，所以最终通过 redo 应用后的 ibd 数据时间点也是和 FTWRL 一致的。

所以恢复过程只涉及 InnoDB 文件的恢复，非 InnoDB 数据是不动的。备份恢复完成后，就可以把数据文件拷贝到对应的目录，然后通过mysqld来启动了。

---

备份产生的文件介绍

find /backup/full -maxdepth 1 -type f |xargs ls -lhrt

./ibdata1
./xtrabackup_binlog_info
./xtrabackup_logfile  # 备份时会开启一个log copy线程，用来监控redo日志文件（ib_logfile），如果修改就会复制到这个文件
./xtrabackup_checkpoints  # 记录备份的类型、开始和结束的日志序列号
./ib_buffer_pool
./backup-my.cnf # 记录innobackup使用到mysql参数
./xtrabackup_info

(1) xtrabackup_binlog_info ******
记录备份时刻的二进制日志信息. 可以作为binlog截取的起点

cat xtrabackup_binlog_info 
mysql-bin.000003	6511	7a995d76-0a32-11eb-9c4a-525400c58226:1-27

(2)xtrabackup_checkpoints ******

cat xtrabackup_checkpoints 
backup_type = full-prepared     
\# full-prepared 代表做完undo和redo的恢复
\# full-backuped 代表 做完全量备份
\# incremental 表示增量备份
from_lsn = 0    \# 备份中包含的LSN号的起点,全备:0,增量:上次备份的结束位置
to_lsn = 191543615      \# ckpt 时的LSN
last_lsn = 191543624    \# 备份结束时的LSN.last - 9 就是下次增量备份的起始位置.
\#  last_lsn与to_lsn相差9，就表示号码一致，5.7版本中，会预留9个号作为维护区间号码。5.6版本没有这个情况
compact = 0
recover_binlog_info = 0

xtrabackup_info #记录备份的基本信息，uuid、备份命令、备份时间、binlog、LSN、以及其他加密压缩等信息。
xtrabackup_logfile #备份的redo日志文件

ib_buffer_pool #buffer pool 中的热数据，当设置 innodb_buffer_pool_dump_at_shutdown=1 ，在关闭 MySQL 时，会把内存中的热数据保存在磁盘里 ib_buffer_pool 文件中，位于数据目录下

backup-my.cnf #备份命令用到的配置选项信息

---

备份方式——物理备份

（1）对于非Innodb表（比如 myisam）是，锁表cp数据文件，属于一种温备份。
（2）对于Innodb的表（支持事务的），不锁表，拷贝数据页，最终以数据文件的方式保存下来，把一部分redo和undo一并备走，属于热备方式。

1.1 XBK --redo-only ,XBK 恢复的过程

全备base full: --apply-log --rede-only
增量1 inc1: --apply-log --rede-only --incremental-dir=inc1
增量2 inc2: --apply-log --incremental-dir=inc2
合并后的 full --apply-log

面试题： xbk 在innodb表备份恢复的流程

0、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
1、备份时，拷贝磁盘数据页，并且记录备份过程中产生的redo和undo一起拷贝走,也就是checkpoint LSN之后的日志
2、在恢复之前，模拟Innodb“自动故障恢复”的过程，将redo（前滚）与undo（回滚）进行应用
3、恢复过程是cp 备份到原来数据目录下

常用参数

-–user=     #指定数据库备份用户
-–password= #指定数据库备份用户密码
-–port=     #指定数据库端口
-–host=     #指定备份主机
-–socket=   #指定socket文件路径
-–defaults-file=    # 指定my.cnf配置文件，且必须作为命令行上的第一个选项
-–databases=  #指定备份的数据库和表，格式为：--databases="db1[.tb1] db2[.tb2]" 多个库之间以空格隔开，如果此选项不被指定，将会备份所有的数据库
--include=REGEXP    # 用正则表达式的方式指定要备份的数据库和表，格式为 --include=‘^mydb[.]mytb’ ，对每个库中的每个表逐一匹配，因此会创建所有的库，不过是空的目录。--include 传递给 xtrabackup --tables。

--tables-file=FILE  # 此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称，格式为databasename.tablename。该选项传递给 xtrabackup --tables-file，与--tables选项不同，只有要备份的表的库才会被创建。
注意：部分备份（--include、--tables-file、--database）需要开启 innodb_file_per_table 

#############################################################
# 准备备份(prepare)时

-–apply-log         #日志回滚，只执行重做(redo)阶段，这对于增量备份非常重要，应用 BACKUP-DIR 中的 xtrabackup_logfile 事务日志文件。一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件处于一致性状态

-–redo-only         #在“准备基本完整备份” 和 “合并所有的增量备份(除了最后一个增备)”时使用此选项。它直接传递给xtrabackup的 xtrabackup --apply-log-only 选项，使xtrabackup跳过"undo"阶段，只做"redo"操作。如果后面还有增量备份应用到这个全备,这是必要的。有关详细信息,请参阅xtrabackup文档

###############################################################
# 增量备份

-–incremental=      #这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。它传递到 xtrabackup 子进程。当指定这个选项，可以设置 --incremental-lsn 或 --incremental-basedir。如果这2个选项都没有被指定，--incremental-basedir 传递给 xtrabackup 默认值，默认值为：基础备份目录的第一个时间戳备份目录
--incremental-basedir=DIRECTORY  # 该选项接受一个字符串参数，该参数指定作为增量备份的基本数据集的完整备份目录。它与 --incremental 一起使用

--incremental-dir=DIRECTORY    # 该选项接受一个字符串参数，该参数指定了增量备份将与完整备份相结合的目录，以便进行新的完整备份。它与 --incremental 选项一起使用

###############################################################

--copy-back    # 拷贝先前备份所有文件到它们的原始路径。但原路径下不能有任何文件或目录，除非指定 --force-non-empty-directories 选项

--force-non-empty-directories   # 恢复时指定此选项，可使 --copy-back 和 --move-back 复制文件到非空目录，即原data目录下可以有其他文件，但是不能有与恢复文件中同名的文件，否则恢复失败

-–no-timestamp  #指定了这个选项备份将会直接存储在 BACKUP-DIR 目录，不再创建时间戳文件夹

-–remote-host=user@ip DST_DIR #备份到远程主机
--use-memory=#    # 此选项接受一个字符参数（1M/1MB,1G/1GB，默认100M），仅与--apply-log一起使用，该选项指定prepare时用于崩溃恢复（crash-recovery）的内存
--parallel=NUMBER-OF-THREADS   # 此选项接受一个整数参数，指定xtrabackup子进程应用于同时备份文件的线程数。请注意，此选项仅适用于文件级别，也就是说，如果您有多个.ibd文件，则它们将被并行复制； 如果您的表一起存储在一个表空间文件中，它将不起作用
--rsync    #此选项可优化本地文件（非InnoDB）的传输。rsync工具一次性拷贝所有非InnoDB文件，而不是为每个文件单独创建cp，在备份恢复很多数据库和表时非常高效。此选项不能和 --stream 一起使用
-–stream=  #指定流的格式做备份，–stream=tar，将备份文件归档

所有库全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=password --no-timestamp /path/backup/full_dir

指定数据库全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=password -–no-timestamp --databases="db_name" /path/backup/full_dir

指定表全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=password -–no-timestamp --databases="db_name tb_name" /path/backup/full_dir

以压缩格式全量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=password -–no-timestamp --stream=tar /path/backup/full_dir | gzip > /path/backup/full_dir/full_`date +%F`.tar.gz


#在节点上进行备份，并把备份传输到其他节点上
innobackupex --defaults-file= /etc/my.cnf --slave-info --user=xtrabackup --password=xtrabackuppass --no-timestamp --stream=tar ./ | ssh root@10.10.30.164 "cat - > /archive/backup/backup_`date +%Y%m%d`.tar"


解压缩：
tar -ixzvf full.tar.gz

备份过程

XBK的全量备份恢复

 innobackupex  --user=root --password=123 --no-timestamp /backup/full
 
 --no-timestamp 在备份目录下，不生成以“年月日时分秒”的目录

利用全备进行恢复

pkill mysqld

rm -rf /data/mysql/data/*

innobackupex --apply-log /backup/full/
--apply-log		# 模拟CSR的全过程,在恢复之前,将数据的LSN号和redo LSN号追平

cp -a /backup/full/* /data/mysql/data/

chown -R mysql.mysql /data/mysql/data/*

/etc/init.d/mysqld start

备份的过程

(1) 非InnoDB表,进行短暂的锁表,然后Copy数据文件
(2) 对于InnoDB表,立即出发checkpoint,会立即记录一个LSN,COPY数据文件.
(3) 将备份过程中产生的redo进行截取和保存,并记录此时最新的LSN

恢复过程

1.prepare

innobackupex --apply-log /data/mysql/backup/full

# 模拟了CSR的全过程,在恢复之前,将数据的LSN号和redo LSN号追平

2.copy-back

恢复方法就是直接cp回去即可

cp -r backup_dir recovery_dir

chown -R mysql.mysql /path/mysql/data/*

XBK 全备和恢复体验

innobackupex --user=root --password=123 --no-timestamp /backup/full 
innobackupex --apply-log /backup/full 

---

---

XBK的增量备份恢复

说明

备份时：

增量必须依赖于全量备份

每次增量都是参照上次备份的LSN号码（xtrabackup_checkpoints），在此基础上变化的数据页，备份走。

并且，会将备份过程中产生新的变化的redo一并备份走

恢复时：

将所有需要的增量inc备份，按顺序合并到全备中。

并且需要将每个备份进行prepare（--apply-log [--rede-only] ）

清空备份路径

rm -rf /backup/*

模拟数据

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

进行周日的全备

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

innobackupex --defaults-file=/etc/my.cnf --user=root --password=w -S /tmp/mysql.sock --no-timestamp /data/backup/full

5.4 模拟周一的数据变化

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

5.5 进行周一的增量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --no-timestamp -S /tmp/mysql.sock --incremental --incremental-basedir=/backup/full /backup/inc1 

说明:
--incremental 开关
--incremental-basedir=/backup/full 基于哪个全备做参照，进行增量备份
/backup/inc1 增量备份的位置点

5.6 检查备份的LSN

cat /backup/full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 217478672
last_lsn = 217478681
compact = 0
recover_binlog_info = 0

cat /backup/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 217478672
to_lsn = 217484653
last_lsn = 217484662
compact = 0
recover_binlog_info = 0

5.7 模拟周二数据变化

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

5.8 周二的增量

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

5.9 周三的数据变化

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

5.10 模拟上午10点数据库崩溃

pkill mysqld 
rm -rf /data/mysql/data/*

5.11 恢复思路

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

5.12 恢复前的准备
(1) 基础全备整理

innobackupex --apply-log --redo-only  /backup/full
--redo-only     #表示，整理备份集时，CSR跳过undo回滚，只做redo前滚恢复

(2) 合并inc1到full,并整理备份

innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc1 /backup/full 

(3) 合并inc2到full,并整理备份

innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full 

(4) 最后一次整理full

innobackupex --apply-log  /backup/full

5.13 截取二进制日志
起点:

cat /backup/inc2/xtrabackup_binlog_info

mysql-bin.000031	1997	aa648280-a6a6-11e9-949f-000c294a1b3b:1-17,
e16db3fd-a6e8-11e9-aee9-000c294a1b3b:1-9

终点:

mysqlbinlog /data/binlog/mysql-bin.000031 |grep 'SET'

SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/*!*/;

mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql

5.14 恢复备份数据

cp -a  /backup/full/* /data/mysql/data/
chown -R mysql. /data/
/etc/init.d/mysqld start

mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql

5.15 验证数据

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

作业1
Xtrabackup企业级增量恢复实战
背景：
某大型网站，mysql数据库，数据量500G，每日更新量20M-30M
备份策略：
xtrabackup，每周日0:00进行全备，周一到周六00:00进行增量备份。
故障场景：
周三下午2点出现数据库意外删除表操作。
如何恢复？

1.停业务，挂维护页面。

2.查找备份集，周日全备 + 周一、二增量备份

3.截取周三故障时间点之前的binlog（0.00-14.00）日志

4.恢复数据

5.验证数据

6.恢复业务，撤销维护页面。

作业2
练习：mysqldump备份恢复例子
1、创建一个数据库 oldboy

mysql> create table oldboy charset utf8mb4;

2、在oldboy下创建一张表t1

mysql> create table t2 (id int);
mysql> alter table t2 add column name varchar(10);

3、插入5行任意数据

mysql> insert into t2 values (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee');
mysql> commit;

4、全备

mysqldump --user=root --password=w --master-data=2 --single-transaction --routines --events --triggers --databases oldboy > /data/backup/mdp_oldboy_full.sql

5、插入两行数据，任意修改3行数据，删除1行数据

mysql> insert into t2 values (6,'66'),(7,'77');
mysql> commit;
mysql> update t2 set name='l3' where id between 2 and 4;
mysql> delete from t2 where id=6;
mysql> commit;

6、删除所有数据

mysql> delete from t2;
mysql> commit;

7、再t1中又插入5行新数据，修改3行数据

mysql> insert into t2 values(10,'t2'),(11,'t2'),(12,'t2'),(13,'t2'),(14,'t2');
mysql> update t2 set name='u3' where id>12;

需求，跳过第六步恢复表数据

恢复：

确认mdp 备份集

# ll /data/backup/mdp_oldboy_full.sql 
-rw-r--r-- 1 root root 2.6K Oct 17 09:15 /data/backup/mdp_oldboy_full.sql

查看备份集包含的事务 起止点：

[root@mysql-node01 backup]# pwd
/data/backup

more mdp_oldboy_full.sql
SET @@GLOBAL.GTID_PURGED='7a995d76-0a32-11eb-9c4a-525400c58226:1-50,
b4dfda8a-0f89-11eb-a610-525400c58226:1-7,
de25c9af-0f86-11eb-a80f-525400c58226:1-9';

查看故障事务时间，确认截取起止点。

mysqlbinlog --base64-output=decode-rows -vvv ./mysql-bin.000008 |more

查看发现，故障时间点GTID是“b4dfda8a-0f89-11eb-a610-525400c58226:10”

跳过故障点，然后截取：

查看跳过内容后的结果

mysqlbinlog --base64-output=decode-rows -vvv --include-gtids='b4dfda8a-0f89-11eb-a610-525400c58226:8-12' --exclude-gtids='b4dfda8a-0f89-11eb-a610-525400c58226:10' ./mysql-bin.000008|more

截取：

mysqlbinlog --skip-gtids --include-gtids='b4dfda8a-0f89-11eb-a610-525400c58226:8-12' --exclude-gtids='b4dfda8a-0f89-11eb-a610-525400c58226:10' ./mysql-bin.000008 > /data/backup/oldboy_ext.bin.sql

开始恢复：

mysql -uroot -p -S /tmp/mysql.sock

mysql> set sql_log_bin=0;
mysql> source /data/backup/mdp_oldboy_full.sql
mysql> use oldboy;
mysql> source /data/backup/oldboy_ext.bin.sql
mysql> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | l3   |
|    3 | l3   |
|    4 | l3   |
|    5 | ee   |
|    7 | 77   |
|   10 | t2   |
|   11 | t2   |
|   12 | t2   |
|   13 | u3   |
|   14 | u3   |
+------+------+
11 rows in set (0.00 sec)

mysql>

作业3
分别写备份脚本和策略

作业4：备份集中单独恢复表
思考:在之前的项目案例中,如果误删除的表只有10M,而备份有500G,该如何快速恢复误删除表?
提示：

确认备份集文件：

[root@mysql-node01 inc2]# pwd
/data/backup/full/inc2
[root@mysql-node01 inc2]# ls
db.opt  t1.frm  t1.ibd

查看数据库表内容：

mysql> use inc2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> 

drop table city;

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> 

create table city like city_bak;

mysql> CREATE TABLE `t1` (
    ->   `id` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql>

alter table city discard tablespace;

、分离表空间文件

mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql>

[root@mysql-node01 inc2]# ll /data/mysql/data/inc2/
total 16K
-rw-r----- 1 mysql mysql   67 Oct 16 16:20 db.opt
-rw-r----- 1 mysql mysql 8.4K Oct 17 10:29 t1.frm
[root@mysql-node01 inc2]# systemctl stop mysqld

cp /backup/full/world/city.ibd  /application/mysql/data/world/
chown -R mysql.mysql  /application/mysql/data/world/city.ibd 

[root@mysql-node01 inc2]# pwd
/data/backup/full/inc2

# 拷贝ibd文件到 inc2库目录，修改权限
[root@mysql-node01 inc2]# cp -a ./t1.ibd /data/mysql/data/inc2/
[root@mysql-node01 inc2]# chown -R mysql. /data/mysql/data/inc2/
[root@mysql-node01 inc2]# systemctl start mysqld

alter table city import  tablespace;

mysql> use inc2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed, 1 warning
mysql> 
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> 
mysql> 
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

作业5： 从mysqldump 全备中获取库和表的备份
1、获得表结构

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE \`city\`/!d;q'  full.sql>createtable.sql

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE /!d;q' mdp_oldboy_full.sql > oldboy_create.sql


cat oldboy_create.sql

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

2、获得INSERT INTO 语句，用于数据的恢复

grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

grep -i 'INSERT INTO\ ' mdp_oldboy_full.sql > oldboy.data.sql
cat oldboy.data.sql
INSERT INTO `t2` VALUES (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee');

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql