标签：mysql

生产环境中可以使用工具实时审计Mysql流量。

介绍两种方式：

• MySQL Sniffer
• PacketBeat

MySQL Sniffer

MySQL Sniffer 是一个基于 MySQL 协议的抓包工具，实时抓取 MySQLServer 端或 Client 端请求，并格式化输出。输出内容包括访问时间、访问用户、来源 IP、访问 Database、命令耗时、返回数据行数、执行语句等。有批量抓取多个端口，后台运行，日志分割等多种使用方式，操作便捷，输出友好。

安装依赖：

yum install glib2-devel libpcap-devel libnet-devel

项目下载地址：

https://github.com/Qihoo360/mysql-sniffer

安装步骤：

cd mysql-sniffer

mkdir proj

cd proj

cmake ../

make

cd bin/

参数如下：

[root@server120 bin]# ./mysql-sniffer -h

Usage ./mysql-sniffer [-d] -i eth0 -p 3306,3307,3308 -l /var/log/mysql-sniffer/ -e stderr

         [-d] -i eth0 -r 3000-4000

         -d daemon mode.

         -s how often to split the log file(minute, eg. 1440). if less than 0, split log everyday

         -i interface. Default to eth0

         -p port, default to 3306. Multiple ports should be splited by ','. eg. 3306,3307

            this option has no effect when -f is set.

         -r port range, Don't use -r and -p at the same time

         -l query log DIRECTORY. Make sure that the directory is accessible. Default to stdout.

         -e error log FILENAME or 'stderr'. if set to /dev/null, runtime error will not be recorded

         -f filename. use pcap file instead capturing the network interface

         -w white list. dont capture the port. Multiple ports should be splited by ','.

         -t truncation length. truncate long query if it's longer than specified length. Less than 0 means no truncation

         -n keeping tcp stream count, if not set, default is 65536. if active tcp count is larger than the specified count, mysql-sniffer will remove the oldest one

测试：

[root@server120 bin]# ./mysql-sniffer -i lo -p 3306

2017-08-16 13:56:04        root        127.0.0.1        NULL                0ms                1  select @@version_comment limit 1

2017-08-16 14:01:56        root        127.0.0.1        NULL                0ms                1  SELECT DATABASE()

2017-08-16 14:01:56        root        127.0.0.1        mysql               0ms                0  use mysql

2017-08-16 14:01:56        root        127.0.0.1        mysql               0ms                5  show databases

2017-08-16 14:01:56        root        127.0.0.1        mysql               0ms               23  show tables

2017-08-16 14:02:04        root        127.0.0.1        mysql               0ms                8  select * from user

输出格式为：时间，访问用户，来源 IP，访问 Database，命令耗时，返回数据行数，执行语句。

保存日志可以用filebeat采集：

[root@server120 bin]# ./mysql-sniffer -i eth0 -p 3306 -l /tmp/
[root@server120 tmp]# head -n 5 3306.log 
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             0  SET NAMES utf8
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             2  SHOW VARIABLES LIKE 'lower_case_%'
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             1  SHOW VARIABLES LIKE 'profiling'
2017-08-16 14:04:58  root    192.168.190.201     NULL             0ms             5  SHOW DATABASES
2017-08-16 14:05:20  root    192.168.190.201     NULL             0ms             0  SET NAMES utf8

-l 指定日志输出路径，日志文件将以 port.log 命名。

需要注意的是：

只能抓取新建的链接，如果是之前创建的链接将获取不到用户名和库名，并有一定几率丢包。

PacketBeat

Packeybeat可以部署在：

• Mysql服务端
• 镜像DB服务器上游交换机流量到服务器

yum -y install libpcap
./packetbeat -c packetbeat.yml

packetbeat.yml为配置文件
packetbeat.template.json为mapping文件

测试：

mysql> select host,user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| %         | soc  |
| 127.0.0.1 | root |
| localhost |      |
| localhost | soc  |
+-----------+------+
5 rows in set (0.00 sec)

输出到Elasticsearch内容如下：

• query：执行的SQL语句
• num_fields：返回的字段数
• num_rows：查询结果行数

背景介绍

在日常工作环境中, 开发或者测试人员经常需要连接测试库、线上库等查看表结构或数据来验证程序的功能. 实际上让 DBA 协助开发者查看信息会是特别繁琐且无趣的事情, 所以为了方便起见会将数据库的权限分发给开发或者测试人员. 不过长此以往下去有几件事情会让 DBA 深恶痛绝:

• 账号会在开发者之间相互传递;
• 为了方便开发者会以快捷命令的方式查看信息, 密码信息容易暴露;
• 开发者忘记密码, DBA 可能需要重置以通知所有其他人员修改密码;

事实上, 上述几种情况是很难避免的, 只要有人工参与就会有这些潜在危险的隐患, 所以我们就需要提供一个相对方便记住的又能保证相对安全的方式供开发者使用. 下面则从不同层面简单的对这种方式进行描述.

管理主机

如果从系统层面来看, 我们建议最好把所有的开发者都集中到一台管理主机上登录, 只有开发者连接到这台机器上, 才能通过该机器连接测试库, 线上库等进行查询信息操作. 如下图所示:

     +------------+     ssh      +--------------+                +-----------+
     | developers |  ----------> | manager host |    --------->  | databases |
     +------------+              +--------------+                +-----------+

开发者通过 ssh 登录该主机, 这个步骤最好是以 key 的方式登录, 开发者的私钥最好设置密码; 在登录主机后, 开发者再连接后面的数据库, 不过这个步骤又回到了我们上述提到的三个问题, 只是发生的环境在我们可控的主机上, 而不是在开发者的层面.

另外如果可以的话, 建议在 manager host 主机中部署 google-authenticator-libpam, 让开发者一次一密以 keyboard interactive 的方式登录 manager host, 这样可以避免开发者私钥文件泄露引起的安全隐患(当然 pam 生成的安全字符串不能泄露)。

这种方式其实并没有本质上的改进, 只是将所有不稳定因素都限制到一台主机中, 在安全方面进步不少.

MySQL pam 插件

官方和 percona, mariadb 等分支版本都提供了 pam 或 auth_pam 插件, 我们可以基于此完成很多类似 ldap, 一次一密, 系统用户等方式登录 MySQL 数据库, 更多见 more.

这些插件确实为我们提供了很方便的方式来连接数据库, 但是它们都有一个共同的问题, 就是不支持远程连接. 如下图所示:

   +--------------+                +-----------+
   | manager host |    --------->  | databases |
   +--------------+                +-----------+

我们需要在数据库主机中开启 pam 插件以方便开发者登录数据库, 但是开发者并不能在 manager 主机中以 pam 的方式连接后面的数据库, 当然或许可以通过 ssh host -e “xxxxx” 的方式连接, 但是作为系统管理员或者 DBA 不大可能为所有数据库主机都开通相关的用户权限, 为每台数据库主机设置 pam 插件及建立相关用户也是特别繁琐的事情.

如果开发者访问的数据库很少, 可以考虑 pam 插件和 google authentication 相结合的方式供开发者访问. 这种方式同样解决不了上述提到的三个问题.

代理访问

我们也可以从中间件层面考虑这个问题, 简单描述则为中间件接收用户发送过来的用户名和密码进行校验, 如果通过则使用真实的数据库用户名和密码去和后端的数据库进行交互, 如下图所示:

             user/password                 mysql_user/pass
  +------+                     +-------+                     +--------------+
  | user |  ---------------->  | proxy | ------------------> | MySQL Server |
  +------+                     +-------+                     +--------------+

这里用户输入的用户名和密码最好是伪造的, password 应该具有既好记又比较安全的特点. 这里我们想到了 google authentication 的方式, 使用基于时间的 totp 方法动态生成用户输入的 password.

portproxy 则基于该方式实现开发者一次一密的访问数据库. 原理则比较简单, portproxy 劫持用户发送过来的用户名和密码信息, portproxy 默认以 user+totp 作为用户的默认密码, 如果校验成功, 则使用真实的用户密码构造 MySQL 的验证报文, 再发送到后端的 MySQL 数据库, 其流程大致如下:

             user/user+totp                   mysql_user/pass
  +------+                     +-----------+                     +--------------+
  | user |  ---------------->  | portproxy | ------------------> | MySQL Server |
  +------+                     +-----------+                     +--------------+

这种方式可以很容易的解决开发者遗忘密码的问题, 只要记住用户名及能够获取对应的 totp 6位数字即可连接数据库; 如果再加上管理机, 就能限制所有开发者在一台机器上操作, 也能比较方便的杜绝开发者互相传递数据库密码; 另外也可以在用户输入密码前封装一层, 只允许 tty 方式接收用户输入的密码, 这样就可以避免开发者以快捷方式连接数据库; 当然如果开发者足够厉害也是可以绕过我们的限制, 这种情况下也能解决上述的1, 3 两个问题.

portproxy 如何实现一次一密验证

事实上, portproxy 是解析了 mysql connection 的验证协议才实现了劫持的目的, 正常情况下, mysql 的连接建立过程如下:

client 和 server 三次握手完成后, server 开始给 client 发送初始的报文, 其中就包含了协议版本, MySQL Server 版本, 用户名, 连接 id 以及随机且固定长度的初始验证数据;

client 接收到 server 的初始报文后, 解析出验证的协议版本(MySQL 老的加密协议或者新的协议), 以及20位初始的随机验证数据. 通过 20位的数据和用户输入的密码经过下面的算法校验用户是否有效:

SHA1( password ) XOR SHA1( "20-bytes random data from server" <concat> SHA1( SHA1( password ) ) )

portproxy 就是通过三次握手后接收 server 发送的初始报文解析出我们需要的用户名和20位随机数据, 再将用户名+totp 作为默认的密码校验开发者是否输入正确的password(user+totp), 如果正确则重新使用真实的用户名和密码以及20位随机数据构造新的验证数据报文发送给 server, 通过后则连接建立完成, 开发者就可以正常访问数据库. 更多通信协议见 client-server-protocol.

以 arster 用户名为例进行以下操作:

# sys-google-totp -secret "OLENMTM3BTB36EUY"
otp message:
202340 (22 second(s) remaining)

# mysql -h 10.0.21.5 -P 33306 -u arster -p
......
......

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql userread@[10.0.21.5:33306 (none)] > 
mysql userread@[10.0.21.5:33306 (none)] > quit

这里输入的密码就应该是 arster202340, totp 默认 30 秒变更一次, 开发者需要保证有足够的时间输入密码, 如果时间不够则重新执行 sys-google-totp 命令获取新的 6 位数字. 校验成功后, portproxy 则使用真实的 userread 用户重新构造数据报文并发送给后面的数据库.

总结

实际工作中, DBA 或系统管理员最烦的可能就是开发者忘记密码, 如果 DBA 也没有记录用户密码, 就只有重置这种方式, 最后再通知所有开发者进行修改. portproxy 的方式能够解决开发者忘记密码的问题, 稍加设置或封装就可以解决另外两个问题. 当然如果一些公司的devops做的足够好的话就可以不用考虑这些, 只需要保证开发者不乱传账号信息或者账号信息不被盗取就可以避免我们上述讨论的三个问题.

并发即指在同一时刻，多个操作并行执行。MySQL对并发的处理主要应用了两种机制——是“锁”和“多版本控制”。

锁

锁分为读锁和写锁两种，也称作共享锁和排他锁。

因为多个读操作同时进行是不会破坏数据的，所以读锁是共享的，多个读操作可以同时进行，互不干扰。

为了防止多个写操作共同执行破坏数据，写锁是排他的，一个写锁会阻塞其它的写锁和读锁，进而保证同一资源在任何时刻只有一个写操作在执行，并防止其它用户读取正在写入的该资源。

在锁粒度方面，MySQL包括表锁和行锁两种类型。锁的粒度越小，越有利于对数据库操作的并发执行。但是管理锁消耗的资源也会更多。如果系统花费大量的时间来管理锁，而不是存储数据，那么系统的性能也会受到影响。

表锁会锁定整张表，它是开销最小的策略。诸如ALTER TABLE之类的语句会使用表锁。

行锁最大程度的支持并发操作，同时也带来了最大的开销。InnoDB实现了行锁。

在MySql中并不只是用锁来维护并发控制。

事务的隔离级别

事务的概念在此不多介绍。我觉得，也可以将事务看成是并发中的一部分——事务包含了一组操作，事务和事务之间可以并行执行。事务和事务之间的并发也和普通的并发操作一样会共享相同的资源，这样并发执行的事务之间就会相互影响。根据事务之间影响程度的不同，提出了事务的隔离级别这个概念，分别是READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

READ UNCOMMITTED就是一个事务对共享数据的修改马上就能够被另一个事务感知到，其实也就是没有对修改操作做任何特殊处理。

SERIALIZABLE是通过加锁的方式强制事务串行执行，这样可以避免幻读。但这种方式会带来大量锁争用问题。

READ COMMITTED和REPEATABLE READ是基于MVCC的方式实现的。

MVCC多版本并发控制

MySql对于事务之间并发控制的实现并不是简单的使用行级锁。MySql在读操作时并不加锁，只有在写操作时才会对修改的资源加锁。

MVCC保存了数据资源在不同时间点上的多个快照。根据事务开始的时间不同，每个事务看到的数据快照版本是不一样的。

InnoDB中的MVCC实现：存储引擎全局维护了一个系统版本号，每开启一个新的事务，这个系统版本号就会递增。事务开始时刻的系统版本号，会作为这个事务本身的版本号。在每行记录中，存储引擎又在每行的后面保存两个隐藏的列，分别保存这一行的开始版本号和过期版本号。在REPEATABLE READ隔离级别下，MVCC的具体操作如下：

• INSERT
  存储引擎为新插入的每一行保存当前的系统版本号作为这一行的开始版本号。

• UPDATE
  存储引擎会新插入一行记录，当前的系统版本号就是新记录行的开始版本号。同时会将原来行的过期版本号设为当前的系统版本号。

• DELETE
  存储引擎将删除的记录行的过期版本号设置为当前的系统版本号。

• SELECT
  当读取记录时，存储引擎会选取满足下面两个条件的行作为读取结果。

  • 读取记录行的开始版本号必须早于当前事务的版本号。也就是说，在当前事务开始之前，这条记录已经存在。在事务开始之后才插入的行，事务不会看到。

  • 读取记录行的过期版本号必须晚于当前事务的版本号。也就是说，当前事务开始的时候，这条记录还没有过期。在事务开始之前就已经过期的数据行，该事务也不会看到。

通过上面的描述，可以看到在存储引擎中，同一时刻存储了一个数据行的多个版本。每个事务会根据自己的版本号和每个数据行的开始及过期版本号选择读取合适的数据行。

MVCC只在READ COMMITTED和REPEATABLE READ这两个级别下工作。

本文只是简单的介绍mysql binlog基本用法，并不涉及到binlog的原理、格式等知识，如果需要了解这些高级的知识，请参见官方文档。

本文重点介绍–start-position和–stop-position参数的使用
–start-position的语法是

--start-position=N 

含义是从相对与二进制日志的第N偏移的事件开始读。 同理，–stop-position=N的介绍和–start-position类似。在默认的情况下， log-bin是关闭的，如下：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

我们可以通过修改my.ini配置文件，在[mysqld] 下面添加 log-bin=日志名：

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

log-bin=mysql-bin

修改完成之后，我们需要重启mysql服务，然后再看下是否启动了binlog

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (1.01 sec)

已经开启了binlog。然后我们创建一个数据库binlog

mysql> create database binlog;
Query OK, 1 row affected (0.00 sec)

mysql> use binlog;
Database changed

然后在binlog数据库下面创建表test，并依次进行如下操作。

mysql> create table test(
       id int auto_increment not null primary key, 
       val int,
       data varchar(20)
);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(val, data) values (10, 'wu');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test(val, data) values (20, 'yang');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(val, data) values (20, 'ping');
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test(val, data) values (40, 'hao');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(val, data) values (50, 'iteblog');
Query OK, 1 row affected (0.01 sec)

mysql> delete from test where id between 4 and 5;
Query OK, 2 rows affected (0.01 sec)

mysql> insert into test(val, data) values (60, 'iteblog1');
Query OK, 1 row affected (0.02 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test(val, data) values (70, 'ping123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(val, data) values (80, 'ping163');
Query OK, 1 row affected (0.01 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database binlog;
Query OK, 0 rows affected (0.00 sec)

经过上述的操作，将会在本地数据库数据存放目录下面生成以下四个文件：

mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.index

*.index是索引文件，其他三个是binlog文件，我们可以用mysqlbinlog 工具来恢复数据。为了下面讲解的方便，我们先将binlog文件解析成txt文件，如下：

mysqlbinlog  datamysql-bin.000001 > E:/1.txt

mysqlbinlog  datamysql-bin.000002 > E:/2.txt

mysqlbinlog  datamysql-bin.000003 > E:/3.txt

通过这三个命令，可以在E盘下生成3个文件，里面分别记录了日志文件的内容，也就是用户操作的步骤。

下面开始恢复binlog日志到Mysql数据库，因为我们需要重做第一个日志文件的所有操作，所以这里只需要将第一个日志文件全恢复就行了。

mysqlbinlog  datamysql-bin.000001 | mysql -uroot -p123456

在第二个binlog里面我们进行了delete操作，我们并不想将delete的操作恢复到数据库，这样我们可以通过读取2.txt文件：

................................

/*!*/;
# at 653
#140902 16:07:43 server id 1  end_log_pos 759   Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1409645263/*!*/;
delete from test where id between 4 and 5
/*!*/;
# at 759
#140902 16:07:43 server id 1  end_log_pos 786   Xid = 175
COMMIT/*!*/;
................................

　　
在这个文件中，我们可以看到DELETE的操作的起始位置是653，终止位置是759.那么我们只要重做第二个日志文件的开头到653的操作，然后再从759到末尾的操作，我们就可以把数据给恢复回来，而不会DELETE数据。所以执行两个命令

mysqlbinlog  datamysql-bin.000002 --stop-pos=653 | mysql -uroot -p123456

mysqlbinlog  datamysql-bin.000002 --start-pos=759 | mysql -uroot -p123456

mysqlbinlog  datamysql-bin.000003 --stop-pos=587 | mysql -uroot -p123456

好了，到这里，所有的数据全部恢复了，我们可以用下面语句查看到：

mysql> select * from test
+----+------+----------+
| id | val  | data     |
+----+------+----------+
|  1 |   10 | wu       |
|  2 |   20 | yang     |
|  3 |   20 | ping     |
|  4 |   40 | hao      |
|  5 |   50 | iteblog  |
|  6 |   60 | iteblog1 |
|  7 |   70 | ping123  |
|  8 |   80 | ping163  |
+----+------+----------+
8 rows in set (0.00 sec)

最近遇到一个案例，很多查询被阻塞没有返回结果，使用show processlist查看，发现不少MySQL线程处于Waiting for table flush状态，查询语句一直被阻塞，只能通过Kill进程来解决。那么我们先来看看Waiting for table flush的官方解释：

https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

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.

那么我们接下来模拟一下线程处于Waiting for table flush状态的情况，如所示：

在第一个会话连接（connection id=13）中，我们使用lock table 锁定表test。

mysql> use MyDB;
Database changed
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              13 |
+-----------------+
1 row in set (0.00 sec)

mysql> lock table test read;
Query OK, 0 rows affected (0.00 sec)

mysql> 

在第二个会话连接（connection id=17）中，我们执行flush table 或 flush table test 皆可。此时你会发现flush table处于阻塞状态。

mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              17 |
+-----------------+
1 row in set (0.00 sec)

mysql> flush table test;

在第三个会话/连接中，当你切换到MyDB时，就会提示“You can turn off this feature to get a quicker startup with -A” ，此时处于阻塞状态。此时你退出会话，使用参数-A登录数据库后，你如果查询test表，就会处于阻塞状态（当然查询其它表不会被阻塞）。如下所示：

mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


mysql> use MyDB;
Database changed
mysql> select * from test;

clip_image002

在第四个会话/连接，我们用show processlist查看到当前数据库所有连接线程状态，你会看到17、18都处于Waiting for table flush的状态。如下截图所示：

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| Id | User | Host      | db   | Command | Time | State                   | Info               |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| 13 | root | localhost | MyDB | Sleep   |   90 |                         | NULL               |
| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |
| 17 | root | localhost | MyDB | Query   |   52 | Waiting for table flush | flush table test   |
| 18 | root | localhost | MyDB | Query   |    9 | Waiting for table flush | select * from test |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> 

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| Id | User | Host      | db   | Command | Time | State                   | Info               |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| 13 | root | localhost | MyDB | Sleep   |   90 |                         | NULL               |
| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |
| 17 | root | localhost | MyDB | Query   |   52 | Waiting for table flush | flush table test   |
| 18 | root | localhost | MyDB | Query   |    9 | Waiting for table flush | select * from test |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> show open tables where in_use >=1;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| MyDB     | test  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> kill 17;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| Id | User | Host      | db   | Command | Time | State                   | Info               |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
| 13 | root | localhost | MyDB | Sleep   |  442 |                         | NULL               |
| 14 | root | localhost | NULL | Query   |    0 | init                    | show processlist   |
| 18 | root | localhost | MyDB | Query   |  361 | Waiting for table flush | select * from test |
+----+------+-----------+------+---------+------+-------------------------+--------------------+
3 rows in set (0.00 sec)

mysql> kill 13;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 14 | root | localhost | NULL | Query   |    0 | init  | show processlist |
| 18 | root | localhost | MyDB | Sleep   |  427 |       | NULL             |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql> 

注意：我们需要Kill线程13， Kill掉线程17是解决不了问题的。

生产环境中，很多时候可能不是lock table read引起的阻塞，而是由于慢查询，导致flush table一直无法关闭该表而一直处于等待状态，例如下面测试案例中，我使用同一张大表做笛卡尔积模拟一个慢查询，其它操作相同，如下所示，你会看到同样产生了Waiting for table flush

mysql> SELECT T.* FROM TEST1 T, TEST1 L;

另外，网上有个案例，mysqldump备份时，如果没有使用参数—single-transaction 或由于同时使用了flush-logs与—single-transaction两个参数也可能引起这样的等待场景，这个两个参数放在一起，会在开始dump数据之前先执行一个FLUSH TABLES操作。

解决方案：

出现Waiting for table flush时，我们一般需要找到那些表被lock住或那些慢查询导致flush table一直在等待而无法关闭该表。然后Kill掉对应的线程即可，但是如何精准定位是一个挑战，尤其是生产环境，你使用show processlist会看到大量的线程。让你眼花缭乱的，怎么一下子定位问题呢？

对于慢查询引起的其它线程处于Waiting for table flush状态的情形：

可以查看show processlist中Time值很大的线程。然后甄别确认后Kill掉，如上截图所示，会话连接14就是引起阻塞的源头SQL。有种规律就是这个线程的Time列值必定比被阻塞的线程要高。这个就能过滤很多记录。

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

对于实验中使用lock table read这种情况，这种会话可能处于Sleep状态，而且它也不会出现在show engine innodb status G命令的输出信息中。 即使show open tables where in_use >=1;能找到是那张表被lock住了，但是无法定位到具体的线程（连接），其实这个是一个头痛的问题。但是inntop这款利器就可以定位到，如下所示，线程17锁住了表test，在innotop里面就能定位到是线程17。所谓工欲善其事必先利其器！

另外，在官方文档中ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE都能引起这类等待，下面也做了一些简单测试，如下所示：

Waiting for table flush的另外一个场景

会话连接（connection id=18)执行下面SQL语句，模拟一个慢查询SQL

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              18 |
+-----------------+
1 row in set (0.00 sec)

mysql> select name, sleep(64) from test;

会话连接（connection id=6)执行下面SQL语句，分析表test

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)
mysql> analyze table test;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| MyDB.test | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.04 sec)

mysql> 

会话连接（connection id=8)执行下面SQL语句

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test;

查看线程的状态，你会发现被阻塞的会话处于 Waiting for table flush状态。 因为当对表做了ANALYZE TABLE后，后台针对该表的查询需要等待，因为MySQL已经检测到该表内部变化，需要使用FLUSH TABLE关闭然后重新打开该表，所以当你查询该表时，就会处于 Waiting for table flush

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------------+----------------------------------+
| Id | User | Host      | db   | Command | Time | State                   | Info                             |
+----+------+-----------+------+---------+------+-------------------------+----------------------------------+
|  6 | root | localhost | MyDB | Sleep   |   22 |                         | NULL                             |
|  8 | root | localhost | MyDB | Query   |   14 | Waiting for table flush | select * from test               |
| 15 | root | localhost | NULL | Sleep   |    3 |                         | NULL                             |
| 16 | root | localhost | NULL | Query   |    0 | init                    | show processlist                 |
| 18 | root | localhost | MyDB | Query   |   46 | User sleep              | select name, sleep(64) from test |
+----+------+-----------+------+---------+------+-------------------------+----------------------------------+
5 rows in set (0.00 sec)

mysql> 

Waiting for table metadata lock

会话连接（connection id=17)执行下面SQL语句，模拟一个慢查询SQL

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              17 |
+-----------------+
1 row in set (0.00 sec)

mysql> select name, sleep(100) from test;

会话连接（connection id=6）执行下面SQL语句， 修改表结构操作

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

mysql> alter table test add tname varchar(10); // rename table test to kkk 同样会引起Waiting for table metadata lock

会话连接（connection id=8）执行下面SQL语句，查询表test

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               8 |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test;

查看线程的状态，你会发现被阻塞的会话处于 Waiting for table metadata lock状态。

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                                   |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
|  6 | root | localhost | MyDB | Query   |   19 | Waiting for table metadata lock | alter table test add tname varchar(10) |
|  8 | root | localhost | MyDB | Query   |    6 | Waiting for table metadata lock | select * from test                     |
| 15 | root | localhost | NULL | Sleep   |    8 |                                 | NULL                                   |
| 16 | root | localhost | NULL | Query   |    0 | init                            | show processlist                       |
| 17 | root | localhost | MyDB | Query   |   55 | User sleep                      | select name, sleep(100) from test      |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------+
5 rows in set (0.00 sec)

mysql>