mysql之mysql各种日志binlog、redolog、undolog(二)

MySQL日志概述

https://segmentfault.com/a/1190000041758784

MySQL日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中比较重要的就是二进制日志binlog（归档日志）、事务日志redo log（重做日志）和undo log（回滚日志）。

binlog 逻辑日志 可以简单理解为记录sql语句，update delete add

redo log 物理日志 因为mysql最终是保存在数据页中的，物理日志记录的就是数据页的变更。

日志关系如下图：

一、binlog （Binary Log Files）

1.1 binlog概念

binlog是Mysql sever层维护的一种二进制日志，所有引擎都可以使用，逻辑日志。

与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中；

1.2 binlog作用

• 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
• 数据恢复：通过mysqlbinlog工具恢复数据
• 增量备份

1.3 binlog日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED。

在MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。

1、STATMENT：

基于SQL语句的复制（statement-based replication, SBR）即SQL语句原文，每一条会修改数据的SQL语句会记录到binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，从而提高了性能；缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。

比如执行一条update T set update_time = now() where id = 1，记录内容如下

2、ROW：

基于行的复制（row-based replication，RBR），不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了。记录的不再是简单的SQL语句了，还包含了操作的具体数据

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、MIXED：

基于STATMENT和ROW两种模式的混合复制（mixed-based replication，MBR），一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog。

MySQL会判断这条SQL语句是否会引起数据不一致，如果是就用row格式，否则就用statement格式。

1.4 binlog写入机制

binlog的写入时机为事务执行过程中，先把日志写到binlog cache，事务提交的时候再把binlog cache写到binlog文件中（实际先会写入page cache，然后再由fsync写入binlog文件）。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一块内存作为binlog cache。可以通过binlog_cache_size参数控制单线程binlog_cache大小，如果存储内容超过了这个参数，就要暂存到磁盘。

上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化硬盘，所以速度比较快。

上图的 fsync才是将数据库持久化到硬盘的操作。

write和fsync的时机可以由参数sync_binlog控制，可以配置成0、1、N(N>1)。对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？MySQL通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

设置成0时：表示每次提交事务都只会write，不去强制要求由系统自行判断什么时候执行fsync。

设置成1时：表示每次提交事务(commit)的时候都会执行fsync写入磁盘，就和redo log日志刷盘流程一样。

设置成N时：表示每次提交事务都会write，但是积累N个事务后才fsync写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

1.4 主从复制

1.4.1 主从复制原理

https://www.cnblogs.com/lemon-flm/p/15219993.html

上图说明，MySQL主从复制过程分成四步：

从库生成两个线程，一个 I/O 线程，一个 SQL 线程

当从库连接主库时，主库会生成一个 二进制转储(binlog dump) 线程，用来给从库 I/O 线程传 binlog

I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log(中继日志) 文件中。（在读取 binlog 的内容的操作中，会对主库的 binlog 加锁，当binlog读取完成并发送给从库后解锁。）

从SQL 线程会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，最终实现主从的数据一致。

复制过程有一个很重要的限制，就是复制在从库上是串行化的，也就是说主库上的并行更新操作不能在 从库上并行操作。

1.4.2 主从复制三种模式

https://blog.csdn.net/huangjhai/article/details/119192761

async : 异步 异步复制指主库以异步的方式同步数据到一个从库或多个从库中。

这种模式下，主节点不会主动推送数据到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。

这样就会有一个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从节点提升为主节点，可能导致新主节点上的数据不完整。

sync :同步

同步复制是在MySQL cluster 中特有的复制方式。

当主库执行完一个事务，然后所有的从库都复制了该事务并成功执行完才返回成功信息给客户端。

因为需要等待所有从库执行完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。

semi-sync : 半同步

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回成功信息给客户端(只能保证主库的 Binlog 至少传输到了一个从节点上)，否则需要等待直到超时时间然后切换成异步模式再提交。

相对于异步复制，半同步复制提高了数据的安全性，一定程度的保证了数据能成功备份到从库，同时它也造成了一定程度的延迟，但是比全同步模式延迟要低，这个延迟最少是一个 TCP/IP 往返的时间。所以半同步复制最好在低延时的网络中使用。

半同步模式不是 MySQL 内置的，从 MySQL 5.5 开始集成，需要 master 和 slave 安装插件开启半同步模式。

另外还有一个延迟复制的模式，延迟复制是在异步复制的基础上，人为设定主库和从库的数据同步延迟时间。

二、 relaylog

详细见 1.4 主从复制

三、redo log

3.1 redo log概念

redo log（重做日志） 是InnoDB引擎所特有的日志 物理日志 它让MySQL有了崩溃恢复的能力 重做日志文件 记录修改后数据

而粉板和账本配合的整个过程，其实就是MySQL里经常说到的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

当MySQL实例挂了或者宕机了，重启的时候InnoDB存储引擎会使用rede log日志恢复数据，保证事务的持久性和完整性。如下图：

3.2 redo log 刷盘时机

理想情况下，事务一提交就会进行刷盘操作，但是实际上是刷盘的时机是根据策略来决定的。

InnoDB存储引擎为redo log的刷盘策略提供了innodb_flush_log_at_trx_commit参数，它支持三种策略：

0：设置为0的时候，每次提交事务时不刷盘。

1：设置为1的时候，每次提交事务时刷盘。

2：设置为2的时候，每次提交事务时都只把redo log buffer写入page cache。

innodb_flush_log_at_trx_commit参数默认为1，当事务提交的时候会调用fsync对redo log进行刷盘，将redo log buffer写入redo log文件中。

另外，Innodb存储引擎有一个后台线程，每隔1秒，就会把会redo log buffer中的内容写入到文件系统缓存page cache，然后调用fsync刷盘。

3.3 redo log日志文件组

硬盘上存储的redo log日志文件不止一个，而是一个日志文件组的形式出现的，每个的redo log文件大小都是一样的。它采用的是环形数组形式，从头开始写，写到末尾回到头循环写，如下图所示：

在日志文件组中有两个重要的属性，分别是witre pos、checkpoint

• wirte pos：是当前记录的位置，一边写一边后移。
• checkpoint：是当前要擦除的位置，也是后台推移。 每次刷盘redo log记录到日志文件组中，wirte log位置就会后移更新。

每次MySQL加载日志文件组恢复数据时，会清空加载过的redo log，并把checkpoint后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 witre pos追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MySQL得停下来，清空一些记录，把checkpoint推进一下。

3.4 两阶段提交

redo log（重做日志）让InnoDB存储引擎有了崩溃恢复的能力。

binlog（归档日志）保证了MySQL集群架构数据的一致性。

虽然它们都属于持久化的保证，但是侧重点不一样。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog日志只有在提交事务的时候才会写入，所以它们写入的时机不一样

为了解决两份日志之间的逻辑不一致的问题，InnoDB存储引擎使用两阶段提交方案。

将redo log日志的写入拆分成两个步骤prepare和commit，如下图：

3.5 redo log与数据页

3.6 Redo Log和 Binlog 区别

Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。

Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。

Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。

Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。

(crash-safe 即在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL突然奔溃，重启后都能保证事务的完整性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。这个能力依赖的就是redo log和unod log两个日志。)

四、undo log

https://blog.csdn.net/Weixiaohuai/article/details/117867353

4.1 undo log概念

回滚日志undo log是mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

4.2 undo log作用

1、提供回滚操作【undo log实现事务的原子性】

想要保证事务的原子性，就需要在发生异常时，对已经执行的操作进行回滚，在MySQL中恢复机制是通过undo log（回滚日志）实现的，所有事务进行的修改都会先被记录到这个回滚日志，然后再执行其他相关的操作。如果执行过程中遇到异常的话，我们直接利用回滚日志中的信息将数据回滚到修改之前的样子。并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

2、提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】