sync_binlog
Controls how often the MySQL server synchronizes the binary log to disk.
sync_binlog=0: Disables syncwww.diuxie.comhronization 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 =0: Turn off the use of MySQL Server to synchronize the binary log to disk. Persistence of the binary log to disk depends on the operating system.
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 prpared 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 =1: Enables synchronization of the binary log to disk before the transaction commits. This approach is the most secure for the database, but has a serious impact on data performance due to the increased disk IO. In the event of a power loss or unexpected shutdown of the host, the transaction is prepared. The uncommitted transactions are rolled back when the machine is restarted to prevent transaction loss.
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.
Sync_binlog =N: N is a different value than 0 and 1. In this case, N committed binlogs are collected and the data is persisted to disk. This improves the performance of the database, but there is a risk that transactions that are not persisted to disk will be lost when the server goes down
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.
Sync_binlog and innodb_flush_log_at_trx_commit are both set to 1 for database persistence and consistency.
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.