MySQL8双1设置保障安全

双1更安全

set global  innodb_flush_log_at_trx_commit=1;
set global  sync_binlog=1;

双0更快速（主从有延迟，加快复制时会临时用到）

set global  innodb_flush_log_at_trx_commit=0;
set global  sync_binlog=0;

其实就是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，都设置为1就是双1设置。

MySQL 默认配置就是双1配置。

innodb_flush_log_at_trx_commit 是 innodb 引擎的配置，sync_binlog 是 MySQL 引擎上层的配置，都是控制磁盘写入策略。

MySQL innoDB引擎在事务 commit 之后：

• binlog 写内存
• redo log 写内存
• 根据这两个配置决定这两个日志是否刷盘（调用fsync）

innodb_flush_log_at_trx_commit：redo log 的刷盘策略，默认为1

• 如果innodb_flush_log_at_trx_commit设置为0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行（也是每秒一次）.该模式下，记录到日志但未落盘的数据，数据库崩溃时会丢失。

• 如果innodb_flush_log_at_trx_commit设置为1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去;

• 如果innodb_flush_log_at_trx_commit设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行，而是每秒一次刷盘，该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

sync_binlog：binlog 的刷盘策略，默认为0

• 如果为0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
• sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

官方链接 (opens new window)

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. You can achieve better performance by changing the default value but then you can lose transactions in a crash.

• The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

• With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

• With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

• For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal InnoDB activities that cause logs to be flushed independently of the innodb_flush_log_at_trx_commit setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.

• Log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where N is 1 ... 2700, with a default value of 1). However, any unexpected mysqld process exit can erase up to N seconds of transactions.

• DDL changes and other internal InnoDB activities flush the log independently of the innodb_flush_log_at_trx_commit setting.

• InnoDB crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.