记录一次mysql元数据锁处理

在文章最开始，大家可以回答一个问题：什么原因会造成MySQL的元数据锁？
Waiting for table metadata lock

• 创建，删除索引
• 修改表结构(alter)
• 表维护操作（optimize table、repair table 等）
• 删除表

还有其他的场景吗？其实大家遇到这种锁，估计第一感觉就是去分析processlist 里面是否有如上修改表结构语句。
但是今天我遇到一个没有修改表结构 也出现元数据锁的情况了。

背景：

• 问题
  开发 反应每天8点31 应用连接数据库总出问题，对应数据库的err.log记录为：
  2020-12-14T12:16:19.437737+08:00 237153 [Note] Aborted connection 237153 to db: 'db_name' user: 'user_name' host: '171.245.49.22' (Got timeout reading communication packets)
• 数据库环境
  mysql5.7.32 ，双主，autocommit=off iso为READ-COMMITTED (从Oracle转到MySQL的)

处理方法：

1. 增加监控信息
   每5分钟监控数据库的连接数和processlist的连接信息，发现8点31分钟出现元数据锁信息
   2.获取修改表结构或者其他导致元数据锁的相关信息
   在31分钟搜索alter 或者drop ,truncate 等等关键字，根本找不到，此时依然感觉元数据锁是修改表结构导致的。
   3.增加更加详细的监控：
   select * from sys.schema_table_lock_waits
select * from performance_schema.metadata_locks
   每5分钟对这两个表进行监控输出日志，但是没有获取到有效信息，sql字段提示信息就一个commit 根本无法分析造成元数据锁的原因。
   4.在8点29-33分钟，短暂开启详细日志， set global general_log=on;
   5.在在processlist中看元数据锁等待的sql 在里面获取到表名， 在general_log grep 表名。
   6.查看到lock tablessql如下：
   2020-12-15T08:30:01.473435+08:00 351856 Query LOCK TABLEStable_name1READ /*!32311 LOCAL */,table_name2READ /*!32311 LOCAL */, ...............
   7.联系上下文，可以看到该连接来源2020-12-15T08:30:01.469776+08:00 351856 Connect user_name@171.245.47.224 on using TCP/IP
   8.由于是每天早晨8点30会被阻塞，所以考虑应该是一个定时任务，所以到上述连接的ip主机下，查看所有的用户的crontab 在8点30的任务。
   9.查找到真的有人在mysqldump 备份数据库，但是没有加这2个参数：--lock-tables=false --single-transaction=true

结果：把8点30 crontab的定时备份脚本删除。

回到文章开头，元数据锁不一定是在修改表结构的时候才会触发，假如给表增加读锁后，没有及时释放掉(unlock tables)，也会触发元数据锁。