3.3 混合二进制日志记录

  当设置基于mixed格式记录日志时(默认以基于statement格式记录日志),MySQL根据下列规则确定何时使用基于row格式替换基于statement格式:

  • DML语句修改NDBCLUSTER引擎的表;
  • 函数中包括UUID();
  • 在5.1.40版本之前,超过两个表的AUTO_INCREMENT列被修改;从5.1.40版本开始,只要是AUTO_INCREMENT的列被修改,并且触发器或存储过程被调用。
  • 执行INSERT DELAYED语句;
  • 当视图主体需要基于ROW格式,比如说视图中调用了UUID()函数;
  • 调用UDF(user-defined functions);
  • 如果在基于row格式下执行语句,而且某个会话执行SQL语句后产生了临时表,则所有子查询语句全部使用基于row格式记录日志(除了产生临时表的那条),直到该会话产生的所有临时表均被删除。基于rows格式记录时不会记录临时表操作,因此,这类语句的子查询可能就存在隐患,MySQL服务处理这种状态是将该会话这期间执行的语句置为unsafe,直到该会话不再持有任何临时表。
  • 从5.1.23版本开始,当操作的语句符合下列条件时,会触发系统BUG:
    • 当使用FOUND_ROWS()/ROW_COUNT()函数时,触发BUG#12092和BUG#30244;
    • 当使用USER()/CURRENT_USER()/CURRENT_USER函数时,触发BUG#28086;
  • 从5.1.24版本开始,当执行的语句引用一个或多个系统变量时,触发BUG#31168。

  注意,下列系统变量在会话级设置后,就不会再触发日志格式切换:

    • n auto_increment_increment 
    • n auto_increment_offset 
    • n character_set_client 
    • n character_set_connection 
    • n character_set_database 
    • n character_set_server 
    • n collation_connection 
    • n collation_database 
    • n collation_server 
    • n foreign_key_checks 
    • n identity 
    • n last_insert_id 
    • n lc_time_names 
    • n pseudo_thread_id 
    • n sql_auto_is_null 
    • n time_zone 
    • n timestamp 
    • n unique_checks 
  • 从5.1.30版本开始,调用mysql中的日志表;
  • 从5.1.34版本,使用LOAD_FILE()函数,触发BUG#39701;

    提示:

    从5.1.20版本开始,基于statement格式如果执行语句时尝试使用了基于row格式记录,那么会抛出一条警告,该警告会同时抛向客户端(show warnings显示)并且记入mysqld的错误日志。

  下列罗列的常用存储引擎支持的日志记录格式:

Storage Engine

Row Logging Supported

Statement Logging Supported

ARCHIVE

Yes

Yes

BLACKHOLE

Yes

Yes

CSV

Yes

Yes

EXAMPLE

Yes

No

FEDERATED

Yes

Yes

HEAP

Yes

Yes

InnoDB

Yes

当事务级别为REPEATABLE READ or SERIALIZABLE时支持,否则不支持。

MyISAM

Yes

Yes

MERGE

Yes

Yes

NDBCLUSTER

Yes

No

  要检测当前使用的日志记录模式,需要综合考虑表执行的操作及其自身特点,基本上遵循如下规则:

  • 待操作的表能够支持基于row格式记录,但部分不支持statement格式,则这类表即为受限的row格式记录;
  • 待操作的表能够支持基于statement格式记录,但部分不支持row格式,则这类表即为受限的statement格式记录;

  一旦确定执行的语句可用的日志记录格式,则与当前binglog_format的设置做比较。下表可用于确定信息如何写入二进制日志,或者,是否抛出异常。

实际的情况

执行的操作

Safe/unsafe

binlog_format

SLR

RLR

Error/Warning

Logged as

Safe

STATEMENT

Yes

Yes

Error: not loggable

 

Safe

STATEMENT

Yes

No

 

STATEMENT

Safe

STATEMENT

No

Yes

Error: not loggable

 

Safe

STATEMENT

No

No

 

STATEMENT

Safe

MIXED

Yes

Yes

Error: not loggable

 

Safe

MIXED

Yes

No

 

STATEMENT

Safe

MIXED

No

Yes

 

ROW

Safe

MIXED

No

No

 

STATEMENT

Safe

ROW

Yes

Yes

Error: not loggable

 

Safe

ROW

Yes

No

Error: not loggable

 

Safe

ROW

No

Yes

 

ROW

Safe

ROW

No

No

 

ROW

Unsafe

STATEMENT

Yes

Yes

Error: not loggable

 

Unsafe

STATEMENT

Yes

No

Warning: unsafe

STATEMENT

Unsafe

STATEMENT

No

Yes

Error: not loggable

 

Unsafe

STATEMENT

No

No

Warning: unsafe

STATEMENT

Unsafe

MIXED

Yes

Yes

Error: not loggable

 

Unsafe

MIXED

Yes

No

Error: not loggable

 

Unsafe

MIXED

No

Yes

 

ROW

Unsafe

MIXED

No

No

 

ROW

Unsafe

ROW

Yes

Yes

Error: not loggable

 

Unsafe

ROW

Yes

No

Error: not loggable

 

Unsafe

ROW

No

Yes

 

ROW

Unsafe

ROW

No

No

 

ROW

  表中safe操作表示操作的结果是可确定(deterministic)的。SLR指的是statement-logging restricted,RLR指定的是row-logging restricted。

  如果语句中操作的一个或多个表不支持基于row格式记录则属于SLR,如果语句中操作的表中有不支持基于statement格式记录,那么该语句就属RLR。

  如果slave端服务启动时指定了--log-warnings,那么slave将输出相关信息到错误日志,记录其自身的状态,包括二进制日志及relay日志相关信息,什么时间执行的relay日志切换等等。

3.4 mysql数据库中对象修改的二进制日志记录

  mysql数据库中权限相关的表(*_privs)可以直接进行操作(比如INSERT/DELETE),也可以通过专用命令行的方式(比如GRANT/CREATE USER),在5.1.17版本中,作用于mysql数据库中表的语句符合下列规则时,也会写入二进制日志:

  • 通过DML语句直接修改mysql库中的表的记录,将以binlog_format系统变量设置的值写入二进制日志,比如像:INSERT、UPDATE、DELETE、REPLACE、DO、LOAD DATA INFILE、SELECT、TRUNCATE TABLE;
  • 非直接修改mysql库中记录的操作,则忽略binlog_format变量的设置,直接以基于statement格式记录。比如GRANT、REVOKE、SET PASSWORD、RENAME USER、CREATE(create table .. select语句不在此列)、ALTER、DROP。

  CREATE TABLE ... SELECT语句即有DDL部分,也有DML部分,其CREATE TABLE部分基于statement格式记录,而SELECT部分则基于binlog_format参数设置的值进行记录。