MYSQL是如何保证数据不丢的？

一、前言

  由于mysql开源、体积小、速度快，总体拥有成本低，目前已广泛被大小公司使用，特别是在互联网，全球前20大互联网网站有18家使用了MYSQL，有些公司除使用外还在mysql的功能基础上做一定的优化和改造，使之更加适合公司特殊业务场景，比如说阿里。

另外，由于mysql的生态越来完善，像阿里的canal、唯品会的RDP、VDB都是基于mysql的binlog,及时监控表的数据变化，让其它应用服务及时感知表的数据变化，异步更新缓存或将数据异步准实时同步ES集群。MYSQL数据库在中国的应用情况，已经是国内互联网公司默认标配，人才储配，架构、解决方案储备非常完善了，而传统企业看，迅速意识到互联网重要性，MYSQL一定是他们的首选方案，它的优势非常多，那么我今天主要聊聊mysql如何保证不丢数据的。

二、binlog&redolog原理

  mysql保证不丢数据，binlog和redolog功不可没，也正是因为有这两个日志相互配合，innodb引擎已被广泛使用重要原因之一。而了解mysql内部确保数据不丢失的原理，学习里面优秀的设计要点，然后我们再借鉴这些优秀的设计要点进行实践应用，加深理解。
  在mysql中有两种LOG，分别是redo log和binlog，只要保证持久化到磁盘，即使异常或服务器cash,它也能确保重启后可以恢复数据,那么今天来探讨MYSQL如何数据不丢失的，在探讨之前先简单了解一下binlog和redolog。

2.1 binlog原理

  MySQL中的binlog是一个二进制文件,它记录了所有的增删改操作，节点之间的复制就是依靠binlog来完成的，包括依赖binlog的应用很多也是基于binlog。

2.1.1 binlog写入流程

我们的先来了解一下binlog写入流程，如下图：

从上图中可以看到，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache的内容写到 binlog 文件中。

• binlog cache:
  在这里需要注意的是，每个线程都自己cache,为什么要这么设计主要是同一个事务的binlog不能被拆开，不管这个事务有多大，必须保证一次性写入，因此binlog_cache_size必须设置合理的值，如果事务需要内存超过此字节数，服务器生成如下错误ERROR 1197 (HY000): Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage;max_binlog_cache_size最低值是4096，最大可能值为16EB。
• page cache:事务提交时，一般并不会马上刷到磁盘，而是先写页缓存，主要是为提升效率。
• 写入binlog时机：
  写入binlog时机是由参数 sync_binlog控制的，这些参数值分别是：
  1）、sync_binlog=0 的时候，表示每次提交事务都只write，不 fsync,由文件系统自己控制它的缓存的刷新，这时候的性能是最好的，但是风险也是最大的；
  2）、sync_binlog=1 的时候，表示每次提交事务都会执行fsync，这样数据的可靠性最强，是最安全但性能损耗最大的设置；
  3）、sync_binlog=N (N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync，但如果突然为断电，这样会丢失这N个事务的数据，但可以获得更高的并发和性能，目前很多DBA都将这个参数设为100。

2.1.2 binlog模式：

  binlog有三种模式，分别是statement、row、mixed，其中mixed其实它就是前两种格式的混合, 决定用哪种模式是由binlog_format这个参数来定的。

• Row模式
  日志中会记录成每一行数据被修改的日志，然后在slave端再对相同的数据进行修改。例如:update xxx where id in(1,2,3,4,5);采用该模式则会记录5条记录。
• statement模式
  每一条会修改数据的sql都会记录到 master的binlog中，slave在复制的时候sql Thread会解析成和原来master端执行过的相同的sql来再次执行，这样可能会带主备执行不一致的问题，比如：update table1 set xxx = sss limit 1,一般生产很少会设置这种模式，如果设置为statement是不合理的。
• mixed模式
  Mixed即混合模式,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

mysql为什么要引入mixed模式呢？因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

2.1.2 binlog组提交

  为了提高性能，通常会将有关联性的多个数据修改操作放在一个事务中，这样可以避免对每个修改操作都执行完整的持久化操作。这种方式，可以看作是人为的组提交(group commit)。除了将多个操作组合在一个事务中，记录binlog的操作也可以按组的思想进行优化：将多个事务涉及到的binlog一次性flush，而不是每次flush一个binlog。事务在提交的时候不仅会记录事务日志，还会记录二进制日志，但是它们谁先记录呢？二进制日志是MySQL的上层日志，先于存储引擎的事务日志被写入，与组提交的相关的参数是分别binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。
1、binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
2、binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync，所以，当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。

2.2 redolog原理

2.2.1 redolog特性

  binlog叫逻辑归档日志，记录的是这个语句的原始逻辑，redo log也叫重做日志，是物理日志，记录的是“在某个数据页上做了什么修改”；它是固定大小的，另外redo log是循环写的，空间固定会用完；

• write pos:是指当前记录的位置
• check point:是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
  write pos和check point之间是指还可以写的部分，用来记录新的记录，当write pos追上check point,则需要先刷盘并清出空间出来。

2.2.2 日志写入流程

  redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。
  为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。要写入到磁盘上的log file中(redo:ib_logfileN文件,undo:share tablespace或.ibd文件)，中间还要经过操作系统内核空间的os buffer，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中，也就是说，从redo log buffer写日志到磁盘的redo log file中，过程如下：

2.2.3 写入策略

为了控制 redo log 的写入策略，InnoDB 提供了innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

• 0：设置为 0 的时候，表示每次事务提交时都只是把redo log留在redo log buffer中 ;
• 1：设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
• 2：设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
  当innodb_flush_log_at_trx_commit设置为0、2时，InnoDB有一个后台线程，每隔 1 秒就会把 redo log buffer 中的日志，调用 write 写到文件系统的page cache，然后调用fsync 持久化到磁盘。
  
  在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下，如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中，总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。上述两项变量的设置保证了每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

2.2.4 check point

内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data),由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。在innodb中，数据刷盘的规则只有一个：checkpoint，但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘，innodb存储引擎中checkpoint分为两种：

• sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。
• fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘，有以下几种情况会触发该检查点：
  1）master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。
  2）flush_lru_list checkpoint：从MySQL5.6开始可通过innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。
  3）async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘。
  4）dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。
  由于刷脏页需要一定的时间来完成，所以记录检查点的位置是在每次刷盘结束之后才在redo log中标记的。

2.2.5 LSN

LSN称为日志的逻辑序列号(log sequence number)，在innodb存储引擎中，lsn占用8个字节。LSN的值会随着日志的写入而逐渐增大，根据LSN，可以获取到几个有用的信息：
1.数据页的版本信息。
2.写入的日志总量，通过LSN开始号码和结束号码可以计算出写入的日志量。
3.可知道检查点的位置。
LSN不仅存在于redo log中，还存在于数据页中，在每个数据页的头部，有一个fil_page_lsn记录了当前页最终的LSN值是多少,通过数据页中的LSN值和redo log中的LSN值比较，如果页中的LSN值小于redo log中LSN值，则表示数据丢失了一部分，这时候可以通过redo log的记录来恢复到redo log中记录的LSN值时的状态。

2.2.6 redo log恢复

在启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作，因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如二进制日志)要快很多。而且，innodb自身也做了一定程度的优化，让恢复速度变得更快。
重启innodb时，checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分。例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。
还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。
另外，事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。例如，某记录中id初始值为2，通过update将值设置为了3，后来又设置成了2，在事务日志中记录的将是无变化的页，根本无需恢复；而二进制会记录下两次update操作，恢复时也将执行这两次update操作，速度比事务日志恢复更慢。

2.2.7 与redolog相关参数

• innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。
• innodb_log_buffer_size：# log buffer的大小，默认8M
• innodb_log_file_size：#事务日志的大小，默认5M
• innodb_log_files_group =2：# 事务日志组中的事务日志文件个数，默认2个
• innodb_log_group_home_dir =./：# 事务日志组路径，当前目录表示数据目录
• innodb_mirrored_log_groups =1：# 指定事务日志组的镜像组个数，但镜像功能好像是强制关闭的，所以只有一个log group。在MySQL5.7中该变量已经移除。

2.3 redolog与binlog区别

redo log不是二进制日志，虽然二进制日志中也记录了innodb表的很多操作，也能实现重做的功能，但是它们之间有很大区别。

• 1、binlog二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志，而redo log是innodb层产生的，只记录该存储引擎中表的修改，并且二进制日志先于redo log被记录。
• 2、binlog二进制日志记录操作的方法是逻辑性的语句,即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而redo log是在物理格式上的日志，它记录的是数据库中每个页的修改。
• 3、二进制日志只在每次事务提交的时候一次性写入缓存中的日志"文件"(对于非事务表的操作，则是每次执行语句成功后就直接写入)。而redo log在数据准备修改前写入缓存中的redo log中，然后才对缓存中的数据执行修改操作；而且保证在发出事务提交指令时，先向缓存中的redo log写入日志，写入完成后才执行提交动作。
• 4、因为二进制日志只在提交的时候一次性写入，所以二进制日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而redo log中是记录的物理页的修改，redo log文件中同一个事务可能多次记录，最后一个提交的事务记录会覆盖所有未提交的事务记录。例如事务T1，可能在redo log中记录了 T1-1,T1-2,T1-3，T1* 共4个操作，其中 T1* 表示最后提交时的日志记录，所以对应的数据页最终状态是 T1* 对应的操作结果。而且redo log是并发写入的，不同事务之间的不同版本的记录会穿插写入到redo log文件中，例如可能redo log的记录方式如下： T1-1,T1-2,T2-1,T2-2,T2*,T1-3,T1* 。
• 5、事务日志记录的是物理页的情况，它具有幂等性，因此记录日志的方式极其简练。幂等性的意思是多次操作前后状态是一样的，例如新插入一行后又删除该行，前后状态没有变化。而二进制日志记录的是所有影响数据的操作，记录的内容较多，例如插入一行记录一次，删除该行又记录一次。

三、总结

WAL 机制主要得益于两个方面：redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；组提交机制，可以大幅度降低磁盘的 IOPS 消耗。如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过以下几种方法来提升性能：
1、设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2、将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3、将 innodb_flush_log_at_trx_commit 设置为 2，这样做的风险是，主机掉电的时候会丢数据。
一般不建议你把 innodb_flush_log_at_trx_commit 设置成 0，因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。

参考资料：
1、详细分析MySQL事务日志(redo log和undo log)
2、MYSQL实战45讲