Mysql复制中Binlog的三种格式

Mysql复制中Binlog的三种格式

Statement (statement-based replication:SBR)：基于SQL语句级别的Binlog，每条修改数据的SQL都会保存在Binlog里面；

Row(RBR)：基于行级别，记录每一行数据的变化，也就是将每行数据的变化都记录到Binlog里面，记录得非常详细，单并不记录原始SQL；在复制过程，并不会因为存储过程或者触发器造成主从数据不一致问题，但记录的binlog大小会比Statement格式大很多,CREATE、DROP、ALTER操作只记录原始SQL，而不会记录每行数据的变化到Binlog；

Mixed(MBR):混合Statement和Row模式，默认是Statement模式记录，某些情况下会切换到Row模式，例如SQL中包含与时间、用户相关的函数等statement无法完成主从复制的操作；

基于Statement复制(Mysql5.5默认格式):

优点：

??Binlog日志量少，节约IO，和减少了主从网络binlog传输量

??只记录在master上所执行的语句的细节，以及执行语句的上下文信息

??同时，审计数据库变的更容易

缺点：

??由于此格式是记录原始执行的SQL，保证能在slave上正确执行必须记录每条语句的上下文信息

??部分修改数据库时使用的函数可能出现无法复制：sleep()、last_insert_id()、 load_file()、uuid()、user()、found_rows()、sysdate()(除非启动时–sysdate-is-now=true)

??可能会导致触发器或者存储过程复制导致数据不一致，如调用NOW()函数

??INSERT…SELECT 可能会产生比RBR更多的行级锁，例如没有order by的insert…select

??复制需要执行全表扫描(WHERE中没有使用索引)的UPDATE时，需比row请求更多的行级锁

??对于AUTO_INCREMENT字段的InnoDB引擎表，INSERT会阻塞其他INSERT语句

注：如果statement不能保证主从正常复制,error日志会有提示：Statement may not be safe to log in statement format

基于Row复制:

优点：

??只记录每一行数据变化的细节，不需要记录上下文信息

??不会出现某些情况下auto_increment columns,timestamps,.triger、function、procedure无法正常复制的问题

??新的row格式已经有了优化， CREATE、DROP、ALTER操作只记录原始SQL，而不会记录每行数据的变化到Binlog

??适用于主从复制要求强一致性的环境

缺点：

??update、delete、load data local infile等频繁更新或者删除大量行时会产生大量的binlog日志，会有一定的I/O压力，主从同步产生不必要的流量

??如：UPDATE products set status='sold' where product_id BETWEEN 30000 and 50000;

??无法很好的进行数据库审计

注:

异步复制中只要binlog不丢失即可保证数据的完整性；当主宕机，从库未收到binlog时，就会丢失数据（主磁盘正常时可以提取差异binlog在从执行），此时就需要用到半同步复制方式

主库每次事务成功提交时并不及时反馈给前端，而是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后，才返回Commit操作成功给客户端；如此半同步就保证了事务成功提交后，至少有两份日志记录，一份在主库Binlog上，另一份在从库的Relay Log上，从而进一步保证数据完整性；半同步复制很大程度取决于主从网络RTT（往返时延）；以插件形式存在，