MySQL Development Advanced article series MySQL Server (Innodb_flush_log_at_trx_commit and Sync_binlog)

I. Innodb_flush_log_at_trx_commit

This parameter name has a log, which is related to the log. Is the time to use to control the writing of data in the buffer (log buffer) to the log file, and the log file data flushing to disk (flush). The setting value of this parameter can compromise the database between performance and data security.

Parameter value Explanation:

When the parameter is 0: the log buffer data is written to the log file once per second, and the log file is flushed to the disk operation. This mode does not actively trigger writes to disk when a transaction commits.

When the parameter is 1: Each time the transaction commits, the log buffer is written to the log file and a disk flush operation is made to the log file, which is the system default. However, because disk I/O is required for each transaction , it is also the slowest.

When the parameter is 2: Each time the transaction commits, the log buffer is written to the log file, but the log file is not disk-flushed. Performs a brush-to-disk operation on the log file once per second.

When setting innodb_flush_log_at_trx_commit=1, it is the default and the safest setting, but there is a certain loss of performance in this mode. If set to 0 or 2 performance will improve, but there is a risk of data loss.
set to 0 when the database crashes, those transactions that are not written to the log file are lost, and up to 1 seconds of transaction loss is the least secure, but also the most efficient. The
set to 2 is simply not flushed to disk, but has been written to the log file, so as long as the operating system does not crash, then there is no data loss, than set to 0 more secure.
in MySQL official, it is recommended to set this parameter value to 1 in order to ensure the consistency of the transaction persistence and replication settings. For some applications where data consistency and integrity requirements are low, configuring 2 is sufficient and can be set to 0 for maximum performance. Some applications, such as payment services, require a high level of consistency and integrity, so even the slowest, preferably set to 1.

Parameter values	Data security	I/O performance
0	Security is the worst. When the database crashes, there is a transaction risk of losing 1 seconds	Optimal
1	Security is best. No loss of data	Worst
2	Security compromise. When the operating system crashes, there is a transaction risk of losing 1 seconds	Compromise

1.1 View Log Submission methods

 like ' Innodb_flush_log_at_trx_commit ';

　　　　

1.2 Modifying parameter values

Still the same. Find my.cnf, modify parameter values

[Email protected] ~]# cd/etc

[Email protected] etc]# vim my.cnf
　　　　

[Email protected] ~]# Systemctl stop Mysqld.service

[Email protected] ~]# systemctl start Mysqld.service

-- Check Again  like ' Innodb_flush_log_at_trx_commit ';

　　　　

Two. Sync_binlog

This parameter is critical to the MySQL system, which affects not only the performance of Binlog to MySQL, but also the integrity of the data in MySQL. In MySQL, the system default setting is sync_binlog=1. The descriptions for the various settings for the "Sync_binlog" parameter are as follows:

Sync_binlog=0: After a transaction commits, the data on the binary log file is not actively flushed to disk, but is determined by the operating system.

Sync_binlog=n: After each binary log file writes n SQL or n transactions, the data of the binary log file is flushed to disk.

When set to "1", it is the safest and most performance-depleting setting. Because when set to 1, even if the system is Crash, a transaction that is not completed in Binlog_cache is lost at most, without any substantial impact on the actual data. |

--   View Binlog write like 'sync_binlog';

　　

Summary: When it comes to data security and performance as a starting point for log files, I think functionally similar to the data recovery model of SQL Server, but the idea of implementation is different.
Innodb_flush_log_at_trx_commit and Sync_binlog are two important parameters of the MySQL InnoDB engine, where Innodb_flush_log_at_trx_commit is the transaction log from InnoDB Log buffer is flushed to disk, Sync_binlog is the binary log file flushed to disk.
Innodb_flush_log_at_trx_commit and Sync_binlog two parameters are key parameters to control MySQL disk write policy and data security, when two parameters are set to 1 write performance is the worst,
Online there are also said to be innodb_flush_log_at_trx_commit=2,sync_binlog=500 or 1000. It is said that for systems with high concurrent transactions, the system write performance gap of "Sync_binlog" set to 0 and set to 1 can be as much as 5 times times or more. In general, it is up to the business to decide on the performance and security to make a choice.

MySQL Development Advanced article series MySQL Server (Innodb_flush_log_at_trx_commit and Sync_binlog)