又是一个线下库查询不出数据，这个库主要是提供给大数据用的一个从库，做些线下数据分析。直接把SQL要了过来，按正常流程，先看下表结构。use dbname时，卡住了，没关系，这里是由于预加载元数据导致的，连接的时候加上-A参数就好了。于是加上-A参数，开始我的show create table t1;怎么还卡？这不对啊，于是我重新开了个SEESION,show processlist看了下什么都明白了。 #### mysql> show processlist; +---------+--------------+---------------------+---------------+------------+--------+------------------------------------------------------------------- ----------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+--------------+---------------------+---------------+------------+--------+------------------------------------------------------------------- ----------+------------------------------------------------------------------------------------------------------+ | 1482342 | barrage | 10.1.100.80:17048 | barrage_hisbk | Query | 42425 | Sending data | SELECT DISTINCT(uuid) AS uuid FROM barrage_hisbk. barrage_audit | | 1484884 | root | 10.1.5.41:8833 | NULL | Query | 41333 | Waiting for table flush | FLUSH /*!40101 LOCAL */ TABLES | | 1489767 | barrage | 10.1.100.80:17988 | barrage_hisbk | Query | 39239 | Waiting for table flush | SELECT uuid, FROM_UNIXTIME(MAX(create_time)) AS last_publish_time FROM barrage_hisbk. barrage_audit W | | 1498147 | barrage | 10.1.100.80:19230 | barrage_hisbk | Query | 35640 | Waiting for table flush | SELECT uuid, FROM_UNIXTIME(MAX(create_time)) AS last_publish_time FROM barrage_hisbk. barrage_audit W | | 1506539 | barrage | 10.1.100.80:20480 | barrage_hisbk | Query | 32040 | Waiting for table flush | SELECT uuid, FROM_UNIXTIME(MAX(create_time)) AS last_publish_time FROM barrage_hisbk. barrage_audit W | | 1512611 | barrage | 10.1.100.251:63443 | barrage | Query | 29432 | Waiting for table flush | select b.nick_name,b.uuid,b.is_vip,b.device,b.remark,count(bt.uuid) as barrage_count, user_level_his | | 1512683 | barrage | 10.1.100.251:63461 | barrage | Query | 29402 | Waiting for table flush | select b.nick_name,b.uuid,b.is_vip,b.device,b.remark,count(bt.uuid) as barrage_count, user_level_his | 表被锁了，仔细看下，有人在FLUSH TABLES，不过顺着这个端口找到进程号，就知道哪个程序来的了，其它还可以找到是谁操作的。这里就不需要再找了，是备份导致的。目前正在mysqldump备份： #### /usr/local/mysql/bin/mysqldump -h10.1.5.41 -P 3307 -u root -px xxxxxxxxxxxxxxxxxx -R -E --flush_logs --single-transaction --master-data=2 --add-drop-database --databases 那就是这个mysqldump中的flush tables 阻塞了其它的查询操作。在执行flush tables前有查询还没有查询完，导致flush tables被阻塞，从而也阻塞了其它查询。是那个查询呢？为了方便查找，把这些等待的SQL全KILL了，因为量比较大。同时把备份也停了。KILL完后，一条全大大大的SQL出来了。 #### SELECT DISTINCT(uuid) AS uuid FROM barrage_hisbk. barrage_audit 这是一上分区表，有上百个分区，UUID是普通索引，导致进行全分区查询去重，执行了4w多秒还没有查出来。（这里显示出监控的不完善，执行了这么久的SQL都没有报警...） 这里就不管其它的，直接KILL，也就恢复了，这个表也是可以正常读了。 mysqldump是有在用--master-data=2这个参数据时，就会有flush tables，主要是保证备份数据一致性，记录日志点。如下是mysqldump时的全日志： #### 41138 Query /*!40100 SET @@SQL_MODE='' */ 41138 Query /*!40103 SET TIME_ZONE='+00:00' */ 41138 Query FLUSH /*!40101 LOCAL */ TABLES 41138 Query FLUSH TABLES WITH READ LOCK 41138 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 41138 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 41138 Query SHOW VARIABLES LIKE 'gtid\_mode' 41138 Query SHOW MASTER STATUS 41138 Query UNLOCK TABLES 如下是无--master-data的日志： #### 41235 Query /*!40100 SET @@SQL_MODE='' */ 41235 Query /*!40103 SET TIME_ZONE='+00:00' */ 41235 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 41235 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 41235 Query SHOW VARIABLES LIKE 'gtid\_mode' 41235 Query UNLOCK TABLES 所以在使用mysqldump备份时，还是得注意，不能有大的慢查询，不然会出现不可想向的问题，还有后面有一个全局读锁FLUSH TABLES WITH READ LOCK，所以说mysqldump并不是无影响的。不过这些问题主要还是出在那个大查询上，正常线上是不允许出现的这样的查询的。所以说，优化掉线上的慢查询是多么的重要啊。 #### #flush tables Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement. In MySQL 8.0, FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_name ... WITH READ LOCK instead. #flush tables with read lock Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.