ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日 [打印本页]
作者: 石小疯    时间: 2025-2-12 16:27
标题: MySQL日志详解——日志分类、二进制日志bin log、回滚日志undo log、重做日
文章目次
一、前言
二、bin log 二进制日志
三、undo log 回滚日志
四、redo log 重做日志
五、补充
MySQL作为最流行的开源数据库，其重要性不言而喻。日志是mysql数据库的重要组成部分，记载着数据库运行期间各种状态信息。常见的日志有以下几种：

作为开发，我们重点需要关注的是 二进制日志bin log（归档日志）、 事务日志redo log（重做日志） 和 undo log（回滚日志），本文接下来会详细介绍这三种日志。
下面就带着这个问题，看看这三种日志是怎么工作的。
一、前言

1.1 MySQL体系结构



1）连接层
最上层是一些客户端和链接服务，包含本地sock 通讯和大多数基于客户端/服务端工具实现的类似于TCP/IP的通讯。主要完成一些类似于连接处理处罚、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操纵权限。
2）服务层
第二层架构主要完成大多数的焦点服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的序次，是否利用索引等，最后天生相应的执行操纵。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操纵的环境中能够很好的提升系统的性能。
3）引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通讯。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
4）存储层
数据存储层，主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥精良作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理处罚和其他的系统使命以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
其中slowlog、binlog、errorlog、relaylog归属于MySQL服务层；undolog、redolog归属于引擎层，为innodb所特有。
1.2 MySQL日志分类

MySQL有不同类型的日志：慢查询日志、通用查询日志、错误日志、事务日志、二进制日志等几大类，在MySQL8之后又新增了两种日志——中继日志、数据界说语句日志。其中比较重要的是二进制日志binlog (归档日志) 、事务日志redo log(重做日志) 和 undo log(回滚日志)。
MySQL日志主要包括八种
除了二进制日志外，其他日志均为文本文件。默认情况下，所有日志均创建于MySQL数据目次中
1.3 其他几种日志

1.3.1 查询日志

查询日志中记载了客户端的所有操纵语句（包括所有的增编削查、DDL、DML、DQL语句），而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：


如果想要禁用查询日志，可将general_log设置为0，而后重启MySQL服务sudo systemctl restart mysql
1.3.2 慢查询日志

慢查询日志记载了所有执行时间超过参数 long_query_time 设置值并且扫描记载数不小于 min_examined_row_limit 的所有SQL语句的日志，默认未开启。long_query_time 默认为10秒，最小为0，精度可以到微秒。如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
  1. # 开启MySQL慢日志查询开关
  2. slow_query_log=1
  3. # 执行时间参数，设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
  4. long_query_time=2
复制代码


同理，如果想要禁用慢查询日志，可将slow_query_log设置为0，而后重启MySQL服务sudo systemctl restart mysql
1.3.3 错误日志

错误日志是MySQL中最重要的日志之一，它记载了当mysqld启动和制止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何以障导致无法正常利用时，建议首先查看这天志。
该日志是默认开启的，默认存放目次/var/log/，默认的日志文件名为mysqld.log。查看日志位置：
  1. -- 登录mysql，查看系统变量
  2. show variables like '%log_error';
复制代码

二、bin log 二进制日志

2.1 bin log简介

二进制日志（BINLOG）记载了所有的DDL（数据界说语言，创建库、表）语句和DML（数据操纵语言，增编削）语句，但不包括数据查询（SELECT、SHOW）语句。
作用：
细节：
在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：
  1. show variables like '%log_bin';
复制代码

常用的5.7版本可能只有以下参数：
  1. mysql> SELECT VERSION();
  2. +-----------+
  3. | VERSION() |
  4. +-----------+
  5. | 5.7.35    |
  6. +-----------+
  7. 1 row in set (0.00 sec)
  9. mysql> show variables like '%log_bin';
  10. +---------------+-------+
  11. | Variable_name | Value |
  12. +---------------+-------+
  13. | log_bin       | OFF   |
  14. | sql_log_bin   | ON    |
  15. +---------------+-------+
  16. 2 rows in set (0.00 sec)
复制代码
2.2 binlog日志格式

binlog日志有三种格式：分别为STATMENT、ROW和MIXED，详细格式及特点如下：
日志格式含义STATEMENT基于SQL语句的日志记载，记载的是SQL语句，对数据进行修改的SQL都会记载在日志文件中ROW默认格式，基于行的日志记载，记载是每一行的数据变更MIXED混淆了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记载
  1. show variables like '%binlog_format%';
  3. -- 如果想修改二进制日志格式
  4. --  1.vim /etc/my.cnf
  5. --  2.往文件内添加 binlog_format=STATEMENT
  6. --  3.systemctl restart mysqld
复制代码

详细日志格式详解
由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看，详细语法：
  1. mysqlbinlog [options] log-files
  3. 参数选项：
  4.     -d, --database=name     指定数据库名称，只列出指定的数据库相关操作
  5.     -o, --offset=#          忽略掉日志中的前n行命令
  6.     -v, --verbose           将行事件(数据变更)重构为SQL语句
  7.     -vv                     将行事件(数据变更)重构为SQL语句，并输出注释信息
  8.    
  9.    
  10. mysqlbinlog -v binlog.00002
复制代码
mysql中有score记载结果，执行 update score set math = math + 1;
ROW格式下，执行mysqlbinlog -v binlog.00002，得到如下内容

STATEMENT格式下，执行mysqlbinlog binlog.00003，得到如下内容

2.3 日志删除

对于比较繁忙的业务系统，每每天生的binlog数据巨大，如果长时间不扫除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：
MySQL指令含义reset master;删除全部binlog日志，删除之后，日志编号将从binlog.000001重新开始purge master logs to 'binlog.******';删除******编号之前的所有日志purge master logs before 'yyyy-mm-dd hh24:mi:ss';删除日志为“yyyy-mm-ddhh24:mi:ss"之前产生的所有日志2.4 写入/刷盘机制

事务执行过程中，先把日志写入binlog cache，事务提交时，再把binlog cache写到binlog文件中。一个事务的binlog不能被拆开，确保一次性写入，系统将给每个线程分配一块内存作为binlog cache
对于InnoDB存储引擎而言，只有在事务提交时才会记载binlog，那么binlog什么时间才会将内存中的数据刷到磁盘呢？实在mysql是通过sync_binlog参数控制binlog的刷盘时机，取值范围是0-N【write和fsync的时机，由参数sync_binlog控制，默认为0】
在出现IO瓶颈时，将sync_binlog设置成一个较大的值，能提升性能。同样的，若呆板宕时机丢失最近N个事务的binlog日志
三、undo log 回滚日志

3.1 undo log简介

undo log（回滚日志）：是Innodb存储引擎层天生的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
它是InnoDB存储引擎在insert、update、delete的时间产生的便于数据回滚的日志。在数据更新之前，MySQL就需要先把更新前的数据记载到 undo log 日志中，当事务回滚时，可以利用 undo log 来进行回滚。作用包含两个——提供回滚、MVCC（多版本并发控制）。undo log主要分为两种：
事务需要包管原子性，也是说事务中的操纵要么全部完成，要么什么也不做。如果事务执行到一半，堕落了怎么办-回滚。但是怎么回滚呢，靠 undo 日志。undo 日志就是我们执行sql的逆操纵
好比现在Tom的账户余额有100，现在有一个事务需要把Tom的账户余额更新为300，大抵的流程如下图：

3.2 隐藏字段 —— 事务ID(TRX_ID)、ROLL_PTR


当我们创建了上面的这张表，我们在查看表结构的时间，就可以显式的看到 id、user_name、balance、wealth 这四个字段。 实际上除了这四个字段以外，InnoDB还会自动的给我们添加三个隐藏字段，分别是：
隐藏字段含义DB_TRX_ID最近修改事务ID，记载插入这条记载或最后一次修改该记载的事务ID。DB_ROLL_PTR回滚指针，指向这条记载的上一个版本，用于共同undo log，指向上一个版本。DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会天生该隐藏字段。而上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段。
3.3 版本链

不同事务或相同事务对同一条记载进行修改，会导致该记载的undolog天生一条记载版本链表，链表的头部是最新的旧记载，链表尾部是最早的旧记载。

然后，有四个并发事务同时在访问这张表。


四、redo log 重做日志

事务日志redo log（重做日志）：是Innodb存储引擎层天生的日志，实现了事务中的长期性，主要用于掉电等故障恢复。好比MySQL实例挂了或宕机了，重启时，InnoDB存储引擎会利用redo log恢复数据
介绍下 缓冲池与数据页的概念
MySQL中数据是以页为单元，你查询一条记载，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到Buffer Pool中。后续的查询都是先从Buffer Pool中找，没有命中再去硬盘加载，淘汰硬盘IO开销，提升性能。
更新表数据的时间，也是如此，发现Buffer Pool里存在要更新的数据，就直接在Buffer Pool里更新。然后会把在某个数据页上做了什么修改记载到重做日志缓存(redo log buffer)里，接着刷盘到redo log文件里。同时，InnoDB引擎会在适当的时间，将这个操纵记载更新到磁盘里面。
redo log的更新流程如下，以一次update操纵为例

4.1 redo log详解

什么是 redo log？为了方便理解，先举个来自极客时间的例子：
还记得《孔乙己》这篇文章，饭店掌柜有一个粉板，专门用来记载客人的赊账记载。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时间，这个时间掌柜一定另有一个专门记载赊账的账本。
如果有人要赊账或者还账的话，掌柜一般有两种做法：
在买卖红火柜台很忙时，掌柜一定会选择后者，由于前者操纵实在是太贫苦了。首先，你得找到这个人的赊账总额那条记载。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘盘算，最后再将结果写回到账本上。
这整个过程想想都贫苦。相比之下，照旧先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？
为什么需要 redo log？
为什么需要写Redo Log Buffer 和 Redo Log Flle？而不是直接长期化到磁盘？
跟上述案例类似，在 MySQL 中，如果每一次的更新要写进磁盘，这么做会带来严重的性能问题：
为了解决这个问题，MySQL 的设计者就用了类似掌柜粉板的思路来提升更新效率。这种思路在 MySQL 中叫 WAL（Write-Ahead Logging），意思就是：先写 redo log 日志，后写磁盘。日志和磁盘就对应上面的粉板和账本。
详细到 MySQL 是这样的：有记载需要更新，InnoDB 把记载写到 redo log 中，并更新内存中的数据页，此时更新就算完成。同时，后台线程会把操纵记载更新异步到磁盘中的数据页。
PS：当需要更新的数据页在内存中时，就会直接更新内存中的数据页；不在内存中时，在可以利用 change buffer（篇幅有限，后面写文章再聊） 的情况下，就会将更新操纵记载到 change buffer 中，并将这些操纵记载到 redo log 中；如果此时有查询操纵，则触发 merge 操纵，返回更改后的记载值。
有些人说 InnoDB 引擎把日志记载写到 redo log 中，redo log 在哪，不也是在磁盘上么？
对，这也是一个写磁盘的过程，但是与更新过程不一样的是，更新过程是在磁盘上随机 IO、费时， 而写 redo log 是在磁盘上序次 IO、效率要高。
PPS：redo log 的存在就是把全局的随机写，变更为局部的序次写，从而提高效率。
4.2 redo log的写入过程、刷盘时机

redo log 记载了事务对数据页做了哪些修改。它包括两部分：分别是内存中的日志缓冲（redo log buffer）和磁盘上的日志文件（redo log file）。
mysql 每执行一条 DML 语句，先将记载写入 redo log buffer，后续某个时间点再一次性将多个操纵记载写到 redo log file。也就是我们上面提到的 WAL 技能。
盘算机操纵系统告诉我们：用户空间下的缓冲区数据是无法直接写入磁盘的。由于中间必须经过操纵系统的内核空间缓冲区（OS Buffer）。
以是，redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer，然后操纵系统调用 fsync() 函数将日志刷到磁盘。过程如下：

mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：建议设置成1，这样可以包管MySQL 异常重启之后数据不丢失。
参数值含义0（耽误写）事务提交时不会将 redo log buffer 中日志写到 os buffer，而是每秒写入os buffer 并调用 fsync() 写入到 redo logfile 中。也就是说设置为0时 是（大约）每秒革新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。1（及时写、及时革新）事务每次提交都会将 redo log buffer 中的日志写入 os buffer 并调用 fsync() 刷到 redo logfile 中。这种方式即使系统崩溃也不会丢失任何数据，但是由于每次提交都写入磁盘，IO的性能差。2（及时写、耽误革新革新）每次提交都仅写入到 os buffer，然后是每秒调用 fsync() 将 os buffer 中的日志写入到 redo log file。4.3 redo log file 的结构

InnoDB 的 redo log 是固定巨细的。好比可以配置为一组 4 个文件，每个文件的巨细是 1GB，那么 redo log file 可以记载 4GB 的操纵。从头开始写。写到末端又回到开头循环写。如下图：

上图中，write pos 表示 redo log 当前记载的 LSN (逻辑序列号) 位置，一边写一遍后移，写到第 3 号文件末端后就回到 0 号文件开头； check point 表示数据页更改记载刷盘后对应 redo log 所处的 LSN(逻辑序列号) 位置，也是今后推移并且循环的。
PS：check point 是当前要擦除的位置，它与数据页中的 LSN 应当是一致的。
write pos 到 check point 之间的部分是 redo log 的未写区域，可用于记载新的记载；check point 到 write pos 之间是 redo log 已写区域，是待刷盘的数据页更改记载。
当 write pos 追上 check point 时，表示 redo log file 写满了，这时间有就不能执行新的更新。得停下来先擦除一些记载（擦除前要先把记载刷盘），再推动 check point 向前移动，腾出位置再记载新的日志。
4.4 什么是 crash-save

有了 redo log ，即在 InnoDB 存储引擎中，事务提交过程中任何阶段，MySQL 忽然奔溃，重启后都能包管事务的完整性，已提交的数据不会丢失，未提交完整的数据会自动进行回滚。这个能力称为 crash-safe，依赖的就是 redo log 和 undo log 两个日志。
好比：重启 innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的 LSN 小于日志中 check point 的 LSN ，则会从 checkpoint 开始恢复。
4.5 redo log细节

五、补充

5.1 总结、利用场景

binlog的应用场景

undolog的利用场景

redolog的利用场景

掉电等故障恢复
redo log一旦提交意味着长期化了，但是有时间需要对其进行rollback操纵，那就需要undo log
主从：写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记载，也不会影响读请求的执行；
5.2 binlog与redolog对比、逻辑日志与物理日志

redo log 和 binlog 主要有三种不同：
逻辑日志：可以简单理解为记载的就是sql语句
物理日志：由于mysql数据最终是保存在数据页中的，物理日志记载的就是数据页变更
如果不警惕整个数据库的数据被删除了，能利用redo log文件恢复数据吗？
不可以利用redo log文件恢复，只能利用binlog文件恢复。
由于redo log文件是循环写，是会边写边擦除日志的，只记载未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从redo log文件里擦除；
binlog文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记载在binlog上的数据，都可以恢复，以是如果不警惕整个数据库的数据被删除了，得用binlog文件恢复数据。
5.3 update语句的执行流程

了解了binlog、redolog两种日志的概念，再来看看执行器和 InnoDB 引擎在执行 update 语句时的流程：
整个过程如下图所示，其中橙色框表示是在 InnoDB 内部执行的，绿色框表示是在执行器中执行的：

5.4 两阶段提交

在执行更新语句过程中，会记载redolog、binlog两块日志，以基本的事务为单元。
redolog在事务执行过程中可以不断写入，binlog只在提交事务时写入
为了解决日志之间的逻辑一致问题，InnoDB存储引擎利用两阶段提交方案

从图中可以看出，在最后提交事务的时间，有3个步调：
先写处于prepare状态的redo log，事务提交后，再写处于commit状态的redo log。由于redo log的提交分为prepare和commit两个阶段，以是称之为两阶段提交。
redo log(重做日志)让 InnoDB 存储引擎拥有了崩溃恢复能力；bin log(归档日志)包管了MySQL集群架构的数据一致性
为什么需要两阶段提交？
由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的序次。我们看看这两种方式会有什么问题（redo log与bin log两份日志之间的逻辑不一致，会出现什么问题）。
仍然用前面的 update 语句来做例子，update table set age=age+1 where id=2。假设当前 id=2 的行，字段 age 的值是 22，再假设执行update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？


再看一个场景，redo log 设置commit阶段发生异常，那会不会回滚事务呢？
并不会回滚事务，固然redo log是处于prepare 阶段，但是能通过事务id找到对应的bin log日志，以是MySQL认为是完整的，就会提交事务恢复数据。

以是，如果不利用"两阶段提交"，数据库的状态就有可能和用 binlog 恢复出来的不一致。
另外：sync_binlog 这个参数建议设置成 1，表示每次事务的binlog都长期化到磁盘，这样可以包管MySQL异常重启之后 binlog 不丢失。
5.5 MySQL主从复制


MySQL主从复制的焦点就是二进制日志binlog
二进制日志（BINLOG）记载了所有的 DDL（数据界说语言，创建库、表）语句和 DML（数据操纵语言，增编削）语句，但不包括数据查询（SELECT、SHOW）语句。
5.5.1 作用

5.5.2 原理

实际上主从同步是基于binlog进行数据同步的。在主从复制过程中，会基于3个线程来操纵，一个主库线程、两个从库线程

复制过程就是将binlog中的数据从主库传输到从库上，这个过程一般是异步的，即主库上执行事务操纵的线程不会等候复制binlog的线程同步完成。主从复制概括为 ：在Master端开启binlog ，从库将master的binlog拷贝到它的中继日志relay log，slave端重放binlog 从而达到主从数据一致
简单来说分成三步：
MySQL复制时异步且串行化的，重启后从接入点开始复制。


详细详细过程如下：
在完成主从复制之后，你就可以在写数据时只写主库、读数据时只读从库，这样即使写请求会锁表或者锁记载，也不会影响读请求的执行。
从库数目增加，从库连接上来的/O线程也比较多，主库也要创建同样多的log dump线程来处理处罚复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。
以是在实际利用中，一个主库一般跟2~3个从库(1套数据库，1主2从1备主)，这就是一主多从的MySQL集群结构。
5.5.3 同步数据一致性

主从同步要求
主从耽误缘故原由
网络正常情况下，主从耽误的主要来源是备库接收完binlog和执行完这个事务之间的时间差
主从耽误的直接体现：从库消费中继日志的速度比主库生产binlog的速度慢
淘汰主从耽误的方案
数据一致性问题的解决
若操纵的数据存储在同一个数据库中，那么对数据进行更新时，可对记载加写锁，这样在读取时就不会发生数据不一致的情况，但从库的作用仅为备份，未起到读写分离、分担主库读压力的作用

读写分离情况下，解决主从同步中数据不一致的问题，就是解决主从之间数据复制方式的问题。若按照数据一致性的从弱到强分别，有3种复制方式：异步复制、半同步复制、组复制
异步复制

半同步复制

组复制
异步复制、半同步复制都无法最终包管数据一致性问题
组复制技能，MRG（MySQL Group Replication），于MySQL在5.7.17推出的一种新的数据复制技能，基于Paxos协议的状态机复制
MGR如何工作?
将多个节点共同组成一个复制组，在执行读写（RW）事务时，需要通过一致性协议层同意，当同意节点数目大于(N/2+1）时才可进行提交，针对只读（RO）事务则不需要组内同意，直接COMMIT即可
在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而包管组内数据一致性

参考：
MySQL面试题（最全、超详细）—— 定位慢查询、undo log与redo log
MVCC 原理分析、MySQL是如何解决幻读的
《MySQL》系列 - 十张图详解 MySQL 日志（建议收藏）
MySQL进阶（日志）——MySQL的日志 & bin log (归档日志) & 事务日志redo log(重做日志) & undo log(回滚日志)
MySQL面试资料

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4