MySQL复制模式的全面剖析

基于语句的复制：就是从库（slave）基于产生变化的SQL语句从主库(master)进行复制。在MySQL5.1.4版本之前是binlog和复制唯一支持

MySQL Replication Format

1：MySQL支持的复制的模式：

基于语句的复制（statement-based replication，简称：SBR）

基于行的复制（row-based replication， 简称：RBR）

基于混合模式的复制（mixed-based replication， 简称：MBR）

2：三种模式的简单定义：

基于语句的复制：就是从库（slave）基于产生变化的SQL语句从主库(master)进行复制。在MySQL5.1.4版本之前是binlog和复制唯一支持的模式，也是MySQL5.5中默认的格式。

基于行的复制：基于行的复制不复制SQL语句，而是将插入，删除或更新操作的各行进行复制。master的binlog记录的是各个表中行的变化。

基于混合模式的复制：它是根据事件的类型实时的改变binlog的格式。当设置为混合模式时，默认为基于语句的格式，但在特定的情况下它会自动的转变为基于行的模式。

3：基于语句的复制和基于行的复制的优缺点：

1）：基于语句的复制的优点

.久经考验，或者说是很成熟的技术，从MySQL3.23版本已经开始支持。

.更少的数据需要写入binlog文件中；当update，，insert或者delete影响很多行时，它会占用很少的存储空间，同样也意味着当从备份中恢复会更快。

.由于binlog文件记录了所有可能导致变化的语句，所以可以用它来审计数据库。

2）：基于语句的复制的缺点

.基于语句的复制的语句，数据是不安全的。不是所有可能修改数据的语句都可以通过基于语句的复制而进行安全，无误差的复制。一些不确定的因素或者说行为导致很难通过基于语句进行复制。例如，一些DML语句：

.如果语句含有UDF的调用或stored programs(stored procedures and functions,triggers and events)的调用，则无法保证slave上使用的是相同的值。由于返回的值不能保证仅仅是跟函数或存储程序的参数有关系。关于此部分请参考：

.如果update,delete或insert语句包含limit从句，则执行期间的数据库崩溃可能带来问题。由于受影响行的顺序没有定义。

.如果语句含有任何不确定的函数调用，则可能会导致Master与Slave之间产生不一致的情况。这些函数有：

LOAD_FILE(filename) :读取文件，以字符串的形式返回值。

UUID() :生成唯一值的函数,128位。

UUID_SHORT() :同UUID(),64位。

USER() :以utf8字符集返回当前连接MySQL的用户名及主机名。

FOUND_ROWS() :返回一个包含limit字句获得的查询总数。

SYSDATE() :返回动态执行的时间。

GET_LOCK(str,timeout) :获得一个以字符串命名的锁，且有超时时间。

IS_USED_LOCK(str) :检测str命名的锁是否在使用中。

IS_FREE_LOCK(str) :检测str命名的锁是否没在使用。

MASTER_POS_WAIT() :控制master/slave的同步。

RAND() :实现随机数的方法。

RELEASE_LOCK() :用来释放因GET_LOCK()获取锁的str。

SLEEP() :实现延迟动作的时间的方法。

VERSION() :返回MySQL版本信息的函数。

特别说明：

关于sysdate(),如果master,salve启动时加上--sysdate-is-now 参数，就不会影响复制了。

.Insert ..... Select语句当其需要大量的行级锁时。

.Update语句当其进行全表扫描时(由于没有索引或者合适的索引)。

.For InnoDB,一个带有AUTO_INCREMENT的Insert语句。

.对于很复杂的语句，当在Slave上执行前还需进行解析，优化。而基于行模式，仅仅是修改影响的行

.Master,Slave表结构必须几乎一样。

.如果在从库上执行复杂的语句出现了错误，很可能将影响到Slave上改变的行数。

3）：基于行复制的优点

.所有的改变都可复制，这是最安全的复制模式。

.基于行复制的实现的技术和其它大多数的数据库是一样的，了解了其它的数据库系统，就相当于了解了它的实现。

.在Master上需要的行锁会很少，从而获得更高的并发性，如下几种语句：

. Insert ...... Select

. 带有AUTO_INCREMENT字段的Insert。

. 带有Where子句且没有使用索引或改变很少满足条件的Update或delete语句。

.Slave上执行Insert,Update,Delete语句时锁更少了。

4）：基于行复制的缺点

.binlog需记录更多的数据，意味着从备份中回滚时需要更多的时间；此外如果遇到改变很多行时，所有改变都会写入binlog中，而基于语句的复制只会写一次，这会导致频繁发生binlog的并发写问题

.UDFs产生的大的BLOB值会导致复制变慢。

.不能够从binlog中看到执行过具体那些语句，同样也不能够查看Slave正在执行那些从Master上得到的语句。

.对MyISAM表并发插入时，不支持基于行的模式。

特别说明：

基于行复制，如果想看binlog那些数据改变了，使用mysqlbinlog加上--base64-output=DECODE-ROWS 和--verbose参数。

4：基于语句，基于行的复制的选择性

.语句是否更新大量的行，还是通常只改变或插入少量行？

如果语句改变大量的行，基于语句的复制执行更快。但是由于语句也在Slave上执行，所以并不总是这样。如果语句的优化和执行计划很复杂，这时可能基于行的复制，因为寻找行的逻辑快的多。

如果语句只改变或插入少量行，则基于行的复制更快，因为不需要解析，所有的处理都直接交给存储引擎。

.是否需要知道执行了那些语句?至少可以说，基于行的复制的事件处理很难解码。而基于语句的复制， 被写入二进制日志中，因此可以直接读取。

.基于语句的复制的复制模型很简单：只要在Slave上执行相同的语句即可。这种技术应用已久，很多 DBA对其熟悉。而基于行的复制相对较新，如果复制过程出现故障，可能难以解决。

.如果Master和Slave上数据不同，执行语句的结果也会不同。有时是故意的(这时应该使用基于语句的复制)，但有时是无意的，可以通过基于行的复制来避免这一情况。

5：基于混合模式的复制

混合模式复制背后的原理很简单：正常情况下使用基于语句的复制，对于不安全的语句切换为基于行的复制。当出现以下情况时，混合模式需要切换到基于行的复制：

.该语句调用了：

UUID函数；

用户自定义函数(UDFs);

CURRENT_USER 或USER函数；

LOAD_FILE函数。

.同一个语句更改了两张或更多包含AUTO_INCREMENT列的表。

.语句中使用了服务器变量。

.存储引擎不允许使用基于语句复制，如：MySQL Cluster引擎。

6：关于binlog_format的简介

Variable Name binlog_format

Variable Scope Global,Session

Dynamic Variable Yes

Permitted Values STATEMENT,ROW,MIXED

特别注意: