mysql数据库丢失_讨论MySQL丢失数据的几种情况

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秒的事务数据的风险。

引用 应元 的一个图如下：

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

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

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

目前MySQL提供两种主备同步的方式：异步(asynchronous)和半同步(Semi-sync)

4.1. MYSQL复制原理简介

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

异步的方式下，几个线程都是独立的，相互不依赖。

而在半同步的情况下，主库的事务提交需要保证至少有一个备库的IO线程已经拉到了数据，这样保证了至少有一个备库有最新的事务数据，避免了数据丢失。这里称为半同步，是因为主库并不要求SQL线程已经执行完成了这个事务。

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

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

图示如下：

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虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。

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

5. MYSQL和INNODB协同

5.1. 两段式事务提交

最后我们需要讨论一下上述两个参数对应的redolog和 binlog协同的问题。这两个log都影响数据丢失，但是他们分别在InnoDB和MySQL server层维护。由于一个事务可能使用两种事务引擎，所以MySQL用两段式事务提交来协调事务提交。我们先简单了解一下两段式事务提交的过程

第一阶段：

首先，协调者在自身节点的日志中写入一条的日志记录，然后所有参与者发送消息prepare T，询问这些参与者(包括自身)，是否能够提交这个事务;

参与者在接受到这个prepare T 消息以后，会根据自身的情况，进行事务的预处理，如果参与者能够提交该事务，则会将日志写入磁盘，并返回给协调者一个ready T信息，同时自身进入预提交状态状态;如果不能提交该事务，则记录日志，并返回一个not commit T信息给协调者，同时撤销在自身上所做的数据库改;

参与者能够推迟发送响应的时间，但最终还是需要发送的。

第二阶段：

协调者会收集所有参与者的意见，如果收到参与者发来的not commit T信息，则标识着该事务不能提交，协调者会将Abort T 记录到日志中，并向所有参与者发送一个Abort T 信息，让所有参与者撤销在自身上所有的预操作;

如果协调者收到所有参与者发来prepare T信息，那么协调者会将Commit T日志写入磁盘，并向所有参与者发送一个Commit T信息，提交该事务。若协调者迟迟未收到某个参与者发来的信息，则认为该参与者发送了一个VOTE_ABORT信息，从而取消该事务的执行。

参与者接收到协调者发来的Abort T信息以后，参与者会终止提交，并将Abort T 记录到日志中;如果参与者收到的是Commit T信息，则会将事务进行提交，并写入记录

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

5.2. INNODB_SUPPORT_XA

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

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

综上，我们列举了影响InnoDB数据丢失的参数innodb_flush_log_at_trx_commit，影响MySQL复制数据丢失的sync_binlog，以及由于MySQL和InnoDB需要协调而可能导致数据丢失的参数innodb_support_xa。原文出处：http://www.woqutech.com/?p=769