乐趣区

关于mysql:MySQL查看及杀掉链接方法大全

jiezi

前言：

在数据库运维过程中，咱们时常会关注数据库的链接状况，比方总共有多少链接、有多少沉闷链接、有没有执行工夫过长的链接等。数据库的各种异样也能通过链接状况间接反馈进去，特地是数据库呈现死锁或重大卡顿的时候，咱们首先应该查看数据库是否有异样链接，并杀掉这些异样链接。本篇文章将次要介绍如何查看数据库链接及如何杀掉异样链接的办法。

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 语句肯定要慎用！特地是此链接执行的是更新语句或表构造变动语句时，杀掉链接可能须要比拟长时间的回滚操作。

总结：

本篇文章解说了查看及杀掉数据库链接的办法，当前狐疑数据库有问题，能够第一工夫看下数据库链接状况。

退出移动版