DBA MySQL事务日志
名词介绍
事务如何保证ACID
呢?这个其实就要从InnoDB
存读数据的底层说起。
下面有一些名词及概念介绍,先做个初步认识:
-
CSR
描述:
MySQL
的自动故障恢复机制,InnoDB
引擎支持该机制
-
磁盘数据页
描述:存储记录及索引信息的文件
位置:
[表名].ibd
文件
-
data buffer pool
描述:数据缓冲区池,从磁盘中加载的记录、索引信息均存放至此
位置:位于内存中
-
redo_log
描述:事务重做日志,
CSR
中用于前滚操作所必须依赖的日志位置:
ib_logfile0
文件,默认50M
,轮询使用
-
redo log buffer
描述:
redo log
的内存缓冲区位置:位于内存中
-
undo log
描述:事务回滚日志,事务回滚
ROLLBACK
与CSR
中用于回滚操作锁必须依赖的日志位置:在
MySQL5.7
中依旧位于 共享表空间ibdata1
文件中,8.0
版本后开始被独立出来
-
undo log buffer
描述:
undo log
的内存缓冲区位置:位于内存中
-
LSN
描述:日志序列号,每次
MySQL
数据库启动时,都会比较磁盘数据页[表名].ibd
文件和redo log
的LSN
必须要求两者LSN
一致数据库才能正常启动位置:
[表名].ibd
文件、data buffer pool
、redo log
、redo log buffer
-
WAL
描述:持久化存储方式的一种,全称为write ahead log,译为日志优先写,
MySQL
以该方式实现持久化,即日志是优先于数据写入磁盘的
-
脏页
描述:内存脏页,由于
WAL
的机制,当数据页在内存中发生了修改,但没写入到磁盘之前时我们把data buffer pool
中的内存页称之为脏页,其页中的数据被称为脏数据
-
CKPT
描述:全称为Checkpoint,检查点的意思,实际上就是将脏页刷写到磁盘的动作
-
TXID
描述:事务号,
InnoDB
会为每一个事务生成一个事务号并伴随着整个事务
基础图示
上述部分名词对应的基础图示如下:
REDO_LOG
事务提交过程
当开启一个事务,并进行COMMIT
操作时,过程如下:
BEGIN;
UPDATE
userInfo
SET
name = "Yun"
WHERE
name = "Ya";
COMMIT;
-
首先进行
BEGIN
时,会先给[表名].ibd
文件中分配并写入一个TXID
号和LSN
号,如tx01
与ls01
-
在
UPDATE
执行时,会查找到对应的数据页加载到data buffer pool
中,由DBWR
线程记录变更数据页内容,并且记录TXID
号和更新LSN
号,此时将产生脏页与脏数据 -
使用
LOGBWR
线程,将更新的数据页变化与TXID
号和LSN
号记录到redo log buffer
中 -
执行
COMMIT
,基于WAL
原则,LOGBWR
线程会先将redo log buffer
中记录的信息写入redo log
文件中,在日志信息完全写入redo log
即ib_logfile0
文件后,会对此日志打上COMMIT
标记 -
触发
CKPT
,将内存数据页更新到磁盘中,并且更新LSN
号
可以看到整个过程是先写日志,再写数据。
其实redo log buffer
刷新到redo log
文件中的策略除开自己手动执行COMMIT
外还有另外的情况。
在多任务时,别的线程COMMIT
操作也可能也会导致该线程redo log buffer
的刷新,刷新的redo log
文件中会打上NOCOMMIT
的标记。
其实这种现象取决于data buffer pool
中存储的数据量占据data buffer pool
总量的多少,一般来说70%左右就会触发该现象,我们可以对其进行设置。
自动故障恢复
如果在COMMIT/ROLLBACK
过程中发生了宕机,内存中的数据全部丢失,并且redo log
文件还没有来得及打上COMMIT
标记,仅仅记录了数据变化日志和TXID
号以及LSN
号重启mysqld.service
服务时将会产生如下情况:
-
重启
mysqld.service
服务,发现redo log
中记录的LSN
号和[表名].ibd
中记录的LSN
号不一致,触发CSR
自动故障恢复,前滚操作开始 -
通过
redo log
文件中的信息记录日志,在内存数据页中恢复出更改的数据,并触发CKPT
将内存中数据页更新到磁盘,同时更新[表名].ibd
中的LSN
号 -
前滚工作完成,
MySQL
正常启动
UNDO_LOG
事务回滚过程
当开启一个事务,并进行ROLLBACK
操作时,过程如下:
BEGIN;
UPDATE
userInfo
SET
name = "Yun"
WHERE
name = "Ya";
ROLLBACK;
-
首先进行
BEGIN
时,会先给[表名].ibd
文件中分配并写入一个TXID
号和LSN
号,如tx01
与ls01
-
在
UPDATE
执行时,会查找到对应的数据页加载到data buffer pool
中,由DBWR
线程记录变更数据页内容,并且记录TXID
号和更新LSN
号,此时将产生脏页与脏数据 -
使用
LOGBWR
线程,将更新的数据页变化与TXID
号和LSN
号记录到redo log buffer
中 -
使用
LOGBWR
线程,将更新前的数据页与TXID
号记录到undo log buffer
中 -
使用
LOGBWR
线程,将undo log buffer
中的数据写入到undo log
即ibdata1
(MySQL5.7)的文件中 -
执行
ROLLBACK
,LOGBWR
线程会先将undo log buffer
中记录的信息重新写回data buffer pool
中,并且会把内存脏页数据恢复到最开始的值,并且对LSN
号进行回滚更正 -
清空
undo log
与undo log buffer
中刚刚的数据
自动故障恢复
如果在并未COMMIT/ROLLBACK
过程中发生了宕机,内存中的数据全部丢失,但是由于其它线程的COMMIT
执行导致本线程事务也进行了自动刷新时重启mysqld.service
服务将会产生如下情况。
注意:现在redo log
已写入TXID
号与LSN
号以及数据变更记录的日志,但是标记为NOCOMMIT
。
-
重启
mysqld.service
服务,发现redo log
中记录的LSN
号和[表名].ibd
中记录的LSN
号不一致,触发CSR
自动故障恢复,前滚操作开始 -
通过
redo log
文件中的信息记录日志,在内存数据页中恢复出更改的数据 -
发现
redo log
文件中的标记是NOCOMMIT
,触发CSR
自动故障恢复第二阶段,回滚操作开始 -
通过
undo log
文件中的信息记录,在内存数据页中对前滚数据进行更改 -
使用
LOGBWR
线程,将更新的数据页变化与TXID
和LSN
号记录到redo log buffer
中 -
LOGBWR
线程会先将redo log buffer
中记录的信息写入redo log
文件中,在日志信息完全写入log file
即ib_logfile0
文件后,会对此日志打上COMMIT
标记 -
触发
CKPT
,将内存数据页更新到磁盘中,不用更新LSN
号 -
回滚工作完成,
redo log
中记录的LSN
和[表名].ibd
中记录的LSN
号一致,MySQL
正常启动
相关参数配置
INNODB_FLUSH_METHOD
配置参数据INNODB_FLUSH_METHOD
,该参数控制的是undo/redo log buffer
和data buffer pool
将数据刷写磁盘的时候是否经过os buffer
。
-
fsync:日志和数据缓冲区向磁盘写入数据时,必须先将数据写入
os buffer
后再刷新至磁盘中,此为默认设置 -
O_DIRECT:数据缓冲区向磁盘写入数据时,不必将数据先写入
os buffer
中,而是直接写入到磁盘 -
O_DSYNC:日志缓冲区向磁盘写入数据时,不必将数据先写入
os buffer
中,而是直接写入到磁盘
INNODB_FLUSH_LOG_AT_TRX_COMMIT
配置参数INNODB_FLUSH_LOG_AT_TRX_COMMIT
,定义COMMIT
时,日志文件从缓冲区到磁盘的刷新方式。
-
要完全符合
ACID
,必须使用默认设置1。日志在每次事务提交时必须先写入os buffer
后再刷新至磁盘中。 -
设置为0时,每秒写入一次日志到
os buffer
并将其刷新到磁盘。未刷新日志的事务可能会在崩溃中丢失。 -
设置为2时,在每次事务提交后写入日志到
os buffer
中,并每秒刷新一次到磁盘。未刷新日志的事务可能会在崩溃中丢失。
使用建议
推荐将配置项写入至配置文件中,达到永久生效的目的。
最高安全模式:
INNODB_FLUSH_LOG_AT_TRX_COMMIT=1
Innodb_flush_method=O_DIRECT
最高性能模式:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync