MySQL主从复制延迟是否由从库事务锁等待引起如何排查-mysql教程-PHP中文网

这不是主从同步问题，而是从库ddl被阻塞导致sql线程卡在“waiting for table metadata lock”；需查performance_schema.metadata_locks定位pending锁及持锁会话，kill对应thread_id释放锁，并禁止从库执行flush tables with read lock、备份须用--single-transaction和--skip-lock-tables。

mysql主从复制延迟是否由从库事务锁等待引起如何排查

从库SQL线程卡在Waiting for table metadata lock怎么办

这是最典型的“无负载型延迟”：Seconds_Behind_Master持续上涨，但CPU、IO使用率都很低，SHOW PROCESSLIST里SQL_THREAD状态长期停在Waiting for table metadata lock。根本原因不是慢，而是被锁住——常见于从库上存在未结束的mysqldump、手动执行的FLUSH TABLES WITH READ LOCK，或备份脚本残留的MDL持有。

确认方法：
在从库执行：

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA NOT IN ('performance_schema', 'mysql') AND LOCK_STATUS = 'PENDING';

重点看OWNER_THREAD_ID是否匹配SQL_THREAD的线程ID（可通过SHOW PROCESSLIST中system user那行的ID列确认）。

临时解法：查出持有锁的THREAD_ID，执行KILL <thread_id></thread_id>释放锁（但务必先查清来源，避免误杀业务连接）
预防要点：从库禁止任何FLUSH TABLES WITH READ LOCK；备份必须用--single-transaction + --skip-lock-tables
注意：performance_schema.metadata_locks需提前开启（performance_schema=ON且metadata_locks=ON）

从库INNODB_TRX里出现大事务RUNNING且trx_rows_modified超10万行

从库SQL线程是单线程回放，一旦遇到主库发来的大事务（比如批量分表插入、历史数据迁移），它就必须完整执行完才能处理后续事件。这时Seconds_Behind_Master会指数级增长，而SHOW PROCESSLIST里SQL_THREAD状态可能是Updating或inserting，Time列数值持续上升（>30秒甚至数小时）。

定位命令：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

SELECT trx_id, trx_state, trx_started, trx_rows_modified, trx_operation_state, trx_tables_locked FROM INFORMATION_SCHEMA.INNODB_TRX ORDER BY trx_started LIMIT 5;

关键判断点：trx_state = 'RUNNING'（非LOCK WAIT）、trx_rows_modified > 100000、trx_operation_state为inserting/updating、trx_started时间远早于当前时间
这类事务往往对应主库的INSERT ... SELECT、ALTER TABLE ... ENGINE=InnoDB或分表写入逻辑
不能只看Seconds_Behind_Master——它可能在事务开始时就“冻结”了，直到事务提交才更新，中间完全失真

UPDATE/DELETE无主键表导致SQL线程反复重试行锁

当binlog_format = ROW且目标表缺失主键或唯一索引时，从库回放UPDATE或DELETE事件无法精确定位行，只能退化为全表扫描 + 行比对。这极易与从库上其他写入事务发生行级冲突，表现为SQL_THREAD状态在Updating和Locked之间反复切换，SHOW ENGINE INNODB STATUS中可见大量lock_wait记录，集中在同一张表。

验证是否存在无主键表：

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema') AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME IN ('PRIMARY', 'UNIQUE'));

修复优先级：给表补主键（哪怕加个id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY）
临时缓解：调大slave_rows_search_algorithms（如设为HASH_SCAN,INDEX_SCAN），但仅对ROW格式生效，且不解决根本问题
注意：slave_rows_search_algorithms在MySQL 5.6+才支持，旧版本只能靠补主键

Relay_Master_Log_File落后多个binlog文件但SQL_THREAD状态正常

如果SHOW SLAVE STATUS\G显示Relay_Master_Log_File是mysql-bin.003731，而主库SHOW MASTER STATUS已到mysql-bin.003735，说明从库至少落后4个binlog文件——这种差距不可能由网络传输或IO线程造成（因为Slave_IO_Running = Yes），根因一定在SQL线程回放层，且大概率是锁等待或大事务。

此时Seconds_Behind_Master可能显示为NULL或一个不合理的值（比如0），因为它依赖事件时间戳，而大事务或锁等待会让这个计算彻底失效。