python3 监控mysql5.7相关的运行状态7

1.代码部分

最好这样

pip3 install --user pymysql 当前用户可以运行代码

[hx@localhost pycode]$ sudo pip3 install pymysql
[sudo] hx 的密码：
WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
Collecting pymysql
^Z
[2]+  已停止               sudo pip3 install pymysql
[hx@localhost pycode]$
[hx@localhost pycode]$ pip3 install --user pymysql
Collecting pymysql
  Using cached https://files.pythonhosted.org/packages/4f/52/a115fe175028b058df353c5a3d5290b71514a83f67078a6482cff24d6137/PyMySQL-1.0.2-py3-none-any.whl
Installing collected packages: pymysql
Successfully installed pymysql-1.0.2
[hx@localhost pycode]$

2实际运行python3相关的代码

#!/usr/bin/env python3
import pymysql
import time
import logging
from datetime import datetime

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    filename='mysql_monitor.log'
)

# MySQL连接配置
MYSQL_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',  # 建议创建一个只有监控权限的用户
    'password': 'Root@1234',
    'database': 'mysql'  # 连接到mysql系统库
}

# 监控阈值配置
THRESHOLDS = {
    'max_connections': 0.8,  # 连接数超过80%最大连接数报警
    'slow_queries': 10,      # 慢查询数超过10报警
    'threads_running': 20,   # 运行线程数超过20报警
    'uptime': 3600,          # 运行时间小于1小时报警(可能刚重启)
}

def safe_int(value, default=0):
    """安全转换为整数"""
    try:
        return int(value)
    except (ValueError, TypeError):
        return default

def get_mysql_connection():
    """建立MySQL连接"""
    try:
        conn = pymysql.connect(**MYSQL_CONFIG)
        return conn
    except pymysql.Error as e:
        logging.error(f"连接MySQL失败: {e}")
        return None

def check_mysql_status():
    """检查MySQL状态"""
    conn = get_mysql_connection()
    if not conn:
        return False
    
    try:
        with conn.cursor() as cursor:
            # 获取全局状态
            cursor.execute("SHOW GLOBAL STATUS")
            status = {k:safe_int(v) for k, v in cursor.fetchall()}
            
            # 获取全局变量
            cursor.execute("SHOW GLOBAL VARIABLES")
            variables = {k:safe_int(v) for k, v in cursor.fetchall()}
            
            # 获取进程列表
            cursor.execute("SHOW PROCESSLIST")
            process_count = len(cursor.fetchall())
            
            # 获取InnoDB状态
            cursor.execute("SHOW ENGINE INNODB STATUS")
            innodb_status = cursor.fetchone()[2]
            
            # 检查关键指标
            check_key_metrics(status, variables, process_count)
            
            # 记录完整状态(可选)
            log_full_status(status, variables, process_count, innodb_status)
            
            return True
            
    except pymysql.Error as e:
        logging.error(f"查询MySQL状态失败: {e}")
        return False
    finally:
        conn.close()

def check_key_metrics(status, variables, process_count):
    """检查关键指标是否超过阈值"""
    # 连接数检查
    max_conn = variables.get('max_connections', 0)
    threads_connected = status.get('Threads_connected', 0)
    
    if max_conn > 0:
        conn_ratio = threads_connected / max_conn
        if conn_ratio > THRESHOLDS['max_connections']:
            logging.warning(
                f"连接数接近上限: {threads_connected}/{max_conn} "
                f"({conn_ratio:.1%})"
            )
    
    # 慢查询检查
    slow_queries = status.get('Slow_queries', 0)
    if slow_queries > THRESHOLDS['slow_queries']:
        logging.warning(f"慢查询数较多: {slow_queries}")
    
    # 运行线程检查
    threads_running = status.get('Threads_running', 0)
    if threads_running > THRESHOLDS['threads_running']:
        logging.warning(f"运行线程数较多: {threads_running}")
    
    # 运行时间检查
    uptime = status.get('Uptime', 0)
    if uptime < THRESHOLDS['uptime']:
        logging.warning(f"MySQL最近重启过，运行时间: {uptime}秒")
    
    # 其他关键指标
    aborted_connects = status.get('Aborted_connects', 0)
    if aborted_connects > 10:
        logging.warning(f"异常连接数较多: {aborted_connects}")
    
    # 打印基本信息
    logging.info(
        f"状态检查: 连接数={threads_connected}, "
        f"运行线程={threads_running}, QPS={status.get('Queries', 0)}, "
        f"运行时间={uptime//3600}小时{uptime%3600//60}分钟"
    )

def log_full_status(status, variables, process_count, innodb_status):
    """记录完整状态信息(可选)"""
    with open('mysql_status.log', 'a') as f:
        f.write(f"\n\n=== MySQL状态报告 {datetime.now()} ===\n")
        f.write(f"总连接数: {process_count}\n")
        f.write("关键状态指标:\n")
        for k in ['Threads_connected', 'Threads_running', 'Queries', 
                 'Slow_queries', 'Uptime', 'Innodb_row_lock_time_avg']:
            if k in status:
                f.write(f"  {k}: {status[k]}\n")
        
        f.write("\nInnoDB状态摘要:\n")
        # 提取InnoDB状态的关键信息
        for line in innodb_status.split('\n'):
            if 'buffer pool hit rate' in line or 'lock wait' in line:
                f.write(f"  {line.strip()}\n")

def main():
    """主监控循环"""
    logging.info("MySQL监控服务启动")
    while True:
        start_time = time.time()
        
        if not check_mysql_status():
            # 连接失败，等待更长时间再重试
            time.sleep(60)
            continue
        
        # 计算本次检查耗时
        elapsed = time.time() - start_time
        sleep_time = max(5 - elapsed, 1)  # 至少间隔1秒
        
        time.sleep(sleep_time)

if __name__ == '__main__':
    main()

3.代码执行和实际显示的情况

[hx@localhost pycode]$ python3 1.py

查看

[hx@localhost pycode]$ ls
1.py  mysql_monitor.log  mysql_status.log
cat mysql_status.log  可以查询到相关的信息