binlog解读1

一、什么是binlog？

答：1、binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。binlog是mysql本身提供的一种逻辑日志，和具体的存储引擎无关，但是不同的存储引擎对binlog写入的模式有要求。

2、作用：当有数据写入到数据库时，会同时把更新的SQL语句写入到对应的binlog文件中，主要作用时用于数据库的主从复制及数据的增量恢复。比如使用mysqldump或者xtrabackup进行一次全量备份后，运行一段时间数据库出现故障，那么就可以通过binlog来进行数据恢复了。

二、如何开启binlog？

show variables like ‘log_bin’，可以看到有没有开启binlog，而且可以在配置文件中配置binlog文件的大小，保留天数等等。

问题： binlog文件的大小是一样的吗？

答：正常情况下，线上binlog文件的大小默认都是1G，但是当写入最后一个事务的时候这个事务数据量比较大，则binlog会记录这一完整的事务导致binlog文件大于1G。

三、什么是二进制格式的文件？

答：1、计算机文件基本上分为二种：二进制文件和 ASCII（utf-8等字符编码）文件（也称纯文本文件），图形文件及文字处理程序等计算机程序都属于二进制文件。这些文件含有特殊的格式及计算机代码。ASCII 则是可以用任何文字处理程序阅读的简单文本文件。

2、使用二进制文件的好处：

（1）第一是二进制文件比较节约空间，这两者储存字符型数据时并没有差别。但是在储存数字，特别是实型数字（小数）时，二进制更节省空间，比如储存数据：3.1415927，文本文件需要 9 个字节，分别储存：3 . 1 4 1 5 9 2 7 这 9 个 ASCII 值，而二进制文件只需要 4 个字节（DB 0F 49 40）（eg：golang的float32类型只占4个字节）。

（2）第二个原因是，内存中参加计算的数据都是用二进制无格式储存起来的，因此，使用二进制储存到文件就更快捷。如果储存为文本文件，则需要一个转换的过程。在数据量很大的时候，两者就会有明显的速度差别了。

 

四、记录binlog的三种模式？

答：1、STATEMENT（SBR，statement-based replication）模式：

记录每条会修改数据的sql语句；为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。

存在问题：

1. 像一些特定函数功能，在MySQL主从复制时，slave要与master上保持一致会有很多相关问题，如sql中包含now(), last_insert_id()、UUID()等函数时。
2. 在执行insert … select …语句时会产生大量行锁。为什么会产生大量行锁呢？因为在select的时候，行锁是针对索引来实现的，如果where字段中有非索引字段，那么就是加上表锁。

2、ROW(RBR，row-based replication)模式：

不记录相关的sql语句，仅保存哪条记录被修改了，也就是说日志中会记录成每行数据被修改的形式。会详细记录每行数据的每个字段对应的值内容。

两个参数：（mysql 5.6.2之后引入）

1. binlog_rows_query_log_events：

在row模式下..开启该参数，将把sql语句打印到binlog日志里面。默认是0(off)；虽然将语句放入了binlog，但不会执行这个sql，就相当于注释一样。但对于dba来说,在查看binlog的时候,很有用处。

1. binlog_row_image：

默认为full，在binlog为row格式下，full模式将记录update前后所有字段的值；minimal时只记录更改字段的值和where字段的值；noblob时,记录除了blob和text的所有字段的值。如果update的blob或text字段,也只记录该字段更改后的值,更改前的不记录。这样做的目的是控制binlog写入的量。

c、缺点是开启row格式后，生成binlog文件的内容太多，比如alter表时，所有修改的记录都会被写进binlog文件中。

3、MIXED(MBR)混合模式：

对于绝大部分操作，都使用 STATEMENT 来进行 binlog 的记录，只有以下操作使用 ROW 来实现：表的存储引擎为 NDB；使用了uuid() 等不确定函数；使用了 insert delay 语句；2 个及以上包含 AUTO_INCREMENT 字段的表被更新时；使用了临时表（比如insert … select语句）。

注：在ROW格式下，使用update语句时如果对行数据没有影响是不会记录binlog中的，但是在MIXED和STATEMENT模式下还是会将语句记录binlog中的（update test set name=’666’ where name=’666’，即使where条件没有匹配的行）。