编者注：本文为历史博文归档；涉及 JDK、框架与工具链版本请以当前官方文档为准。引用外链图片可能失效，阅读时请注意时效性。

1. 概述

很多企业在选型数据库时，会因为担心 MySQL 的数据丢失问题而转向 Oracle。但实际上，许多人并不清楚在何种情况下、由何种原因会导致 MySQL 丢失部分数据。本文不讨论 Oracle 和 MySQL 的优劣，仅仅关注 MySQL 丢失数据的几种典型情况。希望能够抛砖引玉，让各位 MySQL 专家梳理出最安全或性价比合适的、适合各种应用场景的方案。

2. 问题定义

一般我们希望将一系列数据操作作为一个原子操作（Atomic Operation）。这意味着这一系列操作要么全部提交，要么全部回滚。

当我们提交一个事务时，数据库要么告知事务提交成功，要么告知提交失败。

数据库为了效率等原因，数据可能只保存在内存中，并未真正写入磁盘。如果数据库响应为“提交成功”，但由于数据库挂掉、操作系统崩溃、数据库主机故障等任何问题，导致这次“提交成功”的事务对数据库的修改没有生效，那么我们认为这个事务的数据丢失了。这对银行或支付宝等业务场景来说是不可接受的。因此，保证数据不丢失也是数据库选型的一个重要衡量指标。

MySQL 的架构与普通数据库架构最大的差异在于它使用插件式的存储引擎（Storage Engine）。数据的存取由存储引擎负责。要了解 MySQL 数据丢失的问题，就需要从 MySQL Server 层 和 InnoDB（目前最流行的支持事务的存储引擎）分别进行分析。

3. InnoDB 事务数据丢失

首先，我们来看一下 InnoDB 事务数据丢失的情况。

3.1. InnoDB 事务基本原理

InnoDB 的事务提交需要写入 Undo Log、Redo Log，以及真正的数据页。专业的介绍可以参考 丁奇 和 云华 的两篇文章。我们这里通俗地简单介绍一下。

InnoDB 跟 Oracle 非常类似，使用日志先行（Write-Ahead Logging）的策略，将数据的变更在内存中完成，并且将事务记录成 Redo Log，转换为顺序 I/O 高效地提交事务。所谓“日志先行”，也就是说日志记录到数据库以后，对应的事务就可以返回给用户，表示事务完成。但是实际上，这个数据可能还只在内存中修改完成，并没有刷到磁盘上去，俗称“还没有落地”。内存是易失的，如果在数据“落地”之前机器挂了，那么这部分数据就丢失了。

而数据库怎么保证这些数据还是能够找回来？否则，用户提交了一个事务，数据库响应请求并回应为事务“提交成功”，数据库重启以后，这部分修改数据却回到了事务提交之前的状态。

3.2. InnoDB 事务崩溃恢复基本原理

InnoDB 和 Oracle 都是利用 Redo Log 来保证数据一致性的。如果你有从数据库新建一直到数据库挂掉的所有 Redo Log，那么你可以将数据完完整整地重新重建出来。但是这样的话，速度肯定很慢。所以一般每隔一段时间，数据库会做一个 Checkpoint 的操作。做 Checkpoint 的目的就是为了让在该时刻之前的所有数据都“落地”。

这样的话，数据库挂了，内存中的数据丢了，不用从最原始的位置开始恢复，而只需要从最新的 Checkpoint 来恢复。将已经提交的所有事务变更到具体的数据块中，将那些未提交的事务回滚掉。

3.3. InnoDB Redo 日志

这样的话，保证事务的 Redo 日志刷到磁盘就成了事务数据是否丢失的关键。而 InnoDB 为了保证日志的刷写高效，使用了内存的 Log Buffer。另外，由于 InnoDB 大部分情况下使用的是文件系统（Linux 文件系统本身也是有 Buffer 的），而不是直接使用物理块设备，这样的话就有两种丢失日志的可能性：

  1. 日志保存在 Log Buffer 中，机器挂了，对应的事务数据就丢失了；
  2. 日志从 Log Buffer 刷到了 Linux 文件系统的 Buffer，机器挂掉了，对应的事务数据就丢失了。

当然，文件系统的缓存刷新到硬件设备，还有可能被 RAID 卡的缓存，甚至是磁盘本身的缓存保留，而不是真正的写到磁盘介质上去了。这个就不在我们这次讨论的范围内了。

InnoDB 的日志你还可以参考这篇 文章。

3.4. innodb_flush_log_at_trx_commit

所以 InnoDB 有一个特别的参数用于设置这两个缓存的刷新：innodb_flush_log_at_trx_commit。

  • 默认值 1：表示在每次事务提交的时候，都把 Log Buffer 刷到文件系统中去，并且调用文件系统的"flush"操作将缓存刷新到磁盘上去。这样的话，数据库对 I/O 的要求就非常高了。如果底层的硬件提供的 IOPS 比较差，那么 MySQL 数据库的并发很快就会由于硬件 I/O 的问题而无法提升。
  • 设置为 0：为了提高效率，保证并发，牺牲一定的数据一致性。每隔一秒把 Log Buffer 刷到文件系统中去，并且调用文件系统的"flush"操作将缓存刷新到磁盘上去。这样的话，可能丢失 1 秒的事务数据。
  • 设置为 2：在每次事务提交的时候会把 Log Buffer 刷到文件系统中去，但是每隔一秒调用文件系统的"flush"操作将缓存刷新到磁盘上去。如果只是 MySQL 数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失 1 秒之内的事务数据。

这样的好处就是，减少了事务数据丢失的概率，而对底层硬件的 I/O 要求也没有那么高（Log Buffer 写到文件系统中，一般只是从 Log Buffer 的内存转移到文件系统的内存缓存中，对底层 I/O 没有压力）。MySQL 5.6.6 以后，这个"1 秒”的刷新还可以用 innodb_flush_log_at_timeout 来控制刷新间隔。

在大部分应用环境中，应用对数据的一致性要求并没有那么高，所以很多 MySQL DBA 会设置 innodb_flush_log_at_trx_commit=2，这样的话，数据库就存在丢失最多 1 秒的事务数据的风险。

引用 应元 的一个图如下：

innodb_flush_log_at_commit

4. 数据库复制导致数据丢失

MySQL 相比其他数据库更适用于互联网的其中一个重要特性就是 MySQL 的复制（Replication）。对于互联网这种需要提供 7*24 小时不间断服务的要求，MySQL 提供异步的数据同步机制。利用这种复制同步机制，当数据库主库无法提供服务时，应用可以快速切换到跟它保持同步的一个备库中去。备库继续为应用提供服务，从而不影响应用的可用性。

这里有一个关键的问题，就是应用切换到备库访问，备库的数据需要跟主库的数据一致才能保证不丢失数据。由于目前 MySQL 还没有提供全同步的主备复制解决方案，所以这里也是可能存在数据丢失的情况。

目前 MySQL 提供两种主备同步的方式：异步（Asynchronous） 和 半同步（Semi-sync）。

4.1. MySQL 复制原理简介

MySQL 复制的原理简介如下：MySQL 主库在事务提交时写 Binlog，并通过 sync_binlog 参数来控制 Binlog 刷新到磁盘“落地”。而备库通过 I/O 线程从主库拉取 Binlog，并记录到本地的 Relay Log 中；由本地的 SQL 线程再将 Relay Log 中的数据应用到本地数据库中。

  • 异步的方式下：几个线程都是独立的，相互不依赖。
  • 半同步的方式下：主库的事务提交需要保证至少有一个备库的 I/O 线程已经拉到了数据，这样保证了至少有一个备库有最新的事务数据，避免了数据丢失。这里称为半同步，是因为主库并不要求 SQL 线程已经执行完成了这个事务。

半同步在 MySQL 5.5 才开始提供，并且可能引起并发和效率的一系列问题。比如只有一个备库，备库挂掉了，那么主库在事务提交 10 秒（rpl_semi_sync_master_timeout 控制）后，才会继续，之后变成传统的异步方式。所以目前在生产环境下使用半同步的比较少。

在异步方式下，如何保证数据尽量不丢失就成了主要问题。这个问题其实就是如何保证数据库的 Binlog 不丢失，尽快将 Binlog 落地。这样就算数据库挂掉了，我们还可以通过 Binlog 来将丢失的部分数据手工同步到备库上去（MHA 会自动抽取缺失的部分补全备库）。

图示如下：

mysql_replication

4.2. sync_binlog

这个问题就跟上一个 innodb_flush_log_at_trx_commit 的问题类似了。MySQL 提供一个 sync_binlog 参数来控制数据库的 Binlog 刷到磁盘上去。虽然 Binlog 也有 Binlog Cache，但是 MySQL 并没有控制 Binlog Cache 同步到文件系统缓存的相关考虑，所以我们这里不涉及 Binlog Cache。

  • 默认 sync_binlog=0：表示 MySQL 不控制 Binlog 的刷新，由文件系统自己控制它的缓存的刷新。
  • sync_binlog>0：表示每 sync_binlog 次事务提交，MySQL 调用文件系统的刷新操作将缓存刷下去。

最安全的就是 sync_binlog=1 了，表示每次事务提交，MySQL 都会把 Binlog 刷下去。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失 1 个事务的数据。但是 Binlog 虽然是顺序 I/O，但是设置 sync_binlog=1，多个事务同时提交，同样很大地影响 MySQL 和 I/O 性能。虽然可以通过 Group Commit 的补丁缓解，但是刷新的频率过高对 I/O 的影响也非常大。

所以很多 MySQL DBA 设置的 sync_binlog 并不是最安全的 1，而是 100 或者是 0。这样牺牲一定的一致性，可以获得更高的并发和性能。

5. MySQL 和 InnoDB 协同

5.1. 两段式事务提交

最后我们需要讨论一下上述两个参数对应的 Redo Log 和 Binlog 协同的问题。这两个 Log 都影响数据丢失，但是他们分别在 InnoDB 和 MySQL Server 层维护。由于一个事务可能使用两种事务引擎，所以 MySQL 用两段式事务提交（Two-Phase Commit, 2PC）来协调事务提交。我们先简单了解一下两段式事务提交的过程：

transaction_xa

第一阶段：

  1. 首先，协调者在自身节点的日志中写入一条日志记录，然后所有参与者发送消息 prepare T，询问这些参与者（包括自身），是否能够提交这个事务；
  2. 参与者在接受到这个 prepare T 消息以后，会根据自身的情况，进行事务的预处理。如果参与者能够提交该事务，则会将日志写入磁盘，并返回给协调者一个 ready T 信息，同时自身进入预提交状态；如果不能提交该事务，则记录日志，并返回一个 not commit T 信息给协调者，同时撤销在自身上所做的数据库修改；
  3. 参与者能够推迟发送响应的时间，但最终还是需要发送的。

第二阶段：

  1. 协调者会收集所有参与者的意见。如果收到参与者发来的 not commit T 信息，则标识着该事务不能提交，协调者会将 Abort T 记录到日志中，并向所有参与者发送一个 Abort T 信息，让所有参与者撤销在自身上所有的预操作；
  2. 如果协调者收到所有参与者发来 prepare T 信息，那么协调者会将 Commit T 日志写入磁盘，并向所有参与者发送一个 Commit T 信息，提交该事务。若协调者迟迟未收到某个参与者发来的信息，则认为该参与者发送了一个 VOTE_ABORT 信息，从而取消该事务的执行；
  3. 参与者接收到协调者发来的 Abort T 信息以后，参与者会终止提交，并将 Abort T 记录到日志中；如果参与者收到的是 Commit T 信息，则会将事务进行提交，并写入记录。

一般情况下，两阶段提交机制都能较好地运行。当在事务进行过程中，有参与者宕机时，它重启以后，可以通过询问其他参与者或者协调者，从而知道这个事务到底提交了没有。当然，这一切的前提都是各个参与者在进行每一步操作时，都会事先写入日志。

具体的介绍可以参考 《事务和两阶段提交》 以及 《分布式事务设计 - 两阶段提交》。

5.2. innodb_support_xa

innodb_support_xa 可以开关 InnoDB 的 XA 两段式事务提交。默认情况下，innodb_support_xa=true，支持 XA 两段式事务提交。此时 MySQL 首先要求 InnoDB prepare，对应的 Redo Log 将写入 Log Buffer；如果有其他的引擎，其他引擎也需要做事务提交的 prepare，然后 MySQL Server 将 Binlog 写入；并通知各事务引擎真正 commit；InnoDB 将 commit 标志写入，完成真正的提交，响应应用程序为提交成功。这个过程中任何出错将导致事务回滚，响应应用程序为提交失败。也就是说，在这种情况下，基本不会出错。

但是由于 XA 两段式事务提交导致多余 flush 等操作，性能影响会达到 10%。所有为了提高性能，有些 DBA 会设置 innodb_support_xa=false。这样的话，Redo Log 和 Binlog 将无法同步，可能存在事务在主库提交，但是没有记录到 Binlog 的情况。这样也有可能造成事务数据的丢失。

综上，我们列举了影响 InnoDB 数据丢失的参数 innodb_flush_log_at_trx_commit，影响 MySQL 复制数据丢失的 sync_binlog，以及由于 MySQL 和 InnoDB 需要协调而可能导致数据丢失的参数 innodb_support_xa。

说明：本文内容基于 MySQL 5.5/5.6 版本时期的技术背景整理。后续版本（如 MySQL 5.7/8.0）在半同步复制、Group Commit 及事务处理机制上均有优化与改进，具体参数行为请以官方最新文档为准。