How to distinguish MySQL's innodb_flush_log_at_trx_commit and sync_binlog

The two parameters innodb_flush_log_at_trx_commit and sync_binlog are key parameters for controlling MySQL disk write strategy and data security.

show variables like "innodb_flush_log_at_trx_commit";

innodb_flush_log_at_trx_commit:

0: MySQL's main_thread writes the redo log in the storage engine log buffer to the log file every second, and calls the file system's sync operation to refresh the log to disk.

1: Each time a transaction is committed, the redo log in the storage engine log buffer is written to the log file, and the file system's sync operation is called to refresh the log to the disk.

2: Each time a transaction is committed, the redo log in the storage engine log buffer is written to the log file, and the storage engine's main_thread flushes the log to disk every second.

show variables like "sync_binlog";

sync_binlog:

0: The storage engine does not flush binlog to disk, and the operating system's file system controls cache flushing.

1: Each time a transaction is submitted, the storage engine calls the file system's sync operation to refresh the cache. This method is the safest, but has lower performance.

n: When the submitted log group = n, the storage engine calls the file system's sync operation to refresh the cache.

sync_binlog=0 or sync_binlog is greater than 1, the transaction is committed but not yet synchronized to disk. Therefore, in the event of a power failure or operating system crash it is possible that the server has committed some transactions that have not yet been synchronized to the binary log. Therefore it is not possible to perform routine recovery of these transactions, as they will be lost in the binary log.

It is safest when both innodb_flush_log_at_trx_commit and sync_binlog are 1. In the event of a mysqld service crash or a server host crash, the binary log may lose at most one statement or one transaction. However, you cannot have your cake and eat it too. Double 1, 1 will lead to frequent IO operations, so this mode is also the slowest way.

In actual use, it is necessary to consider the business needs for performance and security, and comprehensively consider the settings of the two parameters. The above picture shows the parameters of our online machine.

The above is the details of how to distinguish MySQL's innodb_flush_log_at_trx_commit and sync_binlog. For more information about MySQL's innodb_flush_log_at_trx_commit and sync_binlog, please pay attention to other related articles on 123WORDPRESS.COM!