深入浅出MySQL三大日志

前言

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志和二进制日志几大类，日志记录着数据库运行期间各种状态信息，本文详细解读MySQL的三大日志：二进制日志（bin log）和事务日志（redo log和undo log）

bin log(归档日志)

binlog用于记录数据库执行的写 *** 作（不含查询），以二进制的形式保存在磁盘中

使用场景

• .主从复制

在master端开启binlog，然后将binlog发送到各个slave端，slave重放binlog从而达到主从数据一致

• 数据恢复

通过使用mysqlbinlog工具来恢复数据

刷盘时机

事务执行过程中，会先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中；事务提交后的写入只是写入到文件系统的page cache，并没有持久化到磁盘。
刷盘时机通过sync_binlog参数控制，可配置0~N
0：由系统自行判断何时写入磁盘
1：每次commit都将binlog写入磁盘
N：每N个事务，才将binlog写入磁盘

日志格式

STATMENT：基于sql语句的复制（statement-based replication,SBR），每一条修改的sql语句都会记录到binlog中
优点：不需要记录每一行的变化，减少binlog日志量，节约磁盘IO，从而提高性能
缺点：在某些情况下会导致主从数据不一致，如sysdate()、slepp()等
ROW：基于行的复制（row-based-replication,RBR），不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了
优点：不会出现默写特定情况下的存储过程或function/trigger的调用和触发无法被正常复制的问题
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨
MIXED：基于STATMENT和ROW两种模式的混合复制（mixed-based replication,MBR），一般的复制使用statement模式保存binlog，对于statement模式无法复制的 *** 作使用row模式写入binlog

redo log(重做日志)

redolog是InnoDB存储引擎特有的，保证事务持久性的日志

刷盘时机

mysql每执行一条DML语句，先将记录写入内存中的日志缓冲（redo log buffer），后续某个时间点再一次性即将多个 *** 作记录写到磁盘上的日志文件（redo log file），这种先写日志，再写磁盘的技术称为WAL(Write-Ahead Logging)技术
在计算机 *** 作系统中，用户空间（user space）下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须进过 *** 作系统内核空间（kernel sapce）缓冲区（OS Buffer），因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再调用fsync()刷到redo log file中
刷盘时机通过innodb_flush_log_at_trx_commit参数配置：

参数值	含义	缺点
0（延迟写）	每秒将redo log file中日志写入os buffer并调用fsync()写入到redo log file中	当系统崩溃，会丢失一秒钟的数据
1（实时写，实时刷）	事务每次提交都会将redo log buffer中的日志写入os buffer 并调用fsync()刷到redo log file中	因为每次提交都写入磁盘，IO性能较差
2（实时写，延迟刷）	事务每次提交仅写入os buffer,然后每秒调用fsync()将os buffer中的日志刷到redo log file中

扩展

对于MySQL Innodb存储引擎，每次修改后，不仅需要记录redo log还需要记录bin log，而且这两个 *** 作必须保证同时成功/同时失败，否则会造成数据不一致，MySQL采用两阶段提交解决此问题

场景	问题
先写redo log再写bin log	写完redo log，没有写完bin log，主从同步失败，数据不一致
先写bin log再写redo log	写完bin log，写redo log失败，主从同步完毕，主库没有这条数据，数据不一致
写redo log(prepare状态)，写bin log,提交事务commit状态

undo log(回滚日志)

undo log作用于事务回滚和多版本并发控制（MVCC），保证事务的原子性

存储方式

在InnoDB存储引擎中，undo log存储在回滚段（Rollback Segment）中，每个回滚段记录了个undo log segment，在每个undo log segment段中进行undo页的申请，在mysql5.6之前，Rollback Segment存储在共享表空间，mysql5.6.3之后，可通过innodb_undo_tablespace设置undo存储的位置

undo的类型 insert undo log

insert undo log指在insert *** 作中产生的undo log，因为insert *** 作的记录，只对事务本身可见，对其他事务不可见，所以insert undo log可以在事务提交后之间删除，不需要进行purge *** 作

update undo log

update undo log记录的是对delete和update *** 作产生的undo log，update undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除，提交时放入undo log链表，等待purge线程进行最后的删除

扩展

purge线程两个主要作用是：清理undo页和清除page里面带有Delete_Bit表示的数据行，在InnoDB存在引擎中，事务中的delete *** 作实际上并不是真正的删除掉数据行，而是通过Delete Mark *** 作，在记录上标识Delete_Bit，真正的删除通过purge线程完成