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

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 log是InnoDB存储引擎生成的事务日志，其他存储引擎没有
• 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 log和bin 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的写入拆成了两个步骤prepare和commit。

• 假如现在写入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.cfg或my.ini配置

• log-bin: bin log日志保存的位置
• binlog_expire_logs_seconds: bin log日志保存的时间，单位是秒
• max_binlog_size： 单个bin log日志的容量

bin log内容长啥样？

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

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

• 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，它主要用来做数据恢复和同步的，所以作为程序员的我们，还是很有必要对它有一个深入的认识。