为什么MySQL binlog_format一定要设置成row

作者: Huang Jinqiang 分类: MySQL 发布时间: 2017-05-28 16:03

我们知道statement格式的二进制日志记录的是实际的SQL语句, 其特点是binlog文件较小,由于日志记录了原始的SQL,所以也方便统计和审计,相对来说也比较方便DBA阅读;其缺点也存在安全隐患,可能会导致主从数据不一致,原因在于对一些系统函数不能准确复制或不能复制,如now()、uuid()、user()、load_file()等。

而row格式记录的实际数据的变更,解决了statement格式的缺点问题,其缺点是binlog文件较大,在复制中占用较大的网络IO和磁盘IO,不过在今天SSD和PCIe卡及万兆网络普及和数据安全面前,这些都是可以接受的。

说到binlog_format,当然少不了mixed格式。在mixed格式的二进制日志中,默认采用statement格式记录,在以下6种情况会转化成row格式:
(1) NDB存储引擎的DML语句;
(2) uuid()函数;
(3) 自增字段被更新;
(4) insert delayed语句;
(5) 使用了UDF自定义函数;
(6) 使用了临时表;

但是mixed格式依然存储数据不一致性的问题,请看例子,测试环境binlog_format=mixed;transaction_isolation=repeatable-read
master上t1表有两条数据
root@localhost:mysql_3306.sock [db1]> select * from t1;
+----+--------------------------------------+
| id | n |
+----+--------------------------------------+
| 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 |
| 2 | ddd |
+----+--------------------------------------+
2 rows in set (0.00 sec)

slave上有一条数据
root@localhost:mysql_3306.sock [db1]> select * from t1;
+----+--------------------------------------+
| id | n |
+----+--------------------------------------+
| 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 |
+----+--------------------------------------+
1 row in set (0.00 sec)

当在master上更新一条从库不存在的记录时,主库是可以执行成功的
root@localhost:mysql_3306.sock [db1]> update t1 set n='eee' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

root@localhost:mysql_3306.sock [db1]> select * from t1;
+----+--------------------------------------+
| id | n |
+----+--------------------------------------+
| 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 |
| 2 | eee |
+----+--------------------------------------+
2 rows in set (0.00 sec)

slave上执行show slave status输出,发现没有异常
root@localhost:mysql_3306.sock [db1]> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
root@localhost:mysql_3306.sock [db1]> select * from t1;
+----+--------------------------------------+
| id | n |
+----+--------------------------------------+
| 1 | d24c2c7e-430b-11e7-bf1b-00155d016710 |
+----+--------------------------------------+
1 row in set (0.00 sec)

当然啦,上面这种情况是本身主从数据就不一致了,如果是row格式将会报1062错误。

总结:
MySQL二进制日志格式都配置成Row格式,以保证主库的变更能准确地在从库上重放,确保数据安全以及数据的一致性;此外要定期检查主从复制的数据一致性!

PS: 本方考参资料<MySQL管理之道:性能调优、高可用与监控(第2版)>

2条评论
  • 狗子

    2017年6月6日 17:09

    我一般会在/etc/my.cnf 里面再加上一行 binlog_rows_query_log_events=ON 这样就能在row格式下记录下具体的执行的SQL语句了

    1. Huang Jinqiang

      2017年6月6日 23:15

      如果审计和统计分析需要,我觉得把这个参数开启也是挺方便实现这种需求的

发表评论

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