MySQLbinlog⽇志格式binlog_format
MySQL binlog⽇志格式 binlog_format
MySQL 5.5 中对于⼆进制⽇志 (binlog) 有 3 种不同的格式可选：Mixed,Statement,Row，默认格式是 Statement。总结⼀下这三种格式⽇志的优缺点。
MySQL Replication 复制可以是基于⼀条语句 (Statement Level) ，也可以是基于⼀条记录 (Row Level)，可以在 MySQL 的配置参数中设定这个复制级别，不同复制级别的设置会影响到 Master 端的 bin-log ⽇志格式。
1. Row
⽇志中会记录成每⼀⾏数据被修改的形式，然后在 slave 端再对相同的数据进⾏修改。
优点：在 row 模式下，bin-log 中可以不记录执⾏的 SQL 语句的上下⽂相关的信息，仅仅只需要记录那⼀条记录被修改了，修改成什么样了。所以 row 的⽇志内容会⾮常清楚的记录下每⼀⾏数据修改的细节，⾮常容易理解。⽽且不会出现某些特定情况下的存储过程或function ，以及 trigger 的调⽤和触发⽆法被正确复制的问题。
缺点：在 row 模式下，所有的执⾏的语句当记录到⽇志中的时候，都将以每⾏记录的修改来记录，这样可能会产⽣⼤量的⽇志内容，⽐如有这样⼀条 update 语句：
UPDATE product SET owner_member_id = 'b' WHERE owner_member_id = 'a'
执⾏之后，⽇志中记录的不是这条 update 语句所对应的事件 (MySQL 以事件的形式来记录 bin-log ⽇志) ，⽽是这条语句所更新的每⼀条记录的变化情况，这样就记录成很多条记录被更新的很多个事件。⾃然，bin-log ⽇志的量就会很⼤。尤其是当执⾏ alter table 之类的语句的时候，产⽣的⽇志量是惊⼈的。因为 MySQL 对于 alter table 之类的表结构变更语句的处理⽅式是整个表的每⼀条记录都需要变动，实际上就是重建了整个表。那么该表的每⼀条记录都会被记录到⽇志中。
2. Statement
每⼀条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执⾏过的相同的SQL 再次执⾏。
优点：在 statement 模式下，⾸先就是解决了 row 模式的缺点，不需要记录每⼀⾏数据的变化，减少了 bin-log ⽇志量，节省 I/O 以及存储资源，提⾼性能。因为他只需要记录在 master 上所执⾏的语句的细节，以及执⾏语句时候的上下⽂的信息。
缺点：在 statement 模式下，由于他是记录的执⾏语句，所以，为了让这些语句在 slave 端也能正确执⾏，那么他还必须记录每条语句在执⾏的时候的⼀些相关信息，也就是上下⽂信息，以保证所有语句在 slave 端杯执⾏的时候能够得到和在 master 端执⾏时候相同的结果。另外就是，由于 MySQL 现在发展⽐较快，很多的新功能不断的加⼊，使 MySQL 的复制遇到了不⼩的挑战，⾃然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement 中，⽬前已经发现的就有不少情况会造成 MySQL 的复制出现问题，主要是修改数据的时候使⽤了某些特定的函数或者功能的时候会出现，⽐如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使⽤了
last_insert_id() 函数，可能会使 slave 和 master 上得到不⼀致的 id 等等。由于 row 是基于每⼀⾏来记录的变化，所以不会出现类似的问题。
3. Mixed
从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。
在 Mixed 模式下，MySQL 会根据执⾏的每⼀条具体的 SQL 语句来区分对待记录的⽇志形式，也就是在 statement 和 row 之间选择⼀种。
新版本中的 statment 还是和以前⼀样，仅仅记录执⾏的语句。⽽新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，⽐如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有⾏的变更。
其他参考信息
除以下⼏种情况外，在运⾏时可以动态改变 binlog 的格式：
1. 存储流程或者触发器中间；
2. 启⽤了 NDB；
3. 当前会话使⽤ row 模式，并且已打开了临时表；
如果 binlog 采⽤了 Mixed 模式，那么在以下⼏种情况下会⾃动将 binlog 的模式由 statement 模式变为 row 模式：
1. 当 DML 语句更新⼀个 NDB 表时；
2. 当函数中包含 UUID() 时；
3. 2 个及以上包含 AUTO_INCREMENT 字段的表被更新时；
4. 执⾏ INSERT DELAYED 语句时；
5. ⽤ UDF 时；
6. 视图中必须要求运⽤ row 时，例如建⽴视图时使⽤了 UUID() 函数；
在配置⽂件my.ini设定主从复制模式：
log-bin=mysql-bin
#binlog_format=STATEMENT
#binlog_format=ROW
binlog_format=MIXED
也可以在运⾏时动态修改 binlog 的格式。例如：
1. mysql> SET SESSION binlog_format = 'STATEMENT';
2. mysql> SET SESSION binlog_format = 'ROW';
3. mysql> SET SESSION binlog_format = 'MIXED';
4. mysql> SET GLOBAL binlog_format = 'STATEMENT';
5. mysql> SET GLOBAL binlog_format = 'ROW';
6. mysql> SET GLOBAL binlog_format = 'MIXED';
两种模式的对⽐：
Statement 优点
历史悠久，技术成熟；
产⽣的 binlog ⽂件较⼩；
binlog 中包含了所有数据库修改信息，可以据此来审核数据库的安全等情况；
binlog 可以⽤于实时的还原，⽽不仅仅⽤于复制；
主从版本可以不⼀样，从服务器版本可以⽐主服务器版本⾼；
Statement 缺点：
不是所有的 UPDATE 语句都能被复制，尤其是包含不确定操作的时候；
调⽤具有不确定因素的 UDF 时复制也可能出现问题；
运⽤以下函数的语句也不能被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除⾮启动时启⽤了 –sysdate-is-now 选项)
INSERT … SELECT 会产⽣⽐ RBR 更多的⾏级锁；
复制须要执⾏全表扫描 (WHERE 语句中没有运⽤到索引) 的 UPDATE 时，须要⽐ row 请求更多的⾏级锁；
对于有 AUTO_INCREMENT 字段的 InnoDB 表⽽⾔，INSERT 语句会阻塞其他 INSERT 语句；
对于⼀些复杂的语句，在从服务器上的耗资源情况会更严重，⽽ row 模式下，只会对那个发⽣变化的记录产⽣影响；
存储函数(不是存储流程 )在被调⽤的同时也会执⾏⼀次 NOW() 函数，这个可以说是坏事也可能是好事；
确定了的 UDF 也须要在从服务器上执⾏；
数据表必须⼏乎和主服务器保持⼀致才⾏，否则可能会导致复制出错；
执⾏复杂语句如果出错的话，会消耗更多资源；
Row 优点
任何情况都可以被复制，这对复制来说是最安全可靠的；
和其他⼤多数数据库系统的复制技能⼀样；
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多；
复制以下⼏种语句时的⾏锁更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执⾏ INSERT，UPDATE，DELETE 语句时锁更少；
从服务器上采⽤多线程来执⾏复制成为可能；
Row 缺点
⽣成的 binlog ⽇志体积⼤了很多；
复杂的回滚时 binlog 中会包含⼤量的数据；
主服务器上执⾏ UPDATE 语句时，所有发⽣变化的记录都会写到 binlog 中，⽽ statement 只会写⼀次，这会导致频繁发⽣ binlog 的写并发请求；
UDF 产⽣的⼤ BLOB 值会导致复制变慢；
不能从 binlog 中看到都复制了写什么语句(加密过的)；
当在⾮事务表上执⾏⼀段堆积的 SQL 语句时，最好采⽤ statement 模式，否则很容易导致主从服务器的数据不⼀致情况发⽣；
另外，针对系统库 MySQL ⾥⾯的表发⽣变化时的处理准则如下：
如果是采⽤ INSERT，UPDATE，DELETE 直接操作表的情况，则⽇志格式根据 binlog_format 的设定⽽记录；
如果是采⽤ GRANT，REVOKE，SET PASSWORD 等管理语句来做的话，那么⽆论如何都要使⽤ statement 模式记录；
mysql下载什么版本的使⽤ statement 模式后，能处理很多原先出现的主键重复问题；

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。