mysql客户端加锁后宕机_记录MySQL因Waiting for query cache lock导致业务宕机的处理

早上10点，业务反馈服务器502无法访问，查询所有的WEB服务器、缓存、数据库都是可以连接的。但是MySQL查询一致卡住，为尽快恢复业务，直接重启数据库操作，此时业务恢复。

开始找原因:

1.查看mysql错误日志,都是这样的。

2020-07-08 10:10:14 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7

2020-07-08 10:10:14 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply

2020-07-08 10:10:14 0 [Note] InnoDB: FTS optimize thread exiting.

2020-07-08 10:10:16 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7

2020-07-08 10:10:16 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply

2020-07-08 10:10:18 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7

2020-07-08 10:10:18 0 [Note] Event Scheduler: Waiting for the scheduler thread to reply

2020-07-08 10:10:20 0 [Note] Event Scheduler: Killing the scheduler thread, thread id 7

经过分析不是以上引起的

到了12点的时候又出现问题了，这次在重启前查询show processlist并且导出了文件

mysql -uroot -pxxxxx -N -e "show processlist" >/tmp/sp.log

分析导出的文件

2 system user NULL Daemon NULL InnoDB purge coordinator NULL 0.000

3 system user NULL Daemon NULL InnoDB purge worker NULL 0.000

1 system user NULL Daemon NULL InnoDB purge worker NULL 0.000

4 system user NULL Daemon NULL InnoDB purge worker NULL 0.000

5 system user NULL Daemon NULL InnoDB shutdown handler NULL 0.000

7 event_schbseuler localhost NULL Daemon 6470 Waiting for next activation NULL 0.000

94 replication 192.168.0.174:39478 NULL Binlog Dump 6453 Master has sent all binlog to slave; waiting for binlog