ITPub博客

自己写的杀mysql线程的脚本

原创 自然语言 作者:czxin788 时间:2018-04-27 11:08:49 0 删除 编辑

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

[root@MySQL02 ~]# cat kill_sql.py 

点击(此处)折叠或打开

  1. #!/usr/bin/env python
  2. #-*-coding:utf8-*-
  3. #python版本2.7
  4. #安装mysql-connector-python,版本需要是1.1.17的,2.x的版本运行会有问题,下载地址:https://dev.mysql.com/downloads/connector/python/
  5. #在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 ;


  6. from __future__ import print_function
  7. import os,sys
  8. import subprocess
  9. import mysql.connector as mdb

  10. ###全局变量##########
  11. username = 'root'
  12. password = 'xxx'
  13. hostname = 'localhost'
  14. ####################

  15. config = {
  16.     'user':username,
  17.     'password':password,
  18.     'host':hostname,
  19.     'database':'information_schema'
  20. }


  21. def GetUserInfo():
  22.     show_processinfo = "SELECT \
  23.     USER, \
  24.     count(*) total_count, \
  25.     ( \
  26.         SELECT \
  27.             count(info) \
  28.         FROM \
  29.             mysql.v_process_czxin \
  30.         WHERE \
  31.             info LIKE 'select%' \
  32.         AND USER = p. USER \
  33.     ) select_count, \
  34. ( \
  35.         SELECT \
  36.             count(info) \
  37.         FROM \
  38.             mysql.v_process_czxin \
  39.         WHERE \
  40.             info LIKE 'update%' \
  41.         AND USER = p. USER \
  42.     ) update_count, \
  43. ( \
  44.         SELECT \
  45.             count(info) \
  46.         FROM \
  47.             mysql.v_process_czxin \
  48.         WHERE \
  49.             info LIKE 'delete%' \
  50.         AND USER = p. USER \
  51.     ) delete_count, \
  52. ( \
  53.         SELECT \
  54.             count(info) \
  55.         FROM \
  56.             mysql.v_process_czxin \
  57.         WHERE \
  58.             info LIKE 'insert%' \
  59.         AND USER = p. USER \
  60.     ) insert_count, \
  61. ( \
  62.         SELECT \
  63.             count(info) \
  64.         FROM \
  65.             mysql.v_process_czxin \
  66.         WHERE \
  67.             info LIKE 'alter%' \
  68.         AND USER = p. USER \
  69.     ) alter_count \
  70. FROM \
  71.     mysql.v_process_czxin p \
  72. GROUP BY \
  73.     USER ORDER BY total_count DESC \
  74. "
  75.     cursor.execute(show_processinfo)
  76.     print('############## 摘要 ###############')
  77.     for i in cursor:
  78.         user = i[0]
  79.         total_count = i[1]
  80.         select_count = i[2]
  81.         update_count = i[3]
  82.         delete_count = i[4]
  83.         insert_count = i[5]
  84.         alter_count = i[6]
  85.         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))

  86. def KillSQL():
  87.     while True:
  88.         print('\n')
  89.         temp = str(raw_input('[1] 请问你要杀死的用户名是: '))
  90.             input_user = temp.strip().lower()    
  91.         temp = str(raw_input('[2] 请问你要杀死的sql类型是:[select%/update%/delete%/insert%/alter%~~注意~~:必须加%]: '))
  92.         input_type = temp.strip().lower()
  93.         show_process = """select id,user,host,db,time,info from information_schema.processlist where user='%s' and info like '%s'""" % (input_user,input_type)
  94.         cursor.execute(show_process)
  95.         #如果日志文件out.txt存在,就删除
  96.                 if os.path.isfile('out.txt'):
  97.                         #subprocess.call(["rm","-rf","out.txt"])
  98.             os.remove('out.txt')
  99.          count =0
  100.         for j in cursor:
  101.             id = j[0]
  102.             user = j[1]
  103.             host = j[2]
  104.             db = j[3]
  105.             time = j [4]
  106.             info = j [5]
  107.             print('{0}{1}{2}{3}'.format('杀死',user,'的线程id为:',id))
  108.             #真正的杀死sql id
  109.             #import pdb;pdb.set_trace()
  110.             kill_sql = "kill %s" % id
  111.             subprocess.call(["mysql","-h%s" % hostname,'-u%s' % username,'-p%s' % password,'-e',kill_sql])
  112.             #写入日志文件
  113.             try:
  114.                 count += 1
  115.                 f = open('out.txt','a') #a表示追加写入
  116.                 f.write('####################'+'The '+ str(count) + ' rows' + '#########################\n')
  117.                 f.write(
  118.                     'id: '+ str(id) + '\n'
  119.                     + 'user: '+user + '\n'
  120.                     + 'host: ' + host + '\n'
  121.                     + 'db: ' + db + '\n'
  122.                     + 'exec_time: ' + str(time) + '\n'
  123.                     + 'killed_sql: ' + info + '\n')
  124.                 f.write('\n')
  125.                 f.write('\n')
  126.             except OSError as reason:
  127.                 print('出错了:'+str(reason))
  128.             finally:
  129.                 f.close
  130.         break
  131.     print('\n')
  132.     print('\n')
  133.     print('详细信息请见当前目录下的out.txt文件,里面有被杀死sql语句的详细信息!!!')

  134. #主程序
  135. conn = mdb.connect(**config)
  136. cursor = conn.cursor()
  137. GetUserInfo()
  138. KillSQL()
  139. cursor.close()
  140. conn.close()
执行结果:

点击(此处)折叠或打开

  1. [root@MySQL02 ~]# python kill_sql.py
  2. ############## 摘要 ###############
  3. 用户ygjt_new在线的总线程数是3, 其中select:2 update:1 delete:0 insert:0 alter:0
  4. 用户auod_oms在线的总线程数是2, 其中select:2 update:0 delete:0 insert:0 alter:0
  5. 用户auod在线的总线程数是1, 其中select:1 update:0 delete:0 insert:0 alter:0
  6. 用户root在线的总线程数是1, 其中select:1 update:0 delete:0 insert:0 alter:0


  7. [1] 请问你要杀死的用户名是: ygjt_new
  8. [2] 请问你要杀死的sql类型是:[select%/update%/delete%/insert%/alter%~~注意~~:必须加%]: select%
  9. 杀死ygjt_new的线程id为:14938801
  10. Warning: Using a password on the command line interface can be insecure.
  11. 杀死ygjt_new的线程id为:14938803
  12. Warning: Using a password on the command line interface can be insecure.
  13. 杀死ygjt_new的线程id为:14931430
  14. Warning: Using a password on the command line interface can be insecure.


  15. 详细信息请见当前目录下的out.

报告内容:

点击(此处)折叠或打开

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


  13. ####################The 2 rows#########################
  14. id: 14938803
  15. user: ygjt_new
  16. host: 172.19.2.48:59172
  17. db: cz_bj_oms
  18. exec_time: 23
  19. killed_sql: SELECT
  20.         STAT,ORDER_ID
  21.     FROM DECLAREBILL
  22.     WHERE ORDER_ID IN
  23.      ( '34933646-765f-44b0-8567-a88f44d6d3d9' )


  24. ####################The 3 rows#########################




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2153422/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-06-03

  • 博文量
    163
  • 访问量
    487978