Mysql日志
Redo Log
工作机制
物理日志
- Redo log是物理日志，记录的是数据页在某个位置上的修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新
- WAL：
Innodb中事务的实现是通过
redo log和undo log来实现的，保证事务的持久性流程：
- 事务开始时，会记录该事务的一个LSN（日志序列号）
- 事务执行时，会往Innodb存储引擎的日志缓冲区里插入事务执行的日志
- 事务提交时，必须将日志缓冲区写入磁盘。也就是在写数据前，先写日志。这种方式称为
WAL(Write Ahead Logging)
崩溃恢复
- 当数据库异常崩溃重启时，msyql会重放redo log文件中所记录的已完成的事务日志，未完成的事务进行回滚，以确保事务的持久性和一致性
刷盘机制
刷盘步骤：
- 刷盘一共分为两步：write（写文件）（log buffer -> page cache），fsync（持久化）（page cache -> disk）
触发条件：
Mysql正常关闭时
事务提交时（此策略由innodb_flush_log_at_trx_commit参数来进行控制）
当redo log buffer中写入的日志量占用redo log buffer内存的一半时，会触发刷盘
后台线程每个一秒会触发刷新磁盘
innodb_flush_log_at_trx_commit控制的是什么？
当innodb_flush_log_at_trx_commit=0时，提交事务时，redo log还会停留在redo log buffer中，不会触发刷盘操作
当innodb_flush_log_at_trx_commit=1时，提交事务时，redo log 会立即进行writre+fsync过程
当innodb_flush_log_at_trx_commit=2时，提交事务时，redo log会触发write的操作，但是不会触发fsync的操作
基于以上条件，那么当innodb_flush_log_at_trx_commit=0和2时，什么时候才将redo log写入磁盘？
当innodb_flush_log_at_trx_commit=0时：后台线程每隔一秒会定时刷盘，所以针对于参数0，当mysql实例崩溃后，会导致上一秒中的所有事务数据丢失
当innodb_flush_log_at_trx_commit=2时，后台线程每隔一秒会定时fsync操作，所以针对参数2，当mysql实例崩溃后，不会导致上一秒的事务数据丢失，只有当操作系统崩溃后，才会导致上一秒的事务数据丢失，因为此时redo log会存在page cache中
redo log 文件写满了怎么办？
默认配置：
- 默认情况下，redo log是由一个group、一个group由2个文件组成的
redo log是循环写的：
因为等事务提交后，脏页刷新到磁盘中后，redo log中的日志就没用了，所以采用循环写来节省空间
因为是循环写的，等redo log占用满了，该淘汰哪些？此时会触发内存中脏页的刷新，来腾出空间，当然了不是只有等待redo log 满了才会触发刷新脏页的操作，后台线程也会定时的出发刷新脏页的操作
阻塞mysql的写操作：
- 当write point追上了checkpoint后，mysql的写操作会被阻塞，触发脏页刷盘操作，等可以擦除的擦除后，checkpoint 往后移动，mysql恢复更新操作
Undo Log
工作机制
事务回滚
- 事务操作过程中，发生了异常，可以采用undo log进行回滚，保证了事务的原子性
存储
- undo log 会写入 Buffer Pool 中的 Undo Page页
流程
每当对一条记录进行操作（修改、删除、更新）时，需要把回滚时需要的信息都记录到undo log中，比如：
在插入一条记录时，要把这条记录的主键记录下来，这样之后回滚时只需要把这个主键对应的记录删除掉就好了
在删除一条记录时，要把这条记录的内容下来，这样之后回滚的时候再把这些记录插入到表中就好了
在更新一条记录时，要把被更新列的旧值记录下来，这样之后回滚的时候再把这些列更新为旧值就可以了
针对于delete操作和update操作会有一些特殊的处理
delete操作不会立即删除，而是将delete对象打上delete flag，标记为删除，等待purge thread线程完成删除操作
update分为两种情况：update的列是否为主键列
- 如果不是主键列，在undo中直接记录反向update的，即update是直接执行的
- 如果是主键列，update分两部分执行：先删除该行，在插入一行目标行
- undo 日志对数据行的影响？
一条记录的每一次更新操作产生的undo log格式都有会有一个roll_pointer指针和一个trx_id事务id：
通过trx_id知道是哪个事务改的
通过roll_pointer指针可以将这些undo log串成一个链表，这个链表就称为版本链。如下图：
通过undo log产生的版本链来实现MVCC（多版本控制）
刷盘机制
刷盘策略
- undo log的刷盘机制和数据页刷盘策略是一样的，都需要通过redo log保证持久化
- buffer pool中有undo 页，对undo 页的修改也会记录到redo log。redo log会每秒钟刷盘，提交事务时也会刷盘，数据页和undo页都是靠这个机制保证持久化的
Binlog
使用场景
主从复制
Mysql的主从复制过程依赖于binlog，也就是主库执行过程中将产生的binlog发送到从库中，从库执行binlog
这个过程一般是异步的，也就是主库执行事务的线程不会等待从库备份完成
Mysql主从备份可以分为三个过程：
写入binlog ：主库写binlog日志，提交事务
同步binlog：通过log dump 线程把binlog发送到每个从库中，每个从库通过线程把binlog写到暂存日志中
重放binlog：sql线程回放binlog，并更新存储引擎中的数据
备份恢复：通过 Binlog 记录的日志，可以进行基于时间或位置的精确数据恢复。
记录模式
Row
以行的方式进行记录，记录行数据最终被修改成什么样了，例如插入100行记录，则binlog中会记录100行数据
Statement
会以语句的方式进行记录，例如为表中插入100行记录，只会记录一条sql语句
Mixed
包含了Statement和Row，会根据实际情况进行选择
刷盘机制
- 刷盘步骤
- 事务执行过程，线程会把binlog 日志先写到binlog cache中，每个线程都会有一个binlog cache
- 事务提交后，将日志从binlog cache写到page cache中（write），然后清空binlog cache，然后再刷新到磁盘上（fsync）
- fsync的频率是通过sync_binlog来进行控制的
当事务提交后，会把日志提交到操作系统的缓冲区（Page Cache）中，然后通过参数（sync_binlog）控制刷新到磁盘的时机
sync_binlog参数说明
- 当sync_binlog=0时，每次事务提交后，不会立即触发刷盘操作，只进行write
- 当sync_binlog=1时，每次事务提交后，会立即触发刷盘操作，write+fsync
- 当sync_binlog=N时，每次事务提交后，都会进行wtire过程，但是等待N个事务提交后，才会触发一次fsync过程
日志分析与数据恢复
日志分析
- 使用
mysqlbinlog工具解析 Binlog 文件。 - 支持对各种 Event 类型进行分析。
数据恢复
基于时间恢复
使用mysqlbinlog的--start-datetime和--stop-datetime参数：1
mysqlbinlog --start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS" /path/to/binlog | mysql -u user -p基于位置恢复
使用--start-position和--stop-position参数：1
mysqlbinlog --start-position=POS1 --stop-position=POS2 /path/to/binlog | mysql -u user -p直接恢复
将 Binlog 文件的内容导入到目标数据库：1
mysqlbinlog /path/to/binlog | mysql -u user -p
Redo Log和Binlog的区别
| Redo Log | BinLog | |
|---|---|---|
| 日志类型 | 记录的是物理日志 | 逻辑日志（记录 SQL 和表变更） |
| 适用对象 | 属于Innodb引擎 | 属于Mysql底层日志框架 |
| 使用场景 | 数据库崩溃恢复 | 主从架构、数据库备份恢复 |
| 写入方式 | 循环写 | 追加写 |
总结
基于以上三个日志的内容及分析，详细描述下一条修改的sql语句执行过程中各日志的执行过程？
执行：update user SET usename = 'aa' where id = 1的过程
undo log：先加载undo页到buffer pool中，因为条件为id=1，id为主键，所以undo页会记录一下当前行的旧值到undo 页中，并记录日志redo log到 log buffer中
redo log：根据user表的主键索引树找当前记录所对应的数据页是否在buffer pool中，如果存在的话，直接修改数据页为脏页，并记录redo log 到 log buffer中，在此数据页上将id =1 的这条记录name修改为了aa
binlog：将当前语句写到binlog cache中
事务提交（两阶段提交）：
prepare阶段：将redo log的状态设置为prepare，然后将redo log进行刷盘
commit阶段：将binlog进行刷盘，接着调用引擎的事务接口，将redo log的状态设置为commit
参考资料
小林codingMySQL 日志：undo log、redo log、binlog 有什么用？
《Mysql实战45讲》
《MySQL技术内幕：InnoDB存储引擎》