功能：该脚本可以根据用户和类型杀死mysql线程。

[root@MySQL02 ~]# cat kill_sql.py

点击(此处)折叠或打开

#!/usr/bin/env python
#-*-coding:utf8-*-
#python版本2.7
#安装mysql-connector-python，版本需要是1.1.17的，2.x的版本运行会有问题，下载地址：https://dev.mysql.com/downloads/connector/python/
#在mysql数据库里面创建视图,create view v_process_czxin as select id,user,host,db,info from information_schema.PROCESSLIST where info is not null order by time desc ;
from __future__ import print_function
import os,sys
import subprocess
import mysql.connector as mdb
###全局变量##########
username = 'root'
password = 'xxx'
hostname = 'localhost'
####################
config = {
'user':username,
'password':password,
'host':hostname,
'database':'information_schema'
}
def GetUserInfo():
show_processinfo = "SELECT \
USER, \
count(*) total_count, \
( \
SELECT \
count(info) \
FROM \
mysql.v_process_czxin \
WHERE \
info LIKE 'select%' \
AND USER = p. USER \
) select_count, \
( \
SELECT \
count(info) \
FROM \
mysql.v_process_czxin \
WHERE \
info LIKE 'update%' \
AND USER = p. USER \
) update_count, \
( \
SELECT \
count(info) \
FROM \
mysql.v_process_czxin \
WHERE \
info LIKE 'delete%' \
AND USER = p. USER \
) delete_count, \
( \
SELECT \
count(info) \
FROM \
mysql.v_process_czxin \
WHERE \
info LIKE 'insert%' \
AND USER = p. USER \
) insert_count, \
( \
SELECT \
count(info) \
FROM \
mysql.v_process_czxin \
WHERE \
info LIKE 'alter%' \
AND USER = p. USER \
) alter_count \
FROM \
mysql.v_process_czxin p \
GROUP BY \
USER ORDER BY total_count DESC \
"
cursor.execute(show_processinfo)
print('############## 摘要 ###############')
for i in cursor:
user = i[0]
total_count = i[1]
select_count = i[2]
update_count = i[3]
delete_count = i[4]
insert_count = i[5]
alter_count = i[6]
print('{0}{1}{2}{3}{4}{5}{6}{7}{8}{9}{10}{11}{12}{13}'.format('用户',user,'在线的总线程数是',total_count,', 其中select:',select_count,' update:',update_count,' delete:',delete_count,' insert:',insert_count,' alter:',alter_count))
def KillSQL():
while True:
print('\n')
temp = str(raw_input('[1] 请问你要杀死的用户名是: '))
input_user = temp.strip().lower()
temp = str(raw_input('[2] 请问你要杀死的sql类型是:[select%/update%/delete%/insert%/alter%~~注意~~:必须加%]: '))
input_type = temp.strip().lower()
show_process = """select id,user,host,db,time,info from information_schema.processlist where user='%s' and info like '%s'""" % (input_user,input_type)
cursor.execute(show_process)
#如果日志文件out.txt存在，就删除
if os.path.isfile('out.txt'):
#subprocess.call(["rm","-rf","out.txt"])
os.remove('out.txt')
count =0
for j in cursor:
id = j[0]
user = j[1]
host = j[2]
db = j[3]
time = j [4]
info = j [5]
print('{0}{1}{2}{3}'.format('杀死',user,'的线程id为：',id))
#真正的杀死sql id
#import pdb;pdb.set_trace()
kill_sql = "kill %s" % id
subprocess.call(["mysql","-h%s" % hostname,'-u%s' % username,'-p%s' % password,'-e',kill_sql])
#写入日志文件
try:
count += 1
f = open('out.txt','a') #a表示追加写入
f.write('####################'+'The '+ str(count) + ' rows' + '#########################\n')
f.write(
'id: '+ str(id) + '\n'
+ 'user: '+user + '\n'
+ 'host: ' + host + '\n'
+ 'db: ' + db + '\n'
+ 'exec_time: ' + str(time) + '\n'
+ 'killed_sql: ' + info + '\n')
f.write('\n')
f.write('\n')
except OSError as reason:
print('出错了:'+str(reason))
finally:
f.close
break
print('\n')
print('\n')
print('详细信息请见当前目录下的out.txt文件，里面有被杀死sql语句的详细信息!!!')
#主程序
conn = mdb.connect(**config)
cursor = conn.cursor()
GetUserInfo()
KillSQL()
cursor.close()
conn.close()

执行结果：

点击(此处)折叠或打开

[root@MySQL02 ~]# python kill_sql.py
############## 摘要 ###############
用户ygjt_new在线的总线程数是3, 其中select:2 update:1 delete:0 insert:0 alter:0
用户auod_oms在线的总线程数是2, 其中select:2 update:0 delete:0 insert:0 alter:0
用户auod在线的总线程数是1, 其中select:1 update:0 delete:0 insert:0 alter:0
用户root在线的总线程数是1, 其中select:1 update:0 delete:0 insert:0 alter:0
[1] 请问你要杀死的用户名是: ygjt_new
[2] 请问你要杀死的sql类型是:[select%/update%/delete%/insert%/alter%~~注意~~:必须加%]: select%
杀死ygjt_new的线程id为：14938801
Warning: Using a password on the command line interface can be insecure.
杀死ygjt_new的线程id为：14938803
Warning: Using a password on the command line interface can be insecure.
杀死ygjt_new的线程id为：14931430
Warning: Using a password on the command line interface can be insecure.
详细信息请见当前目录下的out.

报告内容：

点击(此处)折叠或打开

[root@MySQL02 ~]# cat out.txt
####################The 1 rows#########################
id: 14938801
user: ygjt_new
host: 172.19.2.48:59170
db: cz_bj_oms
exec_time: 5
killed_sql: SELECT
STAT,ORDER_ID
FROM DECLAREBILL
WHERE ORDER_ID IN
( '34933646-765f-44b0-8567-a88f44d6d3d9' )
####################The 2 rows#########################
id: 14938803
user: ygjt_new
host: 172.19.2.48:59172
db: cz_bj_oms
exec_time: 23
killed_sql: SELECT
STAT,ORDER_ID
FROM DECLAREBILL
WHERE ORDER_ID IN
( '34933646-765f-44b0-8567-a88f44d6d3d9' )
####################The 3 rows#########################

ITPub博客

自己写的杀mysql线程的脚本

最新文章