MySQL BINLOG

好久没有查看MySQL错误日志文件了，今天一看Warning级别的错误，在不断的被写入到错误日志文件里。在这里简单总结了一下缘由及对应方法。

使用的MySQL版本为5.6.20，其前提条件如下。

• 开启二进制日志功能
• 没有开启MySQL同步功能
• 使用触发器(Trigger)功能

查看当前使用的MySQL版本。

mysql> select version;
+------------+
| version  |
+------------+
| 5.6.20-log |
+------------+

MySQL错误日志文件内容

MySQL错误日志文件里出现大量的如下错误。

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: insert into gameuserrole...

错误缘由

跟错误内容里提示的一样，对设有AUTO_INCREMENT等触发的列，当BINLOG_FORMAT为STATEMENT时，MySQL判定写入二进制日志里是不安全的。

这是由于MySQL 5.1.12后来版本里，虽然MySQL同步使用的是MIXED(默认)，但是二进制日志使用的是STATEMENT(默认)。

确认当前BINLOG_FORMAT设定。

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

对应方法

BINLOG_FORMAT可设定的值，如下。

• STATEMENT(或数字1) : 记录实际执行的SQL语句
• ROW(或数字2) : 记录实际被修改行的数据
• MIXED(或数字3) : STATEMENT和ROW的混合型，列如使用一些比较危险的函数时会用ROW记录，其他时候用STATEMENT记录

我们在这里把BINLOG_FORMAT设定改为MIXED，其修改方法有以下2种。

• 直接在my.cnf文件添加binlog_format = MIXED后重启MySQL。
• 目前无法重启数据库时，可以使用SET GLOBAL命令把BINLOG_FORMAT从STATEMENT修改为MIXED，但是MySQL重启后设定将被无效化。

使用SET GLOBAL命令修改的方法如下，而修改my.cnf的方法在这里不多做介绍了。

mysql> SET GLOBAL binlog_format = 'MIXED';
Query OK, 0 rows affected (0.00 sec)

以防万一，再次查看BINLOG_FORMAT设定

mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

最后查看MySQL错误日志文件里没有出现该错误，就可以了。