MySQL--参数sync_binlog

Sync_binlog

 

By default, the binary log is not synchronized to disk at each write. So if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, use the sync_binlog system variable to synchronize the binary log to disk after every N commit groups. SeeSection 5.1.7, “Server System Variables”. The safest value for sync_binlog is 1, but this is also the slowest. Even with sync_binlog set to 1, there is still the chance of inconsistency between the table content and binary log content in case of a crash.

 

默认情况下，二进制日志不会在每次写入的时候刷新到磁盘，所以如果操作系统宕机（不仅是mysql服务器），二进制日志的最后一个语句可能会丢失。

为了防止这种情况，使用sync_binlog系统变量在每n此提交组之后将二进制日志同步到磁盘，最安全的值是1，但这也是最慢的，即使sync_binlog设置为1，在崩溃的情况下，表内容和二进制日志内容之间仍然存在不一致的可能性

 

 

For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes many prepared transactions to the binary log in sequence, synchronizes the binary log, and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. Such an issue is resolved assuming --innodb_support_xa is set to 1, the default. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized. For this option to provide a greater degree of safety, the MySQL server should also be configured to synchronize the binary log and the InnoDB logs to disk before committing the transaction. The InnoDB logs are synchronized by default, and sync_binlog=1 can be used to synchronize the binary log. 

 

例如，如果使用innodb表并且mysql服务器处理commit语句，它会按顺序将许多已经准备好的事务写入二进制、同步二进制日志，然后将事务提交到innodb。如果服务器在这两个操作之间宕机，事务会在服务器重启之后回滚，但仍然存在于二进制日志中。

将--innodb_support_xa设置为1（默认值）可以解决。

虽然这个选项与innodb中对xa事务的支持有关，但它确保了二进制日志和innodb数据文件同步。为了提供更高程度的安全性，还应配置mysql服务器在innodb提交事务之间将二进制日志和日志同步到磁盘。innodb日志默认是同步的，并设置且sync_binlog=1可以用于二进制同步

 

 

 

The effect of this option is that at restart after a crash, after doing a rollback of transactions, the MySQL server scans the latest binary log file to collect transaction xid values and calculate the last valid position in the binary log file. The MySQL server then tells InnoDB to complete any prepared transactions that were successfully written to the to the binary log, and truncates the binary log to the last valid position. This ensures that the binary log reflects the exact data of InnoDB tables, and therefore the slave remains in synchrony with the master because it does not receive a statement which has been rolled back.

 

这个选项的作用是服务器（包括mysql）在宕机重启时，在执行事务回滚后，mysql服务器扫描最新的二进制日志文件用以收集事务xid值并计算二进制日志文件中的最后一个有效位置。mysql通知innodb完成所有已经成功写入二进制日志文件的事务，并将二进制日志截断到最后一个有效位置。这样就保证可二进制日志文件能够真实反映innodb表的数据，因为从库并不会收到已经回滚的语句，故而从库依然同主库保持同步。

 

If the MySQL server discovers at crash recovery that the binary log is shorter than it should have been, it lacks at least one successfully committed InnoDB transaction. This should not happen if sync_binlog=1 and the disk/file system do an actual sync when they are requested to (some do not), so the server prints an error message The binary log file_name is shorter than its expected size. In this case, this binary log is not correct and replication should be restarted from a fresh snapshot of the master's data

 

如果mysql在宕机重启之后发现二进制日志比它应有的长度短，那么它至少缺少一个成功提交的事务。

如果sync_binlog=1而且文件系统在请求时执行实际同步，就不会发生这种情况，因而服务器会输出错误消息

The binary log file_name is shorter than its expected size.

在这种情况下，此二进制日志不正确，应从主库的新快照重新启动复制

 

 

sync_binlog参数

Property	Value
Command-Line Format	--sync-binlog=#
System Variable	sync_binlog
Scope	Global
Dynamic	Yes
Type	integer
Default Value	0
Minimum Value	0
Maximum Value	4294967295

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.

 

    不同步日志，mysql依赖操作系统定时将二进制日志刷新到磁盘，就像对任何其他文件一样。

性能最好，但是如果服务器宕机，mysql可能已经提交了尚未同步到二进制文件中的日志

 

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.

    

    每n次提交后，将二进制日志同步到磁盘。如果发生宕机，mysql可能已经提交尚未刷新到二进制日志的事务。

较高的值可能提高性能，但会提高数据丢失的风险

 

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.

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.

 

如果有硬件缓存，应禁止