mysql_二日志

MySQL日志文件有哪些？分别介绍下作用？

MySQL日志文件有很多，包括：

错误日志（error log）：错误日志文件对MySQL的启动、运行、关闭过程进行了记录，能帮助定位MySQL问题。
慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
一般查询日志（general log）：一般查询日志记录了所有对MySQL数据库请求的信息，无论请求是否正确执行。
二进制日志（bin log）：关于二进制日志，它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。

还有两个InnoDB存储引擎特有的日志文件：

重做日志（redo log）：重做日志至关重要，因为它们记录了对于InnoDB存储引擎的事务日志。
回滚日志（undo log）：回滚日志同样也是InnoDB引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志；如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子。

binlog和redo log有什么区别？

bin log会记录所有与数据库有关的日志记录，包括InnoDB、MyISAM等存储引擎的日志，而redo log只记InnoDB存储引擎的日志。
记录的内容不同，bin log记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而redo log记录的是关于每个页（Page）的更改的物理情况。
写入的时间不同，bin log仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有redo ertry被写入redo log中。
写入的方式也不相同，redo log是循环写入和擦除，bin log是追加写入，不会覆盖已经写的文件。

一条更新语句怎么执行的了解吗？

更新语句的执行是Server层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志。

执行器先找引擎获取ID=2这一行。ID是主键，存储引擎检索数据，找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redo log的写入。

不仅如此，在对redo log写入时有两个阶段的提交，一是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入。

MySQL中重要的两个日志 – `redo log`、`binlog`

MySQL分层架构就不多说了

MySQL整体分为3层：客户端层，Server层和存储引擎层。我们的binlog日志，由Server层生成，redo log是InnoDB特有的日志，由InnoDB引擎生成。

重做日志（redo log）

什么是redo log

InnoDB为了能够支持事务一系列操作，而事务有4种特性：原子性、一致性、隔离性、持久性，在事务操作中，要么全部执行，要么全部不执行，这就是事务的目的。而我们的redo log用来保证事务的持久性，即我们常说的ACID中的D。我们只需要知道它是通过一套什么样的机制，来保证持久性，就能掌握好redo log。

这里的说的持久性，是说最后落盘到redo log文件（即常见的ib_logfile文件），因为最后我们异常情况的恢复，都是根据文件来做恢复的。

那么我们的MySQL InnoDB是通过一套什么样的机制来确保速度与可靠性的呢？

WAL

在计算机体系中，CPU处理速度和硬盘的速度，是不在同一个数量级上的，为了让它们速度匹配，从而催生了我们的内存模块，但是内存有一个特点，就是掉电之后，数据就会丢失，不是持久的，我们需要持久化的数据，最后都需要存储到硬盘上。

InnoDB引擎设计者也利用了类似的设计思想，先写内存，再写硬盘，这样就不会因为redo log写硬盘IO而导致数据库性能问题。在InnoDB中，这种技术有一个专业名称，叫做Write-Ahead Log（预先日志持久化）

redo log写入策略

上边是保证了处理的速度，但是怎么样保证写入到硬盘的可靠性呢？

InnoDB引擎的设计者也设计了一种写入的策略，首先有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘（即redo log文件 ib_logfile0 ib_logfile1）。

为了控制 redo log写入策略，InnoDB提供了innodb_flush_log_at_trx_commit配置参数，它有三种取值：

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

如果不是对性能要求高的，一般把该参数设置为 1

MySQL里常说的WAL技术，全称是Write Ahead Log，即当事务提交时，先写redo log，再修改页。也就是说，当有一条记录需要更新的时候，InnoDB会先把记录写到redo log里面，并更新Buffer Pool的page，这个时候更新操作就算完成了

Buffer Pool是物理页的缓存，对InnoDB的任何修改操作都会首先在Buffer Pool的page上进行，然后这样的页将被标记为脏页并被放到专门的Flush List上，后续将由专门的刷脏线程阶段性的将这些页面写入磁盘

redo log的擦除

通过上边的设计，速度和可靠性的问题都解决了，但是我们仔细想想，还会有什么问题？

随着文件的增加，落盘的速度会越来越慢，直到有一天 …

聪明的设计者这样子想着，如果我一直处理小文件，最大不能超过某个大小，不就行了？

也确实是这样子处理的，但是这里就涉及到一个删除日志文件的算法，即我们的redo log擦除。

重做日志缓存、重做日志文件都是以块（block）的方式进行保存的，称之为重做日志块（redo log block）,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。

redo log 的大小是固定的，比如可以配置一组4个文件，每个文件大小是8M，那么这个redo log总共就可以记录32M的操作，这个参数可以通过innodb_log_file_size设置。

下图是具体的擦除算法，ib_logfile 从头开始写，写到末尾就又回到开头循环写。

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后移动并且循环的，擦除记录前要把记录更新到数据文件，write pos与check point之间为剩余可用写入的空间。

何时会擦除redo log并更新到数据文件中

MySQL Server正常关闭时
系统空闲时
Redo log文件没有空闲空间时，即write pos追上check point的时候；
事务提交时
后台线程输入

crash-safe

有了以上这一些机制保障，我们可以相信redo log是可靠的，只要持久化到redo log文件中了，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，而我们把这个能力称为 crash-safe。

归档日志（binlog）

前边讲过，redo log是InnoDB引擎特有的日志，是引擎层面的日志，而在我们的数据库的Server层面，也有自己的日志，称为binlog（归档日志）。

binlog是逻辑日志，怎么样来理解这个逻辑日志呢？

我们通过查看一段binlog来理解。

理解逻辑日志

这里一大段的操作，都是为了查看binlog文件里边存储的是什么内容，熟悉的读者可以直接略过。

执行命令，写入新binlog文件，不让之前的逻辑影响。

执行一次flush logs命令行，就会在data目录下新增一个mysql-bin.00000x文件

## 登陆MySQL命令行
mysql -uroot -p
## 刷新binlog
flush BINARY  logs;
## binlog日志列表
SHOW BINARY LOGS;
## 确认刷新binlog成功
SHOW BINARY LOG STATUS;
## 查询binlog日志位置
show variables like'log_bin%';

更新几次你数据库表数据

翻译binlog二进制文件

查看翻译出来的mysqlbin.sql文件

这是翻译出来的sql文件，是因为我在mysqlbinlog -v参数加工而成的。

由此可知，逻辑日志里边就是记录着sql语句，通过sql语句记录着逻辑的变化，比如insert, update等动作，但不是记录具体数据，那个由物理日志完成。

与redo log的区别

redo log是innoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都能使用；
redo log是循环写的，空间固定会用完；binlog是追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

binlog写入策略

通过与redo log的区别，我们知道，binlog是追加写入的，所以与redo log写入相比，没有擦除的概念。那么，还有一些什么样的其他的区别呢？

binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cahce，事务提交的时候，再把binlog cache写到binlog文件中（落盘）。

从上图可以看到，每个线程都有自己的binlog cache，但是共用同一份binlog文件。

图中的write，指的就是把日志写入到数据库系统的page cache，并没有把数据持久化到磁盘，所有速度很快；

图中的fsync，才是将数据持久化到磁盘的操作。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

浅谈两阶段提交

这里讲的两阶段提交，就是纯粹的指redo log和binlog日志的两阶段提交。

而两阶段提交的目的就是让redo log和binlog两个日志逻辑上一致。

如果redo log持久化并进行了提交，而binlog未持久化数据库就crash了，则从库从binlog拉取数据会少于主库，造成不一致。因此需要内部事务来保证两种日志的一致性。

将语句执行
记录redo log，并将记录状态设置为prepare
通知Server，已经修改好了，可以提交事务了
将更新的内容写入binlog
commit，提交事务
将redo log里这个事务相关的记录状态设置为commited

prepare： redolog写入log buffer，并fsync持久化到磁盘，在redolog事务中记录2PC的XID，在redolog事务打上prepare标识

commit： binlog写入log buffer，并fsync持久化到磁盘，在binlog事务中记录2PC的XID，同时在redolog事务打上commit标识其中，prepare和commit阶段所提到的“事务”，都是指内部XA事务，即2PC

恢复步骤

redolog中的事务如果经历了二阶段提交中的prepare阶段，则会打上prepare标识，如果经历commit阶段，则会打上commit标识（此时redolog和binlog均已落盘）。

按顺序扫描redolog，如果redolog中的事务既有prepare标识，又有commit标识，就直接提交（复制redolog disk中的数据页到磁盘数据页）
如果redolog事务只有prepare标识，没有commit标识，则说明当前事务在commit阶段crash了，binlog中当前事务是否完整未可知，此时拿着redolog中当前事务的XID（redolog和binlog中事务落盘的标识），去查看binlog中是否存在此XID

如果binlog中有当前事务的XID，则提交事务（复制redolog disk中的数据页到磁盘数据页）
如果binlog中没有当前事务的XID，则回滚事务（使用undolog来删除redolog中的对应事务）

可以将mysql redolog和binlog二阶段提交和广义上的二阶段提交进行对比，广义上的二阶段提交，若某个参与者超时未收到协调者的ack通知，则会进行回滚，回滚逻辑需要开发者在各个参与者中进行记录。mysql二阶段提交是通过xid进行恢复。

Undo Log

Undo Log在下一篇mvcc中介绍

那ACID靠什么保证的呢？

事务的隔离性是通过数据库锁的机制实现的。
事务的一致性由undo log来保证：undo log是逻辑日志，记录了事务的insert、update、deltete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。
事务的原子性和持久性由redo log来保证：redolog被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入redo log持久化，到事务的提交操作才算完成。

于2025 年 5 月 18 日2025 年 5 月 18 日由jwq发布

MySQL日志文件有哪些？分别介绍下作用？

MySQL中重要的两个日志 – `redo log`、`binlog`