mysql 开发进阶篇系列 19 MySQL Server(innodb_flush_log_at_trx_commit与sync_binlog)
一. innodb_flush_log_at_trx_commit
这个参数名称有个log，一看就是与日志有关。是指：用来控制缓冲区(log buffer)中的数据写入到日志文件(log file)，以及日志文件数据刷新到磁盘(flush)的操作时机。对这个参数的设置值，可以对数据库在性能与数据安全之间，进行折中。
参数值解释：
当参数是0：日志缓冲数据会，每秒一次地写入到日志文件，并且把日志文件刷新到磁盘操作。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
当参数是1：每次事务提交时，日志缓冲被写到日志文件，并且对日志文件做磁盘刷新操作，该模式为系统默认。但由于每次事务都需要进行磁盘I/O，所以也最慢。
当参数是2：每次事务提交时，日志缓冲被写到日志文件，但不对日志文件做磁盘刷新操作。对日志文件每秒执行一次，刷到磁盘操作。
当设置innodb_flush_log_at_trx_commit=1时， 是默认值，也是最安全的设置，但是在这种模式下性能有一定的损失。 如果设置成0或者2 性能会有所改善，但有数据丢失的风险。
设置成0则数据库崩溃的时候，那些没有被写入日志文件的事务丢失，最多丢失1秒钟的事务，是最不安全的，但也是效率最高的。
设置成2则只是没有刷新到磁盘，但已经写入日志文件，所以只要操作系统没有崩溃， 那么并没有数据丢失, 比设置成0更安全。
在mysql官方中， 为了确保事务的持久性和复制设置的一致性，都是建议将这个参数值设置为1。对于一些数据一致性和完整性要求不高的应用，配置为 2 就足够了；如果为了最高性能，可以设置为 0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为 1。
|
参数值 |
数据安全性 |
I/O性能 |
|
0 |
安全最差。当数据库崩溃,有丢失1秒钟的事务风险 |
最优 |
|
1 |
安全最好。无丢失数据 |
最差 |
|
2 |
安全折中。当操作系统崩溃, 有丢失1秒钟的事务风险 |
折中 |
1.1 查看日志提交方式
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
1.2 修改参数值
还是一样找到my.cnf, 修改参数值
[root@xuegod64 ~]# cd /etc
[root@xuegod64 etc]# vim my.cnf
[root@xuegod64 ~]# systemctl stop mysqld.service
[root@xuegod64 ~]# systemctl start mysqld.service
-- 再次查看 SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
二. sync_binlog
这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。在MySQL中系统默认的设置是sync_binlog=1。对于“sync_binlog”参数的各种设置的说明如下：
sync_binlog=0：当事务提交之后，不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定。
sync_binlog=n：每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上。
而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。|
-- 查看binlog写入方式 SHOW VARIABLES LIKE 'sync_binlog';
总结： 在数据安全与性能以日志文件作为出发点时，我认为功能上与sql server 的数据恢复模式比较相像，但实现的思路是不一样的。
innodb_flush_log_at_trx_commit和sync_binlog是MySQL innodb引擎的两个重要的参数，其中innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer刷新到磁盘，sync_binlog是将二进制日志文件刷新到磁盘上。
innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差，
网上也有说将innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000。有说对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。总体上还是要根据业务来判断，在性能和安全上做个选择。