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;

这两条只应该在从库执行，用途是从库落后主库很多时临时提速追平，追平后必须改回双 1。在主库上设双 0 等于放弃「已提交事务不丢」这条底线。

版本说明

本文写于 2022-09，基于 MySQL 8.0。
innodb_flush_log_at_trx_commit=1 + sync_binlog=1（双 1）的语义与权衡未变，经 2026-07 复核仍适用。

另外注意 set global 只改运行时值，重启就没了；要持久化用 SET PERSIST，长期固定就写进 my.cnf（参见 MySQL8 配置文件 my.cnf 重要参数解读）。

其实就是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 的刷盘策略，MySQL 5.7.7 起默认为 1（更早的版本默认 0，网上大量旧文还在按 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.

坑与边界

• 从库设了双 0 之后崩溃，通常只能重建。双 0 下从库的 relay log 位点与已应用数据可能不一致，宕机后 START REPLICA 未必能正确续上；GTID + MASTER_AUTO_POSITION=1 能救回一部分场景，但只要出现「已执行但没落盘」的事务，最稳的做法就是从主库重新克隆（见 MySQL8 快速克隆插件使用指南）。
• 双 1 的代价落在磁盘上，不是 CPU 上。每次提交两次 fsync，在机械盘或网络存储（EBS/云盘）上会直接压住 TPS 上限；SSD + 带电池的 RAID 卡上代价小得多。改配置前先用 oltp_write_only 压一遍看差多少，别凭感觉调（见 MySQL 性能压测：Sysbench 1.0 实战）。
• innodb_flush_log_at_trx_commit=2 不等于安全：MySQL 进程崩溃不丢（数据已交给 OS），但主机掉电会丢最多 innodb_flush_log_at_timeout 秒（默认 1 秒）的事务。把 2 当成「几乎等于 1」是常见误判。
• 组提交会摊薄 fsync 开销。高并发下 MySQL 会把多个事务的日志合并成一次 fsync，所以双 1 在高并发时的相对损失远小于单线程压测的结果——这也是「单线程压测得出双 1 慢一倍」这类结论不能直接搬到线上的原因。

验证

确认当前值与来源（VARIABLE_SOURCE 为 PERSISTED 说明被 mysqld-auto.cnf 接管）：

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_variables
WHERE VARIABLE_NAME IN ('innodb_flush_log_at_trx_commit','sync_binlog');

SELECT VARIABLE_NAME, VARIABLE_SOURCE
FROM performance_schema.variables_info
WHERE VARIABLE_NAME IN ('innodb_flush_log_at_trx_commit','sync_binlog');

预期输出（双 1 且来自配置文件）：

+--------------------------------+----------------+
| VARIABLE_NAME                  | VARIABLE_VALUE |
+--------------------------------+----------------+
| innodb_flush_log_at_trx_commit | 1              |
| sync_binlog                    | 1              |
+--------------------------------+----------------+