为什么要备份
灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
备份注意要点
- 能容忍最多丢失多少数据
- 恢复数据需要在多长时间内完成
- 需要恢复哪些数据
还原要点
- 做还原测试，用于测试备份的可用性
- 还原演练
备份类型
完全备份，部分备份
- 完全备份：整个数据集
- 部分备份：只备份数据子集，如部分库或表
增量备份、差异备份
- 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
- 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
注意：二进制日志文件不应该与数据文件放在同一磁盘
备份和恢复
冷、温、热备份
- 冷备：读写操作均不可进行
- 温备：读操作可执行；但写操作不可执行
- 热备：读写操作均可执行
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常见选项：
-
-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
网友评论