数据库原理四—MySQL日志

重做日志redo log

redo log是重做日志,为InnoDB存储引擎独有。它记录了数据页上的改动。当事务中修改了数据,将会备份存储。
当发生数据库服务器宕机或者脏页未写入磁盘,可以通过redo log恢复。

redo log用于配合MySQL的WAL机制。MySQL进行更新操作时,为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。
但是这样,会存在crash后内存数据丢失的隐患,而redo log具备crash safe的能力。

WAL，Write-Ahead Logging,关键点就是日志先写内存,再写磁盘。
MySQL执行更新操作后,在真正把数据写入到磁盘前,先记录日志。
好处是不用每一次操作都实时把数据写盘,就算crash后也可以通过redo log恢复,所以能够实现快速响应SQL语句。

Crash Safe，指MySQL服务器宕机重启后，能够保证：
1.所有已经提交的事务的数据仍然存在。
2.所有没有提交的事务的数据自动回滚。

redo log的写入方式

redo log包括两部分内容，分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。

mysql每执行一条DML语句，会先把记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术，就是WAL。

在计算机操作系统中，用户空间(user space)下的缓冲区数据，一般是无法直接写入磁盘的，必须经过操作系统内核空间缓冲区(即OS Buffer)。

• 日志最开始会写入位于存储引擎Innodb的redo log buffer，这个是在用户空间完成的。
• 然后再将日志保存到操作系统内核空间的缓冲区(OS buffer)中。
• 最后，通过系统调用fsync()，从OS buffer写入到磁盘上的redo log file中，完成写入操作。这个写入磁盘的操作，就叫做 刷盘。
  

我们可以发现，redo log buffer写入到redo log file，是经过OS buffer中转的。其实可以通过参数innodb_flush_log_at_trx_commit进行配置，参数值含义如下：

• 0：称为延迟写，事务提交时不会将redo log buffer中日志写入到OS buffer，而是每秒写入OS buffer并调用写入到redo log file中。
• 1：称为实时写，实时刷”，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。（默认）
• 2：称为实时写，延迟刷。每次事务提交写入到OS buffer，然后是每秒将日志写入到redo log file。（跳过了redo log buffer）

安全性从0->2->1递增

redo log的执行流程

我们来看下Redo log的执行流程，假设执行的SQL如下：

update T set a =1 where id =666

关于MySQL Server层及存储引擎层的概念,见数据库原理一—MySQL基本架构与索引

Redo log的执行流程如下:

1. MySQL客户端将请求语句update T set a =1 where id =666，发往MySQL Server层。
2. MySQL Server 层接收到SQL请求后，对其进行分析、优化、执行等处理工作，将生成的SQL执行计划发到InnoDb存储引擎层执行。
3. InnoDb存储引擎层将a修改为1的这个操作记录到内存中。
4. 记录到内存以后会修改redo log 的记录，会在添加一行记录，其内容是需要在哪个数据页上做什么修改。
5. 此后，将事务的状态设置为prepare ，说明已经准备好提交事务了。
6. 等到MySQL Server层处理完事务以后，会将事务的状态设置为commit，也就是提交该事务。
7. 在收到事务提交的请求以后，redo log会把刚才写入内存中的操作记录写入到磁盘中，从而完成整个日志的记录过程。

Q&A

Q: redo log 为什么可以保证crash safe机制呢？

• 因为redo log每次更新操作完成后，就一定会写入的，如果写入失败，说明此次操作失败，事务也不可能提交。（注意上文redo log执行流程,在事务提交之前,就已经完成了redo log的更新）
• redo log内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redo log进行重放，就可以恢复数据。

归档日志bin log

• bin log是归档日志，属于 MySQL Server层的日志。可以实现 主从复制和 数据恢复两个作用。
• 当需要恢复数据时，可以取出某个时间范围内的bin log进行重放恢复。
• 但是bin log不可以做crash safe，因为crash之前，bin log可能没有写入完全MySQL就挂了。所以需要配合redo log才可以进行crash safe。

bin log & redo log

redo log bin log 作用 用于崩溃恢复 主从复制和数据恢复 实现方式 Innodb存储引擎实现 Server层实现,所有的存储引擎都可以使用binlog日志 记录方式 循环写的方式记录,写到结尾时,会回到开头循环写日志 通过追加的方式记录,当文件尺寸大于给配置值后,后续的日志会记录到新的文件上 文件大小 redo log的大小是固定的 通过配置参数max_binlog_size设置每个binlog文件大小 crash-safe能力 具有 没有 日志类型 物理日志 逻辑日志

（原文中这里redo log是逻辑日志,bin log是物理日志，这里更正一下：redo log 是物理日志，记录的是”在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑）

如果数据库误操作, 如何执行数据恢复?
数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。

MySQL二阶段提交

MySQL二阶段提交不同于从本地事务到分布式事务，浅谈事务的分布式一致性算法提到的二阶段提交，那篇博文中的二阶段提交指的是分布式事务的二阶段提交,是多节点下的数据同步，而此处的二阶段提交是指单节点下事务提交的两阶段。

• redo log在写入后，进入prepare状态
• 执行器写入bin log
• 进入commit状态，事务可以提交。

为什么需要两阶段提交呢?

• 如果不用两阶段提交的话，可能会出现这样情况：bin log写入之前，机器crash导致需要重启。重启后redo log继续重放crash之前的操作，而当bin log后续需要作为备份恢复时，会出现数据不一致的情况。
• 如果是bin log commit之前crash，那么重启后，发现redo log是prepare状态且bin log完整（bin log写入成功后，redo log会有bin log的标记），就会自动commit，让存储引擎提交事务。
• 两阶段提交就是为了保证redo log和binlog数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。你才能放心的使用redo log帮你将数据库中的状态恢复成crash之前的状态，使用binlog实现数据备份、恢复、以及主从复制。

如果不是两阶段提交, 先写redo log和先写bin log两种情况各会遇到什么问题?

• 先写redo log，crash后bin log备份恢复时少了一次更新，与当前数据不一致。
• 先写bin log，crash后，由于redo log没写入，事务无效，所以后续bin log备份恢复时，数据不一致。

bin log刷盘机制

所有未提交的事务产生的binlog，都会被先记录到binlog的缓存中。等该事务提交时，再将缓存中的数据写入binlog日志文件中。缓存的大小由参数binlog_chache_size控制。
binlog什么时候刷新到磁盘呢？由参数sync_binlog控制

• 当sync_binlog为0时，表示MySQL不控制binlog的刷新，而是由系统自行判断何时写入磁盘。选这种策略，一旦操作系统宕机，缓存中的binlog就会丢失。
• sync_binlog为N时，每N个事务，才会将binlog写入磁盘。。
• 当sync_binlog为1时，则表示每次commit，都将binlog 写入磁盘。

bin log日志三种格式

binlog日志有三种格式

• Statement：基于SQL语句的复制((statement-based replication,SBR))
• Row:基于行的复制。(row-based replication,RBR)
• Mixed:混合模式复制。(mixed-based replication,MBR)

Statement格式
每一条会修改数据的sql都会记录在binlog中

• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
• 缺点：由于只记录执行语句，为了使这些语句在从数据库上正确运行，还必须记录有关每条语句执行的一些信息，以确保所有语句在备用数据库中得到的结果与在主数据库中执行的结果相同。
  
  [En]

  disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave database, some information about the execution of each statement must also be recorded to ensure that all statements get the same results in the standby database as they are executed in the main database.*

Row格式
不记录sql语句上下文相关信息，仅保存哪条记录被修改。

• 优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
• 缺点:可能会产生大量的日志内容。

Mixed格式
实际上就是Statement与Row的结合。一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

MySQL主从复制

什么是MySQL的主从复制
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

原理:

1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中
2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

回滚日志undo log

• undo log 叫做回滚日志，用于记录数据被修改前的信息。
• 它跟redo log重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，这样发生错误时才可以回滚。
• undo log和redo log记录物理日志不一样，它是逻辑日志。 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
• 当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。(MVCC)

MySQL事务提交过程

参考链接

MySQL日志15连问—捡田螺的小男孩
mysql主从复制原理-binlog—低调人生

Original: https://www.cnblogs.com/winter0730/p/15596676.html
Author: cos晓风残月
Title: 数据库原理四—MySQL日志