原文地址：http://hatemysql.com/tag/sync_binlog/
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。
mysql innodb_flush_log_at_trx_commit翻译
知道innodb_flush_log_at_trx_commit的意思，但是对它取值0，1，2一直有点模糊不清。特地找了MySQL 5.1的refrence，自己翻译一下。虽然，也有官方的中文版翻译，但是不好意思，有点不相信它。
英文原文如下：
innodb_flush_log_at_trx_commit
Command-Line Format –innodb_flush_log_at_trx_commit[=#]
Config-File Format innodb_flush_log_at_trx_commit
Option Sets Variable Yes, innodb_flush_log_at_trx_commit
Variable Name innodb_flush_log_at_trx_commit
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Type numeric
Default 1
Valid Values 0, 1, 2
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行；但是，这种模式下，在事务提交的时候，不会有任何动作。如果innodb_flush_log_at_trx_commit设置为1(默认值)，log buffer每次事务提交都会写入log file，并且，flush刷到磁盘中去。如果innodb_flush_log_at_trx_commit设置为2，log buffer在每次事务提交的时候都会写入log file，但是，flush(刷到磁盘)操作并不会同时进行。这种模式下，MySQL会每秒一次地去做flush(刷到磁盘)操作。注意：由于进程调度策略问题，这个“每秒一次的flush(刷到磁盘)操作”并不是保证100%的“每秒”。
The default value of 1 is the value required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB’s crash recovery is not affected and thus crash recovery does work regardless of the value.
默认值1是为了ACID (atomicity, consistency, isolation, durability)原子性，一致性，隔离性和持久化的考虑。如果你不把innodb_flush_log_at_trx_commit设置为1，你将获得更好的性能，但是，你在系统崩溃的情况，可能会丢失最多一秒钟的事务数据。当你把innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。如果你把innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。(下面的这句话到底是针对innodb_flush_log_at_trx_commit为2说的，还是针对前面这一整段说的，我就搞不清楚了，下次问问编写这一段文档的MySQL的人去。感觉是针对整段的：就是说InnoDB的crash recovery会利用log file来恢复数据文件，跟innodb_flush_log_at_trx_commit的值没有关系，管你这个值怎么设置的，我从log file拿到多少数据，就恢复多少数据。)InnoDB的crash recovery崩溃恢复机制并不受这个值的影响，不管这个值设置为多少，crash recovery崩溃恢复机制都会工作。
For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.
为了在使用InnoDB事务的搭建复制环境中，达到最大的持久化和一致性，你需要在你的master主机的my.cnf中设置innodb_flush_log_at_trx_commit = 1并且设置sync_binlog = 1。
Caution
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.
注意：
很多操作系统和一些磁盘硬件系统并不会真正的做flush-to-disk刷新到磁盘的这个操作。他们即使并没有真正刷到磁盘也会告诉mysqld说flush刷新到磁盘的操作已经完成了。这样的话，即使innodb_flush_log_at_trx_commit设置为1，也不能保证事务的持久化，最糟的情况下，一个主机掉电，就有可能导致InnoDB数据库崩溃。你可以考虑在SCSI磁盘控制器里面或者磁盘本身中，使用带蓄电池后备电源的磁盘缓存disk cache，来提高文件刷新操作的速度，使得这个操作更加安全。你同样可以尝试使用Unix的hdparm命令来阻止硬件缓存hardware cache的写磁盘缓存操作，或者使用其他硬件提供商hardware vendor提供的命令来避免写磁盘缓存。
这里既然提到了sync_binlog就顺便把它也翻译一下。
sync_binlog
Command-Line Format –sync-binlog=#
Config-File Format sync_binlog
Option Sets Variable Yes, sync_binlog
Variable Name sync_binlog
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Platform Bit Size 32
Type numeric
Default 0
Range 0-4294967295
Permitted Values
Platform Bit Size 64
Type numeric
Default 0
Range 0-18446744073709547520
If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk — in this case, the server relies on the operating system to flush the binary log’s contents from to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).
当sync_binlog变量设置为大于0的值时，MySQL在每次“sync_binlog”这么多次写二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。sync_binlog的默认值是0，这种模式下，MySQL不会同步到磁盘中去。这样的话，MySQL依赖操作系统来刷新二进制日志binary log，就像操作系统刷其他文件的机制一样。当sync_binlog变量设置为1是最安全的，因为在crash崩溃的情况下，你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是，这也是最慢的一种方式（除非磁盘有使用带蓄电池后备电源的缓存cache，使得同步到磁盘的操作非常快）。
找了一下man fdatasync:
fdatasync() flushes all data buffers of a file to disk (before the system call returns). It resembles fsync() but is not required to update the metadata such as access time.
fdatasync() (在系统调用system call返回前)将文件中所有的数据缓存区data buffers都flush刷到磁盘中去。它类似于fsync()函数，但是它不会更新元数据metadata：比如最后访问时间等。