MySQL数据库备份和恢复方案小结[通俗易懂]

大家好，又见面了，我是你们的朋友全栈君。

这两天在调研MySQL数据库的备份和恢复方案，备份对象是对大量Innodb表，或者加上少量的MyISAM表。

InnoDB备份常见问题：

文件一致性：数据文件、缓存、日志文件必须保持严格一致。加锁的方法没法保证一致性，因为InnoDB后台刷新数据是异步进行的。

数据一致性：不能一次锁住一张表，这个备份的是不同时刻的表数据；如果一次锁住所有表，innodb的mvcc机制会增加undo日志，同时DDL操作的话也会有风险。

复制：如果支持主备结构，还要缓存主备同步的信息。

所以，要在不停止mysql的情况下对innodb表进行热备份，文件一致性和数据一致性很难保证。

本人尝试了以下备份和恢复方法：

1. 使用mysqldump工具，类似的有mypump（5.7.11之后有用，多线程导出，能实现数据一致性）. 这种方法适合在数据量比较少的情况下使用（个人认为数据量<100G可行），在一台30G的数据库上进行备份，备份出来的数据文件8G（因为没有导出来索引等其他数据库信息，因此文件通常比数据库小的多），使用时间8分钟，恢复时间用了差不多办个小时。顺便说一句，导出时使用order-by-primary参数应该可以提高几倍的导入性能（和mysql的聚集索引有关）

2. Mylvnbackup文件系统快照：要求所有的数据文件都在LVM卷上，由于我的测试系统不满足该条件，没有测试成功。

3.SAN\DRBD\RAID：硬件层次的拷贝，不是真正的备份，在主数据库上误操作删除数据库，没法恢复。

4.MySQL EnterpriseBackup： oracle官方出品，直接基于物理文件备份。收费软件， 每台服务器$5000 ，太贵了，直接放弃。

  这个工具有个很大的优点：支持Windows环境上Mysql数据库备份。我查了很多物理备份工具都没有这个功能。

5.Percona XtraBackup： 该工具功能十分强大，能满足中型和部分大型生产环境。该工具也是基于数据库物理文件进行备份，现执行物理备份，然后使用–prepare选项对拷贝的文件进行一次内部崩溃恢复，以便产生一致性备份。该工具可以进行全量备份和增量，我在一台测试环境上测试，30G的数据库备份之后用了17分钟，cpu占用率也不高，备份后大小是20G，几个小时后基于上次的全量备份做了次增量备份，居然花了13分钟。

该工具全量备份效率应该没有问题。增量备份耗时有点超出意料。后续再测试下。

关于该工具的详细信息：https://www.percona.com/software/mysql-database/percona-xtrabackup

6. Zmanda Recover Manager： 有免费和商业版两种，商业版可以提供网页版的管理工具，并且支持windows环境上的mysql备份

  该工具的原理是先创建快照，再根据快照创建全量备份。增量备份采用的是二进制日志。

  该工具的优点可以使用web端进行管理，定制备份策略，监控备份状态。

  该工具的配置文件可以配置采用逻辑备份还是raw（物理备份），但是我在实际测试中发现3.0版本不能对5.6版本的mysql进行物理备份，不管配置的备份方式是什么，该工具都使用mysqldump进行逻辑备份。难道是程序bug？优点坑。

  有兴趣的朋友可以研究下：http://www.zmanda.com/download-zrm.php

要实现数据库的安全备份，我的理解是必须要有以下几点：

1. 全量备份（一周或者二周一次）

2.二进制日志：能够实现基于时间点的恢复。 根据业务场景选择适当的时间间隔定时备份二进制日志，如rsync。

3.至少要有一台备机：否则主机出问题后，恢复数据库期间会造成业务中断

4.完备的数据恢复测试：根据恢复后的日志文件、数据文件的大小、关键事件信息验证备份信息是否有效。

备份和恢复是个吃力不讨好的活，功能正常没有功劳，出点错误都是惊天动地的大事。