mysql-binlog-format三种复制模式

December 22, 2011

复制参数binlogformat
MySQL 5.1 中，在复制方面的改进即便引进了新的复制技巧：基于行的复制。简言之，这种新技巧即便关怀表中发生改变的登记，而非过去的照抄 binlog 形式。从 MySQL 5.1.12 开始，能够用以下三种形式来告终：

基于SQL语句的复制(statement-based replication, SBR)，
    基于行的复制(row-based replication, RBR)，
    混杂形式复制(mixed-based replication, MBR)。

相应地，binlog的款式也有三种：STATEMENT，ROW，MIXED。MBR 形式中，SBR 形式是默认的。

在运行时能够动态低改换binlog的款式，除非以下几种情形：
1. 存储过程可能引发器其中
2. 启用了NDB
3. 目前会话试用 RBR 形式，并且已敞开了临时表
万一binlog批准了 MIXED 形式，那么在以下几种情形下会积极将binlog的形式由 SBR 形式改成 RBR 形式。
1. 当DML语句更新一个NDB表时
2. 当函数中包括 UUID() 时
3. 2个及以上包括 AUTO_INCREMENT 字段的表被更新时
4. 行任何 INSERT DELAYED 语句时
5. 用 UDF 时
6. 视图中定然要求利用 RBR 时，例如创立视图是利用了 UUID() 函数
设定主从复制形式的措施极其容易，凡是在过去设定复制搭配的基础上，再加一个参数：
binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
当然了，也能够在运行时动态修正binlog的款式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
目前来比拟以下 SBR 和 RBR 2中形式各自的优缺点
SBR 的优点：
1. 历史悠久，技巧成熟
2. binlog文件较小
3. binlog中包括了所有数据库改动消息，能够据此来核实数据库的平安等情形
4. binlog能够用于实时的还原，而不但仅用于复制
5. 主从版本能够不一样，从服务器版本能够比主服务器版本高
SBR 的缺点：
1. 不是所有的UPDATE语句都能被复制，尤其是包括不确定垄断的时候。
2. 调用具有不确定因素的 UDF 时复制也可能出问题
3. 利用以下函数的语句也无法被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启用时启用了 --sysdate-is-now 选项)
4. INSERT ... SELECT 会发生比 RBR 更多的行级锁
5. 复制必需举行全表扫描(WHERE 语句中没利于用到索引)的 UPDATE 时，必需比 RBR 哀求更多的行级锁
6. 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
7. 对于一些混杂的语句，在从服务器上的耗资源情形会更严重，而 RBR 形式下，只会对那个发生改变的登记发生波及
8. 存储函数(不是存储过程)在被调用的同时也会厉行顺次 NOW() 函数，这个能够说是坏事也可能是好事
9. 确定了的 UDF 也必需在从服务器上厉行
10. 数据表定然几乎和主服务器坚持统一才行，否则可能会导致复制出错
11. 厉行混杂语句万一出错的话，会花费更多资源
RBR 的优点：
1. 任何情形都能够被复制，这对复制来说是最平安可靠的
2. 和其他大多数数据库系统的复制技巧一样
3. 多数情形下，从服务器上的表万一有主键的话，复制就会快了许多
4. 复制以下几种语句时的行锁更少：
* INSERT ... SELECT
* 包括 AUTO_INCREMENT 字段的 INSERT
* 未曾附带条件可能并未曾修正许多登记的 UPDATE 或 DELETE 语句
5. 厉行 INSERT，UPDATE，DELETE 语句时锁更少
6. 从服务器上批准多线程来厉行复制成为可能
RBR 的缺点：
1. binlog 大了许多
2. 混杂的回滚时 binlog 中会包括许多的数据
3. 主服务器上厉行 UPDATE 语句时，所有发生改变的登记都会写到 binlog 中，而 SBR 只会写顺次，这会导致频繁发生

binlog 的并发写问题
4. UDF 发生的大 BLOB 值会导致复制变慢
5. 无法从 binlog 中看到都复制了写什么语句
6. 当在非事务表上厉行一段堆积的SQL语句时，良好批准 SBR 形式，否则很轻率导致主从服务器的数据不统一情形发生
另外，针对系统库 mysql 里面的表发生改变时的处理法定如下：
1. 万一是批准 INSERT，UPDATE，DELETE 直接垄断表的情形，则日志款式依据 binlog_format 的设定而登记
2. 万一是批准 GRANT，REVOKE，SET PASSWORD 等管教语句来做的话，那么无论如何都批准 SBR 形式登记
注：批准 RBR 形式后，能处理许多本来揭示的主键重复问题例如Pro Publica，SunlightFoundation和维基解密，开始添补鞭策媒体滑坡留下的空白

添加新评论