MySQL中的undo log

转载

mob64ca13fd163c 2024-10-31 18:15:18

文章标签 MySQL中的undo log mysql 数据 MySQL 文章分类 MySQL 数据库

日志文件

查看错误日志

show variables like '%log_error%'

查看通用查询日志

show variables like '%general%'

二进制日志binary log 记录执行的更改操作

show variables like '%log_bin%';//是否开启
show variables like 'binlog' ;//参数查看
show binary logs; //查看日志文件

慢查询日志

show variables like '%slow_query%'; //参数是否开启
show variables liike '%long_query_time%';//时长
set long_query_time=5; // 可以修改值的大小

Undo log

介绍
Undo: 意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作
Undo log: 数据库事务开始之前，会将要修改的记录存到Undo日志里，当事务回滚或者数据库崩溃时，可以利用Undo日志，撤销未提交事务对数据库产生的影响
Undo Log 产生和销毁： Undo Log在事务开始前产生，事务在提交时不会立即删除Undo Log，innodb会将该事务对应的Undo Log放到删除列表中，后面通过后台线程 purge thread 进行回收处理。Undo Log属于一个逻辑日志，记录一个变化过程。例如执行一个delete，Undo Log 会记录一个insert，执行一个update，Undo Log会记录一个相反的update
Undo log 存储：Undo Log 采用段的方式进行管理和记录，在innodb数据文件中包含一种rollback segment 回滚段，内部包含1024个Undo Log segment
作用

实现事务的原子性
Undo Log是为了实现事务的原子性而出现的产物，事务处理过程中，如果出现了错误或者用户执行了 rollback语句，MySQL可以使用Undo Log中的备份将事务恢复到事务开始之前的状态
实现多版本并发控制（MVCC）
Undo Log 在MySQL innoDB引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log中的数据可以作为数据旧版本的快照供其他并发事务进行快照读

show variables like '%innodb_undo%';

Redo log

介绍
Redo ：顾名思义就是重做，以恢复操作为目的，在数据库发生意外时重现操作
Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位（Redo Log），被称为重做日志
Redo Log的生成和释放：随着事务的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入到磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log的使命也就完成了，Redo Log占用的空间就可以重用，被覆盖写入。
工作原理
Redo Log是为了实现事务的持久性而出现的产物，防止在发生故障的时间点，尚有脏页未写入表的IBD文中，在重启MySQL服务的时候，根据Redo Log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性
Redo Log写入机制
Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写

如上面简图

write Pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾就回到0号文件开头继续写
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；
write pos 和checkpoint 之间还空着的部分，可以用来记录新的操作，如果write pos追上checkpoint，表示写满，这时候就不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下

Redo Log相关配置参数
每个iinnoDB存储引擎至少有一个重做日志文件组(Group),每个文件组至少有 2 个重做日志文件,默认为ib-logfile0和 ib-logfile1可以通过下面查看控制 Redo Log 存储

show variables like '%innodb_log%';

Redo Buffer 持久化到 Redo Log 的策略,可通过 innodb_flush_log_at_trx_commit 设置:

0: 每秒提交Redo buffer -> OS cache -> flush cache to disk ,可能丢失一秒内的事务数据,由后台 master 线程每隔一秒执行一次操作
1(默认值): 每次事务提交执行 Redo Buffer -> OS Cache -> flush cache to dish ,最安全,性能最差的方式
2: 每次事务提交执行 Redo Buffer -> OS ,然后由后台 Master 线程每隔一秒执行 OS cache -> flush cache to disk 的操作
一般建议选取值 2,因为 MySQL 挂了数据没有损失,整个服务器挂了才会损失一秒的提交数据.

Bin Log

BinLog 记录模式
Redo Log 是属于 InnoDB引擎所特有的日志,而 MySQl Server 也有自己的日志,即 Binary Log(二进制日志),简称 BinLog, BinLog 是记录所有数据库表结构变更一级表数据修改的二进制日志,不会 SELECT 和 SHOW 这类操作.BinLog 日志是以事件形式记录,还包含语句所执行的消耗事件.开启 BinLog 日志有以下两个使用场景

主从复制在主库中开启 BinLog 功能,这样主库就可以把 BinLog 传递给从库,从库拿到 BinLog后实现数据恢复到主从数据一致性
数据恢复通过 mysqlbinlog 工具来恢复数据

BinLog 文件名默认为主机名 _ binlog-序列号格式,例如tz_binlog-000001,也可以在配置文件中指定名称.

文件记录模式有 STATEMENT 、ROW、MIXED 三种,具体含义如下

ROW(row-based replication ,RBR) : 日志中会记录每一行数据被修改的情况 ,然后在 slave 端对相同的数据进行修改
优点: 能清楚记录每一个行数据的修改细节,能完全实现主从数据同步和数据的恢复
缺点: 批量操作,会产生大量的日志,尤其是 alter table 会让日志暴涨
STATMENT(statement-based replication,SBR): 每一条被修改数据的 SQL 都会记录到 master 的 BinLog 中,slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL再次执行.简称 SQL 语句复制
优点: 日志量小,减少磁盘 IO,提升存储和恢复速度
缺点: 在某些情况下会导致主从数据不一致,比如 last_insert_id(),now()等函数
MIXED(mixed-based replication,MBR): 以上两种模式的混合使用,一般会使用 STATEMENT 模式保存 Binlog,对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 Binlog,MySQL 会根据执行的 SQL 语句选择写入模式

BinLog 文件结构
MySql的 Binlog 文件中记录的是对数据库的修改操作,用来表示修改操作的数据结构是 Log event ,不同的修改操作对应不同的 Log event.比较常用的 log event 有:Query event,Row event,Xid event 等.
Binlog 文件的内容就是各种 Log event 的集合
BinLog文件中 Log event 结束如下所示:

结构	说明
timestamp 4字节	事件开始的执行时间
Event Type 1 字节	指明该事件的类型
server_id 1 字节	服务器的 server ID
Event size 4 字节	该事件的长度
Next_log pos 4 字节	固定 4 字节下一个 event 的开始位置
Flag 2 字节	固定 2 字节的 event flags
Fixed part	每种 Event Type 对应结构体固定的结构部分
Variable part	每种 Event Type 对应结构体可变的结构部分

Binlog 写入机制

根据记录模式和操作触发 event 事件生成 log event(事件触发执行机制)
将事务执行过程中产生 log event 写入缓冲区,每个事务线程都有一个缓冲区
Log event 保存在一个 binlog_cache_mngr 数据结构中,在该结构中有两个缓存区,一个是 stmt_cache,用于存放不支持事务的信息,另一个是 trx_cache,用于存放支持事务的信息
事务在提交阶段会将产生的 log event 写入到外部的 binlog 文件中,不同的事务是以串行的方式将 log event 写入到 binlog 中,所以一个事务包含的 log event 信息在 binlog 文件中是连续的,中间不会插入其它事务的了log event,binlog 是引擎插件上层的功能,事务提交第一个就会调用 binlog 功能接口,然后在调用其他引擎的功能接口,因此先写 binlog然后在执行 innodb 的 redo log/undo 和脏页刷新操作

Binlog 文件操作

Binlog 状态查看

show variables like '%log_bin%';

开启 Binlog 功能

set global log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

需要修改 my.cnf 或者 my.ini 配置文件,在[mysqld]下面增加 log_bin=mysql_bin_log,重启 MySQL 服务

#log-bin=ON
#log-bin-basename=mysqlbinlog
binlog-format=ROW
log-bin=mysqlbiinlog

使用show binlog events命令

show binary logs;//show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';

使用 mysqlbinlog 命令

mysqlbinlog '文件名'
mysqlbinlog '文件名' > 'test.sql'

使用binlog 恢复数据

// 按照指定时间恢复
mysqlbinlog --start-datetime="2020-12-09 18:00:00" --stop-datetime="2020-12-09 19:00:00" mysqlbinlog.000002 | mysql -uroot -p 密码
// 按事件位置号恢复
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p 密码

删除 Binlog 文件

// 删除指定文件
purge binary logs to 'mysqlbinlog.000001';
// 删除指定 时间之前的
purge binary logs before '2020-12-09 00:00:00';
// 删除所有文件
reset master;

可以通过设置 expire_logs_days 参数来启动自动清理功能,默认值为 0 表示没启用,设置为 1 表示超出一天binlog 文件自动清除掉

Redo log 和 BinLog 区别

Redo Log 是属于 InnoDB 引擎功能,Binlog 是属于 MySQL Server 自带功能,并且是二进制文件记录
Redo Log 属于逻辑日志,记录该数据页更新状态内容,BinLog 是逻辑日志,记录更新过程
Redo Log 日志是循环写,日志空间是固定的,BinLog 是追加写,写到一定文件大小写入下一个文件,文件大小可设置
Redo Log 作为服务器异常宕机后事务恢复使用,BinLog 可以作为主从复制和数据恢复使用,BinLog 没有自动 crash-safe 能力

数据文件

查看文件存储位置

show variables like '%datadir%';

db.opt文件：记录这个库默认使用的字符集和校验规则
frm文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息，每一张表都会有一个frm文件
MYD文件： MyISAM引擎专用，存放MyISAM表的数据，每一张表都会有一个MYD文件
MYI文件： MyISAM引擎专用，存放MyISAM表的索引相关信息，每一张表都会有一个MYI文件
ibd文件和IBDATA文件：存放innoDB的数据文件包括索引，innoDB存储引擎有两种表空间方式，独享表空间和共享表空间。独享表空间使用.ibd文件存放数据，每一张表对应一个.ibd文件。共享表空间使用.ibdata文件，所有表共同使用一个（或者多个，可以配置）.ibdata文件
ibdata1文件：系统表空间数据文件，存储表元数据，Undo日志等
ib_logfile0，ib_logfile1文件： redo log日志文件