mysql的binlog日志

mysql的二进制日志中记录了所有对MySQL数据库的修改事件，包括增删改查事件和对表结构的修改事件。在binlog中记录的事件都是已经成功执行了的日志，对于已经回滚了的如语法错误导致的没有成功执行的操作不会记录在二进制日志中。

二进制日志的格式：
1. statement - 基于段的格式 binlog_format=STATEMENT

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

优点：日志记录量相对较小，节约磁盘及网络I/O。

statement下的优点，首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约io，提高性能。因为他只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点：必须要记录上下文信息，以保证语句在从服务器上执行结果和主服务器上是相同的，但是对于特定函数如UUID(),user(),sleep()这样非确定性函数还是无法正确的复制，这样有可能会造成MySQL复制的主从服务器数据不一致。

由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。

演示：先查看一下binlog的模式

> show variables like 'binlog_format';

> set session binlog_format=statement;   //设置binlog模式，有三种 ：statement / row / mixed

> show binary logs; //查看当前的binlog

> flush logs; //刷新binlog , 会产生新的一个binlog日志文件

之后随意的进行一些表的操作

然后再另开一个界面，进行下面的操作，查看binlog日志

由于binlog是段模式，所以直接使用mysqlbinlog + 日志名就可以了，如果是行模式，则需要mysqlbinlog -vv +日志名

在日志中我们可以清楚看到执行的sql语句。接下来我们看看基于行的日志。

2. row - 基于行的格式 binlog_format=ROW

Row格式可以避免MySQL复制中出现的主从不一致问题，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。假如同一SQL语句修改了10000条数据的情况下，基于段的日志格式只会记录这个SQL语句，基于行的日志会有10000条记录分别记录每一行的数据修改。

优点：

1）使MySQL主从复制更加安全

2）对每一行数据的修改比基于段的复制高效

3）由于误操作而修改了数据库中的数据，同时又没有备份可以恢复时，我们就可以通过分析二进制日志，对日志中记录的数据修改操作做反向处理的方式来达到恢复数据的目的

缺点：

记录日志量较大

binlog_row_image=[FULL|MINIMAL|NOBLOB]

演示：

首先改为row模式

> set session binlog_format=row; 

当binlog_row_image参数为FULL时

然后和之前一样进行一些sql操作

然后查看日志，可以看到记录的是每一行的数据

然后修改binlog_row_image=minimal，执行sql操作，然后查看日志

当binlog_row_image=minimal时的日志信息

> update t set c2='this 2' where id=2;

当binlog_row_image=noblog时的日志信息

> update t set c1='fff' where id=3;

小结：当binlog为行模式的时，把binlog_row_image参数改为minimal或noblog时，记录的日志会大大的减小。

3. mixed - 基于混合的日志格式 binlog_format=MIXED

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。

特点：

根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择。

数据量的大小由所执行的SQL语句决定。

如何选择二进制日志格式

建议

binlog_format = mixed 

or

binlog_format = row

binlog_row_image=minimal