mysql--二进制日志(bin-log)三种格式介绍及分析

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

一、Mysql binlog日志有三种格式，分别为ROW、Statement以及MiXED。

Row Level
Binary Log会记录成【每一行数据被修改的形式】，然后在Slave端再对相同的数据进行修改。
如果修改了表的结构，那么binlog日志记录的是重新创建表，在插入字段、update等操作语句，而不是的alter的动作。

优点：在Row
Level模式下，Binnary Log可以不记录执行的Query语句的上下文相关信息，只要记录哪一行修改了，修改成什么样子。Row
Level会详细的记录下每一行数据的修改细节，而且不会出现某个特定情况下的存储过程，或Function，以及Trigger的调用和触发无法被正确复制问题。

缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter
table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

Statment Level
Statment Level【每一条会修改的SQL语句】都会记录到Master的Binnary中。Slave端在复制的时候，SQL线程会解析成和原来Master端执行过相同的SQL语句，并再次执行。

优点：首先，解决了Row
Level下的缺点，不须要记录每一行的数据变化，减少了Binnary
Log日志量，节约了IO成本，提高了性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

缺点：由于它是记录的执行语句，为了让这些语句在Slave端也能正确执行。那么它还必须记录每条语句在执行时的一些相关信息，即上下文信息，以保证所有语句在Slave端被执行的时候能够得到和在Master端执行时相同的结果。另外，由于MySQL发展比较快，很多新功能不断加入，使得MySQL复制遇到了不小的挑战，复制时设计的内容越复杂，越容易出bug。在Statement

Level下，目前已发现不少的情况下会造成MySQL的复制问题。主要是在修改数据使用了某些特定的函数货功能后，出现，比如：Sleep()函数在有些版本中就不能正确的复制，在存储过程中使用了last_insert_id()函数，可能会使Slave和Master的到不一致的ID，等等。
使用以下函数的语句也无法被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

Mixed Level
在Mixed模式下，是以上两种level的混合使用。
一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row
level模式也被做了优化，并不是所有的修改都会以row
level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。除了MySQL认为通过Statement方式可能造成复制过程中Master和Slave之间产生不一致数据。(如特殊Procedure和Funtion的使用，UUID()函数的使用等特殊情况)时，它会选择ROW的模式来记录变更之外，都会使用Statement方式。

二、Binlog基本配制与格式设定
1.基本配制
Mysql BInlog日志格式可以通过mysql的my.cnf文件的属性binlog_format指定。如以下：
binlog_format           = MIXED                 //binlog日志格式
log_bin                  =目录/mysql-bin.log    //binlog日志名
expire_logs_days    = 7               //binlog过期清理时间
max_binlog_size    100m                 //binlog每个日志文件大小

2.Binlog日志格式选择
Mysql默认是使用Statement日志格式，推荐使用MIXED.
由于一些特殊使用，可以考虑使用ROWED，如自己通过binlog日志来同步数据的修改，这样会节省很多相关操作。对于binlog数据处理会变得非常轻松,相对mixed，解析也会很轻松(当然前提是增加的日志量所带来的IO开销在容忍的范围内即可)。

3.mysqlbinlog格式选择
mysql对于日志格式的选定原则:如果是采用
INSERT，UPDATE，DELETE 等直接操作表的情况，则日志格式根据 binlog_format 的设定而记录,如果是采用
GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么无论如何都采用 SBR 模式记录。