MySQL HA架構下innodb_flush_log_at_trx_commit及sync_binlog參數，innodbtrx

MySQL HA架構下innodb_flush_log_at_trx_commit及sync_binlog參數，innodbtrx

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