删库不跑路-详解MySQL备份策略

手抖、写错条件、写错表名、错连生产库造成的误删库表和数据总有听说，那么删库之后除了跑路，还能做什么呢，当然是想办法恢复，恢复数据的基础就在于完善的备份策略。

备份和恢复是同一个话题，篇幅有限，就分开两章写

• MySQL备份策略
• MySQL数据恢复

1 关于备份

1.1 为什么要备份

• 灾难恢复，数据库在运行过程中，终会遇到各种各样的问题: 硬件故障、Bug 导致数据损坏、由于服务器宕机或者其他原因造成的数据库不可用。除此以外还有人为操作：DELETE 语句忘加条件、ALTER TABLE 执行错表、DROP TABLE 执行错表、黑客攻击，即使这些问题你都还没遇到，但是根据墨菲定律，总会有遇上的时候。
• 回滚，由于某种Bug或系统被黑造成大量的损失，这个时候就需要回滚到某个状态。常见的有区块链交易所被黑然后回滚，游戏漏洞被利用然后整体回滚。
• 审计，有时候有这样的需求：需要知道某一个时间点的数据是怎么样的，可能是年末审计，也可能是因为官司。
• 测试，一个基本的测试需求是，定时拉取线上数据到测试环境，如果有备份，就可以非常方便地拉取数据。

1.2 有哪些备份方式

1.2.1 逻辑备份

逻辑备份是最常见的方式，在数据量比较少的时候很常用。

逻辑备份的优势：

• 备份恢复比较简单，例如 mysqldump 就是 MySQL 自带的备份工作，无需额外安装。恢复的时候可以直接使用 mysql 命令进行恢复。
• 可以远程备份和恢复，也就是说，可以在其他机器执行备份命令。
• 备份出来的数据非常直观，备份出来后，可以使用 sed grep 等工具进行数据提取或者修改。
• 与存储引擎无关，因为备份文件是直接从 MySQL 里面提取出来的数据，所以在直观上，备份数据数据不对引擎做区分，可以很方便地从 MyISAM 引擎改到 InnoDB 引擎。
• 避免受到文件损坏的影响，如果直接复制原始文件，可能会受到某个文件损坏的影响而得到一个损坏的备份。使用逻辑备份，只要 MySQL 还能执行 SELECT 语句，就可以得到一份可以信赖的逻辑备份，在文件损坏的时候很有用。

逻辑备份缺点：

• 因为必须使用 MySQL 服务进行数据操作，所以备份的时候会占用更多 CPU，且备份时间可能会更长。
• 逻辑备份在某些场景下比数据库文件更大，文本存储的数据不总是比存储引擎更高效。当然，使用压缩的话会得到一个更小的备份，但是要占用 CPU 资源。（如果索引较多，逻辑备份会比物理备份小。）
• 恢复时间更长，使用逻辑备份的数据恢复，需要占用更多资源去进行锁分配、索引构建、冲突检查、日志刷新。

逻辑备份常用方法:

• mysqldump 是 MySQL自带的备份工具，通用性强，非常常见。使用的使用通常要加上一些参数，后面继续介绍。
• select into outfile，以符号分割数据创建逻辑备份，对于要导入到 CSV 等表格会比较实用。
• mydumper，允许使用多线程进行备份，备份文件会进行表结构和数据分离，在恢复某些表或数据的时候会非常有效。

1.2.2 物理备份

物理备份在数据量较大的时候非常常见。

物理备份的优势：

• 备份速度快，因为物理备份是基于复制进行备份，意味者复制有多快，备份就能有多快。
• 恢复速度快，只需要把文件复制到数据库目录就可以完成恢复，不需要检查锁、构建索引。
• 恢复简单，对于 MySIAM 引擎的表，不需要停库，只需要简单地复制进数据目录就可以。对于 InnoDB，如果是每个表一个表空间，也可以不停库操作，使用卸载加载表空间的方式便可导入（不太安全）。

物理备份缺点：

• 没有官方物理热备份工具的支持。没有官方工具的支持，意味着出问题的概率较大，使用的时候就要谨慎了
• InnoDB 的原始文件通常比逻辑备份要大。InnoDB 表空间往往包含很多未被使用的空间，InnoDB 表在删除数据后不会释放空间，所以即使数据量不大，文件有可能很大。除此以外，文件中除了数据还包含了索引、日志等信息。
• 物理备份不总可以跨平台跨版本。MySQL 文件和操作系统、MySQL 版本息息相关，如果环境与原来不一致，很有可能会出现问题。

物理备份常用方法:

• xtrabackup 是最常用的物理备份工具，由 percona 开源，能够实现对 InnoDB 存储引擎和 XtraDB 存储引擎非阻塞地备份（对于 MyISAM 还是要加锁），得到一份一致性备份。
• 直接复制文件/文件系统快照，这种方式对于 MySIAM 引擎是非常高效的，只需要执行 FLUSH TABLE WITH READ LOCK 就可以复制得到一份备份文件。但是对于 InnoDB 引擎就比较困难，因为 InnoDB 引擎使用了大量的异步技术，即使执行了 FLUSH TABLE WITH READ LOCK，它还是会继续合并日志、缓存数据。所以要用这种方法备份 InnoDB，需要确保 checkpoint 已经最新。

1.2 为什么要备份 binlog

如果有 DBA 告诉你，这个数据库能够恢复到两个个月内任何状态，这说明了，这个数据库的 binlog 日志至少保留了两个月。备份 binlog 的好处:

• 可以实现基于任意时间点的恢复
• 可以用于误操作数据闪回
• 可以用于审计

当你要进行数据恢复的时候，就会非常庆幸有做 binlog 备份。当然，使用 binlog 恢复数据的前提是 binlog 格式要设为 row，不要担心空间问题，当前最不缺的资源就是硬盘空间。对于 binlog，我们推荐的配置是

# 记录每一行数据的变化
binlog_format = row
# 备库在重做数据的时候，记录一条 binlog
log_slave_updates = 1

1.3 复制和备份

主从复制等于多了一个数据副本，但是复制并不等于备份，也不能代替备份。假设在主库执行了 drop table 操作，会立刻同步到备库，并执行相同的操作，没有办法在出现意外的时候使用备库进行数据恢复。

延迟复制也不能代替备份，但是能加快恢复的速度，是一种非常有用的策略。

在实际使用中，为了不影响主库的使用，我们往往会在备库进行备份，同时记录同步点，以方便进行新备库搭建。在备库备份需要注意的是，主从复制并不能保证主备间数据是一致的。实际上，基于复制的 MySQL 集群并不能保证集群内部一致性，当前也没有非常好的办法，常用的是使用 pt-table-checksum 进行一致性检查。

2. 全量备份

全量备份介绍最常用的逻辑备份工具 mysqldump 和物理备份工具 xtrabackup。如果对 mysqldump 不太满意 可以使用 mydumper 来替代 mysqldump。

2.1 mysqldump

mysqldump 是用得最多的工具，但是要用好的话，需要增加一些额外的参数。mysqldump 有很多可用参数，这里不展开，建议直接访问官网 mysqldump。使用 mysqldump 某些参数需要 select,reload,lock tables 权限。

2.1.1 常见例子

2.1.1.1 InnoDB 全库备份

mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -h<host> -u<user> -p<password> -A > backup.sql

• --opt 如果有这个参数表示同时激活了mysqldump命令的quick，add-drop-table，add-locks，extended-insert，lock-tables参数，它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。当备份大表时，这个参数可以防止占用过多内存
• --single-transaction 设置事务的隔离级别为可重复读，然后备份的时候开启事务，这样能保证在一个事务中所有相同的查询读取到同样的数据。注意，这个参数只对支持事务的引擎有效，如果有 MyISAM 的数据表，并不能保证数据一致性
• -A 导出全部数据库
• –-default-character-set=charset 指定导出数据时采用何种字符集
• --master-data=2 表示在备份过程中记录主库的 binlog 和 pos 点，并在dump文件中注释掉这一行，在使用备份文件做新备库时会用到

2.1.1.2 MyISAM 全库备份

mysqldump --opt --lock-all-tables --master-data=2 --default-character-set=utf8 -h<host> -u<user> -p<password> -A > backup.sql

• --lock-all-tables 锁表备份。由于 MyISAM 不能提供一致性读，如果要得到一份一致性备份，只能进行全表锁定。

2.1.1.3 备份带上压缩

mysqldump -h<host> -u<user> -p<password> -A | gzip >> backup.sql.gz

2.1.1.4 备份多个库

mysqldump -h<host> -u<user> -p<password> --databases <dbname1> <dbname2> > backup.sql

2.1.2 恢复

恢复方式比较简单，直接执行 sql 语句就可以了

mysql -h<host> -u<user> -p<password> < backup.sql

2.1.3 mysqldump执行流程

打开 general_log 可以查看 mysqldump 的执行流程，这里以 --single-transaction --opt -A 参数为例

FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION
SHOW VARIABLES LIKE 'gtid\_mode'
SHOW MASTER STATUS
UNLOCK TABLES
...
SHOW CREATE DATABASE IF NOT EXISTS `employees`
SAVEPOINT sp
...
SELECT /*!40001 SQL_NO_CACHE */ * FROM `departments`
....

2.2 xtrabackup

2.2.1 安装方式

更多安装方式参考官网 xtrabackup

这里我们使用 rpm 安装的方式

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum update percona-release
# qpress 用作压缩解压
yum install percona-xtrabackup-24 qpress

2.2.2 使用方法

2.2.2.1 增加备份账号并授权

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup';
GRANT PROCESS,RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

2.2.2.2 全备

innobackupex --defaults-file=/etc/my.cnf --user=<user> --password=<pwd> <要备份到哪个目录> --no-timestamp --compress --compress-threads=4 --parallel=4

• --no-timestamp 不使用当前时间建立文件夹。默认情况下会在备份目录以当前时间创建文件夹
• --compress 压缩
• --compress-threads=N 压缩线程
• --parallel=N 备份线程

2.2.2.3 恢复

# 步骤一：解压
innobackupex --decompress <备份文件所在目录> --parallel=4

# 步骤二：应用日志
innobackupex --apply-log <备份文件所在目录> --parallel=4

# 步骤三：复制备份文件到数据目录
innobackupex --datadir=<MySQL数据目录> --copy-back <备份文件所在目录> --parallel=4

3. 增量备份

当数据了变得庞大时，一个常见策略就是做定期的增量备份。例如：周一做了一次全量备份，然后周二到日做增量备份。

增量备份只包含变化的数据集，一般情况不会比原始数据量大，所以可以减少服务器的开销、备份时间、备份空间。

当然，使用增量备份也会有风险，增量备份每一次迭代都是基于上一次的备份实现，意味着只要其中一份备份出现问题，那么就有可能导致所有备份不可用。

下面介绍一些增量备份方法：

3.1 使用 xtrabackup 做增量备份

xtrabackup 允许进行增量备份，命令如下:

innobackupex --defaults-file=/etc/my.cnf --user=<user> --password=<pwd> --no-timestamp --compress --incremental --incremental-basedir=<全量备份的目录> <要增量备份到什么目录>

恢复：

# 步骤一：对全备解压
innobackupex --decompress <全量备份文件所在目录>

# 步骤二：对全备应用日志
innobackupex --apply-log --redo-only <全量备份文件所在目录>

# 步骤三：对增量备份进行解压
innobackupex --decompress <增量文件所在目录>

# 步骤四：合并增量数据
innobackupex --apply-log --redo-only --incremental <全量备份文件所在目录> --incremental-dir=<增量文件所在目录>

# 步骤五：对合并后的数据应用日志
innobackupex --apply-log <全量备份文件所在目录>

# 步骤六：复制备份文件到数据目录
innobackupex --datadir=<MySQL数据目录> --copy-back <全量备份文件所在目录>

3.2 使用 binlog 做增量备份

使用 binlog 做增量备份比较简单，备份的时候执行 FLUSH LOGS 轮转日志，然后把旧的 binlog 复制到备份目录就可以了。

恢复的时候使用 mysqlbinlog --start-position=<备份集的pos点> binlog日志 | mysql -u<user> -p 就可以了

4. 延迟同步

延迟同步是常见的使用主从复制使用模式，在遇到误操作的时候，无论是用于恢复数据，还是使用跳过的方式跳过错误都是非常有用。

例如在主库做了 drop 的误操作，在主库找到命令所在 binlog 日志和 pos 位置，Delay库停止同步，然后使用 start slave until master_log_file='<对应file>',master_log_pos=<误操作命令前一个SQL的pos>; 等待同步到这个位置，执行跳过一条 SQL 的命令再开启同步。

常见的延迟同步复制模式有:

一主带三从

有时候为了减少主库压力，会把延迟库放在备节点之后

延迟同步开启方式如下:

stop slave;
CHANGE MASTER TO MASTER_DELAY = N秒;
start slave;

5. 数据校验

除了备份，非常重要的一件事情就是验证备份数据的可用性。想象一下，当你需要进行数据恢复的时候，忽然发现过去的备份数据都是无效的，那得有多难受。很多朋友在写好备份脚本加到定时任务后，只要检查到定时任务有执行，备份目录有文件就不再关注了，往往到了需要使用备份文件的时候才发现备份数据有问题。

目前对于备份文件的数据校验没有非常方便的办法，用的比较多的还是定时把备份文件拉出来做备份恢复演练，例如一个月做一次备份恢复演练就可以有效提高备份文件可用性，心里也踏实。

数据校验部分，如果是逻辑备份，往往会抽查某个表的数据，检查是否符合预期。如果是物理备份，首先要使用 mysqlcheck 等命令检查是否有表损坏，没有损坏再抽查表数据。

6. 总结

1. 逻辑备份和物理备份可以一起使用，不同的备份周期使用不同的工具，全备周期不应该太长，至少一周一次全备
2. 如果数据量较大，可以使用增量备份的方式减少数据量，要注意的是，增量备份风险更大
3. binlog功能要开启，设为 row 模式，设置 log_slave_updates = 1，且最好定时备份 binlog
4. 有条件的话可以增加一个 Delay 库，在做紧急恢复的时候有奇效
5. 数据校验要定时去做，否则当需要备份恢复的时候而备份文件又失效，后悔都来不及

参考资料:

• 高性能MySQL（第3版）