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
浙公网安备 33010602011771号