Mysql-Kill杀死连接 – Woo的博客

前言：

在数据库运维过程中，我们时常会关注数据库的连接情况，比如总共有多少连接、有多少活跃连接、有没有执行时间过长的连接等。数据库的各种异常也能通过连接情况间接反应出来，特别是数据库出现死锁或严重卡顿的时候，我们首先应该查看数据库是否有异常连接，并杀掉这些异常连接。

1.查看数据库连接

查看数据库连接最常用的语句就是 show processlist 了，这条语句可以查看数据库中存在的线程状态。普通用户只可以查看当前用户发起的连接，具有 PROCESS 全局权限的用户则可以查看所有用户的连接。

show processlist 结果中的 Info 字段仅显示每个语句的前 100 个字符，如果需要显示更多信息，可以使用 show full processlist 。同样的，查看 information_schema.processlist 表也可以看到数据库连接状态信息。

# 普通用户只能看到当前用户发起的连接
mysql> select user();
+--------------------+
| user()             |
+--------------------+
| testuser@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@%                                                |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+--------+----------+-----------+--------+---------+------+----------+------------------+
| Id     | User     | Host      | db     | Command | Time | State    | Info             |
+--------+----------+-----------+--------+---------+------+----------+------------------+
| 769386 | testuser | localhost | NULL   | Sleep   |  201 |          | NULL             |
| 769390 | testuser | localhost | testdb | Query   |    0 | starting | show processlist |
+--------+----------+-----------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql> select * from information_schema.processlist;
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
| ID     | USER     | HOST      | DB     | COMMAND | TIME | STATE     | INFO                                         |
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
| 769386 | testuser | localhost | NULL   | Sleep   |  210 |           | NULL                                         |
| 769390 | testuser | localhost | testdb | Query   |    0 | executing | select * from information_schema.processlist |
+--------+----------+-----------+--------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)

# 授予了PROCESS权限后，可以看到所有用户的连接
mysql> grant process on *.* to 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants;
+----------------------------------------------------------------------+
| Grants for testuser@%                                                |
+----------------------------------------------------------------------+
| GRANT PROCESS ON *.* TO 'testuser'@'%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'testuser'@'%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show processlist;
+--------+----------+--------------------+--------+---------+------+----------+------------------+
| Id     | User     | Host               | db     | Command | Time | State    | Info             |
+--------+----------+--------------------+--------+---------+------+----------+------------------+
| 769347 | root     | localhost          | testdb | Sleep   |   53 |          | NULL             |
| 769357 | root     | 192.168.85.0:61709 | NULL   | Sleep   |  521 |          | NULL             |
| 769386 | testuser | localhost          | NULL   | Sleep   |  406 |          | NULL             |
| 769473 | testuser | localhost          | testdb | Query   |    0 | starting | show processlist |
+--------+----------+--------------------+--------+---------+------+----------+------------------+
4 rows in set (0.00 sec)

通过 show processlist 所得结果，我们可以清晰了解各线程连接的详细信息。具体字段含义还是比较容易理解的，下面具体来解释下各个字段代表的意思：

Id：就是这个连接的唯一标识，可通过 kill 命令，加上这个Id值将此连接杀掉。
User：就是指发起这个连接的用户名。
Host：记录了发送请求的客户端的 IP 和端口号，可以定位到是哪个客户端的哪个进程发送的请求。
db：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command：是指此刻该线程连接正在执行的命令。
Time：表示该线程连接处于当前状态的时间。
State：线程的状态，和 Command 对应。
Info：记录的是线程执行的具体语句。

当数据库连接数过多时，筛选有用信息又成了一件麻烦事，比如我们只想查某个用户或某个状态的连接。这个时候用 show processlist 则会查找出一些我们不需要的信息，此时使用 information_schema.processlist 进行筛选会变得容易许多，下面展示几个常见筛选需求：

# 只查看某个ID的连接信息
select * from information_schema.processlist where id = 705207;

# 筛选出某个用户的连接
select * from information_schema.processlist where user = 'testuser';

# 筛选出所有非空闲的连接
select * from information_schema.processlist where command != 'Sleep';

# 筛选出空闲时间在600秒以上的连接
select * from information_schema.processlist where command = 'Sleep' and time > 600;

# 筛选出处于某个状态的连接
select * from information_schema.processlist where state = 'Sending data';

# 筛选某个客户端IP的连接
select * from information_schema.processlist where host like '192.168.85.0%';

2.杀掉数据库连接

如果某个数据库连接异常，我们可以通过 kill 语句来杀掉该连接，kill 标准语法是：KILL [CONNECTION | QUERY] processlist_id;

KILL 允许使用可选的 CONNECTION 或 QUERY 修饰符：

KILL CONNECTION 与不含修改符的 KILL 一样，它会终止该 process 相关连接。
KILL QUERY 终止连接当前正在执行的语句，但保持连接本身不变。

杀掉连接的能力取决于 SUPER 权限：

如果没有 SUPER 权限，则只能杀掉当前用户发起的连接。
具有 SUPER 权限的用户，可以杀掉所有连接。

遇到突发情况，需要批量杀连接时，可以通过拼接 SQL 得到 kill 语句，然后再执行，这样会方便很多，分享几个可能用到的杀连接的 SQL ：

# 杀掉空闲时间在600秒以上的连接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` 
where command = 'Sleep' and time > 600;

# 杀掉处于某个状态的连接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` 
where state = 'Sending data';

select concat('KILL ',id,';') from information_schema.`processlist` 
where state = 'Waiting for table metadata lock';

# 杀掉某个用户发起的连接，拼接得到kill语句
select concat('KILL ',id,';') from information_schema.`processlist` 
 user = 'testuser';

这里提醒下，kill 语句一定要慎用！特别是此连接执行的是更新语句或表结构变动语句时，杀掉连接可能需要比较长时间的回滚操作。

3.为什么会存在 kill 不掉连接或查询的情况

MySQL 的 kill 命令并不是马上停止的意思，而是通知执行线程终止执行语句。

由此可以得出其实会有几种情况会导致 kill “不成功”：

线程没有执行到判断线程状态的逻辑。比如这个语句还在等待锁或者并发线程数不足还在等待线程。
终止逻辑耗时较长。比如超大事务、大查询回滚、DDL 命令执行到最后阶段等，此些操作终止执行命令耗时长、需要等待 IO 资源或 IO 操作。

4.使用案例

背景：在使用 navicat（或其他可视化工具）预览一个超大表（上千万）的数据，因为是可视化操作，所以有时候会误操作，直接翻到最后一页，这会导致本地内存使用率直接飙升，而navicat 页面直接卡住不响应，甚至直接把 navicat 关掉了，后续 navicat 想重新连接数据库连不上了，想删除连接，重新建立连接，被提示正在连接中，无法删除。

解决思路：软件上没办法重新连接，就去服务器上删掉原来的连接。

实际操作：

去到 mysql 所在服务器上，进入 mysql 命令行执行（select * from information_schema.processlist where Host like ‘电脑本机IP%’;）。
发现果然之前因为 navicat 超大表查询还在卡住，导致后面的连接一直被堵住。
使用（kill 线程id）命令删除连接后，navicat 重新连接成功。

分析：

navicat 查询超大表最后一页的机制是使用 limit 关键字，会先把符合条件的数据都加载出来，而最后一页就是需要把整张表的数据都加载出来，因为 MySQL 边发边读的机制，导致查询出来的数据全部堆在本地的内存中，导致内存使用率飙升，页面卡死，而且还有着大量的索引数据也会加载到内存，增加了内存的使用量。
navicat 强行关闭掉后，MySQL 的线程任务是没有被断掉的，断掉的是客户端的线程任务，因为 MySQL 是停等协议，在线程执行的语句还没有返回的时候，客户端往 MySQL 该线程发送命令也是不予理会的，无法实际断掉线程任务，就会堵塞住。

Our Visitor

1.查看数据库连接

2.杀掉数据库连接

3.为什么会存在 kill 不掉连接或查询的情况

4.使用案例

发送评论编辑评论

Our Visitor

1.查看数据库连接

2.杀掉数据库连接

3.为什么会存在 kill 不掉连接或查询的情况

4.使用案例

发送评论 编辑评论

发送评论编辑评论