day08-02-备份恢复之逻辑备份
备份恢复
-
在备份恢复中的职责
1.1 备份策略的设计备份方式： 全备:全库备份，备份所有数据 增量:备份变化的数据 逻辑备份=mysqldump+mysqlbinlog 物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog 备份周期: 根据数据量设计备份周期 比如：周日全备，周1-周6增量(1) 备份周期:
根据数据量.
60G:
9T： 每周做全备
(2)备份工具:
mysqldump (MDP) , XBK (PBK) percona Xtrabackup , MEB(MySQL Enterprise BACKUP MEB) ,mysqlbinlog
(3)备份方式:
逻辑:
全备 mysqldump
增量 binlog (flush logs ,cp)
物理备份:
全备 : XBK
增量 : XBK
1.2 检查备份可用性
crontab -l ---->先看有没有备份策略
备份脚本 ---->
备份路径 ---->
查看备份日志 ---->
检查备份文件(大小，内容) ---->
1.3 定期的恢复演练
1.4 数据恢复
只要备份和日志是完整的,恢复到故障之前的时间点(快速)
1.5 数据迁移 ***
操作系统不同的迁移
mysql -> mysql
其他 -> mysql
mysql -> 其他
- 备份的介绍
2.1 备份的策略
2.2 备份的工具
2.3 备份类型
热备 : 对于业务影响最小 InnoDB
温备 : 长时间锁表备份 MyISAM
冷备 : 业务关闭情况下备份
-
mysqldump
优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间
缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB =1024 PB =1000000 T
3.1 连接数据库
-u
-p
-S
-h
P
3.2 基础备份参数
-A (all database)
mysqldump -uroot -p123 -A > /data/backup/db_name-full.sql
mysqldump -uroot -p123 -A >/backup/full.sql
# 把握一个原则，如果对库做备份，就加-B参数。
# 对表做备份，就不要加-B参数，写表名
-B db1 db2 db3
说明：生产中需要备份，生产相关的库和MySQL库
例子2 :
mysqldump -B mysql gtid --set-gtid-purged=OFF >/data/backup/b.sql
库 表
mysqldump -uroot -p123 -B simon_test world > /data/backup/db_name-full.sql
例子3 world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
以上备份恢复时:必须库事先存在,并且ues才能source恢复
第一个是库名，后面只能是表名
mysqldump -uroot -p123 world tb_name1 tb_name2 tb_name3 > /data/backup/db_name-tbname.sql
只写库名，不加-B参数，会把这个库下所有表都逐个备份
mysqldump -uroot -p123 world > /data/backup/db_name-tb_name-all.sql
3.3 特殊备份参数
-R 备份存储过程和函数
-E 备份event事件
--triggers 触发器
-F, --flush-logs 刷新日志
--master-data=2 ******
每天全备 mysqldump 60G
周三，下午2点，数据库损坏
恢复思路？
1.恢复周二的全备。
2.截取全备恢复到损坏之前的binlog
起点=》怎么找？ 要找开始备份那个点
终点=》 故障点之前的position
3.恢复binlog
--master-data=2
两个作用：
1）备份语句添加 --master-data=2 ，开启备份，会立即记录开始备份的起点position。也就是说这个参数是用作截取二进制日志备份起点位置用的。
2）自动锁表
`--master-data=2` 在不添加 `--single-transaction` 的情况下，就会全局锁表进行温备，可能会阻塞其他正常修改更新业务 。
添加 `--sigle-transaction` ，对于InnoDB表，是不锁表备份（快照备份）。非InnoDB的表，还是会锁表进行温备。不锁表只针对有事务功能的存储引擎。
--single-transaction ******
一句话总结：对于InnoDB的表，通过事务进行一致性快照备份，不锁表。又称为热备。
只适用于存储在支持多版本化的存储引擎中的表(目前只适用于InnoDB)
我们公司备份时会加两个核心参数
--master-data=2 和 --single-transaction
所谓“热备”功能，就是--single-transaction实现的。核心理念就是，备份的时候，创建一个单独的事务，然后保存当前备份时候的一致性快照，实际备份的是快照中的数据，所以不会阻塞其他事务的变更。俗称的“热备”，实际是基于MVCC机制通过快照技术实现的，目前只有Innodb具备这个功能。
(1) 记录备份时刻的binlog信息
(2) 自动锁表
不加--single-transaction ,温备份
加了--single-transaction,对于InnoDB表不锁表备份(快照备份)
--single-transaction *****
对于InnoDB的表,进行一致性快照备份,不锁表.
--set-gtid-purged=auto
auto , on
off
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql
--max-allowed-packet=#
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql
--max-allowed-packet=#
The maximum packet length to send to or receive from server.
压缩备份并添加时间戳
例子：
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz
mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）
set sql_log_bin=0;
source /backup/full_2018-06-28.sql
注意：
1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内，大约15-45分钟可以恢复，数据量级很大很大的时候（PB、EB）
3、mysqldump是覆盖形式恢复的方法。
一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.
逻辑备份的优势:
1、可读性强
2、压缩比很高
InnoDB 引擎和 MyisAM引擎 分开备份
1）Myisam引擎：
mysqldump -uroot -p123456 -A -B --master-data=1 -x| gzip > /data/all_$(date +%F).sql.gz
（2）InnoDB引擎：
mysqldump -uroot -p123456 -A -B --master-data=1 --single-transaction > /data/bak.sql
（3）生产环境DBA给出的命令
a、for MyISAM
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql
b、for InnoDB
mysqldump --user=root --all-databases --flush-privileges --single-transaction \
--master-data=2 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql
恢复案例
4.1 背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量1-5M。
4.2 备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
4.3 故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
4.4 思路：
1、停业务，挂维护页,避免数据的二次伤害
2、找一个临时库，恢复周二23：00全备
3、截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常
4.5 故障模拟演练
4.5.1 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;
rm -rf /backup/*
4.5.2 周二 23：00全备
mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
4.5.3 模拟周二 23：00到周三 10点之间数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;
4.5.4 模拟故障,删除表(只是模拟，不代表生产操作)
drop database backup;
4.6 恢复过程
4.6.1 准备临时数据库（多实例3307）
systemctl start mysqld3307
4.6.2 准备备份
（1）准备全备：
cd /backup
gunzip full_2018-10-14.sql.gz
（2）截取二进制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=753;
753
1519
mysqlbinlog --skip-gtids --start-position=753 --stop-position=1519 /data/binlog/mysql-bin.000002 >/backup/bin.sql
4.6.3 恢复备份到临时库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2019-07-15.sql
source /backup/bin.sql
4.6.4 将故障表导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sql
mysql -uroot -p123
set sql_log_bin=0
source /backup/bak.sql;
- 练习：
1、创建一个数据库 oldboy
2、在oldboy下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据
- 扩展参数 ***
在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON
仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF
SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';
1-11 表示，该备份集已经包含 1-11事务
--max_allowed_packet=128M 控制的是备份时传输数据包的大小.
mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
生产环境下完整备份语句
mysqldump -u user_name -p'xxxxxx' \
-S /data/3306/mysql.sock \
--single-transaction \
--master-data=2 \
--set-gtid-purged=OFF \
--hex-blob \
--opt
--routines \
--triggers \
--all-databases > /backup_dir/mysqldump-20190326.sql
