mysqlprocesslist
今天在写报表时，用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客户端就恢复正常了。！
网站题目：mysqlprocesslist
网页URL：http://ybzwz.com/article/ijcjjd.html
于是就想到了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客户端就恢复正常了。！
网站题目：mysqlprocesslist
网页URL：http://ybzwz.com/article/ijcjjd.html
