The innodb_flush_log_at_trx_commit and sync_binlog are two key parameters for controlling the disk write policy and data security of MySQL. Different parameter values have different impacts on performance and data security. They play an important role in determining when data is written to disk and whether to write to binlog synchronously.
Parameter Description
innodb_flush_log_at_trx_commit
Allowed value: 0, 1, or 2
Description: Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. The default value is 1.
• 0: The log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit.
• 1: The log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. This is the default mode on the system.
• 2: The log buffer is written out to the file at each commit, but the flushing to disk operation is not performed on it. However, the flushing on the log file takes place once per second.
Note:
Setting this parameter to 0 is the fastest but less secure option because any mysqld process crash can erase the last second of transactions.
Setting this parameter to 1 is the safest but also the slowest option. In the event of a crash of mysqld service or the server host shutdown, at most one statement or transaction is lost from the log buffer.
Setting this parameter to 2 is faster and more secure than setting this parameter to 0. Only an operating system crash or a power outage can erase the last second of transactions.
sync_binlog
Allowed value: 0 to 4294967295
Description: Synchronize binlog (MySQL flushes binary logs to disks or relies on the operating system).
Note:
By default, the binlog file is not synchronized to the disk in each write. Therefore, in the event of a crash of the operating system or server, the last statement in the binary log may get lost.
To prevent this issue, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize the binlog to disk after every write operation of the N binlog file.
Recommended Configuration Combinations
Combination I
innodb_flush_log_at_trx_commit=1; sync_binlog=1;
Scenario: Suitable for scenarios with extremely high data security requirements where the disk write capacity is sufficient to support the service.
Combination II
innodb_flush_log_at_trx_commit=1; sync_binlog=0;
Scenario: Suitable for scenarios with high data security requirements where the disk write capacity is insufficient to support the service. Slave lagging behind or no replication is allowed.
Combination III
innodb_flush_log_at_trx_commit=2; sync_binlog=0/N(0<N<100);
Scenario: Suitable for scenarios with low data security requirements. A small amount of transaction log loss and replication delay is allowed.
Combination IV
innodb_flush_log_at_trx_commit=0; sync_binlog=0;
Scenario: Suitable for scenarios with limited disk write capability. No replication or long replication delay is allowed.