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

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

总结:

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

以上就是MySQL 查看链接及杀掉异常链接的方法的详细内容,更多关于MySQL 查看链接及杀掉异常链接的资料请关注我们其它相关文章!

(0)

相关推荐

  • python pymysql链接数据库查询结果转为Dataframe实例

    我就废话不多说了,大家还是直接看代码吧! import pymysql import pandas as pd def con_sql(db,sql): # 创建连接 db = pymysql.connect(host='127.0.0.1', port=3308, user='name', passwd='password', db=db, charset='utf8') # 创建游标 cursor = db.cursor() cursor.execute(sql) result = curs

  • 详解MySQL分组链接的使用技巧

    MYSQL中的分组和链接是在操作数据库和数据交互时最常用的两个在功能,把这两项处理好了,MYSQL的执行效率会非常高速. 一.group by ,分组 顾名思义,把数据按什么来分组,每一组都有什么特点. 1.我们先从最简单的开始: select count(*) from tb1 group by tb1.sex; 查询所有数据的条数,按性别来分组.这样查询到的结果集只有一列count(*). 2.然后我们来分析一下,这个分组,我们能在select 和 from 之间放一些什么呢? 当数据分组之

  • MySQL多表查询实例详解【链接查询、子查询等】

    本文实例讲述了MySQL多表查询.分享给大家供大家参考,具体如下: 准备工作:准备两张表,部门表(department).员工表(employee) create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'ma

  • 实例操作MySQL短链接

    MySQL短链接怎么设置 1.查看mysql连接数语句命令: 2.首先作为超级用户登录到MYSQL,注意必须是超级用户,否则后面会提示没有修改权限. 3.按回车键执行后显示目前的超时时间: 4.显示的是默认的超时时间,即8个小时(单位是秒).现在重新设置该参数,例如我们要将超时时间设置成10个小时. 5.按回车键执行显示的结果: 6.可以重新使用show global variables like 'wait_timeout'来验证. 这种方法比较直观,而且设置的参数立即生效. 以上步骤很简单,

  • MySql使用skip-name-resolve解决外网链接客户端过慢问题

    在腾讯云上面搭建的mysql使用开发的电脑上navicat进行访问时总是特别的慢,原来是Mysql会对请求的地址进行域名解析,开发的电脑并没有域名,所以会导致特别的慢,使用以下进行解决 [mysqld] skip-name-resolve skip-grant-tables 官方的解释 How MySQL uses DNS When a new thread connects to mysqld, mysqld will spawn a new thread to handle the requ

  • IDEA链接MySQL报错08001和连接成功后不显示表的问题及解决方法

    报错Connection to blog0@localhost failed. [08001] Could not create connection to database server. Attempted reconnect 3 times. Giving up. 解决办法:在url后面拼接?serverTimezone=UTC 连接成功后数据库不显示表 通过设置解决: scheme选择当前使用的数据库 到此这篇关于IDEA链接MySQL报错08001和连接成功后不显示表的问题及解决方法的

  • mysql安装navicat之后,出现2059,Authentication plugin及本地链接虚拟机docker,远程链接服务器

    序言 在安装完MySql和navicat之后,进行连接时报出了ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: ÕÒ²»µ½Ö¸¶¨µÄÄ£¿é¡£.我起初认为是我没设置密码的原因.便在命令行模式下设置了密码值得一提的是在登陆root之后 设置密码应该是 set password ='你的密码' 而不是 什么password =password 什么的,你看见让报错是syntax

  • MySql 8.0.11 安装过程及 Navicat 链接时遇到的问题小结

    我的系统和软件版本是这样的: 系统环境:win7.64位 MySQL版本:8.0.11 对版本的说明 之所以说是 MySQL5.7 及以上版本,是因为从 MySQL5.7 版本之后,其安全机制有所改变.在安装完成后,登陆 MySQL 时,需要输入一个密码,这个密码其实是在配置 MySQL 的过程中生成的一个随机密码,而我们必须找到这个随机密码才能登陆 MySQL.而在 5.7 之前的版本并不需要这样做,因此对于 5.7 之前的版本,本教程可能并不完全适用,不过网上有很多,如果需要可以自己搜索.

  • 利用ssh tunnel链接mysql服务器的方法

    前言 在某些情况下,我们只能知道数据库的内网地址,无法通过内网连接,但是我们可以登录同一个内网的其他机器,并且这些机器能够在内网访问数据库,那么我们可以通过ssh tunnel的方法来访问数据库. 用法 ssh tunnel的使用方法十分简单,具体格式如下: ssh -L [local port]:[remote host]:[remote port] [username]@[remote host] 使用-L的方式,将对本地端口的访问,通过ssh tunnel,forward到远程主机的端口上

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

    前言: 在数据库运维过程中,我们时常会关注数据库的链接情况,比如总共有多少链接.有多少活跃链接.有没有执行时间过长的链接等.数据库的各种异常也能通过链接情况间接反应出来,特别是数据库出现死锁或严重卡顿的时候,我们首先应该查看数据库是否有异常链接,并杀掉这些异常链接.本篇文章将主要介绍如何查看数据库链接及如何杀掉异常链接的方法. 1.查看数据库链接 查看数据库链接最常用的语句就是 show processlist 了,这条语句可以查看数据库中存在的线程状态.普通用户只可以查看当前用户发起的链接,具

  • MySQL查看和修改字符编码的实现方法

    MySQL的默认编码是Latin1,不支持中文,要支持中午需要把数据库的默认编码修改为gbk或者utf8. 1.需要以root用户身份登陆才可以查看数据库编码方式(以root用户身份登陆的命令为:>mysql -u root –p,之后两次输入root用户的密码),查看数据库的编码方式命令为: >show variables like 'character%'; +--------------------------+----------------------------+ | Variab

  • MySQL查看、创建和删除索引的方法

    本文实例讲述了MySQL查看.创建和删除索引的方法.分享给大家供大家参考.具体如下: 1.索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率.特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍. 例如,有3个未索引的表t1.t2.t3,分别只包含列c1.c2.c3,每个表分别含有1000行数据组成,指为1-1000的数值,查找对应值相等行的查询如下所示. SELECT c1,c2,c3 FROM t1,t2,t3

  • MySQL查看与修改当前数据库编码的方法

    MySQL中,数据库的编码是一个相当重要的问题,有时候我们需要查看一下当前数据库的编码,甚至需要修改一下数据库编码. 查看当前数据库编码的SQL语句为: mysql> use xxx Database changed mysql> show variables like 'character_set_database'; +------------------------+--------+ | Variable_name          | Value  | +--------------

  • MySQL查看目前运行状况的两种方法

    第一种方法:利用mysql命令查看 MySQL 内建直接看 status 就可以看到系统常见讯息, 如下述范例: 复制代码 代码如下: 1.$ mysql -u root -p 2.mysql> s "Threads: 2 Questions: 224857636 Slow queries: 229 Opens: 1740 Flush tables: 1 Open tables: 735 Queries per second avg: 137.566 $ mysql -u root -p

  • Mysql查看死锁与解除死锁的深入讲解

    前言 前段时间遇到了一个Mysql 死锁相关的问题,整理一下. 问题描述:Mysql 的修改语句似乎都没有生效,同时使用Mysql GUI 工具编辑字段的值时会弹出异常. 什么是死锁 在解决Mysql 死锁的问题之前,还是先来了解一下什么是死锁. 死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程. 死锁的表现 死锁的具体表现有两种: Mysql 增改语句无

  • Mysql查看最大连接数和修改最大连接数的讲解

    MySQL查看最大连接数和修改最大连接数 1.查看最大连接数 show variables like '%max_connections%'; 2.修改最大连接数 set GLOBAL max_connections = 200; 以下的文章主要是向大家介绍的是MySQL最大连接数的修改,我们大家都知道MySQL最大连接数的默认值是100, 这个数值对于并发连接很多的数据库的应用是远不够用的,当连接请求大于默认连接数后,就会出现无法连接数据库的错误,因此我们需要把它适当调大一些.在使 用MySQ

  • JS实现点击链接切换显示隐藏内容的方法

    本文实例讲述了JS实现点击链接切换显示隐藏内容的方法.分享给大家供大家参考,具体如下: 先来看看运行效果: 具体代码如下: <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>www.jb51.net 点击链接切换显示隐藏内容</title> <style> a { cursor: pointer

随机推荐