innodb_flush_log_at_trx_commit 是 MySQL 的一个系统变量,运行环境是 InnoDB 引擎。该变量定义了 InnoDB 在每次事务提交时,如何处理未刷入(flush)的重做日志信息(redo log)。它是 InnoDB 确保 ACID 属性中的持久性(Durability)的关键因素。当数据库发生故障,如崩溃或者断电,这项设置可以保护您的数据不会丢失。
innodb_flush_log_at_trx_commit 的取值可以是 0、1 或 2:
当数据库执行事务操作时,会先写入重做日志(redo log),如果此时数据库突然崩溃,重做日志可以帮助恢复没有完全执行的事务。innodb_flush_log_at_trx_commit 控制了这些重做日志何时刷新到磁盘。旨在在持久性和性能之间寻找平衡。
数据库在开启事务处理后在每次事务提交时需要考虑数据的持久性,要保证即使系统崩溃,数据库的数据能够通过重做日志 accurate recovery,即数据恢复到最近一次事务提交后的状态。这就需要重做日志在事务提交的时候将日志记录落盘,而落盘操作对于 I/O 来说是非常耗时的。针对这个问题,MySQL InnoDB 引擎提供了innodb_flush_log_at_trx_commit 变量,让用户可以根据实际需求设置日志落盘策略。
当事务提交或回滚时,InnoDB 会在提交或回滚的末尾将事务写入重做日志。这样,如果系统发生崩溃,InnoDB 能够使用这些日志恢复未提交的事务。innodb_flush_log_at_trx_commit 变量决定了 InnoDB 何时将这些重做日志写入磁盘。
该变量的值决定了写日志的策略。当innodb_flush_log_at_trx_commit=1时,事务提交时,重做日志缓冲(redo log buffer)将会被直接写入(flush)到日志文件并同步(fsync),随后事务才被提交。这样可以保证在任意时刻 MySQL 异常重启时,事务的一致性。
当innodb_flush_log_at_trx_commit=0 或 innodb_flush_log_at_trx_commit=2 时,日志刷新策略是每秒刷新日志文件一次。所以,如果在这一秒内,出现了 MySQL 异常重启或宕机,那么在这一秒内尚未刷新写入日志的部分,将会永久丢失。
在 MySQL 的运行过程中,可以动态地设置这个变量,调整策略。你可以在 MySQL 命令行中运行设置命令。
查看当前 innodb_flush_log_at_trx_commit 的设置:
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
设置 innodb_flush_log_at_trx_commit 为 0:
SET GLOBAL innodb_flush_log_at_trx_commit=0;
为获得最高的持久性,可以设为 1:
SET GLOBAL innodb_flush_log_at_trx_commit=1;
注意,这个设置也可以写入到 MySQL 的配置文件 my.cnf 中,将在 MySQL 启动时生效:
[mysqld]
innodb_flush_log_at_trx_commit=1
如果对数据的实时性及持久性要求非常高,应该选择“1”策略。如果期望获得更高的写性能,并且对小范围数据丢失可以接受,那么“0”或“2”的策略更适合。选择策略时需根据业务模型和应用的特点,兼顾性能和数据的可靠性进行周全考虑。
在 InnoDB 数据库中,innodb_flush_log_at_trx_commit 这个参数是一个重要的参数,它决定了数据库在事务提交时重做日志的刷新方式,保障了 ACID 中的持久性。根据不同应用对性能和数据保障的侧重点,需要谨慎选择并调整这个参数的值。