3.1 认识二进制日志格式
二进制日志在记录时支持以不同的格式记录,mysql提供了三种日志格式:
MySQL的复制特性最初就是基于传播master端的SQL语句到slave端执行,这种方式即被称为"基于语句记录"(statement-based logging, SBL)。要使用这种日志记录方式,设置系统变量binlog-format=statement。
进入5.1.5版本后,MySQL提供了新的日志记录格式,"基于行记录"(row-based logging),master写二进制日志时,将明确记录表的每行记录发生的变化。使用这种日志记录方式,设置系统变量binlog-format=row。
第三种方式即"混合记录"(mixed logging)方式。这种方式下,默认情况下基于语句记录日志,特殊情况下也能自动切换成基于行记录。使用这种日志记录方式,设置系统变量binlog-format=mixed。
从5.1.8版本起才支持混合记录模式,在5.1.12版本时,这一模式被改为默认模式,到了5.1.29版本时,默认模式又被改回了基于语句格式。
从5.1.20版本开始,日志记录格式视所使用的存储引擎,同样可以(set)集合的方式或有限方式,这将有助于消除当复制环境中master和slave执行语句分处不同存储引擎的情况。
对于基于语句记录的复制,可能会遇到复制nondeterministic的语句。决定基于语句复制方式获取的语句是否安全,mysql确定它是否能够确保该语句以基于语句记录方式可被复制,如果mysql不能确定的话,那么会将该语句标记为潜在不可信,并抛出一条警告:Statement may not be safe to log in statement format. 对于这种情况,可以通过使用基于行记录方式替代。
3.2 设置二进制日志格式
设置二进制日志记录的格式,可以在启动MySQL服务时指定--binlog-format=[type]的方式设置,type有三个可选值:
STATEMENT:基于语句方式记录;
ROW:基于行方式记录;
MIXED:基于混合方式记录。
默认的记录方式在不同版本中也不相同,在5.1.12到5.1.28版本时默认使用mixed方式,其它版本默认使用statement方式。
提示:
对于MySQL Cluster发行版,使用NDCLUSTER存储引擎6.1及之后的版本(即使运行于mysql 5.1.29版本),默认二进制记录格式是mixed。
日志记录格式可以在线实时修改,全局方式修改的话可以通过修改系统变量binlog_format,例如:
mysql> SET GLOBAL binlog_format = ¨STATEMENT¨;
mysql> SET GLOBAL binlog_format = ¨ROW¨;
mysql> SET GLOBAL binlog_format = ¨MIXED¨;
会话级也可以进行修改,操作如下:
mysql> SET SESSION binlog_format = ¨STATEMENT¨;
mysql> SET SESSION binlog_format = ¨ROW¨;
mysql> SET SESSION binlog_format = ¨MIXED¨;
需要注意,修改binlog_format,不管是全局还是会话级,都需要操作的用户拥有SUPER权限。
下列情况下可能会需要在会话级设置日志记录格式:
会话执行的操作是进行大量细节的修改(many small changes),因此希望基于row格式记录;
会话执行的UPDATE语句where子句能够匹配大量记录,这种情况下使用基于statement格式记录日志会比基于row格式更有效率。
Master执行的部分语句需要耗费大量时间,但实际被修改的列很少,这种情况下使用基于row格式记录更合适。
下列情况下,不要在运行时修改日志记录格式,否则可能导致错误:
在存储过程或触发器中;
启用了NDBCLUSTER存储引擎;
当前会话以row格式记录,并且打开了临时表;
当存在临时表时,不建议修改日志格式,因为只有基于statement格式才会将临时表写入日志,而基于row格式记录时不会记录。当使用mixed格式时,临时表通常也会被记录,除非使用了UDFs(user-defined functions)或UUID()函数。
即使是设置了基于row格式记录日志,也有可能仍然存在实际基于statement格式记录日志的情况,比如说执行DDL语句类似CREATE TABLE/ALTER TABLE/DROP TABLE等。
在master端手动修改日志记录格式,salve端多数情况下能够自动适应,比如说master端以statement或mixed格式记录日志,在遇到以row格式记录的情况时,slave会临时切换到基于row的复制,而后再切换回初始的日志格式。
--binlog-row-event-max-size选项为基于row格式记录的专用参数,被修改的记录批量写入二进制日志,上述参数的作用是设置批量写缓存区最大值。该参数值设置时必须为256的整倍数,默认值为1024。
提示:
在复制环境中使用基于statement格式,可能会导致master端和slave端修改的数据不一样。