功能：該指令碼可以根據使用者和型別殺死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#########################

自己寫的殺mysql執行緒的指令碼

相關文章