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

目录bin log是什么？bin log和redo log区别？bin log怎么写的？bin log写到哪了？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是在事务执行过程中就会writebin log是在事务提交的时候writebin 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=cxw-binbinlog_expire_logs_seconds=600max_binlog_size=100Mlog-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 :查询总条数（不指定就是所有行）

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非常重要的日志bin log详解的详细内容，更多关于MySQL日志bin log的资料请关注好吧啦网其它相关文章！