mysql 各種binlog_format優缺點比較

关注
mysql 各種binlog_format優缺點比較www.shan-machinery.com

http://homeserver.com.tw/mysql/mysql-%E5%90%84%E7%A8%AEbinlog_format%E5%84%AA%E7%BC%BA%E9%BB%9E%E6%AF%94%E8%BC%83/

MySQL 5.1 中,在复制(Replication)方面的改进就是引进了新的复制技术:基於行的复制。 简言之,这种新技术就是关注表中发生变化的记录,而非以前的照抄 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. 啟用了NDB3. 当前会话试用 RBR 模式,并且已打开了临时表如果binlog採用了 MIXED 模式,那麼在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式。1. 当DML语句更新一个NDB表时2. 当函数中包含 UUID() 时3. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时4. 行任何 INSERT DELAYED 语句时5. 用 UDF 时6. 视图中必须要求使用 RBR 时,例如创建视图是使用了 UUID() 函数设定主从复制(Replication)模式的方法非常简单,只要在以前设定复製配置的基础上,再加一个参数: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’;

PS: SET SESSION binlog_format = ‘STATEMENT’;SET GLOBAL binlog_format = ‘STATEMENT’; 这样用现在来比较以下 SBR 和 RBR 2中模式各自的优缺点 SBR 的优点:1. 歷史悠久,技术成熟2. binlog文件较小3. binlog中包含了所有资料库更改信息,可以据此来审核资料库的安全等情况4. binlog可以用於实时的还原,而不仅仅用於复制(Replication)5. 主从版本可以不一样,从服务器版本可以比主服务器版本高SBR 的缺点:1. 不是所有的UPDATE语句都能被复製,尤其是包含不确定操作的时候。2. 调用具有不确定因素的 UDF 时复制(Replication)也可能出问题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 模式后,能解决很多原先出现的主键重复问题

https://www.shan-machinery.com