数据丢失的定义:当事务提交了,数据因为特殊原因不存在了。

MySQL默认情况下是开启内部的XA事务和事务的实现方式是基于redo log和undo log。也可以理解为MySQL事务是采用日志现行的策略。前提未开启binlog的情况下,数据的变更首先在内存中完成,并且将事务顺序的写入到redo log中,即表示该事务已经完成,就可以返回发给客户端已提交的信息。但此时变更后的数据还在内存中,并没有刷新写入到磁盘中,当达到一定条件,将内存中的数据合并写入到磁盘,即落地到磁盘。这样做的目的是提高性能,但同时也埋下了隐患。在这个过程中,如果服务器宕机,内存中数据将会丢失,重启服务器后,通过redo log日志recovery重做日志,保障了数据不会丢失。因此只要事务能够实时写入到磁盘(redo log),InnoDB存储引擎就不会丢失数据。innodb_flush_log_at_trx_commit参数设置:

0:每秒write cache和flush disk

1:每次commit都 write cache和flush disk

2:每次commit都 write cache

安全性:当innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。

当innodb_flush_log_at_trx_commit和sync_binlog 都为1时是最安全的,在mysqld 服务崩溃或者服务器主机crash的情况下,网上说binary log只有可能丢失最多一个语句或者一个事务,这个目前没法证明只丢一条事务。但是鱼与熊掌不可兼得,都为1会导致频繁的IO操作,因此该模式也是最慢的一种方式,性能最低。

innodb_flush_log_at_trx_commit和sync_binlog是MySQL innodb引擎的两个重要的参数,其中innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer写入到redo log中,sync_binlog是将二进制日志文件刷新到磁盘上。

innodb事务日志redo,binlog逻辑过程如下: 事务写入redo log buffer中--->将log buffer刷新到redo log中,不过会先写一个TX PREPARE标记--->写binlog--->在redo log中写入TX COMMIT标记--->将写binlog成功的标记写入redo log。在这个过程中,Binlog写入成功是否整个事务的关键,如果Binlog提交成功了,redolog没有成功,在重启后会执行safe crash,会在redolog中补充commit,修复数据;如果在innodb层面提交成功,binlog没有成功,重启后这个事务会回滚掉。

当innodb_flush_log_at_trx_commit和sync_binlog 都为1,在整个事务为commit之前会write cache和flush disk,调用OS的fsync刷盘,就MySQL本身异常故障的话,是不会丢失数据的。但innodb_flush_log_at_trx_commit参数在官方文档中有以下注意事项:

配置参数innodb_flush_log_at_trx_commit丢失数据问题
配置参数innodb_flush_log_at_trx_commit丢失数据问题

也就是说如果OS层面突然断电还是会丢失数据的,丢失数据的原因还要查看fsync()这个参数,fsync函数只对由文件描述符filedes指定的单一文件起作用,并且等待写磁盘操作结束,然后返回。fsync可用于数据库这样的应用程序,这种应用程序需要确保将修改过的块立即写到磁盘上。但是fsync一定非常可靠吗?答案是不一定!

因为磁盘(或RAID卡)自身通常会有硬件缓存机制,对于写操作,有write back和write through两种机制,前者将数据写至缓存就会返回,而后者则会将数据写到磁盘介质上。当使用write back机制时,fsync刷的文件数据可能只是写到磁盘缓存就返回了,导致从应用看来,写数据到磁盘的开销很小(实际上并未执行磁盘写操作);所以,使用write back机制时,即使上层应用显式fsync成功,数据也是可能丢失的,比如缓存里的数据还未刷到磁盘时掉电了,因此需要备用电池(BBU,Battery backup unit)来避免掉电时缓存数据丢失。

如果想要绝对保险,确保调用fsync或fdatasync之后数据一定能落地到磁盘上,则可以开启linux内核的write barrier机制,它可以控制在IO操作之前和之后刷新缓存来达到一定能落地磁盘的目的。Ext4 默认启用 barrier,只有当 barrier 之前的数据全部写入磁盘,才能写 barrier 之后的数据。(可通过 "mount -o barrier=0" 命令禁用该特性。)对于该特性的解释如下:

磁盘上配有内部缓存,以便重新调整批量数据的写操作顺序,优化写入性能,因此文件系统必须在日志数据写入磁盘之后才能写 commit 记录(类似WAL),若 commit 记录写入在先,而日志有可能损坏,那么就会影响数据完整性。Ext4 默认启用 barrier。

OS层面的sync、fsync和fdatasync函数

延迟写减少了磁盘读写次数,但是却降低了文件内容的更新速度,使得欲写到文件中的数据在一段时间内并没有写到磁盘上。当系统发生故障时,这种延迟可能造成文件更新内容的丢失。为了保证磁盘上实际文件系统与缓冲区高速缓存中内容的一致性,UNIX系统提供了sync、fsync和fdatasync三个函数。

1、sync函数
sync函数只是将所有修改过的块缓冲区排入写队列,然后就返回,它并不等待实际写磁盘操作结束。通常称为update的系统守护进程会周期性地(一般每隔30秒)调用sync函数。这就保证了定期冲洗内核的块缓冲区。命令sync(1)也调用sync函数。

2、fsync函数
fsync函数只对由文件描述符filedes指定的单一文件起作用,并且等待写磁盘操作结束,然后返回。fsync可用于数据库这样的应用程序,这种应用程序需要确保将修改过的块立即写到磁盘上。

3、fdatasync函数
fdatasync函数类似于fsync,但它只影响文件的数据部分。而除数据外,fsync还会同步更新文件的属性。对于提供事务支持的数据库,在事务提交时,都要确保事务日志(包含该事务所有的修改操作以及一个提交记录)完全写到硬盘上,才认定事务提交成功并返回给应用层。

关于sync_binlog参数:

sync_binlog = 0 (mysql默认值 )由文件系统决定将binlog同步到硬盘。
sync_binlog = 1 每提交一次事务,写一次binlog,并使用fsync()同步到硬盘。
sync_binlog > 1 每提交一次事务,写一次binlog,达到sync_binlog 设定的值后,调用fsync()同步到硬盘。
如果在主从复制架构下:
在 sync_logbin = 1 的情况下, 每次提交事务都会同步到磁盘,保证日志的持久性,也可以保证主从复制的一致性 。
在 sync_logbin = 0,或者>1的值,很有可能机器出现crash,日志并没有同步到磁盘,重启后,二进制日志的position比备库同步过去的position小,造成数据不一致情况。
没有主从架构,单独数据库实例:
在 sync_logbin = 1 的情况下, 每次提交事务都会同步到磁盘,保证日志的持久性,能够保证存储下了所有提交的事务 。能够用来进行恢复。
在 sync_logbin = 0,或者>1的值,很有可能机器出现crash,日志并没有同步到磁盘,重启后,二进制日志很可能丢失已提交事务,所以不能用来进行恢复操作,因为它有丢失事务。

所以,MySQL Innodb数据丢失问题,涉及到MySQl自身参数与事务处理机制、OS层面和硬件层面的缓存刷盘参数及机制。其中一个层面出现问题,都可能出现数据丢失的情况。