研发应该懂的binlog知识！

桃扇骨 2021-09-30 22:34 363阅读 0赞

> 本文公众号来源：孤独烟  作者：孤独烟
> 
>   
> 原作者通俗易懂讲解了binlog的一些知识点！我觉得写得非常不错，建议阅读！

## 引言 ##

### 为什么写这篇文章? ###

大家当年在学MySQL的时候，为了能够迅速就业，一般是学习一下MySQL的基本语法，差不多就出山找工作了。水平稍微好一点的童鞋呢还会懂一点存储过程的编写，又或者是懂一点索引的创建和使用。但是呢，基本上大家都忽略了对底层知识的学习。为什么呢？因为工作中很少用到嘛。然后呢，市面上流传的大部分这种底层的知识，又比较偏运维，研发懂这么多意义也不是太大，很多知识可能这辈子都不会用到。  
因此，我整理了一部分相关的知识，希望大家有所收获。

### 研发究竟要懂哪些? ###

主要分为两个部分

计划分上下两个部分来叙述。上部分讲述**binlog的相关概念**这部分的知识，我们不需要像运维懂的那么深，我会列举一些常见概念和常见配置，大家匆匆扫一眼，有个概念即可。这样大家以后和运维讨论问题的时候，也不会一脸的懵逼。正所谓

> **懵逼树上懵逼果,懵逼树下你和我。   **
> 
> **懵逼树前排排坐,一人一个懵逼果。**

博主一个人默默的把懵逼果收走独享就好，各位读者还是懂点基本概念，以后方便和运维沟通。

另外，这篇文章是给研发大大看的，可能有些概念我理解的也不对，请运维大大轻喷。

## 正文 ##

记得我的"一个定义,两个误解,三个用途,四个常识"

### 一个定义 ###

先从定义开始讲起

> **binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。**
> 
>   
> 
> 
> **binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。**

多说一句，如果`update`操作没有造成数据变化，也是会记入`binlog`。

### 两个误解 ###

**误解一:binlog只是一类记录操作内容的日志文件**  
因为`binlog`称之为二进制日志，很多研发会把这个**二进制日志**和我们平时在代码里写的**代码日志**联系在一起。因为我们的**代码日志**，只有一类记录操作容的文件，并不包含索引文件。然而，这个二进制日志包括两类文件：

这么说可能还有一点抽象，假设文件`my.cnf`中有这么三条配置

log_bin：on 打开binlog日志log_bin_basename：bin文件路径及名前缀（/var/log/mysql/mysql-bin）log_bin_index：bin文件index（/var/log/mysql/mysql-bin.index）
    log_bin_basename：bin文件路径及名前缀（/var/log/mysql/mysql-bin）
    
    log_bin_index：bin文件index（/var/log/mysql/mysql-bin.index）

那么你会在文件目录`/var/log/mysql/`下面发现两个文件`mysql-bin.000001`和`mysql-bin.index`。  
`mysql-bin.index`就是我们所说的索引文件，打开瞅瞅，内容是下面这样,记录哪些文件是日志文件。

./mysql-bin.000001

那么说到日志文件。在`innodb`里其实又可以分为两部分，一部分在缓存中，一部分在磁盘上。这里业内有一个词叫做**刷盘**，就是指将缓存中的日志刷到磁盘上。跟**刷盘**有关的参数有两个个:`sync_binlog`和`binlog_cache_size`。这两个参数作用如下

binlog_cache_size: 二进制日志缓存部分的大小，默认值32ksync_binlog=[N]: 表示写缓冲多少次，刷一次盘,默认值为0
    sync_binlog=[N]: 表示写缓冲多少次，刷一次盘,默认值为0

注意两点:

另外，这里存在一个一致性问题，`sync_binlog=N`，数据库在操作系统宕机的时候，可能数据并没有同步到磁盘，于是再次重启数据库，会带来数据丢失问题。  
当`sync_binlog=1`，事务在`Commit`的时候，数据写入`binlog`，但是还没写入事务日志(`redo log`和`undo log`)。此时宕机，重启数据库，数据被回滚。但是`binlog`里已经记录，这里存在不一致问题。这个事务日志和`binlog`一致性的问题，大家可以查询mysql的内部XA协议，该协议就是解决这个一致性问题的。

**误解二:binlo****g是InnoDb独****有的**  
`binlog`是以事件形式记录的，这句话通俗点说，就是`binlog`的内容都是一个个的事件。这块具体的我会在下一篇讲，这篇记住`binlog`的内容就是一个个事件就行。  
注意了，这里的用词，是一个个事件，而不是事务。大家应该知道`Innodb`和`mysiam`最显著的区别就是一个支持事务，一个不支持事务。  
因此你可以说，`binlog`是基于事务来记录二进制日志，比如`sync_binlog=1`,每提交一次事务，就写入`binlog`。你却不能说`binlog`是事务日志，`binlog`不仅记录`innodb`日志，在`myisam`中，也一样存在`binlog`。

### 三个用途 ###

这三个用途，出自《MySQL技术内幕 InnoDB存储引擎》一书，分别为**恢复**、**复制**、**审计**。这三个用途，研发大大们了解一下即可，比如数据恢复，你碰到同事删库的机会实在太少。假如真的有同事舍己为人，冒着离职的风险给你提供做数据**恢复**的机会，大把运维工程师待命在那，轮不到你的。所以，这三个功能了解即可。

**恢复**：这里网上有大把的文章指导你，如何利用`binlog`日志恢复数据库数据。如果你真的觉得自己很有时间，就自己去创建个库，然后删了，再去恢复一下数据，练练手吧。

**复制**: 如图所示（图片不是自己画的，偷懒了）

![640?wx\_fmt=png][640_wx_fmt_png]

主库有一个 `log dump`线程，将 `binlog`传给从库  
从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的 `binlog`内容并写入到 `relay log`,SQL线程从 `relay log`里面读取内容，写入从库的数据库。

**审计**：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

### 四个常识 ###

**常识一:binlog常见格式**  
这块知识我用一个表格来表示，没必要啰嗦一大堆。

![640?wx\_fmt=png][640_wx_fmt_png 1]

业内目前推荐使用的是`row`模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。  
那么，大家一定想问，为什么不推荐使用`mixed`模式，理由如下  
假设`master`有两条记录，而`slave`只有一条记录。![640?wx\_fmt=png][640_wx_fmt_png 2]

当在`master`上更新一条从库不存在的记录时，也就是`id=2`的记录，你会发现`master`是可以执行成功的。而`slave`拿到这个SQL后，也会照常执行，不报任何异常，只是更新操作不影响行数而已。并且你执行命令`show slave status`，查看输出，你会发现没有异常。但是，如果你是`row`模式，由于这行根本不存在，是会报1062错误的。

**常识二:怎查看binlog**  
`binlog`本身是一类二进制文件。二进制文件更省空间，写入速度更快，是无法直接打开来查看的。

因此mysql提供了命令`mysqlbinlog`进行查看。  
一般的`statement`格式的二进制文件，用下面命令就可以

mysqlbinlog mysql-bin.000001 mysql-bin.000001

如果是`row`格式，加上`-v`或者`-vv`参数就行，如

mysqlbinlog -vv mysql-bin.000001 -vv mysql-bin.000001

**常识三:怎么删binlog**  
删`binlog`的方法很多，有三种是常见的  
(1) 使用`reset master`,该命令将会删除所有日志，并让日志文件重新从000001开始。  
(2) 使用命令

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

例如

purge master logs to "binlog_name.00000X" master logs to "binlog_name.00000X"

将会清空00000X之前的所有日志文件.  
(3) 使用`expire_logs_days=N`选项指定过了多少天日志自动过期清空。

**常识四:binlog常见参数**  
常见参数，列举如下，有个印象就好。

![640?wx\_fmt=png][640_wx_fmt_png 3]

### 思考题 ###

请问，我说的

说的是什么呢？

## 最后 ##

##  ##

> 乐于输出**干货**的Java技术公众号：Java3y。公众号内有200多篇**原创**技术文章、海量视频资源、精美脑图，不妨来**关注**一下！

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg]

有帮助？好看！转发！ ![640][]

推荐阅读：[一文读懂一台计算机是如何把数据发送给另一台计算机的][Link 1]

[640_wx_fmt_png]: /images/20210827/518a133696b746b19daee67caa8b9457.png?wx_fmt=png
[640_wx_fmt_png 1]: /images/20210827/8e1367cdaf284e12a49f603b5805c872.png?wx_fmt=png
[640_wx_fmt_png 2]: /images/20210827/305cf78efd8d4907b4b1fe87494e7f18.png?wx_fmt=png
[640_wx_fmt_png 3]: /images/20210827/facd94ca36244efd9c65ded8a577bc9c.png?wx_fmt=png
[640_wx_fmt_jpeg]: /images/20210827/cb90512495d8423ebc32bf7c57188077.png?wx_fmt=jpeg
[640]: /images/20210827/2560fbc0bf0d4a7e84866413747370a9.png
[Link 1]: https://mp.weixin.qq.com/s?__biz=MzI4Njg5MDA5NA==&mid=2247484964&idx=1&sn=263a86011c8151bd4df84a77a199ed57&chksm=ebd74725dca0ce33ef0354fb2da6270a68b0bffe7cf3302090e216c1dcc3bfed513a8f669195&token=1140137493&lang=zh_CN&scene=21#wechat_redirect