mysql二进制日志格式对复制的影响

根据参与复制的主数据库所使用的二进制日志格式的不同,复制可以分为基于SQL语句的复制,和基于行的复制,

那么基于SQL语句的复制呢,就是指的是主数据库服务器的二进制日志的格式,使用statement这种格式,而基于行的复制呢,

就是主库的二进制日志呢,所使用的是基于行的日志格式,如果我们的主库设置的是混合格式的话,则复制时会根据实际内容

基于SQL语句的复制,和基于行的复制之间呢,切换,前面已经介绍了这两种格式的优缺点

下面我们再来看一看使用这两种格式进行复制,对复制会有什么样的影响,首先基于SQL语句的复制,

也称之为SBR,在MYSQL5.1.4的版本中,只存在于基于SQL语句的方式,所以又称之为逻辑复制,在这种复制模式下呢,

主库会记录进行修改的SQL语句,备库则会读取存放的这些SQL语句,就是把主库上执行的SQL语句呢,从库上再从新

执行一遍,所以大家通过上面的介绍呢,可以知道,在这种复制模式下,主库的二进制日志格式呢,可能就是基于段的这种

格式,因此也会受到这种日志记录格式的影响,我们来看一下这种复制模式的优缺点是什么,首先基于SQL语句复制的优点呢,

在于由于使用的是段的日志格式,所以生成的日志量比较少,这样就会节约网络传输的IO,如果一条更新好几兆的语句呢,只会

占几十个字节,如果从数据库上表的定义呢不同,但是数据类型是可以兼容的,或者是列的顺序不同时,那么基于SQL语句的复制呢,

可以很好地工作,这样对于大表进行表结构修改的时候呢,很容易的在从服务器上进行修改,然后再进行主从服务器的切换,这样就可以

减少大表进行修改的屏蔽时间,特别是在老版本的MYSQL中,几乎这就是大表修改的最好方式了,那么使用基于SQL语句复制的第三个

好处呢,相比于基于行的方式,他更为灵活,由于日志记录中呢,我们主库上所执行的SQL语句,所以我们很容易定位问题的发生,说完了

优点呢,当然这个缺点是什么,首先SBR的复制呢,类型的缺点呢,也和主库所使用的二进制格式,为段的日志格式呢,有很大的关系,

他的第一个缺点,就是对于非确定性事件,无法保证主从复制数据的一致性,比如UUID这个函数,在主从服务器上,分别执行后,所要

得到的结果呢,可能会是不一样的,我见过一些项目呢,把UUID来作为表的主键来使用的,如果使用基于SQL语句的复制,这些表的

主键呢,在主从服务器上就会产生差异,这样的结果呢,我们是无法接受的,同时呢由于主从服务器,处理的不一致,就会导致主从链路的

中断,那么第二个缺点呢,和第一个缺点你也有点类似,由于SBR的一些bug,会使得一些存储过程,像触发器啊,自定义函数啊,主从

服务器上的执行结果也是不一样的,这样也会造成主从服务器数据的不一致,虽然这些bug呢,很多已经得到了解决,不过出于一种

安全的考虑,最好还是避免使用这种复制的类型,使用SBR复制呢,还有一个问题,就是当我们批量执行一个SQL的时候,这个SQL在

主库上执行时,那么在从库上执行时呢,同样要锁定相同行数的数据,而如果使用基于行的复制,在从库上执行的时候呢,就只需要

指定更新的一行数据就可以了,所以同使用基于行的数据相比,基于SQL的复制类型呢,在进行界内SQL操作时呢,所产生SQL修改时,

就是在大批量SQL修改之后,数据同步时,重复的性能要低于基于行的方式,以上就是基于SQL的优缺点,大家可以看到,这些优缺点呢,

是由于主库所使用的二进制日志格式,所决定的,下面我们就来看一下,另外一种复制类型

也就是基于行的复制类型优缺点又是什么呢,前面我们已经提到过了,所谓的基于行的复制,就是指的是,主库服务器的

二进制日志,类型所使用的是ROW格式,所以这种类型的优缺点呢,也和ROW格式的二进制日志格式呢,是息息相关的,其优点,

由于主数据库服务器,也用的是二进制服务器,是ROW格式的,所以在主从同步时,从服务器上的主库上,对某一行数据所做的

修改,所以可以保证,在从上做的数据修改呢,在主上是一致的,而不管主服务器上在修改数据时,否则存储过程,触发器等等,

比如我们在主上对于插入一个UUID函数所生成的值,那么在基于SQL的复制呢,同样的会执行一个UUID函数,以生成这个值,所以

从上生成的新值和我们的这个值呢,是有可能是不同的,另外这个问题也是基于段的二进制日志格式,对数据库进行恢复的时候,

如果是基于行的复制中呢,从主上传输到从上生成的UUI值是什么,那么在从上呢会更新成什么,而不用重新在计算了,所以可以完全

保证主从数据库呢,数据是一致的,那么使用行复制的第二个好处呢,可以减少服务器上锁的使用,看一下下面的这个SQL,那么这个

SQL呢,执行时会对整个表进行锁定,如果使用基于段的复制,那么在从库上呢,执行的时间,同样要对order表进行锁定,而如果使用

基于行的复制,我只要同步order_cnt表,增加行的数据就可以了,在从DB服务器上呢,并不需要对order表进行锁定了,所以说基于

行的复制,可以减少从服务器这种锁的使用,增加从服务器并发的性能,同时对于这种SQL来说呢,同步数据所需要的时间,基于行的复制呢,

要比基于SQL语句的复制方式呢,要少的多,这样也就从另一方面增强了主从复制的性能,以上就是基于行复制的一些优点

同样我们要看看他的缺点是什么,基于行的复制存在的第一个问题呢,是要求主从数据库的表结构,必须要一致,

对于相同的列,即使列的顺序不同,也有可能产生问题,否则就会引起复制链的中断,当然这种情况也是有例外的,

如果我们是在从服务器表的末尾增加列的话,这种情况复制并不会被中断,但是一旦在主上对同一个表增加了其他的列,

并且没有指定这个列的顺序的情况下,当对这个表的修改复制到从上,就会中断复制链路,所以还是建议大家要保持主从

数据库的一致,这是一种最安全的一种方式,另外由于是基于行的复制,由于在从服务器上应用主服务器对行的修改,而不是

从新执行SQL,只想在从服务器上执行一些触发器,以记录数据的变更,比如用于数据的抽取,单独的在从上执行一些触发器,

对某些表的变更呢,进行一些记录,以方便我们做抽取,这种情况下呢,基于SQL语句的复制,是完全可以运行的,因为它只是在

从上执行了SQL语句,同样会触发在从上的触发器,但是基于行的复制呢,方案呢就无法工作了,因为在基于行的复制中呢,

实际上并没有执行SQL语句,而是直接对数据的修改,这样是不会触发在从上的触发器的

以上介绍了两种方式的优缺点,综合考虑还是建议大家,使用基于行的复制,因为这种复制方式呢,对主从复制

的数据呢更加的有保证,现在我们已经对MYSQL的复制方式呢,有了一定的了解,在进行具体的复制配置之前,我们还要

知道MYSQL复制是如何工作的只有知道了MYSQL复制的工作原理,在复制出现问题的时候,我们才能知道如何解决这个问题