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

吾妻雷姆美如画

bin log是什么？

1. bin log

复制代码

全称

1. binary log

复制代码

，二进制日志文件，它记录了数据库所有执行的

1. DDL

复制代码

和

1. DML

复制代码

等数据库更新的语句，但是不包含

1. select

复制代码

或者

1. show

复制代码

等没有修改任何数据的语句。它是MySQL级别的日志，也就是说所有的存储引擎都会产生

1. bin log

复制代码

，而

1. redo log

复制代码

或者

1. undo log

复制代码

事务日志只有

1. innoDB

复制代码

存储引擎才有。
那

1. bin log

复制代码

有什么用呢？

• 数据恢复，如果MySQL数据库意外挂了，可以利用
  1. bin log

  复制代码
  进行数据恢复，因为该日志记录所有数据库所有的变更，保证数据的安全性。
  
• 数据复制，利用一定的机制将主节点MySQL的日志数据传递给从节点，实现数据的一致性，实现架构的高可用和高性能。
  

所以

1. bin log

复制代码

对于数据备份、主从、主主等都都起到了关键作用。

bin log和redo log区别？

看了上面的

1. bin log

复制代码

介绍，是不是感觉和事务日志

1. redo log

复制代码

特别像呢？也是在事务执行的时候记录日志，但是他们还是有区别的。

1. 你知道redo log吗， 如果不了解的话请参考这篇文章：<a href="https://www.jb51.net/database/2921483si.htm"  target="_blank">详解MySQL事务日志redo log_Mysql_脚本之家 (jb51.net)</a>

复制代码

我们现在从多个角度对比下他们俩究竟有什么不一样?
从使用场景角度来说：

• 1. redo log

  复制代码
  主要实现故障情况下的数据恢复，保证事务的持久性
  
• 1. bin log

  复制代码
  主要用于数据灾备、同步
  

从数据内容角度来说：

• 1. redo log

  复制代码
  是"物理日志", 记录的是具体数据页上做了什么修改
  
• 1. bin log

  复制代码
  是"逻辑日志", 记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 name 改为alvin”
  

从生成范围角度来说：

• 1. redo log

  复制代码
  是
  1. InnoDB

  复制代码
  存储引擎生成的事务日志，其他存储引擎没有
  
• 1. bin log

  复制代码
  是MySQL Server生成的日志，所有的存储引擎都有
  

从生成时机角度来说：

• 1. redo log

  复制代码
  是在事务执行过程中就会write
  
• 1. bin log

  复制代码
  是在事务提交的时候write
  

bin log怎么写的？

那

1. bin log

复制代码

是什么时候写的，写入的机制又是怎么样的呢？

1. bin log

复制代码

写入的整体流程如下图所示：

• 为了保证写的效率，会将事务的
  1. bin log

  复制代码
  先写到
  1. binlog cache

  复制代码
  中，注意，这个
  1. cache

  复制代码
  位于事务线程的内存中，主要是一个事务的
  1. bin log

  复制代码
  不能被拆开，是一个整体
  
• 在提交事务的时候，将
  1. binlog cache

  复制代码
  中的数据统一写道文件系统缓存
  1. page cache

  复制代码
  中，这个过程速度也很快
  
• 然后根据不同的策略，将文件系统缓存中的
  1. bin log

  复制代码
  fsync刷到磁盘中，这里的策略后面详细讲解。
  

3种刷盘策略：

1. bin log

复制代码

和

1. redo log

复制代码

类似，都有3种刷盘策略，

1. bin log

复制代码

的write和fsync时机是由参数

1. sync_binlog

复制代码

控制，默认是 0 。

• 1. sync_binlog = 0

  复制代码

为0的时候，表示每次提交事务都只

1. write

复制代码

，由系统自行判断什么时候执行

1. fsync

复制代码

。虽然性能得到提升，但是机器宕机，

1. page cache

复制代码

里面的

1. binglog

复制代码

会丢失。

• sync_binlog = 1
  

• 表示每次提交事务都会执行
  1. fsync

  复制代码
  ,更加安全
  

• sync_binlog = N
  

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

我们已经知道，事务执行时会同时记录

1. redo log

复制代码

和

1. bin log

复制代码

两种日志，那会有日志出错不一致问题吗？

• 1. redo log

  复制代码
  在事务执行过程中可以不断写入
  
• 1. bin log

  复制代码
  只有在提交事务时才写入
  

假如事务执行sql

1. update T set c = 1 where id = 2

复制代码

,在写完

1. redo log

复制代码

日志后，

1. bin log

复制代码

日志写期间发生了异常，会出现什么情况呢？

由于

1. bin log

复制代码

没写完就异常，这时候

1. bin log

复制代码

里面没有对应的修改记录。因此，之后用

1. bin log

复制代码

日志恢复数据时，就会少这一次更新，恢复出来的这一行c值为0，而原库因为

1. redo log

复制代码

日志恢复，这一行c的值是1，最终数据不一致。
那有什么解决方案吗？二阶段提交方案。
为了解决两份日志之间的一致性问题，InnoDB存储引擎使用两阶段提交方案。将

1. redo log

复制代码

的写入拆成了两个步骤

1. prepare

复制代码

和

1. commit

复制代码

。

• 假如现在写入
  1. bin log

  复制代码
  时MySQL发生异常，这时候的
  1. redo log

  复制代码
  还处于
  1. prepare

  复制代码
  阶段，重启MySQL后，根据
  1. redo log

  复制代码
  记录中的事务ID，发现没有对应的
  1. bin log

  复制代码
  日志，回滚前面已写入的数据。
  
• 如果
  1. redo log

  复制代码
  在
  1. commit

  复制代码
  阶段发生移除，但是能通过事务id找到对应的
  1. bin log

  复制代码
  日志，所以MySQL认为是完整的，就会提交事务恢复数据。
  

bin log写到哪了？

前面讲解了

1. bin log

复制代码

写入的过程，那么它写到了哪里去了呢？

• 查看bin log位置
  

可以通过命令

1. show variables like '%log_bin%';

复制代码

查看

1. bin log

复制代码

最终输出的位置。

• 1. log_bin_basename

  复制代码
  : 是
  1. bin log

  复制代码
  日志的基本文件名，后面会追加标识来表示每一个文件
  
• 1. log_bin_index

  复制代码
  : 是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
  

通过

1. SHOW BINARY LOGS;

复制代码

查看当前的二进制日志文件列表及大小，如下图：

• 修改 bin log位置
  

修改MySQL的

1. my.cfg

复制代码

或

1. my.ini

复制代码

配置

1. #启用二进制日志
   
2. log-bin=cxw-bin
   
3. binlog_expire_logs_seconds=600
   
4. max_binlog_size=100M

复制代码

• 1. log-bin

  复制代码
  :
  1. bin log

  复制代码
  日志保存的位置
  
• 1. binlog_expire_logs_seconds

  复制代码
  :
  1. bin log

  复制代码
  日志保存的时间，单位是秒
  
• 1. max_binlog_size

  复制代码
  ： 单个
  1. bin log

  复制代码
  日志的容量
  

bin log内容长啥样？

我们已经知道了

1. bin log

复制代码

的位置了，那它里面的内容长什么样呢？
我们可以用

1. show binlog events

复制代码

命令工具查看

1. 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都会记录在
  1. bin log

  复制代码
  中
  

• 优点：不需要记录每一行的变化，减少了
  1. bin log

  复制代码
  日志量，节约了IO，提高性能。
  
• 缺点：比如sql中存在函数如now()等，依赖环境的函数，会导致主从同步、恢复数据不一致
  

• ROW格式：为了解决Statement缺点，记录具体哪一个分区中的、哪一个页中的、哪一行数据被修改了
  

• 优点：清楚的记录下每一行数据修改的细节，不会出现某些特定情况下 的存储过程，或function无法被正确复制的问题。
  
• 缺点：比如对
  1. ID<600

  复制代码
  的所有数据进行了修改操作,那么意味着很多数据发生变化，最终导致同步的log很多，那么磁盘IO、网络带宽开销会很高。
  

• Mixed格式: 混合模式，即Statment、Row的结合版
  

• 对于可以复制的SQL采用Statment模式记录，对于无法复制的SQL采用Row记录。
  

总结

本文讲解了MySQL中的一个非常重要的日志bin log，它主要用来做数据恢复和同步的，所以作为程序员的我们，还是很有必要对它有一个深入的认识。
以上就是MySQL非常重要的日志bin log详解的详细内容，更多关于MySQL日志bin log的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/database/292151n9j.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！