1.   概述

很多企业选择MySQL都会担心它的数据丢失问题，从而选择Oracle，但是其实并不十分清楚什么情况下，各种原因导致MySQL会丢失部分数据。本文不讨论Oracle和MySQL的优劣，仅仅关注MySQL丢失数据的几种情况。希望能够抛砖引玉，让各位MySQL大牛们梳理出MySQL最安全或者性价比合适的适合各种应用场景的方案。

2.   问题定义

一般我们希望把一系列的数据作为一个原子操作，这样的话，这一系列操作，要么提交，要么全部回滚掉。

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

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

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

3.   INNODB事务数据丢失

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

3.1.  INNODB事务基本原理

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

InnoDB跟Oracle非常类似，使用日志先行的策略，将数据的变更在内存中完成，并且将事务记录成redo，转换为顺序IO高效的提交事务。这里日志先行，也就是说，日志记录到数据库以后，对应的事务就可以返回给用户，表示事务完成。但是实际上，这个数据可能还只在内存中修改完成，并没有刷到磁盘上去，俗称“还没有落地”。内存是易失的，如果在数据“落地”之前，机器挂了，那么这部分数据就丢失了。而数据库怎么保证这些数据还是能够找回来列？否则，用户提交了一个事务，数据库响应请求并回应为事务“提交成功”，数据库重启以后，这部分修改数据的却回到了事务提交之前的状态。

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

InnoDB和Oracle都是利用redo来保证数据一致性的。如果你有从数据库新建一直到数据库挂掉的所有redo，那么你可以将数据完完整整的重新build出来。但是这样的话，速度肯定很慢。所以一般每隔一段时间，数据库会做一个checkpoint的操作，做checkpoint的目的就是为了让在该时刻之前的所有数据都”落地”。这样的话，数据库挂了，内存中的数据丢了，不用从最原始的位置开始恢复，而只需要从最新的checkpoint来恢复。将已经提交的所有事务变更到具体的数据块中，将那些未提交的事务回滚掉。

3.3.  INNODB REDO日志

这样的话，保证事务的redo日志刷到磁盘就成了事务数据是否丢失的关键。而InnoDB为了保证日志的刷写的高效，使用了内存的log buffer，另外，由于InnoDB大部分情况下使用的是文件系统，（linux文件系统本身也是有buffer的）而不是直接使用物理块设备，这样的话就有两种丢失日志的可能性：日志保存在log_buffer中，机器挂了，对应的事务数据就丢失了；日志从log buffer刷到了linux文件系统的buffer，机器挂掉了，对应的事务数据就丢失了。当然，文件系统的缓存刷新到硬件设备，还有可能被raid卡的缓存，甚至是磁盘本身的缓存保留，而不是真正的写到磁盘介质上去了。这个就不在我们这次讨论的范围内了。

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

3.4.  INNODB_FLUSH_LOG_AT_TRX_COMMIT

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

默认，innodb_flush_log_at_trx_commit=1，表示在每次事务提交的时候，都把log buffer刷到文件系统中去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。

为了提高效率，保证并发，牺牲一定的数据一致性。innodb_flush_log_at_trx_commit还可以设置为0和2。

innodb_flush_log_at_trx_commit=0时，每隔一秒把log buffer刷到文件系统中去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，可能丢失1秒的事务数据。

innodb_flush_log_at_trx_commit=2时，在每次事务提交的时候会把log buffer刷到文件系统中去，但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处就是，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。MySQL 5.6.6以后，这个“1秒”的刷新还可以用innodb_flush_log_at_timeout 来控制刷新间隔。

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

引用 应元 的一个图如下：