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_ When binlog = 0: disable the MySQL server to synchronize the binary log to the disk. The binary log is persisted to disk by 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_ When binlog = 1: enable synchronization of binary log to disk before transaction commit. This method is the most secure for the database, but it has a serious impact on data performance because of the increase of disk IO. When the host is powered down or unexpectedly shut down, the transaction is still in the prepared state. After the machine is restarted, these uncommitted transactions will be rolled back, so as to prevent the loss of transactions.
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_ When binlog = n: n is a value different from 0 and 1. In this case, n submitted binlogs are collected and the data is persisted to disk. This will improve the performance of the database, but when the server goes down, these transactions that are not persisted to disk are at risk of loss
For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:
yesMobile GamesIn terms of database persistence and consistency, sync is required_ Binlog and InnoDB_ flush_ log_ at_ trx_ Commit is also set to 1.
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.