innodb_flush_log_at_trx_commit和sync_binlog参数详解
案例
收到邮件报警，mysql主从延迟>1h，导致新的数据无法进行同步。
mysql工作目录下积累了很多的mysqld-relay文件
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.52.35.185
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.011374
Read_Master_Log_Pos: 88856621
Relay_Log_File: mysqld-relay-bin.034139
Relay_Log_Pos: 7119391
Relay_Master_Log_File: mysql-bin.011371
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: performance_schema.*,test.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 7119228
Relay_Log_Space: 2116679529
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 5430
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 185
Master_UUID: 870ab79e-2a14-11ea-ba96-00505697e312
Master_Info_File: /data/mysql/db/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
Seconds_Behind_Master: 越来越大。
Slave_SQL_Running_State: 一直处于Reading event from the relay log状态
innodb_flush_log_at_trx_commit和sync_binlog两个参数是控制ySQL磁盘写入策略以及数据安全性的关键参数。当两个参数为不同值时，在性能，安全角度下会产生不同的影响。
| 参数名称 | 允许值 | 描述 |
|---|---|---|
| innodb_flush_log_at_trx_commit | 0, 1, 2 | 当重新安排并批量处理与提交相关的I/O操作时，可以控制磁盘的写入策略，严格遵守ACID合规性和高性能之间的平衡，该参数默认值为“1” |
| sync_binlog | 0～4,294,967,295 | 同步binlog（MySQL持久化到硬盘，或依赖于操作系统）。 |
参数解析
-
innodb_flush_log_at_trx_commits- 0：日志缓存区将每隔一秒写到日志文件中，并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
- 1：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，并且刷新到磁盘中，该模式为系统默认。
- 2：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作。
说明：
- 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失；
- 当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld服务崩溃或者服务器主机宕机的情况下，日志缓存区只有可能丢失最多一个语句或者一个事务；
- 当设置为2，该模式速度较快，较取值为0情况下更安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失；
-
sync_binlog=1orN默认情况下，并不是每次写入时都将binlog日志文件与磁盘同步。因此如果操作系统或服务器崩溃，有可能binlog中最后的语句丢失。
为了防止这种情况，你可以使用sync_binlog全局变量（1是最安全的值，但也是最慢的），使
binlog在每N次binlog日志文件写入后与磁盘同步。
注意: 如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。
而且mysql服务默认是autocommit打开的
推荐配置组合
| innodb_flush_log_at_trx_commit | sync_binlog | 描述 |
|---|---|---|
| 1 | 1 | 适合数据安全性要求非常高，而且磁盘写入能力足够支持业务。 |
| 1 | 0 | 适合数据安全性要求高，磁盘写入能力支持业务不足，允许备库落后或无复制。 |
| 2 | 0/N(0<N<100) | 适合数据安全性要求低，允许丢失一点事务日志，允许复制延迟。 |
| 0 | 0 | 磁盘写能力有限，无复制或允许复制延迟较长。 |
说明：
- innodb_flush_log_at_trx_commit和sync_binlog两个参数设置为1的时候，安全性最高，写入性能最差。在mysqld服务崩溃或者服务器主机宕机的情况下，日志缓存区只有可能丢失最多一个语句或者一个事务。但是会导致频繁的磁盘写入操作，因此该模式也是最慢的一种方式。
- 当sync_binlog=N(N>1 )，innodb_flush_log_at_trx_commit=2时，在当前模式下MySQL的写操作才能达到最高性能。