innodb_flush_log_at_trx_commit参数测试

参数说明：

  不管在官网还是其他网站上均能看到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 ； --虽然会很影响性能，但是对于数据很重要的情况下，必须设置。