content_image/789344.jpg content_image/789357.jpg content_image/789356.jpg
<img src='content_image/789344.jpg'>

Controls how often the MySQL server synchronizes the binary log to disk. 

• sync_binlog=0 : Disables synchronization of the binary log to disk by the MySQL server. Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log. 

• sync_binlog=1 : Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log. 

• sync_binlog= N , where N  is a value other than 0 or 1: The binary log is synchronized to disk after N  binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss. 

For the greatest possible durability and consistency in a replication setup that uses InnoDB  with transactions, use these settings: 

• sync_binlog=1 . 

• innodb_flush_log_at_trx_commit=1 . 

<img src='content_image/789357.jpg'>

• transaction_write_set_extraction 

## Caution 

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld  that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB  data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches. 

<img src='content_image/789356.jpg'>

