问题描述：

收到报警，连接数激增，主从延迟增大，有大事务提醒, 然后登录上数据库查看show  processlist,发现很多处于waiting for table flush的查询的线程；

一、问题原因分析：

由于在这个slave上做了innobackupex的备份，innobackupex备份过程会执行 flush  table  with read lock！

1、由于有耗时sql，导致无法完成flush  table  with read lock

2、慢sql如下所示

SELECT count(0) FROM customer_account ca LEFT JOIN broker_relation br ON br.source_no = ca.customer_no LEFT JOIN broker_role r ON br.bind_no = r.relation_no LEFT JOIN emp e ON br.bind_no = e.code WHERE ca.is_delete = 0

驱动表 customer_no是int类型，被驱动表source_no字段是varchar类型，发生了类型转换，导致索引失效；

注意： 在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字；

3、问题现象： 当时所有涉及到customer_account、broker_relation、broker_role、emp这四张表的任何操作都会等待，处于 Waiting for table flush的状态，所以延迟也就增大了，查询也处于等待中！

4、由于mysql的连接是前一个请求得到响应后，才可以被下一个请求使用，由于 涉及到customer_account、broker_relation、broker_role、emp这四张表的任何操作都会等待中，所以这些连接一直无法被复用，所以jdbc就会频繁重建新的连接，最终导致jdbc 连接数超过最大可用值，导致无法连接数据库！

二、紧急恢复思路：

Waiting for table flush导致的问题

flush tables 无法完成会出现 waiting for table flush状态，此时这个被 flush的表，即使是执行 select 语句也会被阻塞！！！ 当然其他表还是可以正常的 select的。

注意： 一定要kill那个阻塞flush table  with read lock完成的线程，只kill 执行flush table  with read lock的线程无法解决问题！

Waiting for table flush 处理方法

出现Waiting for table flush时，需要找到那些表被lock住或那些慢查询导致flush table一直在等待而无法关闭该表。然后Kill掉对应的线程即可；

1、对于慢查询引起的其它线程处于Waiting for table flush状态的情形(比较常见的情形) 

可以查看show processlist中Time值很大的线程。然后甄别确认后Kill掉。有种规律就是这个线程的Time列值必定比被阻塞的线程要高。这个就能过滤很多记录。如下sql批量过滤查询：

select  * from  information_schema.PROCESSLIST  where   COMMAND !='Sleep'  and  TIME>600  and  STATE!='';

2、 处于等待状态的如下所示操作：

FLUSH TABLES tbl_name,ALTER TABLE,RENAME TABLE,REPAIR TABLE,ANALYZE TABLE, or OPTIMIZE TABLE.

例如alter table  处于等待状态，导致无法完成FLUSH TABLES WITH READ LOCK，这个可以通过show processlist看出来！

select  * from  information_schema.PROCESSLIST  where   COMMAND !='Sleep'  and  TIME>600  and  STATE!='';

3、对于lock table read引起的其它线程处于Waiting for table flush状态的情形： 

这种会话可能处于Sleep状态，而且它也不会出现在show engine innodb status \G命令的输出信息中。 即使show open tables where in_use >=1;

能找到是那张表被lock住了，但是无法定位到具体的线程（连接），可以通过如下sql查询出所有处于Sleep状态并且已经维持10分钟的线程信息，然后做kill，这样基本上能把有lock 的那个进程包含在内！当然也可能误kill正常的线程，需谨慎！

select  * from  information_schema.PROCESSLIST  where   COMMAND='Sleep'  and  TIME>600  and  STATE='';

三、问题善后处理：

1、把备份放到一个空闲的slave上执行，防止有大查询阻塞innobackupex执行 flush  table  with read lock；

2、优化慢sql，修改表 broker_relation的source_no字段类型为int类型；

问题延申：

一、waiting for table flush出现的原因？

Waiting for table flush

The thread is executing FLUSH TABLES and is waiting for all threads to close their tables, or the thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name,ALTER TABLE,RENAME TABLE,REPAIR TABLE,ANALYZE TABLE, or OPTIMIZE TABLE.

线程正在执行FLUSH TABLES，并等待所有线程关闭其表，或者线程收到通知，表示表的底层结构已更改，需要重新打开表才能获得新结构。但是，要重新打开表，它必须等到所有其他线程都关闭了有问题的表。

如果另一个线程对有问题的表使用了FLUSH TABLES或以下语句之一：FLUSH table tbl_name、ALTER table、RENAME table、REPAIR table、ANALYZE table或OPTIMIZE table，则会发出此提示。（lock tables  tablename命令也会阻塞FLUSH TABLES的执行），如果alter  table正在执行或者处于锁等待中，都会阻塞flush的执行！

也就是说：需要执行 flush tables 的线程，因为某些原因无法关闭表，无法完成flush tables，所以就 waiting for table flush.

二、一个dml或者select操作在执行完成后就关闭表了吗？

select会持有dml读锁；

dml会持有dml写锁；

假设一个事务没有提交；

dml 执行完，就关闭表了，但是持有的MDL写锁需要等提交后才能释放；

select执行完，就关闭表了，但是持有的MDL读锁需要等提交后才能释放；

但是此时可以正常执行FLUSH TABLES WITH READ LOCK，说明MDL表锁也不会阻塞FLUSH TABLES WITH READ LOCK的执行！正在执行的dml或者ddl是因为操作的表处于打开的状态，所以会阻塞FLUSH TABLES WITH READ LOCK的执行！

show open tables where in_use >=1; 可以判断出那个表处于打开状态,并且处于被使用中，所谓打开就是在内存中有缓存表元数据信息！

In_use:表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。这个时候是可以执行FLUSH TABLES WITH READ LOCK，因为虽然是打开状态，但是是没有使用中，可以随时被close!

三、 innobackupex物理备份的流程：

注意：mysql社区版本（8.0之前不支持该锁，8.0引进lock instance for backup 和 unlock instance 语法 ）但是Percona Server在5.6.16-64.0就实现了这一功能！

社区版本5.7的备份流程：

1.get Redo LSN

2.copy 系统表空间+事务引擎表的数据文件+后台子进程(IBACKUP)拷贝Redo

3.FLUSH TABLES WITH READ LOCK

4.copy 所有 *.frm文件，非事务引擎表(MyISAM、ARCHIVE等)数据＋索引文件

5.Get the binary log coordinates(坐标/位点)

6.finalize the background copy of REDO log

7.unlock tables;