MySQL之binlog和redolog

binlog

特性

1. binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
2. binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1”
3. binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

用处

1. 可以归档日志，因为redo log是循环写，所以会覆盖掉老的日志
2. MySQL很多内部系统依赖于binlog，比如主从同步
3. 很多异构系统也依赖于binlog，比如数据分析系统
4. 数据库异常重启后，redo log处于prepare状态的时候，要确定数据是否存在，就是确定binlog是否完整，如果binlog完整，即使redo log没有commit，也认为数据是存在的

redo log

特性

1. redo log 是 InnoDB 引擎特有的
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
3. redo log 是循环写的，空间固定会用完

用处

1. 记录数据页操作的细节，能支持崩溃恢复
2. 由于数据库的写操作，其实很大部分是更新的buffer pool中的数据页或change buffer。所以这一部分数据是没有直接落盘的，当数据恢复的时候其实是直接从数据库中把redo log中记录了的数据页拉入内存再执行redo log上的操作，使MySQL恢复到崩溃前的状态。这也就是为什么redo log满了后要刷脏页的原因

通过两种日志保证MySQL数据不丢失

语句执行流程

UPDATE TABLE_TEST SET c=c+1 WHERE ID=2;

1. 上图的write可以理解为从数据库内存写到操作系统的内存中
2. 上图的fsync可以理解为一次磁盘IO

是怎么保证数据不丢失

由"binlog的用处4"和"redo log用处2"可以确定，只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复。而控制binlog和redo log的参数分别是sync_binlog，innodb_flush_log_at_trx_commit。

1. sync_binlog

   • sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
   • sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
   • sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

2. innodb_flush_log_at_trx_commit

   • 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 （MySQL内存）
   • 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘（磁盘）
   • 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache（系统内存）

如果sync_binlog和innodb_flush_log_at_trx_commit都设置成1，那么证明所有的数据库操作都会经历上面的"redo log prepare: write"，“binlog: write”，“redo log prepare: fsync”，“binlog：fsync”，“redo log commit：write”。

• 在"redo log prepare: fsync"之前宕机，那就很自然的不算这条记录，数据没有保存，但给客户端返回了报错，可以保证数据不丢失。
• 在"redo log prepare: fsync"和"binlog: fsync"之间主机宕机，那么检查binlog的完整性，如果binlog不完整则不算这条记录，否则则算这条记录，数据有保存，也给客户端返回了报错，数据可能会冗余，但是也保证了数据不丢失。
• 在"binlog: fsync"之后，那么则肯定算这条记录，数据有保存，且不一定给客户端返回了报错，保证了数据不丢失。

如果sync_binlog和innodb_flush_log_at_trx_commit其中有一个值设置成非1，比如把innodb_flush_log_at_trx_commit设置成2，那么"redo log prepare: fsync"有可能就不会执行，这时主机在"redo log commit: write"后掉电，服务端不会给客户端返回报错，且服务端没有保存，主库宕机后数据不能从redo log中恢复，造成了数据丢失。且从库通过binlog读取到了改动，也会造成主从的数据不一致。

怎么降低数据库的IOPS

在我的实际工作中有因为把sync_binlog和innodb_flush_log_at_trx_commit都设置成1造成IOPS过高的问题，那有没有什么办法可以降低MySQL的IOPS。

1. 最直接也是提升最明显的方法，修改sync_binlog和innodb_flush_at_trx_commit的值，让日志存储在内存而不实时刷盘，缺点：机器宕机可能会出现数据丢失和主从数据不一致的情况
2. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count，这两个值可以在"binlog：fsync"中等待几次或几毫秒，连同其他事务的binlog进行一次写盘，降低IOPS。
3. binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小（默认32k），就要暂存到磁盘，所以如果你的事务内容过多，或者写的某个字段过大，那么可以调高binlog_cache_size的大小，防止不必要的额外写盘。