mysql-binlog的三种模式

MySQL的binlog（二进制日志）有三种主要模式，分别是Statement、Row和Mixed。这三种模式在记录数据库更改的方式上有显著的区别，以下是对这三种模式的详细解释及对比：

一、Statement模式（基于SQL语句的复制，SBR）

• 记录内容：Statement格式的binlog会记录每一项更改数据的SQL语句本身。每当主库上执行了一个数据修改操作，其对应的SQL语句就会被完整地记录在binlog中。记录的内容相对简洁，不包含每一行具体的变化细节。

• 优点：

  • binlog文件相对较小，记录内容简洁，可以节省磁盘IO，提升性能。

• 缺点：

  • 由于SQL语句在不同环境下的执行可能产生不同的结果（如系统变量、函数值、时间戳等），存在一定的不一致性风险。例如，使用取当前日期的函数sysdate时，主库的当前日期和binlog同步到从库的当前日期存在差异，可能导致数据不一致。
  • 如果SQL包含不可重复读取的数据定义语言（DDL）或数据操作语言（DML），可能导致同步问题。
  • 对于涉及特定存储过程、函数、触发器调用的情况，可能无法确保复制的一致性。

二、Row模式

• 记录内容：Row格式的binlog不再记录SQL语句，而是直接记录数据行级别的更改详情。它明确记录了每一行数据的修改细节，包括表名、列名以及行的旧值和新值。

• 优点：

  • 完全避免了SQL语句在不同环境下的执行差异，保证了主从库间数据的一致性，尤其适用于复杂查询或者触发器导致的隐式更新。
  • 在误删改数据后，同时无备份可以恢复时，通过分析binlog日志进行反向处理可以达到恢复数据的目的。

• 缺点：

  • binlog文件通常比Statement格式大得多，因为需要存储具体的行数据变化。
  • 由于需要记录每一行的具体修改，可能导致binlog日志量增大，占用更多存储空间，增加网络传输负担。

三、Mixed模式

• 记录内容：Mixed格式则是对Statement和Row两种格式的综合运用。MySQL会智能地根据执行的具体SQL语句选择合适的日志记录方式。

• 工作原理：

  • 对于大多数常规SQL语句，MySQL会选择使用Statement格式记录binlog，以减少日志量。
  • 当遇到在备库上直接执行原始SQL语句无法达到与主库相同效果的情况，如涉及不确定性的函数、存储过程、触发器等，MySQL会自动切换到Row格式，确保复制的准确性。

• 优点：

  • 既能尽量减小binlog日志大小，又能最大程度地保障主从复制的一致性。

四、总结

• Statement模式：适用于对日志量有较高要求，且能够确保SQL语句在不同环境下执行结果一致性的场景。
• Row模式：适用于对数据一致性要求极高，且不介意增加日志量和存储成本的场景。
• Mixed模式：是MySQL默认推荐的日志格式，能够平衡日志量和数据一致性的需求，适用于大多数常规场景。

在选择binlog日志格式时，需要根据实际需求和场景进行权衡和选择。