亲宝软件园·资讯

展开

MySQL 查看链接及杀掉异常链接 MySQL 查看链接及杀掉异常链接的方法

MySQL技术 人气:0
想了解MySQL 查看链接及杀掉异常链接的方法的相关内容吗,MySQL技术在本文为您仔细讲解MySQL 查看链接及杀掉异常链接的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL,查看链接,MySQL,杀掉异常链接,下面大家一起来学习吧。

前言: 

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

  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 所得结果,我们可以清晰了解各线程链接的详细信息。具体字段含义还是比较容易理解的,下面具体来解释下各个字段代表的意思:

当数据库链接数过多时,筛选有用信息又成了一件麻烦事,比如我们只想查某个用户或某个状态的链接。这个时候用 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 修饰符:

杀掉链接的能力取决于 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 语句一定要慎用!特别是此链接执行的是更新语句或表结构变动语句时,杀掉链接可能需要比较长时间的回滚操作。

总结: 

本篇文章讲解了查看及杀掉数据库链接的方法,以后怀疑数据库有问题,可以第一时间看下数据库链接情况。

加载全部内容

相关教程
猜你喜欢
用户评论