MySQL中使用binlog时binlog格式的选择

MySQL中使用binlog时binlog格式的选择

MySQL中的binlog是二进制日志文件，用于记录MySQL中的所有数据变更操作，包括增删改查等操作。binlog可以用于数据恢复、数据备份、主从复制等场景。在使用binlog时，需要选择合适的binlog格式。本文将介绍MySQL中的binlog格式，并讨论何时使用哪种binlog格式。

1. binlog的三种模式

MySQL中有三种binlog格式：STATEMENT、ROW、MIXED。

1） Statement level模式：记录每个修改数据的SQL语句到主服务器的binlog中。在从服务器复制数据时，解析binlog中的SQL并再次执行。

优点：相比其他两种模式，能有效减少binlog的日志量、节约IO。

缺点：由于binlog的记录是一个SQL语句执行的结果，为了在从服务器中获取到和主服务器相同的执行结果，必须记录相关的上下文信息。同时，该模式在MySQL新版本中会出现一些 bug。

2） Row level模式：将每一行的变化记录在binlog中，从服务器在执行的时候，直接将相同数据进行修改。

优点：可以记录变化细节，日志内容清晰。

缺点：记录的日志量大，相同SQL的执行需要记录很多行的变化，会耗费大量存储容量。

3） Mixed模式：结合以上两种模式，在执行时根据具体情况选择使用STATEMENT模式还是ROW模式。比如，更新数据时直接执行SQL语句的情况使用STATEMENT模式，其他情况使用ROW模式。

优点：能在不同的场景下自动选择合适的模式，既能保证日志清晰，也能节省日志容量。

缺点：对于一些复杂的存储过程和触发器，还存在一些bug。

2. 我们应该选择什么格式？

以上三种binlog格式中，应该选择哪一种呢？这要根据具体情况来判断。

在使用时，需要考虑以下因素：

1）MySQL的版本：新版本的MySQL在ROW模式下有很多优化，同时也修复了一些STATEMENT模式下的bug。

2）MySQL的存储引擎：ROW模式在MyISAM、MEMORY等非事务存储引擎中表现更好。而在InnoDB等事务存储引擎中，STATEMENT模式会存在一些限制，需要慎重选择。

3）MySQL的隔离级别：在READ-COMMITTED、READ-UNCOMMITTED隔离级别或者参数innodb_locks_unsafe_for_binlog为ON时，STATEMENT模式将被禁止。

4）业务特点：如果数据修改较多，并且数据修改前后差异比较大，建议选择ROW模式。如果数据修改较少，可以尝试选择MIXED模式，以自动判断使用合适的模式。

3. 为什么RC、RU下无法使用statement格式binlog？

在RC和RU隔离级别下，事务操作需要读取和操作其他事务提交或正在写入的数据，而事务提交后再将操作写入binlog，binlog和数据库实际数据可能存在不一致的问题。例如，一个事务修改数据后提交，同时另一个事务插入一条新数据并提交，在binlog中这两个操作的先后顺序可能会和实际执行顺序不一致。

为了避免这种情况，只有在RR隔离级别及以上才能使用STATEMENT格式binlog。在使用RR隔离级别时，MySQL会使用其他机制来保证数据的一致性。

4. 总结

在使用MySQL的binlog时，需要根据具体情况选择合适的binlog格式。如果使用的是事务存储引擎，建议使用ROW模式以避免STATEMENT模式下的一些限制。在使用非事务存储引擎时，ROW模式比STATEMENT模式性能更佳。同时，在RC和RU隔离级别下无法使用STATEMENT格式binlog，应该优先选择READ-COMMITTED隔离级别及以上。

扫码添加UTHEME微信为好友

· 分享WordPress相关技术文章，主题上新与优惠动态早知道。
· 微信端最大WordPress社群，限时免费入群。