MySQL日志系统

一、Undo Log

1.1 Undo Log 介绍

Undo： 以为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。
Undo Log： 数据库事务开始之前，会将要修改的记录存放到Undo 日志里，当事务回滚或者数据库崩溃时，可以利用Undo log撤销未提交事务对数据库产生的影响。
Undo Log产生和销毁： Undo Log 在事务开始前产生；事务在提交时，并不会立即删除Undo Log 日志，innodb 会将该事务对应的undo log 放入删除列表中，后面会通过后台线程purge thread 进行回收处理。undo log 属于逻辑日志，记录一个变化过程。
Undo Log存储： undo log 采用段的方式管理和记录。在 innodb 数据文件中包含一种rollback segment 回滚段，内部包含 1024 个undo log segment。
可以通过下面一组参数控制 undo log 存储：

show variables like '%innodb_undo%'

如：执行一个delete，undo log 会记录一个insert…

1.2 Undo Log 的作用

1.2.1 实现事务的原子性

undo log 是为了实现事务的原子性而出现的，事务处理过程中，如果出现了错误或者 ROLLBACK 语句，MySQL 可以利用 undo log中备份的数据恢复到数据开始之前的状态。

1.2.2 实现多版本并发控制(MVCC)

undo log 在MySQL innodb 中用来实现多版本并发控制。事务提交之前，undo log 保存了未提交之前的版本数据，undo log 中的数据可以作为数据旧版本快照供其他并发事务进行快照读。

事务A 手动开启事务，执行更新操作前，首先会把更新命中的数据备份到 Undo Buffer 中。
事务B 手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读取。

二、Redo Log

2.1 Redo Log 介绍

Redo： 顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。
Redo Log： 指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。
Redo Log 的生成和释放： 随着事务操作的执行，就会生成Redo Log，在事务提交时将会产生 Redo Log 写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成，Redo Log 占用的空间就可以重用（被覆盖写入）。

2.2 Redo Log 的工作原理

Redo Log 是事务实现持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启MySQL 服务的时候，根据Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

2.3 Redo Log 的写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则会回溯到第一个文件，进行覆盖写。

2.4 Redo Log 的相关参数配置

每个 innodb 存储引擎至少有 1个重做日志文件组（group），每个文件组至少有2个重做日志文件，默认是ib_logfile0 和ib_logfile1。
可以通过下面一组参数控制 Redo Log存储：

show variables like '%innodb_log%'

Redo Buffer 持久化到 Redo Log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

三、Bin Log

3.1 Binlog 记录模式

Redo Log 是属于InnoDB 引擎所持有的日志，而MySQL Server 也有自己的日志，即Binary log（二进制日志）。Binlog 是记录所有数据库表结构变更以及表数据修改的二进制文件，不会记录select、show 这类操作。
开启Binlog 日志有以下两个最重要使用场景：

• 主从复制： 在主库中开启binlog 功能，这样主库就可以把binlog 传递给从库，从库拿binlog 后实现数据恢复达到主从数据一致。
• 数据恢复： 通过mysqlbinlog 工具来恢复数据。

binlog 文件名默认为 “主机名-binlog-序列号”格式，也可以在配置文件中指定名称。
文件的记录模式有 STATEMENT、ROW、MIXED三种：

• ROW： 日志中会有记录每一行数据被修改的情况，然后在slave 端对相同的数据进行修改。（主从架构建议使用）
  优点： 记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
  缺点： 批量操作，会产生大量的日志，尤其是alter table 会让日志暴涨。

• STATEMENT： 每一条被修改的SQL 都会被记录到master 的binlog 中，slave 在复制的时候SQL 进程会解析和原来master 端执行过的相同的SQL 再次执行。简称SQL 语句复制。
  优点： 日志量小，减小磁盘IO，提升存储和恢复速度。
  缺点： 在某些情况下会导致主从数据不一致。如last_insert_id，now()等函数。

• MIXED： 以上两种模式的混合模式，一般会用STATEMENT模式保存binlog，对于STATEMENT 模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

3.2 Binlog 文件结构

binlog 文件记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是log event。
比较常用的log event 有：Query event、Row event、Xid event 等。
binlog 文件的内容就是各种Log event 的集合。

binlog 文件中 log event 结构如下图所示：

3.3 Binlog 的写入机制

• 根据记录模式和操作触发event 事件生成log event。
• 将事务执行过程中产生的log event 写入缓冲区，每个事务线程都有一个缓冲区。
  log event 保存在一个binlog_cache_mngr 数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
• 事务在提交阶段会将产生的log event 写入到外部的binlog 文件中。
  不同的事务以串行方式将log event 写入到binlog文件，所以一个事务包含的log event 信息在binlog 中是连续的，中间不会插入其他事务的binlog 。binlog是引擎插件上层的功能，事务提交第一个就会调用binlog 功能接口，然后再调用其他存储引擎功能接口。因此先写binlog，然后再执行innodb redolog、undo 和脏页刷新操作。

四、Binlog 文件操作

4.1 Binlog 状态查看

show variables like '%log_bin%'
show variables like 'log_bin'

修复数据的时候可以把sql_log_bin 关掉，以防止数据记录冗余。

4.2 开启Binlog 功能

set global log_bin=1;
> 1238 - Variable 'log_bin' is a read only variable
> 时间: 0s

windows 系统需修改my.ini 配置文件，linux 系统需要修改my.cnf 配置文件，在 [mysqld] 下面增加如下代码，然后重启服务。

#log-bin=on
#log-bin-basename=mysqlbin
log-bin=mysqlbinlog  //简化了上面两个命令
binlog-format=ROW

4.3 使用show binlog events命令

show binary logs //等价于show master logs
show master status //当前使用的日志文件
show binlog events //查看内容(事件)
show binlog events in 'mysqlbinlog.000001'

show binlog events in 'mysqlbinlog.000001'

4.4 mysqlbinlog 命令

mysqlbinlog  的使用得进入到系统命令里面，和mysql的启动、停止、
备份等命令是平级的，在Data 目录下执行。
mysqlbinlog “mysqlbinlog.000001”

输出一个文件：
mysqlbinlog "mysqlbinlog.000001" > "mybinlog.sql"

按事件位置号恢复：
mysqlbinlog --start-position=154 --stop-position=385 mysqlbinlog.000001 | mysql -uroot -p
按时间恢复：
mysqlbinlog --start-datetime="2020-20-20 23:23:23" --stop-datetime="2020-20-20 23:23:23" mysqlbinlog.000001 | mysql -uroot -p

mysqldump：定期全部备份数据库数据。mysqlbinlog 可以做增量备份会恢复操作。

删除binlog 文件
purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
purge binary logs before '2020-20-20 20:20:20'; //删除指定时间之前的文件
reset master; //清除所有文件

可以通过设置expire_logs_days参数启动自动清理，默认值为0，表示未启用。设置1表示超出1天，binlog文件会自动删除掉：
show variables like '%expire_logs_days%'

五、Redo Log 与Binlog 的区别

• Redo Log 是属于InnoDB 引擎功能（与事务有关），Binlog 是属于MySQL server层自动的功能，并且以二进制文件记录。
• Redo Log 是物理日志，记录该数据页更新状态内容，Binlog 是逻辑日志，记录更新过程。
• Redo Log 是循环写的，日志空间大小是固定的，Binlog 是追加写入，不会覆盖使用。
• Redo Log 作为服务器异常宕机后事务数据恢复，内部自动机构使用，Binlog 是作为主从复制和数据恢复使用，没有自动恢复（crash-safe）能力。