mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.08 sec)
我们使用pt工具pt-query-digest或者命令行,都能够分析出binlog做了哪些操作。使用pt-query-digest的话可以结合mysqlbinlog命令,对日志进行分析。 Part3:rootcase
delete from tablename where xxxx limit 100;
这种语法会将MIXED格式的binlog,转为ROW格式记录,而笔者案例中的这张表包含TEXT大字段,每次delete都会把整个TEXT大字段带入binlog,进而导致binlog激增,从库追不上主库产生延迟的情况。 Part4:解决办法
根本原因找到后,解决起来就得心应手了,找到相关开发,去掉delete from table where xxx limit 这种用法,就能够避免row格式的记录。 Warning:警告其实,delete/update limit、insert .....select limit这种用法是危险的,很容易产生问题。真的要使用这种这种方法的话,也需要结合order by语句来保证limit的有效性。
遇到此类语句时:
When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions:
When a DML statement updates an NDBCLUSTER table.
When a function contains UUID().
When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.
When any INSERT DELAYED is executed.
When a call to a UDF is involved.
If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.
This is true whether or not any temporary tables are actually logged.
Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all subsequent statements using that table are unsafe. The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables.
When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)
When a statement refers to one or more system variables. (Bug #31168)
可以看出,在官方文档中,何时MIXED格式会转换为ROW格式中,并未提到limit语句会将MIXED格式转换为ROW,国内不少书籍和博客上也未有提及,本文记录这个案例,希望对遇到这个问题和未来可能遇到这个问题的读者能够节省处理时间,尽快定位到根源。
官方文档对于MIXED格式在使用limit语法时转换为ROW格式记录在其他章节,是如下描述的:
Statement-based replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an ORDER BY clause.) When such a statement is encountered:
When using STATEMENT mode, a warning that the statement is not safe for statement-based replication is now issued.
When using STATEMENT mode, warnings are issued for DML statements containing LIMIT even when they also have an ORDER BY clause (and so are made deterministic). This is a known issue. (Bug #42851)
When using MIXED mode, the statement is now automatically replicated using row-based mode.