MySQL HA架构下innodb_flush_log_at_trx_commit及sync_binlog参数

      HeartBeat + DRBD以及MySQL replication是很多企业比较普遍使用的方式。对于数据的完整性和一致性的问题，这两种架构需要考虑2个重要的参数innodb_flush_log_at_trx_commit以及sync_binlog参数。本文主要参考了MySQL 5.6 Reference Manual列出对这2个参数的具体描述。

 

1、Heartbeat + DRBD or replication

Cost: Additional passive master server (not handing any application traffic) is needed

Performance: To make HA really work on DRBD replication environments, innodb-flush-log-at-trx-commit and sync-binlog must be 1. But these kill write performance

Otherwise necessary binlog events might be lost on the master. Then slaves can’t continue replication, and data consistency issues happen

 

2、参数innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit参数为全局动态参数，其取值范围为0，1，2，缺省值为0

value	action
0	With a value of 0, any mysqld process crash can erase the last second of transactions. 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 no writes are done at a transaction commit.(mysqld 进程crash会导致丢失最后一秒的事务)
1	The default value of 1 is required for full ACID compliance. With this value, 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.
2	With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. The log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. Before MySQL 5.6.6, the flushing on the log file takes place once per second. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. As of MySQL 5.6.6, flushing frequency is controlled by innodb_flush_log_at_timeout instead.( 操作系统crash或电源故障导致丢失最后一秒的事务)

 

InnoDB's crash recovery works regardless of the value. Transactions are either applied entirely or erased entirely.(Innodb存储引擎存与该参数无关，可以通过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.

 

3、参数sync_binlog

sync_binlog为全局动态参数，取值范围为0 .. 18446744073709547520，缺省值为0。

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. 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).