Binlog日志的三种模式介绍及模式修改

Binlog日志的三种模式介绍

 

一、Binlog日志的三种模式

 

1.1 Statement Level模式

 

每一条修改数据的sql都会记录到master的bin_log中，slave在复制的时候sql进程会解析成master端执行过的相同的sql在slave库上再次执行。

 

优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行的变化，较少bin-log日志量，节约IO，提高性能。因为它只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文信息。

 

缺点：由于它是记录执行语句，所以，为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，来保证所有语句在slave端能够得到和在master端相同的执行结果。由于mysql更新较快，使mysql的赋值遇到了不小的挑战，自然赋值的时候就会涉及到越复杂的内容，bug也就容易出现。在statement level下，目前就已经发现了不少情况会造成mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现。比如：sleep（）函数在有些版本中就不能正确赋值，在存储过程中使用了last_insert_id（）函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行记录的裱花，所以不会出现类似的问题。

 

总结：

Statement level优点：

1、解决了row level的缺点，不需要记录每一行的变化。

2、日志量少，节约IO，从库应用日志块。

Statement level缺点：一些新功能同步可能会有障碍,比如函数、触发器等。

 

1.2 Row Level模式

 

日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改。

 

优点：在row level的模式下，bin_log中可以不记录执行的sql语句的上下文信息，仅仅只需要记录哪一条记录被修改，修改成什么样。所以row level的日志内容会非常清楚的记录每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或fuction，以及trigger的调用或处罚无法被正确复制的问题。

 

缺点：row level模式下，所有的执行语句都会记录到日志中，同时都会以每行记录修改的来记录，这样可能会产生大量的日志内容。

 

总结：

row level的优点：

1、记录详细

2、解决statement level模式无法解决的复制问题。

row level的缺点：日志量大，因为是按行来拆分。

 

1.3 Mixed模式（混合模式）

 

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。

 

新版本中的mysql中对row level模式也做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

 

二、调整binlog日志模式

 

binlog日志默认模式：

Server version: 5.6.17 Source distribution

mysql> show variables like "%binlog_format%";

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

 

方法一：在线修改立即生效

mysql> set global binlog_format='MIXED';

Query OK, 0 rows affected (0.00 sec)

 

退出mysql，查看当前mysql日志模式

 

mysql> show variables like "%binlog_format%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

 

方法二：在配置文件中参数如下：

[mysqld]

log-bin=/var/lib/mysql/mysql-bin

#binlog_format="ROW"

binlog_format="MIXED"   #开启MIXED模式

#binlog_format="STATEMENT"

 

修改后重启mysql服务日志模式：

mysql> show variables like "%binlog_format%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

 

三、在日志模式当前为row的模式下，记录日志的形式内容。

mysql> show variables like "%binlog_format%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW   |

+---------------+-------+

1 row in set (0.00 sec)

 

[root@i-3vsptbun mysql]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000011 小结：执行sql部分的sql显示为base64编码格式 #171126 17:35:53 server id 1  end_log_pos 458 Table_map: `boy`.`tomcat1` mapped to number 15 #171126 17:35:53 server id 1  end_log_pos 571 Delete_rows: table id 15 flags: STMT_END_F ### DELETE FROM `boy`.`tomcat1` ### WHERE ###   @1=11 ###   @2='huang1' ### DELETE FROM `boy`.`tomcat1` ### WHERE ###   @1=2 ###   @2='huang2' ### DELETE FROM `boy`.`tomcat1` ### WHERE ###   @1=3 ###   @2='huang3' ###   @2='zhang7' # at 571 # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;