MySQl的备份与恢复

如果没有提前做好备份规划，也许以后会发现已经错失了一些最佳的选择。例如，在服务器已经配置好以后，才想起应该使用LVM，以便可以获取文件系统的快照——但这时已经太迟了。在为备份配置系统参数时，可能没有注意到某些系统配置对性能有着重要影响。如果没有计划做定期的恢复演练，当真的需要恢复时，就会发现并没有那么顺利。

我们在此假设大部分用户主要使用InnoDB而不是MyISAM。在本章中，我们不会涵盖一个精心设计的备份和恢复解决方案的所有部分——而仅涉及与MySQL相关的部分。我们不打算包括的话题如下：

• 安全（访问备份，恢复数据的权限，文件是否需要加密）。
• 备份存储在哪里，包括它们应该离源数据多远（在一块不同的盘上，一台不同的服务器上，或离线存储），以及如何将数据从源头移动到目的地。
• 保留策略、审计、法律要求，以及相关的条款。
• 存储解决方案和介质，压缩，以及增量备份。
• 存储的格式。
• 对备份的监控和报告。
• 存储层内置备份功能，或者其他专用设备，例如预制式文件服务器。

像这样的话题已经在许多书中涉及，例如W.Curtis Preston的 Backup & Recouery(O’Reilly) _。

在开始本章之前，让我们先澄清几个核心术语。首先，经常可以听到所谓的热备份、暖备份和冷备份。人们经常使用这些词来表示一个备份的影响：例如，“热”备份不需要任何的服务停机时间。问题是对这些术语的理解因人而异。有些工具虽然在名字中使用了“热备份”，但实际上并不是我们所认为的那样。我们尽量避开这些术语，而直接说明某个特别的技术或工具对服务器的影响。

另外两个让人困惑的词是还原和恢复。在本章中它们有其特定的含义。还原意味着从备份文件中获取数据，可以加载这些文件到MySQL里，也可以将这些文件放置到MySQL期望的路径中。恢复一般意味着当某些异常发生后对一个系统或其部分的拯救。包括从备份中还原数据，以及使服务器完全恢复功能的所有必要步骤，例如重启MySQL、改变配置和预热服务器的缓存等。

在很多人的概念中，恢复仅意味着修复崩溃后损坏的表。这与恢复一个完整的服务器是不同的。存储引擎的崩溃恢复要求数据和日志文件一致。要确保数据文件中只包含已经提交的事务所做的修改，恢复操作会将日志中还没有应用到数据文件的事务重新执行。这也许是恢复过程的一部分，甚至是备份的一部分。然而，这和一个意外的DROP TABLE事故后需要做的事是不一样的。

1.为什么要备份

下面是备份非常重要的几个理由：

灾难恢复

灾难恢复是下列场景下需要做的事情：硬件故障、一个不经意的Bug导致数据损坏，或者服务器及其数据由于某些原因不可获取或无法使用等。你需要准备好应付很多问题：某人偶然连错服务器执行了一个ALTER TABLED的操作，机房大楼被烧毁，恶意的黑客攻击或MySQL的Bug等。尽管遭受任何一个特殊的灾难的几率都非常低，但所有的风险叠加在一起就很有可能会碰到。

人们改变想法

不必惊讶，很多人经常会在删除某些数据后又想要恢复这些数据。

审计

有时候需要知道数据或Schema在过去的某个时间点是什么样的。例如，你也许被卷入一场法律官司，或发现了应用的一个Bug,想知道这段代码之前干了什么（有时候，仅仅依靠代码的版本控制还不够）。

测试

一个最简单的基于实际数据来测试的方法是，定期用最新的生产环境数据更新测试服务器。如果使用备份的方案就非常简单:只要把备份文件还原到测试服务器上即可。

检查你的假设。例如，你认为共享虚拟主机供应商会提供MySQL服务器的备份？许多主机供应商根本不备份MySQL服务器，另外一些也仅仅在服务器运行时复制文件，这可能会创建一个损坏的没有用处的备份。

2.定义恢复需求

如果一切正常，那么永远也不需要考虑恢复。但是，一旦需要恢复，只有世界上最好的备份系统是没用的，还需要一个强大的恢复系统。

不幸的是，让备份系统平滑工作比构造良好的恢复过程和工具更容易。原因如下：

• 备份在先。只有已经做了备份才可能恢复，因此在构建系统时，注意力自然会集中在备份上。
• 备份由脚本和任务自动完成。经常不经意地，我们会花些时间调优备份过程。花5分钟来对备份过程做小的调整看起来并不重要，但是你是否天天同样地重视恢复呢？
• 备份是日常任务，但恢复常常发生在危急情形下。
• 因为安全的需要，如果正在做异地备份，可能需要对备份数据进行加密，或采取其他措施来进行保护。安全性往往只关注数据被盗用的后果，但是有没有人想过，如果没有人能对用来恢复数据的加密卷解锁，或需要从一个整块的加密文件中抽取单个文件时，损害又是多大？
• 只有一个人来规划、设计和实施备份。当灾难袭来时，那个人可能不在。因此需要培养几个人并有计划地互为备份，这样就不会要求一个不合格的人来恢复数据。

这里有一个我们看到的真实例子：一个客户报告说当mysqldump加上-d选项后，备份变得像闪电一般快，他想知道为什么没有一个人提出该选项可以如此快地加速备份过程。如果这个客户已经尝试还原这些备份，就不难发现其原因:使用-d选项将不会备份数据！这个客户关注备份，却没有关注恢复，因此完全没有意识到这个问题。

规划备份和恢复策略时，有两个重要的需求可以帮助思考：恢复点目标（PRO)和恢复时间目标（RTO)。它们定义了可以容忍丢失多少数据，以及需要等待多久将数据恢复。在定义RPO和RTO时，先尝试回答下面几类问题：

• 在不导致严重后果的情况下，可以容忍丢失多少数据？需要故障恢复，还是可以接受自从上次日常备份后所有的工作全部丢失？是否有法律法规的要求？
• 恢复需要在多长时间内完成？哪种类型的宕机是可接受的？哪种影响（例如，部分服务不可用）是应用和用户可以接受的？当那些场景发生时，又该如何持续服务？
• 需要恢复什么？常见的需求是恢复整个服务器，单个数据库，单个表，或仅仅是特定的事务或语句。

建议将上面这些问题的答案明确地用文档记录下来，同时还应该明确备份策略，以及备份过程。

讨论：备份误区1: “复制就是备份”

这是我们经常碰到的一个误区。复制不是备份，当然使用RAID阵列也不是备份。为什么这么说？可以考虑一下，如果意外地在生产库上执行了DROP DATABASE，它们是否可以帮你恢复所有的数据？RAID和复制连这个简单的测试都没法通过。它们不是备份，也不是备份的替代品。只有备份才能满足备份的要求。

3.设计MySQL备份方案

备份MySQL比看起来难。最基本的，备份仅是数据的一个副本，但是受限于应用程序的要求、MySQL的存储引擎架构，以及系统配置等因素，会让复制一份数据都变得很困难。

在深入所有选项细节之前，先来看一下我们的建议：

• 在生产实践中，对于大数据库来说，物理备份是必需的：逻辑备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间。基于快照的备份，例如Percona XtraBackup和MySQL Enterprise Backup是最好的选择。对于较小的数据库，逻辑备份可以很好地胜任。
• 保留多个备份集。
• 定期从逻辑备份（或者物理备份）中抽取数据进行恢复测试。
• 保存二进制日志以用于基于故障时间点的恢复。expire_logs_days参数应该设置得足够长，至少可以从最近两次物理备份中做基于时间点的恢复，这样就可以在保持主库运行且不应用任何二进制日志的情况下创建一个备库。备份二进制日志与过期设置无关，二进制日志备份需要保存足够长的时间，以便能从最近的逻辑备份进行恢复。
• 完全不借助备份工具本身来监控备份和备份的过程。需要另外验证备份是否正常。
• 通过演练整个恢复过程来测试备份和恢复。测算恢复所需要的资源(CPU、磁盘空间、实际时间，以及网络带宽等)。
• 对安全性要仔细考虑。如果有人能接触生产服务器，他是否也能访问备份服务器？反过来呢？

弄清楚RPO和RTO可以指导备份策略。是需要基于故障时间点的恢复能力，还是从昨晚的备份中恢复但会丢失此后的所有数据就足够了？如果需要基于故障时间点的恢复，可能要建立日常备份并保证所需要的二进制日志是有效的，这样才能从备份中还原，并通过重放二进制日志来恢复到想要的时间点。

一般说来，能承受的数据丢失越多，备份越简单。如果有非常苛刻的需求，要确保能恢复所有数据，备份就很困难。基于故障时间点的恢复也有几类。一个“宽松”的故障时间点恢复需求意味着需要重建数据，直到“足够接近”问题发生的时刻。一个“硬性”的需求意味着不能容忍丢失任何一个已提交的事务，即使某些可怕的事情发生（例如服务器着火了）。这需要特别的技术，例如将二进制日志保存在一个独立的SAN卷或使用DRBD磁盘复制。

3.1 在线备份还是离线备份

如果可能，关闭MySQL做备份是最简单最安全的，也是所有获取一致性副本的方法中最好的，而且损坏或不一致的风险最小。如果关闭了MySQL,就根本不用关心InnoDB缓冲池中的脏页或其他缓存。也不需要担心数据在尝试备份的过程被修改，并且因为服务器不对应用提供访问，所以可以更快地完成备份。

尽管如此，让服务器停机的代价可能比看起来要更昂贵。即使能最小化停机时间，在高负载和髙数据量下关闭和重启MySQL也可能要花很长一段时间。我们演示过一些使这个影响最小化的技术，但并不能将其减少为零。因此，必须要设计不需要生产服务器停机的备份。即便如此，由于一致性的需要，对服务器进行在线备份仍然会有明显的服务中断。

在众多的备份方法中，一个最大问题就是它们会使用FLUSH TABLES WITH READ LOCK操作。这会导致MySQL关闭并锁住所有的表，将MyISAM的数据文件刷新到磁盘上（但InnoDB不是这样的！），并且刷新査询缓存。该操作需要非常长的时间来完成。具体需要多长时间是不可预估的；如果全局读锁要等待一个长时间运行的语句完成，或有许多表，那么时间会更长。除非锁被释放，否则就不能在服务器上更改任何数据，一切都会被阻塞和积压。FLUSH TABLES WITH READ LOCK不像关闭服务器的代价那么髙，因为大部分缓存仍然在内存中，并且服务器一直是“预热”的，但是它也有非常大的破坏性。如果有人说这样做很快，可能是准备向你推销某种从来没有在真正的线上服务器上运行过的东西。

避免使用FLUSH TABLES WITH READ LOCK的最好的方法是只使用InnoDB表。在权限和其他系统信息表中使用MyISAM表是不可避免的，但是如果数据改变量很少（正常情况下），你可以只刷新和锁住这些表，这不会有什么问题。

在规划备份时，有一些与性能相关的因素需要考虑。

锁时间

需要持有锁多长时间，例如在备份期间持有的全局FLUSH TABLES WITH READ LOCK?

备份时间

复制备份到目的地需要多久？

备份负载

在复制备份到目的地时对服务器性能的影响有多少？

恢复时间

把备份镜像从存储位置复制到MySQL服务器，重放二进制日志等，需要多久？

最大的权衡是备份时间与备份负载。可以牺牲其一以增强另外一个。例如，可以提高备份的优先级，代价是降低服务器性能。

同样，也可以利用负载的特性来设计备份。例如，如果服务器在晚上的8小时内仅仅有50%的负载，那么可以尝试规划备份，使得服务器的负载低于50%且仍能在8小时内完成。可以采用许多方法来完成这个目标，例如，可以用ionice和nice来提髙复制或压缩操作的优先级，使用不同的压缩等级，或在备份服务器上压缩而不是在MySQL服务器上。甚至可以利用lzo或pigz以获取更快的压缩。也可以使用0_DIRECT或fadvise()在复制操作时绕开操作系统的缓存，以避免污染服务器的缓存。像PerccmaXtraBackup和MySQL Enterprise Backup这样的工具都有限流选项，可在使用pv时加—rate-limit选项来限制备份脚本的吞吐量。

3.2 逻辑备份还是物理备份

有两种主要的方法来备份MySQL数据：逻辑备份（也叫“导出”）和直接复制原始文件的物理备份。逻辑备份将数据包含在一种MySQL能够解析的格式中，要么是SQL，要么是以某个符号分隔的文本。原始文件是指存在于硬盘上的文件。

任何一种备份都有其优点和缺点。

逻辑备份

逻辑备份有如下优点：

• 逻辑备份是可以用编辑器或像grep和sed类的命令査看和操作的普通文件。当需要恢复数据或只想査看数据但不恢复时，这都非常有帮助。
• 恢复非常简单。可以通过管道把它们输入到mysql,或者使用mysqlimport。
• 可以通过网络来备份和恢复——就是说，可以在与MySQL主机不同的另外一台机器上操作。
• 可以在类似Amazon RDS这样不能访问底层文件系统的系统中使用。
• 非常灵活，因为mysqldump-大部分人喜欢的工具-可以接受许多选项，例如可以用WHERE子句来限制需要备份哪些行。
• 与存储引擎无关。因为是从MySQL服务器中提取数据而生成，所以消除了底层数据存储和不同。因此，可以从InnoDB表中备份，然后只需极小的工作量就可以还原到MyISAM表中。而对于原始数据却不能这么做。
• 有助于避免数据损坏。如果磁盘驱动器有故障而要复制原始文件时，你将会得到一个错误并且/或生成一个部分或损坏的备份。如果MySQL在内存中的数据还没有损坏，当不能得到一个正常的原始文件复制时，有时可以得到一个可以信赖的逻辑备份。

尽管如此，逻辑备份也有它的缺点：

• 必须由数据库服务器完成生成逻辑备份的工作，因此要使用更多的CPU周期。
• 逻辑备份在某些场景下比数据库文件本身更大ASCII形式的数据不总是和存储引擎存数据一样高效。例如，一个整型需要4字节来存储，但是用ASCII写入时，可能需要12个字符。当然也可以压缩文件以得到一个更小的备份文件，但这样会使用更多的CPU资源。（如果索引比较多，逻辑备份一般要比物理备份小。）
• 无法保证导出后再还原出来的一定是同样的数据。浮点表示的问题、软件Bug等都会导致问题，尽管非常少见。
• 从逻辑备份中还原需要MySQL加载和解释语句，转化为存储格式，并重建索引，所有这一切会很慢。

最大的缺点是从MySQL中导出数据和通过SQL语句将其加载回去的开销。如果使用逻辑备份，测试恢复需要的时间将非常重要。

Percona Server中包含的mysqldump,在使用InnoDB表时能起到帮助作用，因为它会对输出格式化，以便在重新加载时利用InnoDB的快速建索引的优点。我们的测试显示这样做可以减少2/3甚至更多的还原时间。索引越多，好处越明显。

物理备份

物理备份有如下好处：

• 基于文件的物理备份，只需要将需要的文件复制到其他地方即可完成备份。不需要其他额外的工作来生成原始文件。
• 物理备份的恢复可能就更简单了，这取决于存储引擎。对于MyISAM，只需要简单地复制文件到目的地即可。对于InnoDB则需要停止数据库服务，可能还要采取其他一些步骤。
• InnoDB和MyISAM的物理备份非常容易跨平台、操作系统和MySQL版本。（逻辑导出亦如此。这里特别指出这一点是为了消除大家的担心。）
• 从物理备份中恢复会更快，因为MySQL服务器不需要执行任何SQL或构建索引。如果有很大的InnoDB表，无法完全缓存到内存中，则物理备份的恢复要快非常多——至少要快一个数量级。事实上，逻辑备份最可怕的地方就是不确定的还原时间。

物理备份也有其缺点，比如：

• InnoDB的原始文件通常比相应的逻辑备份要大得多。InnoDB的表空间往往包含很多未使用的空间。还有很多空间被用来做存储数据以外的用途(插入缓冲，回滚段等)。
• 物理备份不总是可以跨平台、操作系统及MySQL版本。文件名大小写敏感和浮点格式是可能会遇到麻烦。很可能因浮点格式不同而不能移动文件到另一个系统（虽然主流处理器都使用IEEE浮点格式。）

物理备份通常更加简单髙效。尽管如此，对于需要长期保留的备份，或者是满足法律合规要求的备份，尽量不要完全依赖物理备份。至少每隔一段时间还是需要做一次逻辑备份。

除非经过测试，不要假定备份（特别是物理备份）是正常的。对InnoDB来说，这意味着需要启动一个MySQL实例，执行InnoDB恢复操作，然后运行CHECK TABLES。也可以跳过这一操作，仅对文件运行innochecksum,但我们不建议这样做。对于MyISAM,可以运行CHECK TABLES,或者使用mysqlcheck。使用mysqlcheck可以对所有的表执行CHECK TABLES操作。

建议混合使用物理和逻辑两种方式来做备份：先使用物理复制，以此数据启动MySQL服务器实例并运行mysqlcheck。然后，周期性地使用mysqldump执行逻辑备份。这样做可以获得两种方法的优点，不会使生产服务器在导出时有过度负担。如果能够方便地利用文件系统的快照，也可以生成一个快照，将该快照复制到另外一个服务器上并释放，然后测试原始文件，再执行逻辑备份。

3.3 备份什么

恢复的需求决定需要备份什么。最简单的策略是只备份数据和表定义，但这是一个最低的要求。在生产环境中恢复数据库一般需要更多的工作。下面是MySQL备份需要考虑的几点。

非显著数据

不要忘记那些容易被忽略的数据：例如，二进制日志和InnoDB事务日志。

代码

现代的MySQL服务器可以存储许多代码，例如触发器和存储过程。如果备份了mysql数据库，那么大部分这类代码也备份了，但如果需要还原单个业务数据库会比较麻烦，因为这个数据库中的部分“数据”，例如存储过程，实际是存放在mysql数据库中的。

复制配置

如果恢复一个涉及复制关系的服务器，应该备份所有与复制相关的文件，例如二进制日志、中继日志、日志索引文件和.info文件。至少应该包含SHOW MASTER STATUS和/或SHOW SLAVE STATUS的输出。执行FLUSH LOGS也非常有好处，可以让MySQL从一个新的二进制日志开始。从日志文件的开头做基于故障时间点的恢复要比从中间更容易。

服务器配置

假设要从一个实际的灾难中恢复，比如说，地震过后在一个新数据中心中构建服务器，如果备份中包含服务器配置，你一定会喜出望外。

选定的操作系统文件

对于服务器配置来说，备份中对生产服务器至关重要的任何外部配置，都十分重要。在UNIX服务器上，这可能包括cron任务、用户和组的配置、管理脚本，以及sudo似而规则。

这些建议在许多场景下会被当作“备份一切”。然而，如果有大量的数据，这样做的开销将非常髙，如何做备份，需要更加明智的考虑。特别是，可能需要在不同备份中备份不同的数据。例如，可以单独地备份数据、二进制日志和操作系统及系统配置。

增量备份和差异备份

当数据量很庞大时，一个常见的策略是做定期的增量或差异备份。它们之间的区别有点容易让人混淆，所以先来澄清这两个术语：差异备份是对自上次全备份后所有改变的部分而做的备份，而增量备份则是自从任意类型的上次备份后所有修改做的备份。

例如，假如在每周日做一个全备份。在周一，对自周日以来所有的改变做一个差异备份。

在周二，就有两个选择：备份自周日以来所有的改变（差异），或只备份自从周一备份后所有的改变（增量）。

增量和差异备份都是部分备份：它们一般不包含完整的数据集，因为某些数据几乎肯定没有改变。部分备份对减少服务器开销、备份时间及备份空间而言都很适合。尽管某些部分备份并不会真正减少服务器的开销。例如，Percona XtraBackup和MySQL Enterprise Backup,仍然会扫描服务器上的所有数据块，因而并不会节约太多的开销，但它们确实会减少一定量的备份时间和大量用于压缩的CPU时间，当然也会减少磁盘空间使用。

不要因为会用髙级备份技术而自负，解决方案越复杂，可能面临的风险也越大。要注意分析隐藏的危险，如果多次迭代备份紧密地耦合在一起，则只要其中的一次迭代备份有损坏，就可能会导致所有的备份都无效。

下面有一些建议：

• 使用Percona XtraBackup 和MySQL Enterprise Backup中的增量备份特性。
• 备份二进制日志。可以在每次备份后使用FLUSH LOGS来开始一个新的二进制日志，这样就只需要备份新的二进制日志。
• 不要备份没有改变的表。有些存储引擎，例如MyISAM，会记录每个表最后修改时间。可以通过査看磁盘上的文件或运行SHOW TABLE STATUS来看这个时间。如果使用InnoDB，可以利用触发器记录修改时间到一个小的“最后修改时间”表中，帮助跟踪最新的修改操作。需要确保只对变更不频繁的表进行跟踪，这样才能降低开销。通过定制的备份脚本可以轻松获取到哪些表有变更。

例如，如果有包含不同语种各个月的名称列表，或者州或区域的简写之类的“查找” 表，将它们放在一个单独的数据库中是个好主意，这就不需要每次都备份这些表。

• 不要备份没有改变的行。如果一个表只做插入，例如记录网页页面点击的表，那么可以增加一个时间戳的列，然后只备份自上次备份后插入的行。
• 某些数据根本不需要备份。有时候这样做影响会很大——例如，如果有一个从其他数据构建的数据仓库，从技术上讲完全是冗余的，就可以仅备份构建仓库的数据，而不是数据仓库本身。即使从源数据文件重建仓库的“恢复”时间较长，这也是个好想法。相对于从全备中可能获得的快速恢复时间，避免备份可以节约更多的总的时间开销。临时数据也可以不用备份，例如保留网站会话数据的表。
• 备份所有的数据，然后发送到一个有去重特性的目的地，例如ZFS文件管理程序。

增量备份的缺点包括增加恢复复杂性，额外的风险，以及更长的恢复时间。如果可以做全备，考虑到简便性，我们建议尽量做全备。

不管如何，还是需要经常做全备份——建议至少一周一次。你肯定不会希望使用一个月的所有增量备份来进行恢复。即使一周也还是有很多的工作和风险的。

3.4 存储引擎和一致性

MySQL对存储引擎的选择会导致备份明显更复杂。问题是，对于给定的存储引擎，如何得到一致的备份。

实际上有两类一致性需要考虑：数据一致性和文件一致性。

数据一致性

当备份时，应该考虑是否需要数据在指定时间点一致。例如，在一个电子商务数据库中，可能需要确保发货单和付款之间一致。恢复付款时如果不考虑相应的发货单，或反过来，都会导致麻烦。

如果做在线备份（从一个运行的服务器做备份），可能需要所有相关表的一致性备份。这意味着不能一次锁住一张表然后做备份——因而意味着备份可能比预想的要更有侵入性。如果使用的不是事务型存储引擎，则只能在备份时用LOCK TABLES来锁住所有要一起备份的表，备份完成后再释放锁。

InnoDB的多版本控制功能可以帮到我们。开始一个事务，转储一组相关的表，然后提交事务。（如果使用了事务获取一致性备份，则不能用LOCK TABLES，因为它会隐式地提交事务-—详情参见MySQL手册。）只要在服务器上使用REPEATABLE READ事务隔离级别，并且没有任何DDL，就一定会有完美的一致性，以及基于时间点的数据快照，且在备份过程中不会阻塞任何后续的工作。

尽管如此，这种方法并不能保护逻辑设计很差的应用。假如在电子商务库中插入一条付款记录，提交事务，然后在另外一个事务中插入一条发货单记录。备份过程可能在这两个操作之间开始，备份了付款记录却不包括发货单记录。这就是必须仔细设计事务以确保相关的操作放在一个组内的原因。 

也可以用mysqldump来获得InnoDB表的一致性逻辑备份，采用—single-transaction选项可以按照我们所描述的那样工作。但是，这可能会导致一个非常长的事务，在某些负载下会导致开销大到不可接受。

文件一致性

每个文件的内部一致性也非常重要——例如，一条大的UPDATE语句执行时备份反映不出文件的状态——并且所有要备份的文件相互间也应一致。如果没有内部一致的文件，还原时可能会感到惊讶（它们可能已经损坏）。如果是在不同的时间复制相关的文件，它们彼此可能也不一致。MyISAM的.MYD和.MYI文件就是个例子。InnoDB如果检测到不一致或损坏，会记录错误日志乃至让服务器崩溃。

对于非事务性存储引擎，例如MyISAM，可能的选项是锁住并刷新表。这意味着要么用LOCK TABLES和FLUSH TABLES结合的方法以使服务器将内存中的变更刷到磁盘上，要么用FLUSH TABLES WITH READ LOCK。一旦刷新完成，就可以安全地复制MyISAM的原始文件。 

对于InnoDB，确保文件在磁盘上一致更困难。即使使用FLUSH TABLES WITH READ LOCK，InnoDB依旧在后台运行：插入缓存、日志和写线程继续将变更合并到日志和表空间文件中。这些线程设计上是异步的——在后台执行这些工作可以帮助InnoDB取得更高的并发性——正因为如此它们与LOCK TABLES无关。因此，不仅需要确保每个文件内部是一致的，还需要同时复制同一个时间点的日志和表空间文件。如果在备份时有其他线程在修改文件，或在与表空间文件不同的时间点备份日志文件，会在恢复后再次因系统损坏而告终。可以通过下面几个方法规避这个问题。

• 等待直到InnoDB的清除线程和插入缓冲合并线程完成。可以观察SHOW INNODB STATUS的输出，当没有脏缓存或挂起的写时，就可以复制文件。尽管如此，这种方法可能需要很长一段时间；因为InnoDB的后台线程涉及太多的干扰而不太安全。所以我们不推荐这种方法。
• 在一个类似LVM的系统中获取数据和日志文件一致的快照，必须让数据和日志文件在快照时相互一致；单独取它们的快照是没有意义的。在本章后续的LVM快照中会讨论。
• 发送一个STOP信号给MySQL,做备份，然后再发送个C0NT信号来再次唤醒MySQL。看起来是一个很少推荐的方法，但如果另外一种方法是在备份过程中需要关闭服务器，则这种方法值得考虑。至少这种技术不需要在重启服务器后预热。

在复制数据文件到其他地方后，就可以释放锁以使MySQL服务器再次正常运行。

复制

从备库中备份最大的好处是可以不干扰主库，避免在主库上增加额外的负载。这是一个建立备库的好理由，即使不需要用它做负载均衡或髙可用。如果钱是个问题，也可以把备份用的备库用于其他用途，例如报表服务——只要不对其做写操作，以确保备份时不会修改数据。备库不必只用于备份的目的；只需要在下次备份时能及时跟上主库，即使有时因作为其他用途导致复制延时也没有关系。 

当从备库备份时，应该保存所有关于复制进程的信息，例如备库相对于主库的位置。这对于很多情况都非常有用：克隆新的备库，重新应用二进制日志到主库上以获得指定时间点的恢复，将备库提升为主库等。如果停止备库，需要确保没有打开的临时表，因为它们可能导致不能重启备库。

故意将一个备库延时一段时间对于某些灾难场景非常有用。例如延时复制一小时，当一个不期望的语句在主库上运行后，将有一个小时的时间观察到并在从中继日志重放之前停掉复制。然后可以将备库提升为主库，重放少量相关的日志事件，跳过错误的语句。这比我们后面将要讨论的指定时间点的恢复技术可能要快很多。Percona Toolkit中pt-slave-delay工具可以帮助实现这个方案。

提示：备库可能与主库数据不完全一样。许多人认为备库是主库完全一样的副本，但以我们的经验，主库与备库数据不匹配是很常见的，并且MySQL没有方法检测这个问题。检测这个问题的唯一方法是使用Percona Toolkit中的pt-table-checksum之类的工具。拥有一个复制的备库可能在诸如主库的硬盘烧坏时提供帮助，但却不能提供保证。复制不是备份。

4.管理和备份二进制日志

服务器的二进制日志是备份的最重要因素之一。它们对于基于时间点的恢复是必需的，并且通常比数据要小，所以更容易进行频繁的备份。如果有某个时间点的数据备份和所有从那时以后的二进制日志，就可以重放自从上次全备以来的二进制日志并“前滚”所有的变更。

MySQL复制也使用二进制日志。因此备份和恢复的策略经常和复制配置相互影响。

二进制日志很”特别”。如果丢失了数据，你一定不希望同时丢失了二进制日志。为了让这种情况发生的几率减少到最小，可以在不同的卷上保存数据和二进制日志。即使在LVM下生成二进制日志的快照，也是可以的。为了额外的安全起见，可以将它们保存在SAN上，或用DRBD复制到另外一个设备上。

经常备份二进制日志是个好主意。如果不能承受丢失超过30分钟数据的价值，至少要每30分钟就备份一次。也可以用一个配置--log_slave_update的只读备库，这样可以获得额外的安全性。备虑上日志位置与主库不匹配，但找到恢复时正确的位置并不难。最后，MySQL5.6版本的mysqlbinlog有一个非常方便的特性，可连接到服务器上来实时对二进制日志做镜像，比起运行一个mysqld实例要简单和轻便。它与老版本是向后兼容的。

4.1 二进制日志格式

二进制日志包含一系列的事件。每个事件有一个固定长度的头，其中有各种信息，例如当前时间戳和默认的数据库。可以使用mysqlbinlog工具来査看二进制日志的内容，打印出一些头信息。下面是一个输出的例子。

1  # at 277
2  #071030 10:47:21 server id 3  end_log_pos 369   Query   thread_id=13    exec_time=0
   error_code=0
3  SET TIMESTAMP=1193755641/*!*/;
4  insert into test(a) values(2)/*!*/;

第一行包含日志文件内的偏移字节值（本例中为277)。

第二行包含如下几项。

• 事件的日期和时间，MySQL会使用它们来产生SET TIMESTAMP语句。
• 原服务器的服务器ID，对于防止复制之间无限循环和其他问题是非常有必要的。
• end_log_pos，下一个事件的偏移字节值。该值对一个多语句事务中的大部分事件是不正确的。在此类事务过程中，MySQL的主库会复制事件到一个缓冲区，但这样做的时候它并不知道下个日志事件的位置。
• 事件类型。本例中的类型是Query,但还有许多不同的类型。
• 原服务器上执行事件的线程ID，对于审计和执行C0NNECTI0N_ID()函数很重要。
• exec_time，这是语句的时间戳和写入二进制日志的时间之差。不要依赖这个值，因为它可能在复制落后的备库上会有很大的偏差。
• 在原服务器上事件产生的错误代码。如果事件在一个备库上重放时导致不同的错误，那么复制将因安全预警而失败。

后续的行包含重放变更时所需的数据。用户自定义的变更和任何其他特定设置，例如当语句执行时有效的时间戳，也将会出现在这里。

提示：如果使用的是MySQL5.1中基于行的日志，事件将不再是SQL。而是可读性较差的由语句对表所做变更的“镜像”。

4.2 安全地清除老的二进制日志

需要决定日志的过期策略以防止磁盘被二进制日志写满。日志增长多大取决于负载和日志格式（基于行的日志会导致更大的日志记录）。我们建议，如果可能，只要日志有用就尽可能保留。保留日志对于设置复制、分析服务器负载、审计和从上次全备按时间点进行恢复，都很有帮助。当决定想要保留日志多久时，应该考虑这些需求。

一个常见的设置是使用expire_log_days变量来告诉MySQL定期清理日志。这个变量直到MySQL4.1才引入；在此之前的版本，必须手动清理二进制日志。因此，你可能看到一些用类似下面的cron项来删除老的二进制日志的建议。

0 0 * * * /usr/bin/find /var/log/mysql -mtime +N -name "mysql-bin.[0-9]*" | xargs rm

尽管这是在MySQL4.1之前清除日志的唯一办法，但在新版本中不要这么做！用rm删除日志会导致状态文件与磁盘上的文件不一致，有些语句，例如SHOW MASTER LOGS可能会受到影响而悄然失败。手动修改mysql-bin.index文件也不会修复这个问题。应该用类似下面的cron命令。

0 0 * * * /usr/bin/mysql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY"

expire_logs_days设置在服务器启动或MySQL切换二进制日志时生效，因此，如果二进制日志从没有增长和切换，服务器不会清除老条目。此设置是通过査看日志的修改时间而不是内容来决定哪个文件需要被清除。

5.备份数据

大多数时候，生成备份有好的也有差的方法——有时候显而易见的方法并不是好方法。一个有用的技巧是应该最大化利用网络、磁盘和CPU的能力以尽可能快地完成备份。这是一个需要不断去平衡的事情，必须通过实验以找到“最佳平衡点”。

5.1 生成逻辑备份

对于逻辑备份，首先要意识到的是它们并不是以同样方式创建的。实际上有两种类型的逻辑备份：SQL导出和符号分隔文件。

SQL导出

SQL导出是很多人所熟悉的，因为它们是mysqldump默认的方式。例如，用默认选项导出一个小表将产生如下（有删减）输出。

$ mysqldump test t1
-- [Version and host comments]
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
-- [More version-specific comments to save options for restore]
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
-- [More option restoration]

导出文件包含表结构和数据，均以有效的SQL命令形式写出。文件以设置MySQL各种选项的注释开始。这些要么是为了使恢复工作更高效，要么是因为兼容性和正确性。接下来可以看到表结构，然后是数据。最后，脚本重置在导出开始时变更的选项。

导出的输出对于还原操作来说是可执行的。这很方便，但mysqldump默认选项对于生成一个巨大的备份却不是太适合。

mysqldump不是生成SQL逻辑备份的唯一工具。例如，也可以用mydumper或phpMyAdmin工具来创建。我们想指出的是，不是某一个特定的工具有多大的问题,而是做SQL逻辑备份本身就有一些缺点。下面是主要问题点：

Schema和数据存储在一起

如果想从单个文件恢复这样做会非常方便，但如果只想恢复一个表或只想恢复数据就很困难了。可以通过导出两次的方法来减缓这个问题--次只导出数据，另外一次只导出Schema-但还是会有下一个麻烦。

　　巨大的语句

服务器分析和执行SQL语句的工作量非常大，所以加载数据时会非常慢。

　　单个巨大的文件

大部分文本编辑器不能编辑巨大的或者包含非常长的行的文件。尽管有时候可以用命令行的流编辑器——例如sed或grep--来抽出需要的数据，但保持文件小型化仍然是更合适的。

　　逻辑备份的成本很高

比起逻辑备份这种从存储引擎中读取数据然后通过客户端/服务器协议发送结果集的方式，还有其他更高效的方法。

这些限制意味着SQL导出在表变大时可能变得不可用。不过，还有另外一个选择：导出数据到符号分隔的文件中。

符号分隔文件备份

可以使用SQL命令SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份。（可以用mysqldump的--tab选项导出到符号分隔文件中）。符号分隔文件包含以ASCII展示的原始数据，没有SQL、注释和列名。下面是一个导出为逗号分隔值（CVS)格式的例子，对于表格形式的数据来说这是一个很好的通用格式。

mysql> SELECT * INTO OUTFILE '/tmp/t1.txt'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> FROM test.t1;

比起SQL导出文件，符号分隔文件要更紧凑且更易于用命令行工具操作，这种方法最大的优点是备份和还原速度更快。可以和导出时使用一样的选项，用LOAD DATA INFILE方法加载数据到表中：

mysql> LOAD DATA INFILE '/tmp/t1.txt'
    -> INTO TABLE test.t1
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

下面这个非正式的测试演示了SQL文件和符号分隔文件在备份和还原上的速度差异。在测试中，我们对生产数据做了些修改。导出的表看起来像下面这样：

CREATE TABLE load_test (
   col1 date NOT NULL,
   col2 int NOT NULL,
   col3 smallint unsigned NOT NULL,
   col4 mediumint NOT NULL,
   col5 mediumint NOT NULL,
   col6 mediumint NOT NULL,
   col7 decimal(3,1) default NULL,
   col8 varchar(10) NOT NULL default '',
   col9 int NOT NULL,
   PRIMARY KEY  (col1, col2)
) ENGINE=InnoDB;

这张表有1500万行，占用近700MB的磁盘空间。表15-1对比了两种备份和还原方法的性能。可以看到测试中还原时间有较大的差异。

但是SELECT INTO OUTFILE方法也有一些限制。

• 只能备份到运行MySQL服务器的机器上的文件中。（可以写一个自定义的SELECT INTO OUTFILE程序，在读取SELECT结果的同时写到磁盘文件中，我们已经看到有些人采用这种方法。）
• 运行MySQL的系统用户必须有文件目录的写权限，因为是由MySQL服务器来执行文件的写入，而不是运行SQL命令的用户。
• 出于安全原因，不能覆盖已经存在的文件，不管文件权限如何。
• 不能直接导出到压缩文件中。
• 某些情况下很难进行正确的导出或导入，例如非标准的字符集。

5.2 文件系统快照

文件系统快照是一种非常好的在线备份方法。支持快照的文件系统能够瞬间创建用来备份的内容一致的镜像。支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理（LVM)，以及许多的SAN系统和文件存储解决方案，例如NetApp存储。

不要把快照和备份相混淆。创建快照是减少必须持有锁的时间的一个简单方法；释放锁后，必须复制文件到备份中。事实上，有些时候甚至可以创建InnoDB快照而不需要锁定。我们将要展示两种使用LVM来对InnoDB文件系统做备份的方法，可以选择最小化锁或零锁的方案。

快照对于特别用途的备份是一个非常好的方法。一个例子是在升级过程中遇到有问题而回退的情况。可以在升级前创建一个镜像，这样如果升级有问题，只需要回滚到该镜像。可以对任何不确定和有风险的操作都这么做，例如对一个巨大的表做变更（需要多少时间是未知的）。

LVM快照是如何工作的

LVM使用写时复制（copy-on-write)的技术来创建快照-例如，对整个卷的某个瞬间的逻辑副本。这与数据库中的MVCC有点像，不同的是它只保留一个老的数据版本。

注意，我们说的不是物理副本。逻辑副本看起来好像包含了创建快照时卷中所有的数据，但实际上一开始快照是不包含数据的。相比复制数据到快照中，LVM只是简单地标记创建快照的时间点，然后对该快照请求读数据时，实际上是从原始卷中读取的。因此，初始的复制基本上是一个瞬间就能完成的操作，不管创建快照的卷有多大。

当原始卷中某些数据有变化时，LVM在任何变更写入之前，会复制受影响的块到快照预留的区域中。LVM不保留数据的多个“老版本”，因此对原始卷中变更块的额外写入并不需要对快照做其他更多的工作。换句话说，对每个块只有第一次写入才会导致写时复制到预留的区域。

现在，在快照中请求这些块时，LVM会从复制块中而不是从原始卷中读取。所以，可以继续看到快照中相同时间点的数据而不需要阻塞任何原始卷。图15-1描述了这个方案。

快照会在/dev目录下创建一个新的逻辑卷，可以像挂载其他设备一样挂载它。

理论上讲，这种技术可以对一个非常大的卷做快照，而只需要非常少的物理存储空间。但是，必须设置足够的空间，保证在快照打开时，能够保存所有期望在原始卷上更新的块。如果不预留足够的写时复制空间，当快照用完所有的空间后，设备就会变得不可用。这个影响就像拔出一个外部设备：任何从设备上读的备份工作都会因I/O错误而失败。

先决条件和配置

创建一个快照的消耗几乎微不足道，但还是需要确保系统配置可以让你获取在备份瞬间的所有需要的文件的一致性副本。首先，确保系统满足下面这些条件。

• 所有的InnoDB文件（InnoDB的表空间文件和InnoDB的事务日志）必须是在单个逻辑卷（分区）。你需要