MySQL的Warning之「BINLOG_FORMAT = STATEMENT」


=Start=

缘由:

Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

参考解答:
方法1:修改binlog_format格式为mixed

在配置文件中设定主从复制模式(需重启生效):

log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"

也可以在运行时动态修改binlog的格式。例如:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
方法2:不要使用这类SQL

其中,使用第1种方法,需要注意下:

如果是 master->slave 结构的数据库架构。并且 slave上 开启了 log_slave_updates 。那么在master上修改完binlog格式,开启了log_slave_updates的从库,会中断同步。所以,需要先在 slave 上,设置 binlog_format=mixed ,之后再在master上设置。

参考链接:

=END=

,

《“MySQL的Warning之「BINLOG_FORMAT = STATEMENT」”》 有 1 条评论

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注