Innodb_flush_log_at_trx_commit and Sync_binlog parameters under MySQL ha architecture

HeartBeat + DRBD and MySQL replication are a common way for many businesses to use. For the integrity and consistency of the data, the two architectures need to consider 2 important parameter innodb_flush_log_at_trx_commit and Sync_binlog parameters. This article mainly refers to the MySQL 5.6 Reference manual A detailed description of the 2 parameters listed.

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 andSync-bin Log must be 1. But these kill write performance

? Otherwise necessary Binlog events might is lost on the master. Then slaves can ' t continue replication, and data consistency issues happen

2. Parameter Innodb_flush_log_at_trx_commit

The Innodb_flush_log_at_trx_commit parameter is a global dynamic parameter with a value range of 0,1,2 and a default value of 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 are performed on the log file , but no writes is done at a transaction commit. (Mysqld process crash will cause the last second transaction to be lost)
1	The default value of 1 is required to full ACID compliance. With this value, the log buffer was written out to the log file at each transaction commit and the flush to disk operation is performed the log file.
2	With a value of 2, only the operating system crash or a power outage can erase the last second of transactions. The log buffer is written the-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 are 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. (Operating system crash or power failure causes the last second transaction to be lost)

InnoDB ' s crash recovery works regardless of the value. Transactions is either applied entirely or erased entirely. (InnoDB storage Engine is independent of this parameter, can be resolved by crash recovery, either committed, or rolled back)

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. Parameter Sync_binlog

Sync_binlog is a global dynamic parameter with a value range of 0. 18446744073709547520, the default value is 0.

If The value of this variable was 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 Lo G. However, it is also the slowest choice (unless the disk have a battery-backed cache, which makes synchronization very FA ST).

Innodb_flush_log_at_trx_commit and Sync_binlog parameters under MySQL ha architecture