python 监控mysql性能

import psutil
import mysql.connector
from datetime import datetime

# 连接MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 查询MySQL性能指标
cursor.execute("SHOW GLOBAL STATUS LIKE 'Threads_connected';")
threads_connected = cursor.fetchone()[1]

cursor.execute("SHOW GLOBAL STATUS LIKE 'Uptime';")
uptime = cursor.fetchone()[1]

# 获取系统资源使用情况
cpu_percent = psutil.cpu_percent(interval=1)
memory_info = psutil.virtual_memory()

# 打印监控数据
print(f"[{datetime.now()}] Threads Connected: {threads_connected}, Uptime: {uptime} seconds")
print(f"CPU Usage: {cpu_percent}%, Memory Usage: {memory_info.percent}%")

# 关闭数据库连接
cursor.close()
db.close()

# 定义报警阈值
cpu_threshold = 80
memory_threshold = 80
threads_connected_threshold = 100

# 获取实时性能数据（同上）

# 判断是否触发报警
if cpu_percent > cpu_threshold:
    print("CPU usage is too high!")

if memory_info.percent > memory_threshold:
    print("Memory usage is too high!")

if int(threads_connected) > threads_connected_threshold:
    print("Too many connections to MySQL!")

# 启用慢查询日志（需要在MySQL配置文件中设置）
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow-query.log

# 定期读取慢查询日志并分析
with open('/var/log/mysql/slow-query.log', 'r') as f:
    slow_queries = f.readlines()

for query in slow_queries:
    # 分析慢查询语句并采取相应措施（如优化查询、添加索引等）
    print(query)