mysql processlist
发表于:2024-10-24 作者:千家信息网编辑
千家信息网最后更新 2024年10月24日,今天在写报表时,用Navicat客户端运行一个存储过程,由于语句的原因导致程序一直运行中,整个Navicat客户端都卡住了,无法关闭Navicat客户端。于是就想到了kill掉这个线程,登录服务器my
千家信息网最后更新 2024年10月24日mysql processlist今天在写报表时,用Navicat客户端运行一个存储过程,由于语句的原因导致程序一直运行中,整个Navicat客户端都卡住了,无法关闭Navicat客户端。
于是就想到了kill掉这个线程,登录服务器mysql,想用show processlist方法找出程序ID,结果发现有很多用户在登录并且在执行语句,而且show processlist没办法加条件检索
mysql> show processlist;
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| 2158 | root | 172.158.8.136:50154 | NULL | Sleep | 20755 | | NULL | 219 | 219 |
| 2159 | root | 172.158.8.136:50157 | ADM | Sleep | 4087 | | NULL | 0 | 0 |
| 2187 | prouser | 172.158.8.226:49647 | NULL | Sleep | 21335 | | NULL | 403 | 403 |
| 2189 | admin | 172.158.8.226:49692 | skw_reportdata | Sleep | 4269 | | NULL | 0 | 0 |
| 2203 | admin | 172.158.8.226:49716 | skw_reportdata | Sleep | 20874 | | NULL | 1000 | 1000 |
| 2207 | admin | 172.158.8.226:49725 | skw_reportdata | Sleep | 20844 | | NULL | 0 | 0 |
| 2212 | root | 172.158.8.136:50556 | CDM | Sleep | 930 | | NULL | 0 | 1 |
| 2217 | prouser | 172.30.249.28:47190 |account | Sleep | 11360 | | NULL | 0 | 0 |
| 2218 | root | 172.158.8.136:50601 | DW | Sleep | 20095 | | NULL | 0 | 0 |
| 2220 | admin | 172.158.8.61:49553 | NULL | Sleep | 20247 | | NULL | 19 | 19 |
| 2221 | admin | 172.158.8.61:49554 | ADM | Sleep | 20246 | | NULL | 7 | 7 |
| 2233 | prouser | 172.158.8.125:63769 | account | Sleep | 19659 | | NULL | 21 | 21 |
| 2234 | prouser | 172.158.8.125:63771 | account | Sleep | 19512 | | NULL
.......此处省略大部分结果
结果很不理想,当然我们可以用其他办法,show processlist的结果是来自information_schema.processlist表中
mysql> desc information_schema.processlist;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| ID | bigint(21) unsigned | NO | | 0 | |
| USER | varchar(16) | NO | | | |
| HOST | varchar(64) | NO | | | |
| DB | varchar(64) | YES | | NULL | |
| COMMAND | varchar(16) | NO | | | |
| TIME | int(7) | NO | | 0 | |
| STATE | varchar(64) | YES | | NULL | |
| INFO | longtext | YES | | NULL | |
| TIME_MS | bigint(21) | NO | | 0 | |
| ROWS_SENT | bigint(21) unsigned | NO | | 0 | |
| ROWS_EXAMINED | bigint(21) unsigned | NO | | 0 | |
| TID | bigint(21) unsigned | YES | | NULL | |
+---------------+---------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
我们可以通过这张表进行检索出我们需要的结果
mysql> SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'\G;
*************************** 1. row ***************************
id: 2695
user: root
host: localhost
time: 0
command: Query
info: SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'
*************************** 2. row ***************************
id: 2645
user: root
host: 172.158.8.136:53258
time: 1522
command: Query
info: insert into `CDM`.cdm_product(product_id,product_type,product_name,add_rate,base_rate,year_rate,startdate,enddate,is_current)
SELECT a.id,CASE
WHEN IFNULL(i.enlending_type, '9') = '0' THEN '1'
WHEN IFNULL(i.enlending_type, '9') = '1' THEN '2'
WHEN IFNULL(i.enlending_type, '9') = '4' THEN '3'
WHEN IFNULL(i.enlending_type, '9') = '5' THEN '4'
WHEN IFNULL(i.enlending_type, '9') = '6' THEN '5'
WHEN IFNULL(i.enlending_type, '9') = '7' THEN '6'
WHEN IFNULL(i.enlending_type, '9') = '9' THEN '7' ELSE IFNULL(i.enlending_type, '9')END,
a.loan_info_title,0,a.loan_info_interest,a.loan_info_interest,'20140808','99990101',1
FROM DW.dw_biz_invest_statements a
left JOIN DW.dw_biz_loan_info i
ON a.loan_info_id=i.id
GROUP BY loan_info_title
2 rows in set (0.01 sec)
第二条就是我们要的结果kill掉就OK了
mysql>kill 2645;
Query OK, 0 rows affected (0.00 sec)
这个时候Navicat客户端就恢复正常了。!
于是就想到了kill掉这个线程,登录服务器mysql,想用show processlist方法找出程序ID,结果发现有很多用户在登录并且在执行语句,而且show processlist没办法加条件检索
mysql> show processlist;
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| 2158 | root | 172.158.8.136:50154 | NULL | Sleep | 20755 | | NULL | 219 | 219 |
| 2159 | root | 172.158.8.136:50157 | ADM | Sleep | 4087 | | NULL | 0 | 0 |
| 2187 | prouser | 172.158.8.226:49647 | NULL | Sleep | 21335 | | NULL | 403 | 403 |
| 2189 | admin | 172.158.8.226:49692 | skw_reportdata | Sleep | 4269 | | NULL | 0 | 0 |
| 2203 | admin | 172.158.8.226:49716 | skw_reportdata | Sleep | 20874 | | NULL | 1000 | 1000 |
| 2207 | admin | 172.158.8.226:49725 | skw_reportdata | Sleep | 20844 | | NULL | 0 | 0 |
| 2212 | root | 172.158.8.136:50556 | CDM | Sleep | 930 | | NULL | 0 | 1 |
| 2217 | prouser | 172.30.249.28:47190 |account | Sleep | 11360 | | NULL | 0 | 0 |
| 2218 | root | 172.158.8.136:50601 | DW | Sleep | 20095 | | NULL | 0 | 0 |
| 2220 | admin | 172.158.8.61:49553 | NULL | Sleep | 20247 | | NULL | 19 | 19 |
| 2221 | admin | 172.158.8.61:49554 | ADM | Sleep | 20246 | | NULL | 7 | 7 |
| 2233 | prouser | 172.158.8.125:63769 | account | Sleep | 19659 | | NULL | 21 | 21 |
| 2234 | prouser | 172.158.8.125:63771 | account | Sleep | 19512 | | NULL
.......此处省略大部分结果
结果很不理想,当然我们可以用其他办法,show processlist的结果是来自information_schema.processlist表中
mysql> desc information_schema.processlist;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| ID | bigint(21) unsigned | NO | | 0 | |
| USER | varchar(16) | NO | | | |
| HOST | varchar(64) | NO | | | |
| DB | varchar(64) | YES | | NULL | |
| COMMAND | varchar(16) | NO | | | |
| TIME | int(7) | NO | | 0 | |
| STATE | varchar(64) | YES | | NULL | |
| INFO | longtext | YES | | NULL | |
| TIME_MS | bigint(21) | NO | | 0 | |
| ROWS_SENT | bigint(21) unsigned | NO | | 0 | |
| ROWS_EXAMINED | bigint(21) unsigned | NO | | 0 | |
| TID | bigint(21) unsigned | YES | | NULL | |
+---------------+---------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
我们可以通过这张表进行检索出我们需要的结果
mysql> SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'\G;
*************************** 1. row ***************************
id: 2695
user: root
host: localhost
time: 0
command: Query
info: SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'
*************************** 2. row ***************************
id: 2645
user: root
host: 172.158.8.136:53258
time: 1522
command: Query
info: insert into `CDM`.cdm_product(product_id,product_type,product_name,add_rate,base_rate,year_rate,startdate,enddate,is_current)
SELECT a.id,CASE
WHEN IFNULL(i.enlending_type, '9') = '0' THEN '1'
WHEN IFNULL(i.enlending_type, '9') = '1' THEN '2'
WHEN IFNULL(i.enlending_type, '9') = '4' THEN '3'
WHEN IFNULL(i.enlending_type, '9') = '5' THEN '4'
WHEN IFNULL(i.enlending_type, '9') = '6' THEN '5'
WHEN IFNULL(i.enlending_type, '9') = '7' THEN '6'
WHEN IFNULL(i.enlending_type, '9') = '9' THEN '7' ELSE IFNULL(i.enlending_type, '9')END,
a.loan_info_title,0,a.loan_info_interest,a.loan_info_interest,'20140808','99990101',1
FROM DW.dw_biz_invest_statements a
left JOIN DW.dw_biz_loan_info i
ON a.loan_info_id=i.id
GROUP BY loan_info_title
2 rows in set (0.01 sec)
第二条就是我们要的结果kill掉就OK了
mysql>kill 2645;
Query OK, 0 rows affected (0.00 sec)
这个时候Navicat客户端就恢复正常了。!
结果
客户
客户端
办法
程序
语句
检索
登录
运行
原因
可以通过
大部分
就是
报表
方法
时候
服务器
条件
理想
用户
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
企业服务器要多少费用
广元服务器显卡厂家
服务器资源分配策略
佳话网络技术有限公司
斐讯网络安全密钥重置
中国网络安全谷选址在哪儿
服务器错误挂断电话
手机端商务需求软件开发
服务器和宝塔
文件服务器管理需求
网络安全法 隐私保护
谁可以查询金融信息基础数据库
web服务器安全设置 心得
主流数据库产品名称
软件开发的阶段的任务是什么
虚拟服务器集群技术
服务器cpu开启睿频
包你说小程序软件开发
贵州本地软件开发公司
湖北振达辉网络技术有限公司
linux 服务器 路由
电脑访问内部服务器速度不一样
笔记本远程解除不了服务器连接
微软mr 软件开发
湖北pdu服务器电源排名
江西网络软件开发活动方案
暗月网络安全教程2020
计算机网络技术基础款T恤穿搭
戴尔R210服务器 风扇转速
湖南省金泉网络技术有限公司