MySQL 语句复制(SBR)的缺陷列举

作者介绍: 赵伟 腾讯TDSQL数据库开发者

MySQL (这里的MySQL是指广义的mysql,包括oracle,mysql,percona,mariadb等)的Statement Based Replication (SBR)是一个暗坑无数的功能,可能导致主备机数据不一致,以及其它问题,所以在TDSQL中我们使用RBR。这里就列举几条SBR的坑。

在此之前,先说说SBR的有点。与Row based Replication (RBR)相比,它可以避免传输大量的binlog日志从而减小网络和存储系统(binlog文件)的负载。另外,某些时候在备机上面重新执行SQL语句反而会比逐条执行RBR的binlog要快,一个极端的例子是,如果一个表没有主键,然后一个delete/update语句需要删除/更新大量的行,那么使用RBR将是噩梦,因为备机处理每一条这样的binlog都会导致全表扫描从而大大降低备机的性能(所以在TDSQL中我们默认强制创建含有主键的表)。但是使用SBR的话,一次执行即可更新/删除全部行,这样就快了很多。

下面正式开始批评SBR了。

首先,由于SBR模式下,SQL语句到了备机需要被重新执行,与RBR相比,就增加了重新执行SQL语句的额外开销,包括解析,优化和执行SQL语句。RBR则直接调用mysql的存储引擎接口(handler API)来执行行的插入、删除和更新,完全跳过了mysql的优化器的处理逻辑。

SQL语句到了备机需要被重新执行,在多种情况下就会导致主备机数据不一致,比如一个SQL语句调用了用户定义的函数,调用了返回随机值的函数,在数据表中使用了自增列,以及使用了上下文数据(context data,比如用一个表的行数作为某个插入字段值,或者在update/delete语句中使用了limit子句)等等。

另一个一致性陷阱在于使用MyISAM等非事务存储引擎。在完全使用innodb等事务存储引擎的情况下,MySQL replication是crash safe的,也就是说,无论任何时刻mysql server crash了,或者OS crash了,或者机器断电了,mysql server都可以恢复数据到crash之前的状态,确保事务的持久性和一致性,确保所有之前提交的事务的改动都存在,因为innodb自身支持事务,可以恢复,并且在近年版本的mysql和mariadb中,innodb可以使用到binlog数据来完成恢复。(尽管组提交时候并没有刷盘commit日志) 但是一旦使用了非事务存储引擎,那么一个事务的完整性就不存在了,也不是crash safe的了。在master和slave上面都是这样。虽然mysql的各个分支和版本在replication实现中做了很多努力来避免一些问题,并且给用户强制了一大堆暗坑无数的DOs&DONTs,但是无法完全解决这个问题。当发生了crash之后,只要一个事务读取 并且/或者 写入到了MyISAM表,那么这个事务的改动可能部分存在,部分消失,并且binlog与数据表也可能会有各种不同,然后,用户的数据就无法使用了。

第三,在使用innodb/xtradb并且隔离级别是read committed时候,如果设置binlog_format为statement,那么mysql会拒绝后续的插入/更新/删除操作,错误信息是:

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

但是在设置这两个变量的时候却并没有报错。这个组合也是会导致slave在并发执行时候的数据一致性问题。 所以,还是推荐大家使用RBR和INNODB(或者其他支持事务的存储引擎)的搭配,我们一直用它:)

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

扫码关注云+社区