MYSQL中redo log和binlog之间的区别

雾岛与鲸

已于 2023-04-06 17:25:47 修改

阅读量177

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

于 2023-03-29 15:14:24 首次发布

原文链接：https://blog.csdn.net/qq_45243783/article/details/125040675

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

mysql给我们提供了很多有用的日志，这是mysql服务层给我们提供的：

解析MySQL中的六种日志

日志类型	写入日志的信息
二进制日志	记录了对MySQL数据库执行更改的所有操作
慢查询日志	记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
错误日志	记录在启动，运行或停止mysqld时遇到的问题
通用查询日志	记录建立的客户端连接和执行的语句
中继日志	从复制主服务器接收的数据更改

假设读者这里已经对于mysql的底层有了一定了解，知道一条sql语句的大致执行流程，再来阅读此文章效果可能会更好

redo log：InnoDB 存储引擎层方面的日志，所以如果你使用的存储引擎不是 InnoDB 的话，那就根本谈不上 redo log。
bin log: MySQL Server 层记录的日志，所以不管是用的什么存储引擎，只要是 MySQL 都是会有 binlog 的存在，在做 MySQL 主从复制的时候，利用的就是 binlog。

接下来，我们就详细来看看它们都分别做了啥？

redo log

在这里插入图片描述

为什么要有这个 redo log 日志文件呢？

这里，我们可以举个例子，现在我们想对数据库的数据进行修改操作，现在一条 update 语句过来，一般 update 操作都伴随着查询的操作，得先找到这条数据，然后再进行更新操作对吧。

如果数据量比较小还好，很快就能找到并且更新完毕，但是如果数据量比较大，里面有一亿条数据，怎么办？而且更新操作肯定是要写到磁盘上去的，那这中间的 IO 成本呢？

如果我有好几十条 update 语句先后更新呢？这样想的话，你就能想到，就这些操作，成本就高的不行，那能不能降低一下这些成本呢？

这时候，redo log 就起到作用了。当有一条记录更新的时候， InnoDB 引擎就会先把记录写到 redo log 里面去，同时更新内存，这样就算是更新这条数据成功了。

但是此时，它并没有更新到磁盘上去对吧？别担心， InnoDB 会在恰当的时候，把这条及记录更新到磁盘上去,而这个更新往往是在系统比较空闲的时候做。

这样的思想或者技术，有个专有名词: WAL 技术，也就是WriteAheadLogging，核心就是先写日志，再写磁盘。

redo log 不能一直写吧？

redo log 的大小是固定的，前面的内容会被覆盖，一旦写满，就会触发 redo log 到磁盘的同步，以便腾出空间记录后面的修改。

数据库发生宕机或者重启，数据也将不会丢失。

因为有了 redo log ，之前提交的记录都还在，只需要根据 redo log 里面的记录进行相应恢复就可以了。

bin log

binlog 是 MySQL Server 层的记录日志。
1、概述
二进制日志（binnary log）以【事件形式】记录了对MySQL数据库执行更改的所有操作。

binlog记录了所有数据库【表结构】变更（例如CREATE、ALTER TABLE…）以及【表数据】修改（INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

binlog是mysql server层维护的，跟采用何种引擎没有关系，记录的是所有的更新操作的日志记录。binlog是在事务最终commit前写入的。我们执行SELECT等不涉及数据更新的语句是不会记binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如innodb而言，必须要提交了事务才会记录binlog。

binlog 文件写满后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别于 redo log，redo log 是循环写入的，即后面写入的可能会覆盖前面写入的。

binlog有两个常用的使用场景：

主从复制：我们会专门有一个章节代领大家搭建一个主从同步的两台mysql服务。
数据恢复：通过mysqlbinlog工具来恢复数据。

mysql8中的binLog默认是开启的，5.7默认是关闭的，可以通过参数log_bin控制：

2、数据恢复
（1）确认binlog开启，log_bin变量的值为ON代表binlog是开启状态：

show variables like '%log_bin%';

  
  
  
  1

（2）为了防止干扰，我们flush刷新log日志，自此刻会产生一个新编号的binlog日志文件：

flush logs;

  
  
  
  1

（3）查看所有binlog日志列表：

show master logs;

  
  
  
  1

在这里插入图片描述

（4）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点,这一步可有可无：

在这里插入图片描述

（5）执行sql

先创建表，并插入一些数据：

DROP TABLE IF EXISTS ydl_student;
CREATE TABLE `ydl_student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd');

  
  
  
  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

执行删除操作，假装误删除，直接全部删除也可以，把表删了都行，一样的道理：

delete from ydl_student where id in (3,5);

  
  
  
  1

（6）查看binlog日志，我们因为刷新了日志，所以本次操作都会在最新的日志文件上：

因为 binlog 的日志文件是二进制文件，不能用文本编辑器直接打开，需要用特定的工具来打开，MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容：

# 查看全部的日志信息
/www/server/mysql/bin/mysqlbinlog -v mysql-bin.000008

  
  
  
  1
2

# 指定位置范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-position=0 --stop-position=986
# 指定时间范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2022-06-01 11:18:00" --stop-datetime="2022-06-01 12:18:00" 

  
  
  
  1
2
3
4

真实的情况下，我们的日志文件比较复杂，内容比较多使用时间范围查询后任然可能需要花费时间去排查问题，这里我们找到了误删除的位置：

在这里插入图片描述
（7）执行恢复，通过上一步的操作，我们找到了删除的位置3228（即第二个红框），执行下面的语句：

/www/server/mysql/bin/mysqlbinlog -v mysql-bin.000008 --stop-position=3228 -v | mysql -uroot -p

  
  
  
  1

（8）至此，数据已完全恢复了：
在这里插入图片描述

binlog的数据恢复的本质，就是将之前执行过的sql，从开始到指定位置全部执行一遍，如果报错【当前表已经存在】，就将数据库的表删除，重新恢复。

redo log 和 binlog 的区别：

redo log 是 InnoDB 引擎特有的； binlog 是 MySQL 的 Server 层实现的，所有的引擎都是可以的。
redo log 是物理日志，记录的是”在 XXX 页上做了 XXX 修改”；binlog 是逻辑日志，比如” 给 id = 2 这一行的 c 字段加 1”。
redo log 是有固定大小的，所以它的空间会用完，如果用完的话，一定要进行一些写入磁盘的操作才可以继续；binlog 是可以追加写入的，也就是 binlog 没有空间的概念，一直写就行了。

binlog 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。

在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操作重放一遍，来实现数据的恢复。

有了这两个日志之后，我们来看一下一条更新语句是怎么执行的（redo 不能一次写入了）：

在这里插入图片描述

例如一条语句：update user set name='小马' where id=1;

先查询到这条数据，如果有缓存，也会用到缓存
把 name 改成小马，然后调用引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。
执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log为 commit 状态
更新完成。

你能发现 redo log 竟然是先 prepare 状态，等 binlog 写完之后，才是 commit 状态，这种方式就叫”两阶段提交”。为什么会有这种方式呢？

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

可以假设一下，如果不采用这种方式，而是就先写 redo log ，再写 binlog ，会怎样？如果在写 binlog 时，发生了异常，更新操作已经到 redo log 中了，但是此时 binlog 并没有进行更新，是不是出现了数据不一致？

先写 binlog 再写 redo log 也是一样的道理。所以，在写时，先让 redo log 处于 prepare 状态，等 binlog 写完之后，再让 redo log 处于 commit 状态，这样就保持了逻辑上的一致。

-数据库连接池与connection分析