python脚本处理SQL慢查询

python脚本处理SQL慢查询

注意：处理SQL慢查询、大于20秒以上的SQL、就执行kill掉、 （ 生成环境按照自己环境参数修改、慎重使用、一定要多测试 ）

import pymysql
import schedule
import time
from datetime import datetime

# 数据库连接配置
config = {
    'user': 'root',
    'password': '123456',
    'host': '172.12.16.155',
    'port': 3306,
    'database': 'database-a',
    'autocommit': True
}

conn = None
cursor = None
log_file = f"prod_output_{datetime.now().strftime('%Y%m%d_%H')}.log"

def getConn():
    global conn, cursor
    try:
        if conn is not None and conn.open:
            conn.ping()
        else:
            conn = pymysql.connect(**config)
            cursor = conn.cursor(pymysql.cursors.DictCursor)
    except Exception as e:
        conn = pymysql.connect(**config)
        cursor = conn.cursor(pymysql.cursors.DictCursor)

def execute_query():
    global conn, cursor
    getConn()

    # 打印当前时间
    with open(log_file, "a") as log:
        #log_entry = f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - Killed query: {row}\n"
        #log_entry = f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - Killed query ID: {row['ID']}, User: {row['USER']}, DB: {row['DB']}, State: {row['STATE']}, Info: {row['INFO']}\n"
        # log.write(log_entry)    
        log_entry1 = f"Executing query at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n"
        log.write(log_entry1)
    #print(f"Executing query at: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")


    query = "SELECT * FROM information_schema.processlist WHERE user = 'root' AND command = 'Query' AND time > 21 AND info LIKE 'SELECT%'"
    cursor.execute(query)

    results = cursor.fetchall()
    if not results:
        print("No queries to kill.")
        return

    for row in results:
       # print(row)
        cursor.execute("KILL {}".format(row["ID"]))
    # 写入日志文件
    with open(log_file, "a") as log:
        log_entry = f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - Killed query: {row}\n"
        #log_entry = f"{datetime.now().strftime('%Y-%m-%d %H:%M:%S')} - Killed query ID: {row['ID']}, User: {row['USER']}, DB: {row['DB']}, State: {row['STATE']}, Info: {row['INFO']}\n"
        log.write(log_entry)

# 设置每隔 20 秒执行一次查询
schedule.every(8).seconds.do(execute_query)

while True:
    schedule.run_pending()
    time.sleep(8)