捉虫日记 | MySQL 8.0从库某些情况下记录重放的CREATE TABLE、DROP...

发布网友发布时间：2024-10-09 10:57

共1个回答

热心网友时间：2024-10-13 21:34

当主从复制采用 binlog 的行模式时，如果从库启用 slow_query_log、log_slow_replica_statements 且从库重放 CREATE TABLE、DROP TABLE 时因特殊情况（比如被从库其他 SQL 占用 MDL 锁）执行耗时较长，会被从库记录到慢日志（slow log），而 ALTER TABLE 却不会被记录到慢日志。

ALTER TABLE 等管理语句是否会记录到慢日志，受参数 slow_query_log、log_slow_admin_statements 控制。

本文基于 MySQL 8.0.30 版本。

搭建主从复制，主（master）、从（replica）my.cnf 中启用 binlog 的行模式，然后从库动态设置配置参数，主库执行 SQL 语句，查看从库慢日志，查看从库slow_query_log_file参数指定的慢日志文件，其中出现 DROP TABLE 语句。

这与官方对 log_slow_replica_statements 参数的描述不符，官方描述为：When the slow query log is enabled, log_slow_replica_statements enables logging for queries that have taken more than long_query_time seconds to execute on the replica. Note that if row-based replication is in use ( binlog_format=ROW), log_slow_replica_statements has no effect. Queries are only added to the replica's slow query log when they are logged in statement format in the binary log, that is, when binlog_format=STATEMENT is set, or when binlog_format=MIXED is set and the statement is logged in statement format. Slow queries that are logged in row format when binlog_format=MIXED is set, or that are logged when binlog_format=ROW is set, are not added to the replica's slow query log, even if log_slow_replica_statements is enabled.

Setting log_slow_replica_statements has no immediate effect. The state of the variable applies on all subsequent START REPLICA statements. Also note that the global setting for long_query_time applies for the lifetime of the SQL thread. If you change that setting, you must stop and restart the replication SQL thread to implement the change there (for example, by issuing STOP REPLICA and START REPLICA statements with the SQL_THREAD option).

按照官方的描述，在 binlog_format 是行模式的情况下，即使启用 log_slow_replica_statements 参数，从库重放时也不该产生慢日志。

按照上述同样的步骤执行 ALTER TABLE 语句，则不会记录到 slow log。

通过阅读手册及自行验证，ALTER TABLE 等管理语句是否记录到从库的 slow log 受参数log_slow_admin_statements控制。

log_slow_admin_statements Include slow administrative statements in the statements written to the slow query log. Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

最终会调用Query_logger::slow_log_write函数。

我查看了 8.0.31-8.0.35 版本的 change log，其中并无对DROP TABLE相关的 Bug Fix，说明该问题官方尚未修复。

公司同事向官方提交了 BUG，官方已经确认，其中的 patch 采用的思路 1：MySQL Bugs: #113251: the slow log in slave is logged ,when binlog_format is row