正德厚生,臻于至善

MySQL日志系统原理

在MySQL中,redo、undo、binlog经常见,但很容易混淆这三者。

redo undo binlog
作用 保持事务的持久性 帮助事务回滚及MVCC的功能 进行Point-In-Time的恢复及主从复制环境的建立
产生主体 InnoDB MySQL MySQL
类型 物理日志 逻辑日志 逻辑日志
内容 每个页的修改 修改前的行数据 执行的SQL语句
每个事务的日志数量 事务执行中不断写入,多事务可并发写入 看修改的行数据量 事务提交后记一条SQL语句
幂等性

Undo Log介绍

Undo:意为撤销或取消,以撤销操作为目的,返回指定某个状态的操作。

Undo Log:数据库事务开始之前,会将要修改的记录存放到 Undo 日志里,当事务回滚时或者数据库崩溃时,可以利用 Undo 日志,撤销未提交事务对数据库产生的影响。

Undo Log产生和销毁:Undo Log在事务开始前产生;事务在提交时,并不会立刻删除undo log,innodb会将该事务对应的undo log放入到删除列表中,后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志,记录一个变化过程。例如执行一个delete,undolog会记录一个insert;执行一个update,undolog会记录一个相反的update。

Undo Log存储:undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback

segment回滚段,内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。

show variables like ‘%innodb_undo%’;

Undo Log作用

  • 实现事务的原子性

Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中,如果出现了错误或者用户执行了 ROLLBACK 语句,MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

  • 实现多版本并发控制(MVCC)

Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前,Undo Log 保存了未提交之前的版本数据,Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

事务A手动开启事务,执行更新操作,首先会把更新命中的数据备份到 Undo Buffer 中。

事务B手动开启事务,执行查询操作,会读取 Undo 日志数据返回,进行快照读。

Redo Log介绍

Redo:顾名思义就是重做。以恢复操作为目的,在数据库发生意外时重现操作。

Redo Log:指事务中修改的任何数据,将最新的数据备份存储的位置(Redo Log),被称为重做日志。

Redo Log 的生成和释放:随着事务操作的执行,就会生成Redo Log,在事务提交时会将产生

Redo Log写入Log Buffer,并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后,Redo Log 的使命也就完成了,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相关配置参数

每个InnoDB存储引擎至少有1个重做日志文件组(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秒执行一次操作。
  • 1(默认值):每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk,最安全,性能最差的方式。
  • 2:每次事务提交执行 Redo Buffer -> OS cache,然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。

一般建议选择取值2,因为 MySQL 挂了数据没有损失,整个服务器挂了才会损失1秒的事务提交数据。

Binlog介绍

Binlog记录模式

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

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

Binlog文件名默认为“主机名_binlog-序列号”格式,例如oak_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结构如下图所示:

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状态查看:show variables like ‘log_bin’;
  • 开启Binlog功能

mysql> set global log_bin=mysqllogbin;
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=mysqlbinlog

  • 使用show binlog event命令

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-04-25 18:00:00″ –stop datetime=”2020-04-26 00:00:00″ mysqlbinlog.000002 | mysql -uroot -p123456
//按事件位置号恢复
mysqlbinlog –start-position=154 –stop-position=957 mysqlbinlog.000002 | mysql -uroot -p123456

mysqldump:定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

  • 删除binlog文件

purge binary logs to ‘mysqlbinlog.000001’; //删除指定文件
purge binary logs before ‘2020-04-28 00:00:00’; //删除指定时间之前的文件
reset master; //清除所有文件

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

Redo Log和Binlog区别

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

 

Reference:

http://mysqldump.azundris.com/archives/78-Configuring-InnoDB-An-InnoDB-tutorial.html

赞(0) 打赏
未经允许不得转载:徐万新之路 » MySQL日志系统原理
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏