理解MySQL的日志 redo、undo、binlog
1、MySQL 日志文件解决的问题
事务有 4 种特性(CAID):原子性、一致性、隔离性和持久性。
- 事务的隔离性由
锁机制
实现。 - 事务的原子性、一致性、持久性由事务的 redo 日志和 undo 日志来保证。
redo log
称为重做日志
,提供再写入操作,恢复提交事务修改的页操作,用来保证事务的持久性
。undo log
称为回滚日志
,回滚行记录到某个特定版本,用来保证事务的原子性、一致性
。
关于 MySQL 的几种日志 :
redo log
:是存储引擎层(InnoDB)生成的日志
,记录的是物理级别
上的页修改操作,比如页号xxx、偏移量yyy 写入’ddd’ 数据,主要为了保证数据的可靠性。undo log
:是存储引擎层(InnoDB)生成的日志
,记录的是逻辑操作日志
,保证了事务的原子性。例如当对某一条数据做 insert操作时,那么 undo log 会记录一条与之相反的 delete 操作。主要用于事务的回滚
(undo log 记录的是每个修改操作的逆操作)和一致性非锁定读
(undo log 回滚行记录到某条特定的版本-----MVCC,即多版本并发控制)。bin log
: 是数据库层产生的
。
2、redo 日志
2.1、redo log 的组成
InnoDB 存储引擎是以
页为单位
来管理存储空间的。在真正访问页面之前,需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才能访问
。所有的变更都必须先更新缓冲池中的数据
,然后缓冲池中的脏页会以一定的频率被刷入磁盘(checkPoint机制)
,通过缓冲池来优化 CPU 和磁盘之间的鸿沟,这样就可以保证整体的性能不会下降太快。
关于 redo 日志的组成:redo log 的写入并不是直接写入磁盘的,InnoDB 引擎会在写 redo log 的时候先写 redo log buffer,之后以一定的频率(根据刷盘策略)
刷入到真正的 redo log file 中。
-
重做日志的缓冲(redo log buffer)
,保存在内存中,是易丢失的。在服务器启动时,就像操作系统申请了一大片叫做 redo log buffer 的
连续内存
空间(redo日志缓冲区),该空间被划分成若干连续的 redo log block. -
重做日志文件(redo log file)
,保存在磁盘中,是持久的。
redo log 存储表空间ID、页号、偏移量以及需要更新的值,所需要的存储空间是很小的
,刷盘快(降低了刷盘频率)
。其特点是:
-
redo 日志是顺序写入磁盘的
。在执行事务的过程中,每执行一条语句,可能会产生多条redo日志,且这些redo日志是按照产生的顺序写入磁盘的,也就是使用顺序IO,效率比随机IO快
。 -
事务执行过程中,redo log不断记录
。假设一个事务,需要 insert 5万条数据,在这个过程中,一直不断的往 redo log 顺序记录。而bin log 是直到事务提交,才会一次写入到 bin log 文件中。
2.2、redo log 刷盘策略
redo log buffer 刷盘到 redo log file 的过程,并没有真正刷到磁盘中,只是刷到了**
文件系统缓存(page cache)
**,真正的写入会交给系统自己来决定(比如当page cache 足够大了)。所以,对于存储引擎 InnoDB而言存在一个问题,如果交给系统来同步,如果系统宕机,也会丢失数据。由此原因,InnoDB 给出了
innodb_flush_log_at_trx_commit
参数,该参数控制提交事务时,如何将 redo log buffer 中的日志刷新到 redo log file 中。
innodb_flush_log_at_trx_commit
参数说明:
参数值 | 参数值说明 |
---|---|
0 | 每次事务提交时不进行刷盘操作,系统默认 master thread 每隔 1 秒进行一次redo log 的同步。 |
1 | 每次事务提交时,都将进行同步,刷盘操作(默认值)。 |
2 | 每次事务提交时,只把redo log buffer内容写入 page cache,但不进行同步,由OS自己决定什么时候同步到磁盘文件。 |
注意:另外,InnoDB 引擎有一个后台线程,每隔 1 秒,会把 redo log buffer 中的内容写到文件系统缓存(page cache),然后调用刷盘操作
。因为在事务执行过程中, redo log 记录是会写入 redo log buffer 中的,这些 redo log 记录可能会被后台线程
刷盘,所以一个没有提交事务的 redo log 记录,也可能刷盘
。
-
值为1 时,只要事务提交成功,redo log 记录就一定在硬盘里,不会有任何数据丢失。如果事务执行期间 MySQL 挂了或宕机,这部分日志丢了,但是事务并没有提交,所以日志丢了也不会有损失,可以保证 ACID 中的 D,
数据绝对不会丢失
,但是效率是最差的
。建议使用默认值,虽然操作系统宕机的概率理论小于数据库宕机的概率,但是一般既然使用了事务,那么数据的安全相对来说更重要些。 -
值为0时,master thread 中的每一秒进行一次重做日志的 fsnc 操作,因此实例 crash 最多丢失 1秒钟内的事务(master thread 是负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性)。
数值为 0 的话,它的 IO 效率理论高于 值为 1 ,低于值为 2 . 这种策略也有丢失数据的风险,无法保证 D
.
2.3、MySQL 的 redo log解决了哪些问题
事务的持久性
:redo log确保在事务提交后,即使数据库发生故障或崩溃,也能恢复和持久化事务的更改。通过redo log,MySQL能够在重启后重新执行未写入数据文件的更改,保证数据的一致性和持久性。数据恢复
:在数据库崩溃或发生故障时,redo log可以作为恢复数据的重要手段。通过重做日志中的记录,MySQL可以将未提交的事务进行回滚,将已提交但未写入数据文件的事务进行恢复,以确保数据库的一致性。减少磁盘I/O操作
:redo log的存在可以减少对磁盘的I/O操作。相比于每次事务提交都直接写入数据文件,MySQL可以先将事务的更改写入redo log并刷盘,然后在适当的时机异步地将这些更改应用到数据文件中。这样可以减少磁盘I/O的次数,提高数据库的性能。
总而言之,MySQL的redo log解决了事务持久性、数据恢复和性能优化等方面的问题,确保数据库在故障或崩溃时能够保持数据的一致性和可恢复性,同时提升了数据库的整体性能。
3、undo 日志
3.1、undo 日志作用
-
①
回滚数据
-
undo log 是逻辑日志,其将数据库逻辑地恢复到原来的样子,所有修改都被逻辑地取消了,但是数据结构和页本身在回滚之后可能大不相同。
这是因为在多用户并发系统中,可能会有数百上千的并发事务。数据库的主要任务是协调对数据记录的并发访问。比如,一个事务在修改当前一个页中某几条记录,同时还有其他事务在对同一个页中另几条记录进行修改,所以为保证不影响其他事务正在进行的工作,不能将一个页回滚到事务开始的样子。
-
-
②
MVCC
- 在 InnoDB 存储引擎中 MVCC的实现是通过 undo log 来完成的。当用户读取一行记录时,若该记录已经被其他事务占用,当前事务可以通过 undo 读取之前的行版本信息,以此实现非锁定读取。
undo log 的产生会伴随着 redo log 的产生,因为 undo log 也需要持久性的保护。
3.2、undo log 的类型
在InnoDB 存储引擎中,undo log 分为:
-
①
insert undo log
- insert undo log 是指在 insert 操作中产生的 undo log。因为insert 操作的记录,只对事务本身可见,对其他事务不可见(这是事务隔离性的要求),故该 undo log 可以在事务提交后直接删除,不需要进行 purge 操作。
-
②
pdate undo log
-
update undo log 记录的是对 delete 和 update 操作产生的 undo log,该 undo log 可能需要提供 MVCC 机制,因此不能在事务提交时就进行删除。提交时放入 undo log 链表,等待 purge 线程进行最后的删除。
purge 线程两个主要作用:清理undo页和清除page里面带有Delete_Bit标识的数据行。在 InnoDB 中,事务中的 delete 操作实际上并不是真正的删除掉数据行,而是一种 Delete Mark 操作,在记录上标识 Delete_Bit,而不删除记录,真正的删除是后台 purge线程去完成。
-
3.3、undo log 的生命周期
举例说明undo log 的生命周期,假设 有两个数值 A = a1, B =b1,然后将 A 修改为a2,将 B 修改为 b2.
1. start transaction;
2. 记录 A=a1 到 undo log;
3. update A=a2;
4. 记录 A=a2 到 redo log;
5. 记录 B=b1 到 undo log;
6. update B=b2;
7. 记录 B=b2 到redo log;
8. 将 redo log 刷新到磁盘;
9. commit;
- 在1-8 步骤的任意一步系统宕机,事务未提交,该事务就不会对磁盘上的数据做任何影响。
- 如果在8-9之间宕机,恢复之后可以选择回滚,也可以选择继续完成事务提交,因为此时redo log 已经持久化。
- 若在9步之后
系统宕机
,内存映射中变更的数据还来不及刷回磁盘,那么系统恢复后,可以根据redo log 把数据刷会磁盘。
3.4、事务回滚相关的几个隐藏字段
对于InnoDB引擎来说,每个行记录除了记录本身的数据之外,还存在几个隐藏的列。
DB_ROW_ID
:如果没有为表显式的定义主键,并且表中也没有定义唯一索引,那么InnoDB会自动为表添加一个 row_id 的隐藏列作为主键。DB_TRX_ID
:每个事务都会分配一个事务ID,当对某条记录发生变更时,就会将这个事务的事务ID写入 trx_id 中。DB_ROLL_PTR
:回滚指针,本质上就是指向 undo log 的指针。
4、binlog 日志
4.1、关于 binlog 日志
二进制日志(bin log),记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句,但是不包含没有修改任何数据的语句(如查询语句 select、show 等),其以事件形式记录并保存在 二进制文件
中。binlog 主要应用场景:
- ① 用于
数据恢复
,如果MySQL 数据库意外停止,可以通过二进制日志文件来查看用户执行了哪些操作,对数据库服务器文件做了哪些修改,然后根据二进制日志文件中的记录来恢复数据库服务器。 - ② 用于
数据复制
,由于日志的延续性和时效性,master 把它的二进制日志传递给 slaves 来达到 master-slave 数据一致的目的。
MySQL 的
数据备份
、主备
、主主
、主从
都离不开binlog,需要依靠binlog 来同步数据,保证数据一致性。如果想要记录所有语句(例如,为了识别有问题的查询),需要使用通用查询日志。
注意:
数据库文件最好不要与日志文件放在同一个磁盘上
,当数据库文件所在磁盘发生故障时,可以使用日志文件恢复数据。
---------------------------------------------------------------------
# 查看binlog 默认配置
show variables like '%log_bin%';
---------------------------------------------------------------------
# 查看当前二进制文件列表及大小
show binary logs;
---------------------------------------------------------------------
# binlog 的格式查看
show variables like 'binlog_format';
---------------------------------------------------------------------
# 恢复数据的语法
mysqlbinlog [option] filename|mysql -uuser -ppass
# filename : 日志文件名
# option : 可选项,--start-date、--stop-date 和 --start-position、--stop-position
## --start-date、--stop-date :可以指定恢复数据库的起始时间点和结束时间点。
## --start-position、--stop-position:可以指定恢复数据的开始位置和结束位置。
### 注意,使用mysqlbinlog恢复时,必须是文件编号小的先恢复。
# 使用举例:
/usr/bin/mysqlbinlog --start-position=120 --stop-position=135 --database=zim-data /var/my_binlog/zim-bin.0001 | /usr/bin/mysql -uroot -p123455 -v zim-data
---------------------------------------------------------------------
# 修改MySQL的my.cnf或my.ini文件可以设置二进制日志(binlog)的相关参数,这里不展开讲了。
4.2、binlog 的三种格式
binlog 的三种格式(**在MySQL 5.7.7版本之前,默认的binlog格式是Statement ,在MySQL 5.7.7及之后的版本,默认的binlog格式是Row **):
- ①
Statement
:每一条会修改数据的sql都会记录在binlog中。
- 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。
- 缺点:在某些情况下会导致主从数据不一致,比如执行system()或者now()等
- ②
Row
:不记录sql语句上下文相关信息,仅保存哪条记录被修改。
- 优点:Row level的日志内容会非常清楚的记录下每一行数据修改的细节,而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题。
- 缺点:会产生大量的日志,尤其是alter table的时候会让日志暴涨。
- ③
Mixed
:Statement 和 Row 的结合。普通的复制使用STATEMENT,特殊的情况使用ROW来进行复制。
二进制日志(binlog)
可以通过数据库的全量备份
和二进制日志中保存的增量信息
,完成数据库的无损失恢复
,但是,如果遇到数据量大、数据库和数据表多(比如分库分表的应用)的场景,用二进制日志进行数据恢复,是很有挑战性的,因为起止位置不容易管理。这种情况下,可以配置主从数据库服务器
,甚至是一主多从
的架构,把二进制日志文件的内容通过中继日志,同步到从数据库服务器中,就可以有效的避免数据库故障导致的数据异常等问题。
4.3、binlog 刷盘流程
事务执行过程中,先把日志写到 binlog cache,事务提交时,再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开,无论这个事务多大,也要确保一次性写入,所以系统会给每个线程分配一个块内存作为binlog cache.
可以通过 binlog_cache_size
参数控制单个线程 binlog cache 大小,如果存储内容超过了这个参数,就要暂时到磁盘(Swap)。binlog 日志刷盘流程如下:
- binlog 日志刷盘流程图中的 write,是在把日志写入到文件系统的 page cache,并没有把数据持久化到磁盘,所以速度比较快。
- binlog 日志刷盘流程图中的 fsync,才是将数据持久化到磁盘的操作。
write和fsync的时机,由参数 sync_binlog 控制。sync_binlog参数控制了二进制日志(binlog)何时被写入磁盘。具体说明如下:
sync_binlog=0
:当设置为0时,MySQL不会同步binlog到磁盘。而是依赖操作系统来刷新binlog到磁盘。这种设置能提供最好的性能,但在电源故障或操作系统崩溃的情况下,服务器可能丢失一些事务。该值为默认值
。sync_binlog=1
:在每次提交事务时,MySQL都会把binlog同步写入磁盘。这是最安全的设置,但由于频繁的磁盘IO操作,可能会对性能产生影响。sync_binlog=N (N>1)
:在每N个事务提交后,MySQL同步一次binlog到磁盘。这种设置是性能和安全性之间的折衷。
该参数对于确保数据的持久性和一致性非常重要,尤其是在高并发和写密集型的场景中。考虑到数据的安全性,很多场景下推荐将sync_binlog设置为1
。
4.4、InnoDB两阶段提交
由于 binlog是Server层执行器操作写入的,redo log 是存储引擎层面写入的,所以两份日志可能存在不一致的问题。为了解决redo log 和binlog 两份日志之间的逻辑一致问题,InnoDB 存储引擎使用两阶段提交
方案。两阶段提交方案原理:将redo log 的写入拆分为两个步骤 prepare 和 commit,这就是两阶段提交。
采用两阶段提交方案后,遇到异常解决流程:
-
① 写入 binlog时发生异常,MySQL根据 redo log 日志恢复数据时,发现 redo log 还处于 prepare 阶段,并且没有对应 binlog日志,就会回滚该事务。
-
② redo log 设置commit 阶段发生MySQL服务器异常,重启MySQL服务时并不会回滚事务,虽然此时redo log 处于 prepare阶段,但是能通过事务id 找到对应的 binlog 日志,MySQL会认为是完整的,就会提交事务恢复数据。
4.5、redo log 和 binlog 的区别
- 日志类型与存储引擎:
binlog是MySQL Server层的日志,对所有存储引擎都通用
。而redo log是InnoDB存储引擎特有的日志
。 - 日志内容:
binlog是一种逻辑日志
,记录的是所有数据的改变信息。而redo log是物理日志
,记录的是每个数据页的具体修改。 - 作用:
binlog的主要用于主从复制和数据恢复,主从节点间通过复制和应用binlog实现数据的同步
。而redo log主要用于保证事务的原子性和持久性
,即用于在数据库发生故障时进行数据的恢复,确保已提交事务的修改能被持久化到数据库中。
5、扩展:MySQL 的日志类型
MySQL日志类型:
- 慢查询日志:记录所有执行时间超过 long_query_time 的所有查询,方便我们对查询进行优化。
- 通用查询日志:记录所有连接的起始时间和终止时间,以及连接发送给数据库服务器的所有指令,对复原操作的实际场景、发现问题、甚至是对数据库操作的审计都有很大的帮助。
- 错误日志:记录MySQL 服务的启动、运行或者停止 MySQL 服务时出现的问题,方便我们了解服务器的状态,从而对服务器进行维护。
- 二进制日志:记录所有更改数据的语句,可以用于主从服务器之间的数据同步,以及服务器遇到故障时数据的无损失恢复。
- 中继日志:用于主从服务器架构中,从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容,来同步主服务器上的操作。(MySQL8.0 开始有)
- 数据定义语句日志:记录数据定义语句执行的元数据操作。(MySQL8.0 开始有)
除二进制日志外,其他日志都是文本文件
。默认清空下,所有日志创建于 MySQL数据目录
中。实际工作中并不是需要使用所有日志功能,日志功能会降低MySQL数据库的性能
,同时也会占用大量的磁盘空间
。
系列文章:
一: 《搞懂 MySql 的架构和执行流程》
二: 《从InnoDB索引的数据结构,去理解索引》
三: 《从 Hash索引、二叉树、B-Tree 与 B+Tree 对比看索引结构选择》
四: 《MySQL 的索引分类和设计原则》
五: 《MySQL 优化思路篇》
六: 《理解MySQL的日志 redo、undo、binlog》
七: 《并发事务下,不同隔离级别可能出现的问题》
八: 《多维度梳理 MySQL 锁》
九: 《MySQL 之多版本并发控制 MVCC》
.