mysql slave seconds behind too large（Waiting for table metadata lock）

遇到zabbix监控的mysql slave 服务器报警 mysql slave seconds behind too large

马上登陆主及从，查看主从同步状态，发现好久没有更新了

进入相关库查看

show processlist

状态：Waiting for table metadata lock

对表有alter 操作，我的是从库，system user， 主从同步锁表了？？？what？

下面还有一个select， mysql为innodb引擎，select 锁表了？？？what？

百度得： 

1、alter table语句无法获取到metadata 独占锁，会进行等待。

2、未提交事物，阻塞DDL，继而阻塞所有同表的后续操作

查看未提交的事务

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx\G

果然是它，小哪吒。

kill 12008665;

kill 11981114;

再查看下，已经恢复正常，主从也正常了，alter操作也被同步完成了。

如不能这样确定，或show processlist里面没有信息，如下语句可以帮助查线程ID

SELECT distinct b.trx_id blocking_trx_id,
b.trx_mysql_thread_id 源头锁thread_id,
SUBSTRING(p. HOST, 1, INSTR(p. HOST, ':') - 1) blocking_host,
SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) blocking_port,
IF(p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx,
b.trx_query blocking_query,
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) wait_time,
r.trx_query waiting_query,
l.lock_table waiting_table_lock
FROM information_schema.INNODB_LOCKS l
LEFT JOIN information_schema.INNODB_LOCK_WAITS w
ON w.requested_lock_id = l.lock_id
LEFT JOIN information_schema.INNODB_TRX b
ON b.trx_id = w.blocking_trx_id
LEFT JOIN information_schema.INNODB_TRX r
ON r.trx_id = w.requesting_trx_id
LEFT JOIN information_schema. PROCESSLIST p
ON p.ID = b.trx_mysql_thread_id
ORDER BY wait_time DESC;

如果没有返回值 ，使用下面的sql来查：

select i.trx_mysql_thread_id
from  information_schema.innodb_trx i,
  (select 
         id, time
     from
         information_schema.processlist
     where
         time = (select 
                 max(time)
             from
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                     and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);

根据返回的结果继续查相关的session：

select * from information_schema.processlist where id=11981114;

别犹豫，kill 掉即可。

经验码住，再也不怕小哪吒闹海了。