备份恢复

备份注意事项

基本原则

1. 备份文件不要放在同一个主机上, 异地备份
2. 备份文件需要校验hash并记录
3. 需要偶尔去验证一下备份恢复流程是否可行
4. 性能敏感性，避免在主库上备份, 去二级数据库上进行备份
5. 注意binlog文件的连续性
6. 对重点表进行单独表级别的备份
7. lvm或存储层的快照备份不可靠
8. 重要业务可以考虑一个延迟同步实例

恢复场景

1. 全量恢复
2. 单库恢复
3. 单表或多表
4. 单表内的部分行
5. 某1条数据

恢复方式

1. 使用物理备份文件全覆盖
2. 从逻辑备份文件恢复到指定库
3. 从备份文件 -> 新的恢复专用空库 -> 提取数据 -> 恢复到生产库
4. 从 binlog 中提取 sql –> 编辑sql –> 恢复(insert/update)

恢复校验: 基础校验(大小, 行数) + 业务校验(金额, 时间, 状态等业务指标)

其它

备份时附加记录的元数据:
备份时间, 文件大小, 文件hash, 行数, 表数量, 备份使用的完整命令等

热备份: 读写不受影响 温备份: 类似于只读从库, 实时同步但仅可以执行读操作 冷备份: 离线备份

备份组合: 每周全量 + 每日增量 + 实时备份(同步binlog)

常用工具简介

mysqldump
优点: 自带工具, 一般够用 缺点: 因为是逻辑备份, 所以备份和恢复耗时都比较长

xtrabackup
物理备份, 备份和恢复速度快, 可以不影响数据库服务情况下进行热拷贝

mysqlpump
5.7+ 新增的官方备份工具，是 mysqldump 的一个衍生； 支持基于库和表的并行导出

mydumper
早期的并行dump工具

逻辑备份和物理备份

逻辑备份的特点是: 恢复时内容是需要被执行的, 本质是插入sql

优点: 跨版本迁移方便, 适合升级前备份, 支持编辑后恢复

物理备份的特点是: 拷贝的底层数据文件, 恢复时可以直接启动, 不需要再执行一遍sql;(增量的部分是重放redo)

优点: 速度快，缺点: 空间占用大, 不能跨版本和平台进行迁移。

增量备份的方式

非侵入型

1. 刷新binlog后，备份增量的binlog文件
2. xtrabackup 的增量备份

侵入型

1. 纯插入场景, 记录下每次的 id 偏移量, 然后备份新增部分
2. 表内如果有 update_time 字段, 备份时按时间筛选
3. 准备一个备份状态表, 管理备份的阶段信息
4. 触发器模式
5. etl 模式

恢复

# 直接加载文件
mysql>source url\file.sql

# 非交互式恢复
mysql -uroot -p123 -h 192.x.x.x < /backup/alldb.sql

在线恢复

禁止普通用户写, 只允许超级用户写(便于导入数据)

set read_only=on;
set super_read_only=off;

加速恢复速度；

1. 可以先关闭普通索引

alter table t_name disable keys     -- 关闭普通索引
load data infile file.txt
alter table t_name enable keys      -- 开启普通索引

2. 如果确定导入值不会冲突, 可以先关闭唯一索引检查; 仅适用于InnoDB, 必须在事务外使用;

set unique_checks=0     -- 关闭
set unique_checks=1     -- 开启

3. 针对 innodb 表, 如果导入数据是按主键顺序排列，可以提高导入效率
4. 关闭自动提交，在导入完成后，再开启也会加快导入速度
5. 关闭 binlog
6. innodb_flush_log_at_trx_commit=0

基于 binlog 的恢复

导出mysql-bin.00000012二进制文件进行还原及时点

# 一般不这样搞, 除非场景特别简单
mysqlbinlog mysql-bin.00000012 > tmp.sql
mysql -uroot -p < tmp.sql