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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 详解MySQL非常重要的日志—bin log

前言


bin log想必大家多多少少都有听过,它是MySQL中一个非常重要的日志,所以各位架构师们,如果有不了解的,一定要好好学习了,因为它涉及到数据库层面的主从复制、高可用等设计。


bin log是什么?


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

bin log有什么用呢?

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

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

1671200456645.jpg


bin log和redo log区别?


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

你知道redo log吗, 如果不了解的话请参考这篇文章:详解MySQL事务日志——redo log

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

使用场景角度来说:

  • 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写入的整体流程如下图所示:


1671200467564.jpg


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

3种刷盘策略:

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

  1. sync_binlog = 0

1671200479736.jpg

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

  1. sync_binlog = 1

1671200487622.jpg

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

1671200493330.jpg

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

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

1671200499333.jpg

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

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

1671200505397.jpg

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

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

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

1671200510873.jpg

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


bin log写到哪了?


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

  1. 查看bin log位置

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


1671200530098.jpg


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

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

1671200555655.jpg

  1. 修改 bin log位置

修改MySQL的my.cfgmy.ini配置

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


bin log内容长啥样?


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

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

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 :查询总条数(不指定就是所有行)

1671200575010.jpg


bin log 格式


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


1671200584492.jpg


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


总结


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

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
canal 消息中间件 关系型数据库
Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
【9月更文挑战第1天】Canal作为一款高效、可靠的数据同步工具,凭借其基于MySQL binlog的增量同步机制,在数据同步领域展现了强大的应用价值
207 4
|
5天前
|
消息中间件 canal 关系型数据库
Maxwell:binlog 解析器,轻松同步 MySQL 数据
Maxwell:binlog 解析器,轻松同步 MySQL 数据
38 11
|
23天前
|
Java
日志框架log4j打印异常堆栈信息携带traceId,方便接口异常排查
日常项目运行日志,异常栈打印是不带traceId,导致排查问题查找异常栈很麻烦。
|
1月前
|
存储 监控 数据可视化
SLS 虽然不是直接使用 OSS 作为底层存储,但它凭借自身独特的存储架构和功能,为用户提供了一种专业、高效的日志服务解决方案。
【9月更文挑战第2天】SLS 虽然不是直接使用 OSS 作为底层存储,但它凭借自身独特的存储架构和功能,为用户提供了一种专业、高效的日志服务解决方案。
66 9
|
2月前
|
API C# 开发框架
WPF与Web服务集成大揭秘:手把手教你调用RESTful API,客户端与服务器端优劣对比全解析!
【8月更文挑战第31天】在现代软件开发中,WPF 和 Web 服务各具特色。WPF 以其出色的界面展示能力受到欢迎,而 Web 服务则凭借跨平台和易维护性在互联网应用中占有一席之地。本文探讨了 WPF 如何通过 HttpClient 类调用 RESTful API,并展示了基于 ASP.NET Core 的 Web 服务如何实现同样的功能。通过对比分析,揭示了两者各自的优缺点:WPF 客户端直接处理数据,减轻服务器负担,但需处理网络异常;Web 服务则能利用服务器端功能如缓存和权限验证,但可能增加服务器负载。希望本文能帮助开发者根据具体需求选择合适的技术方案。
68 0
|
2月前
|
C# Windows 监控
WPF应用跨界成长秘籍:深度揭秘如何与Windows服务完美交互,扩展功能无界限!
【8月更文挑战第31天】WPF(Windows Presentation Foundation)是 .NET 框架下的图形界面技术,具有丰富的界面设计和灵活的客户端功能。在某些场景下,WPF 应用需与 Windows 服务交互以实现后台任务处理、系统监控等功能。本文探讨了两者交互的方法,并通过示例代码展示了如何扩展 WPF 应用的功能。首先介绍了 Windows 服务的基础知识,然后阐述了创建 Windows 服务、设计通信接口及 WPF 客户端调用服务的具体步骤。通过合理的交互设计,WPF 应用可获得更强的后台处理能力和系统级操作权限,提升应用的整体性能。
69 0
|
2月前
|
存储 消息中间件 监控
Java日志详解:日志级别,优先级、配置文件、常见日志管理系统ELK、日志收集分析
Java日志详解:日志级别,优先级、配置文件、常见日志管理系统、日志收集分析。日志级别从小到大的关系(优先级从低到高): ALL < TRACE < DEBUG < INFO < WARN < ERROR < FATAL < OFF 低级别的会输出高级别的信息,高级别的不会输出低级别的信息
|
2月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID:如何让数据库备份恢复变得轻松简单?
【8月更文挑战第22天】MySQL的binlog日志记录数据变更,用于恢复、复制和点恢复;GTID为每笔事务分配唯一ID,简化复制和恢复流程。开启binlog和GTID后,可通过`mysqldump`进行逻辑备份,包含binlog位置信息,或用`xtrabackup`做物理备份。恢复时,使用`mysql`命令执行备份文件,或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
171 2
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
2月前
|
关系型数据库 MySQL Shell
MySQL回滚脚本: 误操作delete binlog回滚shell脚本
MySQL回滚脚本: 误操作delete binlog回滚shell脚本
下一篇
无影云桌面