参数说明:
不管在官网还是其他网站上均能看到innodb_flush_log_at_trx_commit=【0,1,2】三种值:
innodb_flush_log_at_trx_commit = 0 :每秒将日志缓冲区写入log file,并同时flush到磁盘。跟事务提交无关。在机器crash并重启后,会丢失一秒的事务日志数据(并不一定是1s,也许会有延迟,跟操作系统调度有关)。
innodb_flush_log_at_trx_commit = 1:每次事务提交将日志缓冲区写入log file,并同时flush到磁盘。(crash不会丢失事务日志)
innodb_flush_log_at_trx_commit = 2:每次事务提交将日志缓冲区写入log file,每秒flush一次到磁盘。(crash有可能丢失数据)
测试验证:
环境说明:
操作系统:windows 7 MySQL:5.6.26
准备:
-- 建立测试表 use test create table t_innodb (a int) engine=innodb; create table t_myisam (a int) engine=myisam; -- 建立插入数据存储过程 delimiter // create procedure insert_testdata(p1 int) begin set @x:=0; repeat set @x:=@x+1; insert into t_myisam values (@x); insert into t_innodb values (@x); commit; until @x>p1 end repeat; end // delimiter ;
(1)innodb_flush_log_at_trx_commit = 0 测试
net stop mysql --停止Mysql服务 更新my.ini innodb_flush_log_at_trx_commit = 0 --更新innodb_flush_log_at_trx_commit = 0 net start mysql --重新启动Mysql服务
mysql -u root -p
use test
call insert_testdata(1000000);
将mysqld进程杀掉。模拟crash场景。
net start mysql
结果截图:
(2)innodb_flush_log_at_trx_commit = 1 测试
truncate table t_innodb; truncate table t_myisam; net stop mysql 更新my.ini innodb_flush_log_at_trx_commit = 1 net start mysql mysql -u root -p use test call test_insertdata(1000000); 将mysqld进程杀掉。模拟crash场景。
(3)innodb_flush_log_at_trx_commit = 2 测试
truncate table t_innodb; truncate table t_myisam; net stop mysql 更新my.ini innodb_flush_log_at_trx_commit = 2 net start mysql mysql -u root -p use test call insert_testdata(1000000);
将mysqld进程杀掉。模拟crash场景。
重启后,数据未丢失。
再次 call insert_testdata(1000000); 将mysqld进程杀掉。模拟crash场景。丢失一条数据。
注意:
大家肯定记得还有控制二进制日志的 sync_binlog 参数,而且还会很容易混淆,下面谈一谈sync_binlog:
sync_binlog = 0 (mysql默认值 )由文件系统决定将binlog同步到硬盘。
sync_binlog = 1 每提交一次事务,写一次binlog,并使用fdatasync()同步到硬盘。
sync_binlog > 1 每提交一次事务,写一次binlog,达到sync_binlog 设定的值后,调用fdatasync()同步到硬盘。
(1)如果在主从复制架构下:
在 sync_logbin = 1 的情况下, 每次提交事务都会同步到磁盘,保证日志的持久性,也可以保证主从复制的一致性 。
在 sync_logbin = 0,或者>1的值,很有可能机器出现crash,日志并没有同步到磁盘,重启后,二进制日志的position比备库同步过去的position小,造成数据不一致情况。
(2)没有主从架构,单独数据库实例:
在 sync_logbin = 1 的情况下, 每次提交事务都会同步到磁盘,保证日志的持久性,能够保证存储下了所有提交的事务 。能够用来进行恢复。
在 sync_logbin = 0,或者>1的值,很有可能机器出现crash,日志并没有同步到磁盘,重启后,二进制日志很可能丢失已提交事务,所以不能用来进行恢复操作,因为它有丢失事务。
总结:
innodb_flush_log_at_trx_commit 参数对数据完整性有非常大的作用,强烈建议使用 innodb_flush_log_at_trx_commit = 1 ; sync_binlog = 1 ; --虽然会很影响性能,但是对于数据很重要的情况下,必须设置。