10：数据库备份恢复-1（主要是mysqldump和Percona Xtrabackup）

备份恢复
1. 作用?
处理数据损坏.
数据损坏:
物理损坏 : 磁盘,核心数据文件被删除或损坏.
逻辑损坏 : drop , delete ,truncate ,update

数据损坏方法:
物理: 主从复制 , 高可用
逻辑: 备份,binlog,binlog2sql,延时从库

2. 我们的职责
a. 设计能备份和恢复的策略
b. 备份要定期检查
c. 定期的恢复演练
d. 数据损坏的恢复(效率,一致性)
f. 迁移,升级,主从.

3. 备份工具

a. 逻辑备份
mysqldump (MDP)
mysqlbinlog
mydumper

b. 物理备份
Percona Xtrabackup (PXB,XBK,Xbackup)
MySQL Enterprise Backup (MEB)

c. 8.0 克隆(8017+版本)
Clone Plugin

4. mysqldump(MDP)应用

4.1 介绍
4.1.1 分类
逻辑备份工具.
备份的结果: SQL语句,Create database, create table , insert into 语句
4.1.2 备份逻辑 :
RR 级别
获取一致性快照
A. 元数据备份
FTWRL ---->
Flush tables with read lock; —> MDL

Flush tables ---> close tables,阻止所有commit
read lock    ---> all  read lock 

show create database ..  --->  建库语句
show create  table   ..  --->  建表语句
unlock tables; 

B. 备份数据行
select —>
InnoDB 可以进行快照备份
拼接成insert
非InnoDB表,锁表备份的.

4.1.3 适合的场景
单节点数据量,100G以内,单表2000w以内.可以使用MDP
恢复时间一般是备份时间的3-5倍.
4.2 参数应用

4.2.1 连接参数
-u
-p
-h
-P
-S

4.2.2 基础备份参数

-A : 全库备份
[root@db01 ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -A >/tmp/full.sql

-B : 单库或多库备份
[root@db01 ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock -B oldboy test >/tmp/db.sql

备份单表或多表
[root@db01 ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock test t100w >/tmp/t100.sql
[root@db01 ~]# mysqldump -uroot -p123 -S /tmp/mysql.sock test t100w t1 >/tmp/t100.sql

说明: 以上方法,在恢复时先建立数据库,use到库中,再source
4.2.3 特殊备份参数
a. --master-data=2
–master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
–lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don’t
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.

解:
1. 自动记录binlog信息
2. 自动进行GRL锁(FTWRL),加了 --single-transaction 会有不一样的效果.

mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 >/tmp/full.sql

b. --single-transaction

–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.

解:
1. 对于InnoDB表进行一致性快照备份
2. 备份期间如果有DDL会备份失败.

c. --max_allowed_packet=128M

mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 --single-transaction --max_allowed_packet=128M >/tmp/full.sql

d. -R -E --triggers

备份时,同时将 存储过程\函数\事件\触发器 等高级对象备份走.

mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 --single-transaction --max_allowed_packet=128M -R -E --triggers >/tmp/full.sql

4.2.4 标准化备份
[root@db01 tmp]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M |gzip -9 >/tmp/full_date +%F.sql.gz

5. Percona Xrabackup 应用
5.1 介绍
物理备份工具. 备份恢复更快.
支持全备和增量备份

5.2 全备备份逻辑
a. 8.0 之前
预备: 初始化进程和线程,分配专用内存.
备份:
0. 连接目标数据库,获取数据库信息.
a. 当前LSN号码记录 —> show engine InnoDB stutus ; 主要: Last checkpoint at NO.
b. non-InnoDB 数据 :frm , csv , MYI ,MYI ----> FTWRL
c. unlock tables;
d. Copy Innodb 数据: ibd ibdata1 undo tmp ,可以允许DML
e. 备份期间的redo截取和备份,并且记录LSN号.
结束:
记录binlog的当前位置点,将所有备份信息记录至专用日志文件中.

b. 8.0 之前
预备: 初始化进程和线程,分配专用内存.
备份:
0. 连接目标数据库,获取数据库信息.
a. 当前LSN号码记录 —> show engine InnoDB stutus ; 主要: Last checkpoint at NO.
b. LOCK INSTANCE FOR BACKUP; UNLOCK INSTANCE;
c. Copy Innodb 数据: ibd ibdata1 undo tmp ,可以允许DML
d. 备份期间的redo截取和备份,并且记录LSN号.
结束:
记录binlog的当前位置点,将所有备份信息记录至专用日志文件中.

5.3 增量备份逻辑
a. 第一次增量备份,使用全备作为"参照物",把变化的数据页和备份过程中产生的redo保存.
b. 往后所有的增量,都基于上一次备份作为参照物.把变化的数据页和备份过程中产生的redo保存.
c. 增量备份只针对InnoDB表有效,所以在8.0之前,针对非InnoDB表,都是全备.

5.4 恢复过程

例如:备份策略为,FULL+inc1+inc2…
a. prepare 全备 (CR)
应用redo前滚
应用undo回滚(省略)
b. 合并所有增量到全备并且prepare
应用redo前滚
应用undo回滚(除了最后一次增量,这步省略)
c. 合并后的全备prepare
d. 恢复备份

5.5 PXB的版本兼容性
MySQL 5.6 ,5.7 : PXB 2.4版本
MySQL 8.0.11 ~ 8.0.19 : PXB 8.0 稳定版.
MySQL 8.0.20 : PXB 8.0.12+
5.6 全量备份
5.6.0 安装

5.6.1 全量备份
mkdir -p /data/backup
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123 --backup --target-dir=/data/backup/full

5.6.2 数据恢复：

a 搞破坏

[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# rm -rf /data/3306/logs/*
[root@db01 ~]# rm -rf /data/3306/binlog/*

b 准备：(CR)
xtrabackup --prepare --target-dir=/data/backup/full

说明： 模拟CR过程，将redo前滚，undo回滚，让备份数据是一致状态

c 拷回数据：
xtrabackup --copy-back --target-dir=/data/backup/full

d 修改权限并启动数据库
[root@db01 data]# chown -R mysql.mysql /data/*
[root@db01 data]# /etc/init.d/mysqld start

知识铺垫: 迁移表空间.
需求: 将3306(源端)的test.t100w表迁移到3307(目标端)的test

1. 目标数据库3307中创建一个源端结构一致空表(t100w)
mysql> create database test;
mysql> use test
mysql> CREATE TABLE `t100w` (   `id` int DEFAULT NULL,   `num` int DEFAULT NULL,   `k1` char(2) DEFAULT NULL,   `k2` char(4) DEFAULT NULL,   `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   KEY `idx` (`k1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2. 目标端删除表空间,保留表结构,删除表空间文件
mysql> alter table t100w discard tablespace;

3. 源端锁定需要迁移的表
mysql> flush table t100w with read lock;

4. 拷贝ibd文件到目标端目录下
[root@db01 test]# cp -a /data/3306/data/test/t100w.ibd  /data/3307/data/test/
[root@db01 test]# pwd
/data/3307/data/test
[root@db01 test]# ll
total 94208
-rw-r----- 1 mysql mysql 96468992 Dec 28 17:38 t100w.ibd
5. 目标端导入表空间文件
mysql> alter table t100w import  tablespace;

6. 释放源端表锁
mysql> unlock tables;

作业: 

1. 场景1:  80G,mysqldump全备+binlog备份,误删除了10M app表,进行模拟和恢复

思路:  
	1. 检查备份 全备 + binlog 
	2. 从全备中抽取app 表的备份 
	CREATE TABLE `app` (
     `id` int DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
	[root@db01 ~]# grep -i 'insert into `app` ' /tmp/full.sql
	INSERT INTO `app` VALUES (1),(2);
	[root@db01 ~]# grep -i 'insert into `app` ' /tmp/full.sql >/tmp/insert.sql


	3. 从binlog中抽取app表的日志
	[root@db01 binlog2sql-master]# 
	[root@db01 binlog2sql-master]# mysqlbinlog --base64-output=decode-rows -vvv \--start-position=843 /data/3306/binlog/mysql-bin.000012 |grep '^###'>/tmp/bin.sql

	### INSERT INTO `mdp`.`app`
	### SET
	###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
	### INSERT INTO `mdp`.`app`
	### SET
	###   @1=20 /* INT meta=0 nullable=1 is_null=0 */

	4. 恢复 
2. 场景2:  500G数据库,PXB 周日全备+周一-周五binlog,周三时候完全损坏,进行模拟和恢复 

[root@db01 full]# cat xtrabackup_binlog_info 
mysql-bin.000013	236	591cc55e-4610-11eb-95db-000c2905f029:1-5,95972e36-43fe-11eb-a366-000c2905f029:1-70


3. 扩展题:  500G数据库,PXB 周日全备+周一-周六binlog,周三时候误删除50M的小表,怎么快速恢复? 

扩展1: 8.0之前,如何通过frm文件获取建表语句

[root@db01 ~]# cd /opt
[root@db01 ~]# wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz
[root@db01 ~]# tar xvf mysql-utilities-1.6.5.tar.gz
[root@db01 ~]# cd mysql-utilities-1.6.5
[root@db01 ~]# python setup.py build
[root@db01 ~]# python setup.py install
[root@db01 ~]# mysqlfrm --diagnostic /data/3356/data/world/city.frm 
[root@db01 test]# mysqlfrm --diagnostic /data/3356/data/world/city.frm |grep -Ev '^#|^$'  >/tmp/create.sql

扩展2 : 8.0之后,只有ibd文件怎么获取建表语句? 
[root@db01 test]# ibd2sdi /data/3306/data/test/t100w.ibd 

5.7 增量备份

5.7.1 介绍

增量备份，是基于上一次备份LSN变化过的数据页进行备份，在备份同时产生的新变更，会将redo备份。
第一次增量是依赖于全备的。将来的恢复也要合并到全备中，再进行统一恢复。

5.7.2 增量备份演练

全量备份的目录为： mkdir  -p /data/backup/full
增量备份的目录为： mkdir  -p /data/backup/inc

# 1.备份操作：
# 1.1.全量备份：
xtrabackup --defaults-file=/etc/my.cnf  --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/full

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


# 1.2.增量备份：
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/inc  --incremental-basedir=/data/backup/full



# 模拟损坏  
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*



# 2.恢复操作：
# 2.1 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full


# 2.2 准备增量备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full  --incremental-dir=/data/backup/inc


# 2.3 全备份准备：
# xtrabackup --prepare --target-dir=/data/backup/full


# 2.4 拷回数据：
xtrabackup    --copy-back --target-dir=/data/backup/full

#2.5 修改数据目录的权限和属性：
chown -R mysql:mysql /data/*