系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

MySQL非常重要的日志bin log详解

时间:2023-10-30来源:系统城装机大师作者:佚名

bin log是什么?

bin log全称binary log,二进制日志文件,它记录了数据库所有执行的 DDL 和 DML 等数据库更新的语句,但是不包含select或者show等没有修改任何数据的语句。它是MySQL级别的日志,也就是说所有的存储引擎都会产生bin log,而redo log或者undo log事务日志只有innoDB存储引擎才有。

bin log有什么用呢?

  • 数据恢复,如果MySQL数据库意外挂了,可以利用bin log进行数据恢复,因为该日志记录所有数据库所有的变更,保证数据的安全性。
  • 数据复制,利用一定的机制将主节点MySQL的日志数据传递给从节点,实现数据的一致性,实现架构的高可用和高性能。

所以bin log对于数据备份主从主主等都都起到了关键作用。

bin log和redo log区别?

看了上面的bin log介绍,是不是感觉和事务日志redo log特别像呢?也是在事务执行的时候记录日志,但是他们还是有区别的。

你知道redo log吗, 如果不了解的话请参考这篇文章:详解MySQL事务日志redo log_Mysql_脚本之家 (jb51.net)

我们现在从多个角度对比下他们俩究竟有什么不一样?

使用场景角度来说:

  • redo log主要实现故障情况下的数据恢复,保证事务的持久性
  • bin log主要用于数据灾备、同步

数据内容角度来说:

  • redo log是"物理日志", 记录的是具体数据页上做了什么修改
  • bin log是"逻辑日志", 记录内容是语句的原始逻辑,类似于“给 ID=2 这一行的 name 改为alvin”

生成范围角度来说:

  • redo logInnoDB存储引擎生成的事务日志,其他存储引擎没有
  • bin log是MySQL Server生成的日志,所有的存储引擎都有

生成时机角度来说:

  • redo log是在事务执行过程中就会write
  • bin log是在事务提交的时候write

bin log怎么写的?

bin log是什么时候写的,写入的机制又是怎么样的呢?

bin log写入的整体流程如下图所示:

  • 为了保证写的效率,会将事务的bin log先写到binlog cache中,注意,这个cache位于事务线程的内存中,主要是一个事务的bin log不能被拆开,是一个整体
  • 在提交事务的时候,将binlog cache中的数据统一写道文件系统缓存page cache中,这个过程速度也很快
  • 然后根据不同的策略,将文件系统缓存中的bin logfsync刷到磁盘中,这里的策略后面详细讲解。

3种刷盘策略:

bin log和 redo log类似,都有3种刷盘策略, bin log的write和fsync时机是由参数 sync_binlog 控制,默认是 0 。

  • sync_binlog = 0

为0的时候,表示每次提交事务都只 write,由系统自行判断什么时候执行fsync。虽然性能得到提升,但是机器宕机,page cache里面的 binglog 会丢失。

  • sync_binlog = 1

  • 表示每次提交事务都会执行fsync,更加安全
  • sync_binlog = N

  • 可以设置为N(N>1),表示每次提交事务都write,但累积N个事务后才fsync

我们已经知道,事务执行时会同时记录redo logbin log两种日志,那会有日志出错不一致问题吗?

  • redo log在事务执行过程中可以不断写入
  • bin log只有在提交事务时才写入

假如事务执行sqlupdate T set c = 1 where id = 2,在写完redo log日志后,bin log日志写期间发生了异常,会出现什么情况呢?

由于bin log没写完就异常,这时候bin log里面没有对应的修改记录。因此,之后用bin log日志恢复数据时,就会少这一次更新,恢复出来的这一行c值为0,而原库因为redo log日志恢复,这一行c的值是1,最终数据不一致。

那有什么解决方案吗?二阶段提交方案。

为了解决两份日志之间的一致性问题,InnoDB存储引擎使用两阶段提交方案。将redo log的写入拆成了两个步骤preparecommit

  • 假如现在写入bin log时MySQL发生异常,这时候的redo log还处于prepare阶段,重启MySQL后,根据redo log记录中的事务ID,发现没有对应的bin log日志,回滚前面已写入的数据。
  • 如果redo log 在commit阶段发生移除,但是能通过事务id找到对应的bin log日志,所以MySQL认为是完整的,就会提交事务恢复数据。

bin log写到哪了?

前面讲解了bin log写入的过程,那么它写到了哪里去了呢?

  • 查看bin log位置

可以通过命令show variables like '%log_bin%';查看bin log最终输出的位置。

  • log_bin_basename: 是bin log日志的基本文件名,后面会追加标识来表示每一个文件
  • log_bin_index: 是binlog文件的索引文件,这个文件管理了所有的binlog文件的目录

通过 SHOW BINARY LOGS;查看当前的二进制日志文件列表及大小,如下图:

  • 修改 bin log位置

修改MySQL的my.cfgmy.ini配置

1
2
3
4
#启用二进制日志
log-bin=cxw-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M
  • log-binbin log日志保存的位置
  • binlog_expire_logs_secondsbin log日志保存的时间,单位是秒
  • max_binlog_size: 单个bin log日志的容量

bin log内容长啥样?

我们已经知道了bin log的位置了,那它里面的内容长什么样呢?

我们可以用show binlog events命令工具查看bin log日志中的内容。

1 show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  • IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)
  • FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
  • LIMIT [offset] :偏移量(不指定就是0)
  • row_count :查询总条数(不指定就是所有行)

bin log 格式

实际上bin log输出的格式类型有3种,默认是ROW类型,就是上面例子中的格式。

  • Statement格式:每一条会修改数据的sql都会记录在bin log
  • 优点:不需要记录每一行的变化,减少了bin log日志量,节约了IO,提高性能。
  • 缺点:比如sql中存在函数如now()等,依赖环境的函数,会导致主从同步、恢复数据不一致
  • ROW格式:为了解决Statement缺点,记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了
  • 优点:清楚的记录下每一行数据修改的细节,不会出现某些特定情况下 的存储过程,或function无法被正确复制的问题。
  • 缺点:比如对ID<600的所有数据进行了修改操作,那么意味着很多数据发生变化,最终导致同步的log很多,那么磁盘IO、网络带宽开销会很高。
  • Mixed格式: 混合模式,即Statment、Row的结合版
  • 对于可以复制的SQL采用Statment模式记录,对于无法复制的SQL采用Row记录。

总结

本文讲解了MySQL中的一个非常重要的日志bin log,它主要用来做数据恢复和同步的,所以作为程序员的我们,还是很有必要对它有一个深入的认识。

分享到:

相关信息

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

  • MySQL清理数据并释放磁盘空间的实现示例

    在我们的生产环境中有一张表:courier_consume_fail_message,是存放消息消费失败的数据的,设计之初,这张表的数据量评估在万级别以下,因此没有建立索引。但目前发现,该表的数据量已...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载