MySQL全面瓦解30：备份与恢复
1 为什么需要数据库备份
- 灾难恢复：当发生数据灾难的时候，需要对损坏的数据进行恢复和还原
- 需求的变更或者回滚：当需求发生变更，或者需要回滚到之前的版本时，数据库备份也显得很重要。
- 审计：需要知道某一个阶段的数据或者Schema的实际情况
- 测试：将实际的生产环境的数据导入到本地备份为测试数据，来验证新功能，可以省去很多麻烦。
2 备份需要考虑的几个关键点
- 恢复点目标（PRO）：可以容忍丢失多少数据
- 恢复时间目标（RTO）：需要等待多久将数据恢复
- 恢复的时候是需要持续提供服务 还是 停机恢复。
- 需要恢复的内容：整个服务器，多库多表，单库单表，或是特定的事务或语句。
3 备份方案
3.1 离线备份和在线备份
离线备份：就是传统意义上的cold backup（冷备份）：需要关闭MySQL服务，读写请求均不允许状态下进行，这种模式下数据损坏和不一致性风险最小。
半离线备份：也就是我们说的warm backup（温备份）： MySQL服务不关闭，但只开放了Read操作，关闭了Write操作。
在线备份：也就是hot backup（热备份）：在数据备份的同时，MySQL业务持续进行中，仅限于InnoDB引擎。
3.2 逻辑备份和物理备份
3.2.1 逻辑备份：导出数据库表的定义和数据
逻辑备份有如下优点：
- 恢复非常简单
- 可以通过网络来备份和恢复
- 备份的结果为ASCII文件，可以编辑
- 与存储引擎无关
- 非常灵活，可以使用mysqldump的工具提供很多可选项。
逻辑备份的缺点：
- 必须由数据库服务器来完成备份和恢复过程
- 备份结果占据更多的空间：逻辑备份在某些场景下比数据库文件本身还要大
- 精度问题，无法保证还原出来的数据强一致
- 还原时间长：还原之后，加载注释语句，转换存储格式，重建索引都需要消耗一定时间
3.2.2 物理备份：直接复制原数据文件
物理备份的优点：
- 备份和恢复操作都比较简单，且能够跨平台，操作系统和MySQL版本。
- 恢复速度快，都是基于文件的，复制到对应的目的地即可，InnoDB需要停止数据库服务，有额外的动作。
- 步骤更少：不需要执行重新生成数据和重建索引的动作，效率提升。
物理备份的缺点：
- InnoDB备份的原始文件往往比逻辑备份的大很多，空间要求大。
3.3 根据要备份的数据集合的范围
- 完全备份：full backup，备份整个数据库信息。
- 增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，需与完全备份配合使用。一般来说增量频率高，备份频率也高。
- 差异备份：differential backup 上次完全备份以来改变了的数据。
- 建议的恢复策略：
- 完全+增量+二进制日志
- 完全+差异+二进制日志
4 备份的内容主要有哪些？
- 数据：基础数据。
- 日志：包含 二进制日志 和 InnoDB事务日志 等。
- 配置信息：包括服务器配置 和 复制相关的配置（主从复制中的中继日志和日志索引文件等）。
- 代码：存储过程、函数、触发器、视图等
- 选定的OS文件：入UNIX服务器上的 cron任务、用户和组的配置、管理的脚本、sudo规则等。
5 数据备份和数据恢复方案介绍
5.1 输出outfile文件
使用 select into outfile 方式实现数据的备份和还原
具体的操作步骤如下：
# 选择对应的数据库
mysql> use attend;
Database changed
# 查询需要备份的数据
mysql> select * from userinfo where id < 10000;
+----+----------+------------------+---------+
| id | usercode | username | usersex |
+----+----------+------------------+---------+
| 1 | 374532 | 翁智华_attend | 1 |
| 2 | 123456 | 小度 | 0 |
+----+----------+------------------+---------+
2 rows in set (0.01 sec)
# 选择备份的数据（可以精确条件），应该有两条数据，注意备份的地址具备write权限
mysql> select * from userinfo where id < 10000 into outfile '/Users/Brand/Downloads/tmp/userinfo.txt' ;
# 检查文件是否存在
brand@MacBook-Pro ~ % cd /Users/Brand/Downloads/tmp/
# 因为它是文本模式，所以我们使用 load data infile 恢复，并且在恢复之前先删除掉要恢复的数据，做个测试
mysql> delete from userinfo where id < 10000;
mysql> load data infile '/Users/Brand/Downloads/tmp/userinfo.txt' into table userinfo;
5.2 使用工具进行备份与还原
可以使用类似 mysqldump工具 或者 mysqlhotcopy工具对数据进行备份和还原，也可以使用免费的热备份软件 Percona XtraBackup。
这边以 mysqldump 为例子演示温备的实现：
5.2.1 备份基本语法
mysqldump -h主机 -P端口 -u用户名 -p密码 param1, param2, param3... > bak_filename.sql
这边对各个字段坐下说明：
- h：登录用户所在的主机名称
- P：主机端口
- u：登录用户用户名
- p：用户密码
- param：导出参数（库、表、加锁等参数）
- ">"：将备份数据表的定义和数据写入备份文件的定义
- bak_filename.sql：备份的文件名
5.2.2 导出全部数据库
–all-databases 或者 -A
mysqldump -uroot -p123456 --all-databases > /user/brand/db_bak/all.sql
mysqldump -uroot -p123456 -A > /user/brand/db_bak/all.sql
5.2.3 导出部分数据表
-databases [dbname,[dbname...]] --tables [tbname,[tbname...]] ,如果多个表where条件相同，也可以组合在一起使用：
mysqldump -uroot -p123456 --databases db1 --tables tb1 --where="id>1000" > /user/brand/db_bak/db1_tb1.sql
5.2.4 创建之前先删库或表
–add-drop-database 、 –add-drop-table
- 在create database 前先 drop database；在create table之前先 drop table
- 默认关闭，所以一般在导入时需要保证数据库已存在。。
mysqldump -uroot -p123456 -A --add-drop-database --skip-add-drop-table > /user/brand/db_bak/all.sql
5.2.5 锁表
–add-locks：备份数据库表时锁定数据库表，默认就是打开的状态，可以使用–skip-add-locks取消
# 不佳参数选项的时候，默认是添加LOCK的
mysqldump -uroot -p123456 -A > /user/brand/db_bak/all.sql
# 取消LOCK的状态
mysqldump -uroot -p123456 -A --skip-add-locks > /user/brand/db_bak/all_skip_lock.sql
5.2.6 进行压缩
–compact：压缩模式，去掉注释、头尾等结构信息，让输出更少
mysqldump -uroot -p123456 -A --compact > /user/brand/db_bak/all_compact.sql
5.2.7 数据恢复
使用mysql命令进行恢复，语法如下
mysql -u user -p pwd [dbname] < bak_filename.sql
注意箭头方向
# 删除数据库，模拟数据库损坏
mysql> drop database db1;
# 导入完全备份的文件
mysql < /user/brand/db_bak/all_compact.sql
6 总结
备份和恢复主要使用在以下几个方面：
- 灾难恢复
- 需求的变更或者版本回滚
- 数据和变更审计
- 多版本测试
