# Day11 备份与恢复
# 本节关键词
全备 mysqldump截取 mysqlbinlog恢复 source
# 备份中的职责
1. 备份策略
工具
周期
建议：小于200G，每天一次
2. 日常备份巡检
日志
备份大小
3. 定期恢复演练
季度
半年
4. 故障恢复
# 备份工具介绍
逻辑：
mysqldump
mydumper
物理：
PXB
8017+ Clone Plugin
一般在同数据量级
1. 物理备份要比逻辑备份速度快
2. 逻辑备份的优势:
- 可读性强
- 压缩比很高
# 工具-mysqldump
- 客户端通用参数
-u -p -S -h -P
本地备份: mysqldump -uroot -p -S /tmp/mysql.sock
远程备份: mysqldump -uroot -p -h 10.0.0.51 -P3306
- 备份专用参数
-A 全备参数
-B db1 db2 db3 备份多个单库 备份单个或多个表
-R 备份存储过程及函数
--triggers 备份触发器
-E 备份事件
--no-data 只备份表结构
--max-allowed-packet=128M
--master-data=2 以注释的形式,保存备份开始时间点的binlog的状态信息
功能：
1. 在备份时，会自动记录，二进制日志文件名和位置号
2. 自动锁表（FTWRL），但配合--single-transaction效果不同
* 非InnoDB表进行锁表备份
* InnoDB表进行“热“”备，实际上是实现快照备份
--singe-transaction
InnoDB 存储引擎开启热备(快照备份)功能
总结：
master-data可以自动加锁
（1）在不加--single-transaction，启动所有表的温备份，所有表都锁定
（2）加上--single-transaction，对innodb进行快照备份，对非innodb表可以实现自动锁表功能
形象比喻：数班级人数
- 锁上门，数
- 拍张照片，数
- 恢复
mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full.sql;
set sql_log_bin=1;
注意：
1、mysqldump在备份和恢复时都需要mysql实例启动为前提
2、一般数据量级100G以内，大约15-45分钟可以备份成功，但恢复时间通常需要5-10倍时间，数据量级很大很大的时候（PB、EB）
3、mysqldump是覆盖形式恢复的方法
# 备份恢复实践
- 场景描述
1. 库表容量小于200G，每日mysqldump全备
2. binlog开启 GTID
3. 当日下午2:00发生故障，当日数据丢失，需要恢复
- 恢复步骤
1. 根据全备恢复数据到凌晨
2. 根据binlog恢复当日数据
- GTID 开始点 全备文件中获取
- GTID 结束点 show binlog events in 'binlog.000004';
3. 执行 source 恢复
- 模拟步骤
# 新增库表
create database mdb;
create table t1(id int);
insert into t1 values(1),(11),(111);
# 执行备份
mysqldump -uroot -p123 -S /tmp/mysql.sock -A --master-data=2 --single-transaction --max_allowed-packet=128M -R -E --triggers --set-gtid-purged=on > /data/full_1.sql
# 当日数据
insert into t1 values(2),(22),(222);
# 模拟崩溃
drop database mdb;
# 分析 bin log 恢复时 GTID 结束点 dd07cc10-4d07-11ed-9e7c-00163e160f39:18
> vim /data/full_1.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'dd07cc10-4d07-11ed-9e7c-00163e160f39:1-17';
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=3718;
# 分析 bin log 恢复时 GTID 开始点 截取到 'dd07cc10-4d07-11ed-9e7c-00163e160f39:20'
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------+
| binlog.000004 | 4531 | | | dd07cc10-4d07-11ed-9e7c-00163e160f39:1-20 |
+---------------+----------+--------------+------------------+-------------------------------------------+
mysql> show binlog events in 'binlog.000004';
| binlog.000004 | 4229 | Xid | 51 | 4260 | COMMIT /* xid=1148 */ |
| binlog.000004 | 4260 | Gtid | 51 | 4339 | SET @@SESSION.GTID_NEXT= 'dd07cc10-4d07-11ed-9e7c-00163e160f39:20' |
| binlog.000004 | 4339 | Query | 51 | 4413 | BEGIN |
| binlog.000004 | 4413 | Table_map | 51 | 4460 | table_id: 286 (mdb.t1) |
| binlog.000004 | 4460 | Write_rows | 51 | 4500 | table_id: 286 flags: STMT_END_F |
| binlog.000004 | 4500 | Xid | 51 | 4531 | COMMIT /* xid=1149 */ |
| binlog.000004 | 4531 | Gtid | 51 | 4608 | SET @@SESSION.GTID_NEXT= 'dd07cc10-4d07-11ed-9e7c-00163e160f39:21' |
| binlog.000004 | 4608 | Query | 51 | 4709 | drop database mdb /* xid=1152 */ |
+---------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
# 截取 bin log 日志
> cd /data/3306/log
mysqlbinlog --skip-gtids --include-gtids='dd07cc10-4d07-11ed-9e7c-00163e160f39:18-20' binlog.000004 > /data/bin.sql
# 执行恢复，恢复时间约为备份3-5倍
set sql_log_bin=0;
/data/full_1.sql;
source /data/bin.sql;
set sql_log_bin=1;