MySQL之：备份和恢复

为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景

备份注意要点

• 能容忍最多丢失多少数据
• 恢复数据需要在多长时间内完成
• 需要恢复哪些数据

还原要点

• 做还原测试，用于测试备份的可用性
• 还原演练

备份类型

完全备份，部分备份

• 完全备份：整个数据集
• 部分备份：只备份数据子集，如部分库或表

增量备份、差异备份

• 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
• 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘

备份和恢复

冷、温、热备份

• 冷备：读写操作均不可进行
• 温备：读操作可执行；但写操作不可执行
• 热备：读写操作均可执行

MyISAM：温备，不支持热备
InnoDB：都支持

物理和逻辑备份

• 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
• 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份时需要考虑的因素

温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长

备份什么

数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、存储函数、触发器、事件调度器）
服务器的配置文件

设计备份方案

• 数据集：完全+增量
• 备份手段：物理，逻辑

备份工具：

• mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备
• cp，tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
• LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统管理工具进行备份
• mysqlhotcopy：几乎冷备；仅适用于MyISAM存储引擎

备份工具的选择：

• mysqldump+复制binlog：
  mysqldump：完全备份
  复制binlog中指定时间范围的event：增量备份
• LVM快照+复制binlog：
  LVM快照：使用cp或tar等做物理备份；完全备份
  复制binlog中指定时间范围的event：增量备份
• xtrabackup：由Percona提供技术支持对InnoDB做热备（物理备份）的工具，支持完全备份、增量备份
• MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
• mysqlbakcup：热备份，MySQL Enterprise Edition组件

逻辑备份工具：mysqldump，mydumper，phpMyAdmin

Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump工具：客户端命令，通过mysql协议连接至mysqld服务器进行备份
  mysqldump [OPTIONS] database [tables]
  mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
  mysqldump [OPTIONS] -A [OPTIONS]

• mysqldump参考：
  https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump常见选项：

• -A，--all-databases：备份所有数据库，含 create database
• -B，--databases db_name...：指定备份的数据库，包括 create databases 语句
• -E，--events：备份相关的所有event scheduler
• -R，--routines：备份所有存储过程和存储函数
• --triggers：备份表相关触发器，默认启用，用--skip-triggers，不备份触发器
• --master-data[=#]：此选项须启用二进制日志
  1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
  2：记录为注释的 CHANGE MASTER TO 语句
  此选项会自动关闭 --lock-tables 功能，自动打开 --lock-all-tables 功能（除非开启--single-transaction）
• -F，--flush-logs：备份前滚动日志，锁定表完成后，执行flush logs 命令，生产新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用--flush-logs和-x，--master-data或-single-transaction，此时只刷新一次，建议：和-x，--master-data 或 --single-transaction 一起使用
• --compact：去掉注释，适合调试，生产不使用
• -d，--no-data：只备份表结构
• -t，--no-create-info：只备份数据，不备份create table
• -n，--no-create-db：不备份create database，可被 -A 或 -B 覆盖
• --flush-privileges：备份mysql或相关时需要使用
• -f，--force：忽略SQL错误，继续执行
• --hex-blob：使用十六进制符号转储二进制列（例如：“abc”变为0x616263），受影响的数据类型包括BINARY，VARBINARY，BLOG，BIT
• -q，--quick：不缓存查询，直接输出，加快备份速度

MyISAM备份选项：

支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作锁定方法如下：
-x，--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能

注意：数据量大时，可能会导致长时间无法并发访问数据库

-l，--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on，--skip-lock-tables选项可禁用，对备份 MyISAM 的多个库，可能会造成数据不一致

注：以上选项对 InnoDB 表一样生效，实现温备，但不推荐使用

InnoDB备份选项：

支持热备，可用温备但不建议用
--single-transaction
此选项 InnoDB 中推荐使用，不适用 MyISAM，此选项会开始备份前，先执行 START TRANSACTION 指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）；转储不保证与其他存储引擎保持一致。在进行单个事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其它连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将--single-transaction选项和--quick结合一起使用

---

生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -A -F [-E -R] --single-transaction --master-data=1 --flush-privileges [--triggers --hex-blob] > $BACKUP/fullabk_$BACKUP_TIME.sql

MyISAM建议备份策略

mysqldump -uroot -A -F [-E -R] -x --master-data=1 --flush-privileges [--triggers --hex-blob] > $BACKUP/fullabk_$BACKUP_TIME.sql