概念描述

在生产环境中，可能会出现这种情况，由于主库的二进制日志文件未刷新，主服务器不正常关闭（例如崩溃）可能会导致主库的二进制日志的最终位置小于从库读取的最新位置。这可能会导致从库在主恢复时无法进行复制，sync_binlog=1 有助于最大限度地减少此问题，因为它会使主库更频繁地刷新其二进制日志。为了在使事务在复制过程中获得最大可能的持久性和一致性，建议设置 innodb_flush_log_at_trx_commit=1，通过此设置，使 InnoDB 重做日志缓冲区在每次事务提交时写入日志文件，并将日志文件刷新到磁盘。

一. sync_binlog

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。
（1）sync_binlog=0，默认设置，表示每 sync_binlog 次事务提交后，MySQL 调用文件系统的刷新操作将缓存刷下去，这时候的性能是最好的，但是风险也是最大的。因为一旦系统 Crash，在 binlog_cache 中的所有 binlog 信息都会被丢失。
（2）sync_binlog=1，最安全的设置，表示每次事务提交，MySQL 都会把 binlog 刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失 1 个事务的数据。但是binlog 虽然是顺序 IO，但是设置 sync_binlog=1，多个事务同时提交，同样也非常的影响 MySQL 和 IO 性能。虽然可以通过 group commit 的补丁缓解，但是刷新的频率过高对 IO 的影响也非常大。对于高并发事务的系统来说，“ sync_binlog ”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
（3）sync_binlog=n，当每进行n次事务提交之后，MySQL 将进行一次fsync之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。

二. innodb_flush_log_at_trx_commit

（1）完全符合 ACID 需要默认设置 1。每次事务提交时都会将日志写入并刷新到磁盘。写入最慢，最大安全性。
（2）设置为 0 时，每秒将日志写入并刷新到磁盘一次。写入速度最快，未刷新日志的事务可能会在崩溃中丢失。
（3）设置为 2 时，日志会在每次事务提交后写入，并每秒刷新到磁盘一次。写入速度较快，未刷新日志的事务可能会在崩溃中丢失。
设置0和2都不能 100% 保证每秒一次的刷新。由于大量 DDL 导致日志刷新可能会频繁地发生，有时由于调度问题，刷新可能也会不那么频繁。如果日志每秒刷新一次，则在崩溃中最多可能会丢失一秒的事务。如果日志刷新频率高于或低于每秒一次，则可能丢失的事务量也会相应变化。

测试验证

测试1
innodb_flush_log_at_trx_commit=2
sync_binlog=1000
测试2
innodb_flush_log_at_trx_commit=1
sync_binlog=1000
测试3
innodb_flush_log_at_trx_commit=1
sync_binlog=1

综上所述，当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 )， innodb_flush_log_at_trx_commit=2 时，MySQL 的写操作性能最好。

知识总结

大多数情况下，为了追求高并发，提升性能，对数据的一致性可能并没有很严格的要求，但是对于金融类服务，比如订单、交易、充值、支付消费系统的应用，建议 sync_binlog 和 innodb_flush_log_at_trx_commit 设置为1。最后，我们对系统的优化需要寻找一个平衡点，合适的才是最好的，还是需要根据不同的业务场景需求，将两个参数做组合调整，以便使数据库的性能达到最优化。

参考文档