MYSQL binlog_format 解释

MySQL 5.1 在复制方面的改进是引进新的复制技术：基于行的复制。简而言之，这种新技术是关注表中变化的记录，而不是以前 的照抄 binlog 模式。从 MySQL 5.1.12 从以下三种模式开始：基于SQL语句的复制(statement-based replication, SBR)，复制基于行(row-based replication, RBR)，复制混合模式(mixed-based replication, MBR)。相应地，binlog有三种格式：STATEMENT，ROW，MIXED。MBR 模式中，SBR 默认模式。 除以下情况外，binlog的格式可以在运行过程中动态低变化： 或触发器中间的存储过程 NDB启用 当前会话试用 RBR 模式已打开，临时表已打开 如果binlog使用 MIXED 在以下情况下，binlog模式将在以下情况下自动使用 SBR 模式改成 RBR 模式。 当DML语句更新NDB表时 包含在函数中 UUID() 时 包括两个或两个以上 AUTO_INCREMENT 当字段表更新时 行任何 INSERT DELAYED 语句时 用 UDF 时 必须在视图中使用 RBR 时，例如，使用了创建视图。 UUID() 函数 设置主从复制模式的方法很简单，只要在以前设置复制配置的基础上，再加一个参数： binlog_format="STATEMENT" #binlog_format="ROW" #binlog_format="MIXED" 当然了，binlog格式也可以在运行过程中动态修改。例如 mysql> SET SESSION binlog_format = 'STATEMENT'; mysql> SET SESSION binlog_format = 'ROW'; mysql> SET SESSION binlog_format = 'MIXED'; mysql> SET GLOBAL binlog_format = 'STATEMENT'; mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED'; 现在比较以下几点 SBR 和 RBR 二中模式各有优缺点： SBR 的优点： 历史悠久，技术成熟 binlog文件较小 binlog包含所有数据库更改信息，可以相应地审查数据库的安全性 binlog可用于实时还原，而不仅仅是复制 主从版本可以不同，从服务器版本可以高于主服务器版本 SBR 的缺点： 并非所有的UPDATE语句都可以复制，特别是当包含不确定操作时。 具有不确定因素的调用 UDF 复制也可能有问题 不能复制使用以下函数的句子： * LOAD_FILE() * UUID() * USER() * FOUND_ROWS() * SYSDATE() (除非启动时启用 --sysdate-is-now 选项) INSERT ... SELECT 会产生比 RBR 更多的行级锁 复制需要进行全表扫描(WHERE 索引没有用于句子中) UPDATE 时，需要比 RBR 请求更多的行级锁 对于有 AUTO_INCREMENT 字段的 就InnnoDB而言，INSERT 句子会阻止其他人 INSERT 语句 对于一些复杂的句子，从服务器上消耗资源会更严重， RBR 模式下，只会影响改变的记录 存储函数(不是存储过程)在被调用时也会执行一次 NOW() 函数，可以说是坏事，也可能是好事 确定了的 UDF 也需要从服务器上执行 数据表必须几乎与主服务器一致，否则可能会导致复制错误 如果执行复杂的句子出错，会消耗更多的资源 RBR 的优点： 任何情况都可以复制，这是复制最安全可靠的 与大多数其他数据库系统的复制技术一样 在大多数情况下，如果服务器上的表有主键，复制会快很多 复制以下句子时，行锁较少： * INSERT ... SELECT * 包含 AUTO_INCREMENT 字段的 INSERT * 没有附带条件或许多记录没有修改 UPDATE 或 DELETE 语句 执行 INSERT，UPDATE，DELETE 语句锁定较少 可以从服务器上复制多线程 RBR 的缺点： binlog 大了很多 复杂的回滚 binlog 它将包含大量的数据 执行在主服务器上 UPDATE 在句子中，所有变化的记录都将被写入 binlog 中，而 SBR 只写一次，这将导致频繁发生 binlog 并发写问题 UDF 产生的大 BLOB 这个值会导致复制变慢 无法从 binlog 我看到他们都复制了写什么句子 在非事务表上执行积累的SQL语句时，最好使用 SBR 模式，否则很容易导致主服务器的数据不一致 另外，针对系统库 mysql 内表发生变化时的处理规则如下： 如果是采用 INSERT，UPDATE，DELETE 根据直接操作表的情况，日志格式应根据 binlog_format 并记录设置 如果是采用 GRANT，REVOKE，SET PASSWORD 等待管理语句来做，所以无论如何都要用 SBR 模式记录 注：采用 RBR 模式结束后，可以解决许多原始主键重复问题。